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 ist für Nutzer gedacht, die Folgendes ausgeführt haben:

In diesem Leitfaden wird davon ausgegangen, dass Sie mit BigQuery vertraut sind. Weitere Informationen: erhalten Sie in der Kurzanleitung, in der Sie erfahren, wie Sie Daten.

Tabelle mit Änderungsprotokollen öffnen

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

    BigQuery aufrufen

  2. Maximieren Sie Ihr Projekt im Bereich Explorer.

  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 hauptsächlich 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

Abfrage 1 Spalte

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. Sie können einen Wert in seinen mit einer Conversion Funktionen.

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 eine Datenänderung Datensatz, der vom Änderungsstream aus der entsprechenden Zeile in Ihrem Bigtable-Tabelle. 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")
)

Pivoting 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 Parsing-Funktionen verwenden, nachdem Sie den Wert aus dem JSON-Objekt. 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