Consulte um registo de alterações do Bigtable no BigQuery

Esta página fornece orientações e exemplos de consultas para ajudar a processar um registo de alterações do Bigtable no BigQuery.

Esta página destina-se a utilizadores que concluíram o seguinte:

Este guia pressupõe algum conhecimento do BigQuery. Para saber mais, pode seguir o início rápido que mostra como carregar e consultar dados.

Abra a tabela do registo de alterações

  1. Na Google Cloud consola, aceda à página BigQuery.

    Aceda ao BigQuery

  2. No painel Explorador, expanda o seu projeto.

  3. Expanda o conjunto de dados.

  4. Clique na tabela com o sufixo: _changelog.

Formato de tabela

O esquema de saída completo contém várias colunas. Este guia centra-se na associação das linhas às respetivas colunas e valores, e na análise dos valores em formatos analisáveis.

Consultas básicas

Os exemplos nesta secção usam uma tabela do Bigtable para monitorizar as vendas de cartões de crédito. A tabela tem uma família de colunas (cf) e as seguintes colunas:

  • Chave da linha com o formato credit card number#transaction timestamp
  • Comerciante
  • Montante
  • Categoria
  • Data da transação

Consultar uma coluna

Filtre os resultados para apenas uma família de colunas e uma coluna através de uma cláusula WHERE.

SELECT row_key, column_family, column, value, timestamp,
FROM your_dataset.your_table
WHERE
  mod_type="SET_CELL"
  AND column_family="cf"
  AND column="merchant"
LIMIT 1000

Analise valores

Todos os valores são armazenados como strings ou strings de bytes. Pode converter um valor no tipo pretendido com funções de conversão.

SELECT row_key, column_family, column, value, CAST(value AS NUMERIC) AS amount
FROM your_dataset.your_table
WHERE
  mod_type="SET_CELL"
  AND column_family="cf"
  AND column="amount"
LIMIT 1000

Realize agregações

Pode realizar mais operações, como agregações em valores numéricos.

SELECT SUM(CAST(value AS NUMERIC)) as total_amount
FROM your_dataset.your_table
WHERE
  mod_type="SET_CELL"
  AND column_family="cf"
  AND column="amount"

Reorganize os dados

Para executar consultas que envolvam várias colunas do Bigtable, tem de dinamizar a tabela. Cada nova linha do BigQuery inclui um registo de alteração de dados devolvido pelo fluxo de alterações da respetiva linha na tabela do Bigtable. Consoante o seu esquema, pode usar uma combinação da chave de linha e da data/hora para agrupar os dados.

SELECT * FROM (
  SELECT row_key, timestamp, column, value
  FROM your_dataset.your_table
)
PIVOT (
  MAX(value)
  FOR column in ("merchant", "amount", "category", "transaction_date")
)

Criar tabelas dinâmicas com um conjunto de colunas dinâmico

Se tiver um conjunto dinâmico de colunas, pode fazer algum processamento adicional para obter todas as colunas e colocá-las na consulta de forma programática.

DECLARE cols STRING;
SET cols = (
  SELECT CONCAT('("', STRING_AGG(DISTINCT column, '", "'), '")'),
  FROM your_dataset.your_table
);

EXECUTE IMMEDIATE format("""
SELECT * FROM (
  SELECT row_key, timestamp, column, value
  FROM your_dataset.your_table
)
PIVOT (
  MAX(value)
  FOR column in %s
)""", cols);

Dados JSON

Se estiver a definir todos os valores com JSON, tem de analisá-los e extrair os valores com base nas chaves. Pode usar funções de análise assim que tiver derivado o valor do objeto JSON. Estes exemplos usam os dados de vendas de cartões de crédito introduzidos anteriormente, mas, em vez de escrever dados em várias colunas, os dados são escritos como uma única coluna como um objeto JSON.

SELECT
  row_key,
  JSON_VALUE(value, "$.category") as category,
  CAST(JSON_VALUE(value, "$.amount") AS NUMERIC) as amount
FROM your_dataset.your_table
LIMIT 1000

Consultas de agregação com JSON

Pode executar consultas de agregação com valores JSON.

SELECT
  JSON_VALUE(value, "$.category") as category,
  SUM(CAST(JSON_VALUE(value, "$.amount") AS NUMERIC)) as total_amount
FROM your_dataset.your_table
GROUP BY category

O que se segue?