概要

運用中に日増しにBigQueryのコストが増大していっています。
それは、運用する間にデータがどんどん蓄積し、調べたいデータが増えて行くことによって必然的に起きてしまいます。

基本的にはパーティションを使い、クエリ時にスキャンするデータを最小限にすることでコストを小さくしていくのが王道です。

また、BigQueryには一度クエリした結果をキャッシュして、再度同じクエリが実行されたら、スキャンをせず、キャッシュから結果を返却する機能があるようなので、そちらを調査してみました。

• キャッシュに保存されているクエリ結果を使用する  |  BigQuery  |  Google Cloud の内容を参考にしました。
• BigQuery の ベストプラクティス 〜 2017年度版 〜 - Qiita

内容の把握

ユーザーAさんが、プロジェクトXでBigQueryでクエリを流す場合

• 自動的に結果がキャッシュテーブルに書き込まれる。
  • キャッシュテーブルはコンソール上からは見れないけれど、コマンドラインツールなどからは見ることができる。
  • キャッシュはユーザー単位とプロジェクト単位で保存される
  • オーナーは実行したユーザーに自動でつく
• 全く同じクエリを実行すると、キャッシュから結果を返す
  • 例外
    • CURRENT_TIMESTAMP(), NOW(), CURRENT_USER() など、実行タイミングによって異なる値を返す場合はキャッシュを利用しない
    • ワイルドカードを使用して複数のテーブルに対してクエリを実行する場合
    • キャッシュが切れてしまった場合（通常は24時間キャッシュされるが、まれにそれより早く切れてしまう場合がある模様）

プロジェクトでの実施事項

• BigQueryに保存されているクエリ（よく使われているもの）のクエリの設定 > キャッシュの設定で、「キャッシュされた結果を使用」にチェックがついているか確認しました。
  • デフォルトでチェックがついているので、チェックが漏れているものは幸いなかったです

• 流れているクエリで、（特にコストが高いものの中に）CURRENT_TIMESTAMP(), NOW(), CURRENT_USER() のような関数が使われていないか確認しました。

SELECT
  JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson, "$.jobChange.job.jobConfig.queryConfig.query")
FROM
  `project-name.dataset-name.cloudaudit_googleapis_com_data_access`
WHERE
  _PARTITIONDATE BETWEEN '2020-05-19' AND '2020-05-20'
  AND JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson, "$.jobChange.job.jobConfig.type") = "QUERY"
  AND 
    (
      STRPOS(LOWER(JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson, "$.jobChange.job.jobConfig.queryConfig.query")), 'current_timestamp()') > 0
      OR
      STRPOS(LOWER(JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson, "$.jobChange.job.jobConfig.queryConfig.query")), 'now()') > 0
      OR
      STRPOS(LOWER(JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson, "$.jobChange.job.jobConfig.queryConfig.query")), 'current_user()') > 0
    )
LIMIT
  10