BigQuery で Bigtable の変更ログをクエリする

このページでは、BigQuery で Bigtable 変更ログを処理する際に役立つガイダンスとサンプルクエリについて説明します。

このページは、以下を完了しているユーザーを対象としています。

このガイドは、BigQuery についてある程度の知識があることを前提としています。詳細については、データの読み込みとクエリの方法を説明するクイックスタートをご覧ください。

変更ログテーブルを開く

  1. Google Cloud コンソールで [BigQuery] ページに移動します。

    BigQuery に移動

  2. [エクスプローラ] ペインでプロジェクト を展開します。

  3. データセットを開きます。

  4. サフィックス _changelog の付いたテーブルをクリックします。

表形式

出力スキーマ全体にはいくつかの列が含まれます。このガイドでは、行を列と値に接続し、値を解析して分析可能な形式に変換することに重点を置いて説明します。

基本的なクエリ

このセクションの例では、Bigtable テーブルを使用してクレジット カードによる売上を追跡します。このテーブルには、1 つの列ファミリー（cf）と次の列があります。

  • credit card number#transaction timestamp 形式の行キー
  • 販売者
  • 金額
  • カテゴリ
  • 取引日

1 つの列をクエリする

WHERE 句を使用して、結果を 1 つの列ファミリーと 1 つの列だけにフィルタリングします。

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 テーブルの対応行から返された 1 つのデータ変更レコードが含まれます。スキーマによっては、行キーとタイムスタンプの組み合わせを使用してデータをグループ化できます。

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 オブジェクトから値を導出したら、解析関数を使用します。これらの例では、以前にご紹介したクレジット カード販売データを使用していますが、複数の列にデータを書き込むのではなく、1 つの列として 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

次のステップ