Bigtable-Änderungsprotokoll in BigQuery abfragen

Auf dieser Seite finden Sie eine Anleitung und Beispielabfragen, die Ihnen bei der Verarbeitung eines Bigtable-Änderungsprotokolls in BigQuery helfen.

Diese Seite richtet sich an Nutzer, die Folgendes getan haben:

In diesem Leitfaden wird davon ausgegangen, dass Sie mit BigQuery vertraut sind. Weitere Informationen finden Sie in der Kurzanleitung zum Laden und Abfragen von Daten.

Tabelle „Änderungsprotokoll“ öffnen

  1. Öffnen Sie in der Google Cloud Console die Seite BigQuery.

    BigQuery aufrufen

  2. Maximieren Sie im Bereich Explorer Ihr Projekt.

  3. Erweitern Sie Ihr Dataset.

  4. Klicken Sie auf die Tabelle mit dem Suffix _changelog.

Tabellenformat

Das gesamte Ausgabeschema enthält mehrere Spalten. In diesem Leitfaden geht es darum, die Zeilen mit ihren Spalten und Werten zu verknüpfen und Werte in analysierbare Formate zu parsen.

Einfache Abfragen

In den Beispielen in diesem Abschnitt wird eine Bigtable-Tabelle zum Erfassen von Kreditkartenverkäufen verwendet. Die Tabelle enthält eine Spaltenfamilie (cf) und die folgenden Spalten:

  • Zeilenschlüssel im Format credit card number#transaction timestamp
  • Händler
  • Betrag
  • Kategorie
  • Transaktionsdatum

Eine Spalte abfragen

Mit einer WHERE-Klausel können Sie die Ergebnisse auf eine Spaltenfamilie und eine Spalte beschränken.

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

Werte parsen

Alle Werte werden als Strings oder Bytestrings gespeichert. Mit Conversion-Funktionen können Sie einen Wert in den gewünschten Typ umwandeln.

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

Aggregationen durchführen

Sie können weitere Vorgänge ausführen, z. B. Aggregationen auf numerische Werte.

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"

Daten pivotieren

Wenn Sie Abfragen mit mehreren Bigtable-Spalten ausführen möchten, müssen Sie die Tabelle pivotieren. Jede neue BigQuery-Zeile enthält einen Datenänderungseintrag, der vom Änderungsstream aus der entsprechenden Zeile in Ihrer Bigtable-Tabelle zurückgegeben wird. Je nach Schema können Sie die Daten mit einer Kombination aus Zeilenschlüssel und Zeitstempel gruppieren.

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

Pivot-Analyse mit einem dynamischen Spaltensatz

Wenn Sie eine dynamische Spaltengruppe haben, können Sie eine zusätzliche Verarbeitung durchführen, um alle Spalten abzurufen und sie programmatisch in die Abfrage einzufügen.

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

JSON-Daten

Wenn Sie alle Werte mit JSON festlegen, müssen Sie sie parsen und die Werte anhand der Schlüssel extrahieren. Sie können Parsefunktionen verwenden, sobald Sie den Wert aus dem JSON-Objekt abgeleitet haben. In diesen Beispielen werden die bereits vorgestellten Kreditkartenverkaufsdaten verwendet. Anstatt die Daten in mehrere Spalten zu schreiben, werden sie als JSON-Objekt in eine einzelne Spalte geschrieben.

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

Aggregationsabfragen mit JSON

Sie können Aggregationsanfragen mit JSON-Werten ausführen.

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

Nächste Schritte