Zenn
🦑

(BigQuery)GA4テーブルを必要期間だけ呼び出して節約したい

2023/09/18に公開
BigQuery
GA4
tech

先に結論

  • _TABLE_SUFFIXという擬似カラムを使って、期間を指定する事で全データを参照する事がなくなり、参照バイト数を削減する事ができる(指定しない場合は毎回全期間のデータを読み込んでしまう)

ga4データとは

  • Google アナリティクス4（GA4）のデータ
  • bigqueryにエクスポート可能で、その際にワイルドカードテーブルと呼ばれる形式で日付単位のテーブルが追加されていく
    (例 events_20230109,events_20230110,events_20230111な感じ)

今回の問題

  • あるお客様のga4データがそこそこのボリュームがあり、そのデータを呼び出す度に250GBくらいの処理が発生していた。
  • WHERE句で1日だけを指定しても、全ボリュームである250GB分が読み込まれていた(そもそも、BigQueryの仕様上、WHERE句を書いたとしても、絞る前にフルデータスキャンされているので、WHERE文はコスト削減に繋がらない)

先に結論

  • ワイルドカードテーブルで一部期間だけ取得する場合は TABLE_SUFFIXという擬似列を駆使する必要がある。仮にgsod*'('gsod_230101','gsod_230102','gsod_23103'のテーブル)の場合、 _TABLE_SUFFIXには 230101,230102,230103という値が格納される
  • この値を指定することにより、全テーブルをスキャンするのではなく、選択したテーブルのみスキャンできるようになリマス

例

SELECT *
FROM gsod_*
where _TABLE_SUFFIX  between "20230101" and  "20230103"
  • 上記のようにすることで、_TABLE_SUFFIXが"20230101","20230102","20230103"のみのデータを取得するようになる

ワイルドカードテーブルって何？?

  • 以下がGoogleのドキュメント内の説明です
ワイルドカード テーブルは、ワイルドカード式に一致するすべてのテーブルが結合されたものを表します。たとえば次の FROM 句は、ワイルドカード式 gsod* を使用しており、noaa_gsod データセット内の gsod という文字列で始まるすべてのテーブルに一致します。

https://cloud.google.com/bigquery/docs/querying-wildcard-tables?hl=ja

  • つまり、'gsod_230101','gsod_230102','gsod_23103'などのワイルドカードテーブルが存在している場合、'FROM gsod_*'のようにワイルドカードを使うことで、gsodという接頭を持つすべてのテーブルにアクセスすることができる
  • うまく接頭語をコントロールすることで、必要なデータのみにアクセスできる

おまけ

_TABLE_SUFFIXはVIEWからVIEWへ伝播できる

複雑なクエリを複数のVIEWに分割して管理することはよくあることだと思います。
参照元テーブルがワイルドカードテーブルの場合、_TABLE_SUFFIXを各VIEWに持たせることで
_TABLE_SUFFIXを伝播することができます。

前提

  • 構成は以下の場合で考える
    • テーブルA（ワイルドカードテーブル　スキーマ(id:integer))
    • VIEW B（テーブルAを参照にするVIEW B)
    • VIEW C（VIEW Bを参照にするVIEW C)
  • やりたい事として、VIEWCを呼び出すときに_TABLE_SUFFIXを指定して、元々の参照テーブルであるワイルドカードテーブルのスキャンするデータを絞ってみる

やり方

  • _TABLE_SUFFIXをカラム化し、参照するVIEW毎にそのカラムを含ませる
  • VIEWCを呼び出すときにTABLE_SUFFIXを指定する。結果的に VIEWB → テーブルAと_TABLE_SUFFIXを伝播することができる

例

## VIEW B
SELECT 
   id,
   -- 下記のように_TABLE_SUFFIXのカラムを作る
   _TABLE_SUFFIX as table_suffix 
FROM 
   テーブルA

## VIEW C   
SELECT 
   id,
FROM 
   VIEW B
WHERE
   table_suffix = "20220101"

注意

  • 途中のVIEW(今回でいうVIEW B)のクエリ内に、サブクエリ/分析関数があるとうまくVIEWの伝播は機能しなかった

Discussion