在 BigQuery 中查詢 Bigtable 變更記錄

本頁提供指南和查詢範例，協助您在 BigQuery 中處理 Bigtable 變更記錄。

本頁面適用於已完成下列事項的使用者：

本指南假設您已具備 BigQuery 相關知識。如要瞭解詳情，請參閱快速入門導覽課程，瞭解如何載入及查詢資料。

開啟變更記錄資料表

  1. 前往 Google Cloud 控制台的「BigQuery」頁面。

    前往 BigQuery

  2. 在「Explorer」窗格中展開專案。

  3. 展開資料集。

  4. 按一下尾碼為 _changelog 的資料表。

資料表格式

整個輸出結構定義包含多個資料欄。本指南的重點在於將資料列連結至資料欄和值，並將值剖析為可分析的格式。

基本查詢

本節的範例會使用 Bigtable 資料表追蹤信用卡銷售量。資料表有一個資料欄系列 (cf) 和下列資料欄：

  • 格式為 credit card number#transaction timestamp 的資料列索引鍵
  • 商家
  • 金額
  • 類別
  • 交易日期

查詢單一資料欄

使用 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

剖析值

所有值都會儲存為字串或位元組字串。您可以使用轉換函式，將值轉換為預期型別。

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

執行匯總作業

您可以執行更多作業，例如匯總數值。

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"

透視資料

如要執行涉及多個 Bigtable 資料欄的查詢，您需要樞紐分析資料表。每個新的 BigQuery 資料列都包含一個資料變更記錄，這是變更串流從 Bigtable 資料表中對應資料列傳回的記錄。視結構定義而定，您可以使用資料列鍵和時間戳記的組合，將資料分組。

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

使用動態資料欄集透視

如果有一組動態資料欄，您可以進行一些額外處理，以取得所有資料欄，並以程式輔助方式將這些資料欄放入查詢中。

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 資料

如要使用 JSON 設定所有值，您需要剖析這些值，並根據鍵擷取值。從 JSON 物件衍生值後，您就可以使用剖析函式。這些範例使用稍早介紹的信用卡銷售資料，但資料不是寫入多個資料欄，而是以 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

使用 JSON 進行匯總查詢

您可以使用 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

後續步驟