Consultar um registro de alterações do Bigtable no BigQuery

Esta página fornece orientação e consultas de exemplo para ajudar você a processar uma Registro de alterações do Bigtable no BigQuery.

Esta página é destinada a usuários que concluíram as seguintes etapas:

Este guia considera algum conhecimento sobre o BigQuery. Para saber mais, siga o guia de início rápido que mostra como carregar e consultar dados.

Abrir a tabela do registro de alterações

  1. No Console do Google Cloud, acesse a página BigQuery.

    Ir para o BigQuery

  2. No painel Explorer, abra projeto.

  3. Expanda seu conjunto de dados.

  4. Clique na tabela com o sufixo: _changelog.

Formato da tabela

Todo o esquema de saída contém várias colunas. Neste guia, focamos a conexão das linhas às respectivas colunas e valores, além da análise dos valores em formatos possíveis.

Consultas básicas

Os exemplos nesta seção usam uma tabela do Bigtable para rastrear vendas de cartão de crédito. A tabela tem um grupo de colunas (cf) e as seguintes colunas:

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

Consultar uma coluna

Filtre os resultados para apenas um grupo de colunas e uma coluna usando 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

Analisar valores

Todos os valores são armazenados como strings ou strings de bytes. É possível transmitir um valor para o 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

Realizar agregações

É possível 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"

Dinamizar os dados

Para realizar consultas que envolvem várias colunas do Bigtable, é preciso dinamizar a tabela. Cada nova linha do BigQuery contém um registro de alteração de dados retornado pelo fluxo de alterações da linha correspondente na tabela do Bigtable. Dependendo do esquema, é possível usar uma combinação da chave de linha e do carimbo de 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")
)

Como dinamizar com um conjunto de colunas dinâmicas

Quando você tem um conjunto de colunas dinâmicas, é possível realizar alguns processamentos adicionais para colocar todas as colunas na consulta de maneira 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 você estiver definindo todos os valores com JSON, precisará analisá-los e extrair os valores com base nas chaves. É possível usar funções de análise depois de derivar o valor com base no objeto JSON. Esses exemplos usam os dados de vendas de cartão de crédito apresentados anteriormente. No entanto, em vez de gravar dados em várias colunas, os dados são gravados 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

É possível 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

A seguir