BigQuery移行で学んだクエリコスト削減Tips

はじめに

こちらのブログの小話です。

とあるオンプレDWHからBigQuery用にクエリを移行しました。
つまり、クエリの実行コストが高いとお金のコストもかかる領域にお引越しました。

その時BigQuery用にコスト削減を施したTipsを少しご紹介します。

パーティション分割項目

大概これで削減できます。
1日24回動くクエリが複数あり、その中にこんなクエリが有りました。

SELECT
  user_id
  ,item_id
  ,accessed_at
FROM
  access_log
WHERE
  accessed_at >= TIMESTAMP_SUB(current_timestamp, INTERVAL 2 hour)

このクエリの実行コストが大体2ドル/1回実行位でした。
24回実行するので約20ドル/日。1ヶ月30日とすると、600ドル/月。
じわじわお財布が泣いてます。でもビジネスロジック上このクエリを変えられない。

このaccess_logにはパーティションがなかったので
accessed_atをパーティション項目にすることで上記のようなクエリは
0.002ドル/1回実行 まで下げることができ、月の金額は見えない程度になりました。

これは一例ですが、ほとんどのケースはこれでかなりお安くできました。

おまけ：どれをパーティション分割項目にする？

作成日時、更新日時、配信日時、アクセス日時、購入日時
全てtimestampのこんな項目が1テーブルに並んでいたら
あなたはどれをパーティション分割項目にしますか？？

極力並ばないようにテーブルを分けたりデータの蓄積方法を変えるのが
綺麗かもしれませんが、そうもいかないケースは多々あります。

何も考えたくなければ、とりあえず作成日時にすることが個人的お勧めです。
作成日時でしぼった後で他のtimestamp項目で絞っても
出力データを変えずに済む可能性が高いからです。

ただBigQueryに既存クエリを移行する場合は
"実はクエリは全て配信日時で絞って使っているため、配信日時が適切"
てこともあるので、移行の時のパーティション項目は事前に見極める必要があります。

コストのかかるクエリの実行回数を減らす

これはBigQuery問わず時間というコストを下げるために実施することが多いかと思います。
移行前クエリも元々こうなってて課金コストかからずに済んだことがあります。

要は処理ごとに何度もコストの高いテーブルを参照するのではなく

1回だけ参照して、結果だけ何度も使いましょうってやつです。
図で言うhigh_cost_tableの期間項目をパーティション分割項目にすると
よりコストが下げられる可能性があります。

この方法は、速度もお金も含めて安パイな方法です。

おまけ: 安パイは適切？

今まではクエリが遅いと言う理由だけで1日1回にしてた。
BigQueryに移行したら、1日1回が都度実行に変更許容できる程度の実行速度になる。

なんてこともあります。

上の図で言うと、batchごとに何度もデータをリアルタイムに取得することで
BigQueryの課金は上がりますが、実は費用対効果がある可能性もあります。
パーティション分割等で課金を下げられる可能性もあります。

もちろん逆も然りで
リアルタイムでなくても費用対効果は変わらない可能性も十分あります。

ので、用途や効果を見極めた上で検討しましょう。

パーティションが効かないケースにご用心

パーティション項目でwhere句作ればいいんでしょ？って思ったそこのあなたへ。

パーティション項目の比較対象が動的な値の場合にご用心。
この場合コストはパーティションなし状態と同等にかかります。以下例

# date: パーティション分割項目
SELECT
  id
FROM
  tableB
WHERE
  date = (select max(created_at) from tableC)

詳しくはこちらの公式ドキュメントをご覧ください。

最後に

移行後は定期的にデータポータル(現Looker Studio)で
クエリごとのコストランキングを載せたダッシュボードを作り
何のクエリにコストが掛かってるかをチーム内で確認した上で
上記のTipsを施しました。

Tipsは方法(How)の話ですが
まずは1つ1つのクエリのかかるコストについて
これは何でなぜこのクエリを使ってるのか(What, Why)を把握することが
結果的にBigQueryのクエリが早い！安い！うまい！状態につながります。

BigQueryコンソールの右上に出るコストが
元々1GiBだったのに、自分の修正で500GiB, 1TiBに変わってないかを
チラ見して把握しておくだけでもコスト感覚が掴め
チューニングのきっかけになるかもしれません。

"共通の参照テーブルの仕様が変わったやべえ課金があああ！"

なんて心配が、常にコスト意識してるかしてないかで
微増になるか急激な上昇になるかの境目になったりするかもしれません。

そんなTipsでした。