Esegui una query su un log delle modifiche di Bigtable in BigQuery

Questa pagina fornisce indicazioni ed esempi di query per aiutarti a elaborare un log delle modifiche di Bigtable in BigQuery.

Questa pagina è rivolta agli utenti che hanno completato quanto segue:

Questa guida presuppone una conoscenza di BigQuery. Per scoprire di più, puoi seguire la guida rapida che mostra come caricare e eseguire query sui dati.

Apri la tabella del log delle modifiche

  1. Nella console Google Cloud, vai alla pagina BigQuery.

    Vai a BigQuery

  2. Nel riquadro Explorer, espandi il progetto.

  3. Espandi il set di dati.

  4. Fai clic sulla tabella con il suffisso: _changelog.

Formato tabella

L'intero schema di output contiene diverse colonne. Questa guida si concentra sul collegamento delle righe alle rispettive colonne e ai relativi valori e sull'analisi dei valori in formati analizzabili.

Query di base

Gli esempi in questa sezione utilizzano una tabella Bigtable per monitorare le vendite con carta di credito. La tabella ha una famiglia di colonne (cf) e le seguenti colonne:

  • Chiave di riga con il formato credit card number#transaction timestamp
  • Commerciante
  • Importo
  • Categoria
  • Data transazione

Eseguire una query su una colonna

Filtra i risultati in base a una sola famiglia di colonne e una sola colonna utilizzando una clausola 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

Analizza i valori

Tutti i valori vengono archiviati come stringhe o stringhe di byte. Puoi eseguire il casting di un valore al tipo previsto con le funzioni di conversione.

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

Eseguire aggregazioni

Puoi eseguire altre operazioni, ad esempio aggregazioni su valori numerici.

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"

Esegui il pivot dei dati

Per eseguire query che coinvolgono più colonne Bigtable, devi eseguire il pivot della tabella. Ogni nuova riga BigQuery include un record di variazione dei dati restituito dallo stream di variazioni dalla riga corrispondente nella tabella Bigtable. A seconda dello schema, puoi utilizzare una combinazione della chiave di riga e del timestamp per raggruppare i dati.

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

Eseguire un pivot con un insieme di colonne dinamico

Se hai un insieme dinamico di colonne, puoi eseguire un'elaborazione aggiuntiva per recuperare tutte le colonne e inserirle nella query in modo programmatico.

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);

Dati JSON

Se imposti tutti i valori con JSON, devi analizzarli ed estrarre i valori in base alle chiavi. Puoi utilizzare le funzioni di analisi dopo aver ricavato il valore dall'oggetto JSON. Questi esempi utilizzano i dati sulle vendite con carta di credito introdotti in precedenza, ma invece di scrivere i dati in più colonne, i dati vengono scritti come un'unica colonna come oggetto 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

Query di aggregazione con JSON

Puoi eseguire query di aggregazione con valori 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

Passaggi successivi