BigQuery最適化ガイド(第3回)

第1回

第2回

これまでの2回では、前提知識、知っておくと役立つ基礎、そしてデータ分析のための環境構築まで、幅広いトピックを扱ってきました。

本シリーズで使用するコードはすべて、以下のGitHubリポジトリにまとめてあります: https://github.com/doitintl/bigquery-optimization-queries

2種類のモニタリング手法ではスキーマが大きく異なるため、SQLファイルは audit_log と information_schema の2つのフォルダに分けて配置しています。

各ディレクトリには用途別のSQLファイルをまとめており、各フォルダの README.md でスクリプトごとの目的を解説しています。

ルートディレクトリには generate_sql_files.py というPythonスクリプトを置いてあり、ご自身のプロジェクトとデータセット情報を反映したSQLファイルを生成できます。

このGitHubリポジトリは随時更新していくため、各クエリと説明を一覧にまとめた記事をこちらに用意しています。

新しいクエリを追加するたびに更新していきます。

次のセクション以降、本シリーズの残りでは多くのクエリを参照し、大量のデータを扱っていきます。BigQueryのUIでも多数のクエリや分析にそれなりに対応できますが、データはスプレッドシートにエクスポートすることを強くおすすめします。

BigQueryから直接エクスポートできる(サイズに若干の制約はあります)Google Sheetsが最適です。同じクエリを何度も実行するより、一度スプレッドシートにエクスポートしてしまったほうがコストも抑えられます。

またコストにも注意してください。本記事で紹介するクエリには非常に高コストなものもあるため、実行前に必ずコストを確認することを強くおすすめします。各クエリには interval_in_days という変数を用意しており、さかのぼる期間を制御してスキャンするデータ量を減らせるようにしています。お客様のなかには WHERE 句を直接書き換えて独自の範囲を指定している方もいらっしゃるなど、活用方法は工夫次第です。

本パートで紹介する手順はすべて、CMP内のBQ Lensでも実行できます。Explorer ペインを使えばGUIで同じことが可能で、特に監査ログシンクのクエリを使っている場合、その多くはCMPがデータ表示のために実行しているクエリとほぼ同一です。

BigQueryのコスト最適化の第一歩は、環境内で最もコストのかかっているクエリを見つけることです。多くの場合、その存在自体が把握されていません。GI Joeが土曜の朝のアニメで残した名言のとおり、「知ることは戦いの半分」です。

対象のクエリを特定したら、次はそれを最適化し、原因となっているプロセスやユーザーを突き止めて修正していきます。

ここからは、先述のGitHubリポジトリ(リンク)にあるSQLファイルを参照していきます。なお、audit_logとinformation_schemaのどちらのファイルでも同じ結果が得られます。

分析で最初に使うのは top_costly_queries.sql ファイルのクエリで、コストの観点では本シリーズのなかでも最も重要と言えるものです。このクエリは、対象期間中に同じクエリが繰り返し実行された分も含めた合計コストを算出します。これにより、複数回実行されることで見かけ以上にコストがかさんでいる、本当の意味で高コストなクエリを把握できます。

このクエリの目的は2つです。1つは環境内で最もコストのかかるクエリを特定すること、もう1つは高コストなクエリが必要以上に実行されていないかを見極めることです。多くの場合、cronジョブ、Airflow上のDAGに含まれるタスク、Cloud Functionなどがクエリを複数回(しばしば必要以上に)実行しています。さらに多いのは、作成者・保守担当者・クラウド料金の支払者が、そのクエリ1回あたり、または月あたりのコストをまったく把握していないケースです。そんなときに役立つのがこのクエリです。

このセクションで2つ目に使うのは top_billed_queries.sql ファイルのクエリです。このクエリは、環境内で最もコストのかかるクエリを高い順に並べて表示します。先ほどのクエリと違い、複数回実行されたかどうかは考慮せず、1回あたりのコストのみを表示する点に注意してください。

実行すると、最もコストのかかるクエリが結果セットの上位に一気に並びます。まずは上位のクエリに目を向け、処理したデータ量(total*Billed列)を確認しましょう。その数値を踏まえてクエリ本体を見直し、なぜこれほどのコストになっているのかを特定します。

原因の多くは前述の問題ですが、それ以外の要因が潜んでいることもあります。クエリそのものを見て、明らかな問題がないか確認してみてください。

結果に同一のクエリが何度も返ってきて偏りが出たり見づらくなったりする場合は、top_billed_queries_deduplicated.sql ファイルを使ってみてください。これは中身は同じクエリですが、完全一致するクエリを重複排除して1件にまとめます。実行時間とスキャンするデータ量は増えますが、重複を取り除いた結果が得られます。

なお、本記事執筆時点でBigQueryには、ハミング距離やレーベンシュタイン距離のようなネイティブの「文字列類似度」集約関数が用意されていません。そのため、日付など軽微な違いだけが異なる類似クエリを除外するのは比較的難しいのが実情です。必要であればBigQuery向けに「文字列類似度」アルゴリズムを実装したUDFがいくつか公開されていますが、こうした計算負荷の高いアルゴリズムをUDFで実装するとかなり遅くなるため、BigQueryの外側で解決策を組むことをおすすめします。

コスト分析で使う3つ目、最後のクエリは top_cost_users.sql ファイルです。このクエリは、環境内の高コストなクエリをユーザー単位でまとめ、そのなかで最もコストのかかるクエリ順に並べて表示します。

狙いは、どのユーザーやサービスアカウントが、どのクエリにどれだけコストを使っているかを可視化することです。このリストには、気づかないうちに非効率なクエリを実行しているプロセスが含まれていることがよくあります。極端なケースでは、別プロジェクトや別クラウド上のプロセスが含まれることもあるため、それらを除外するフィルターを追加するのも有効です。

以上が、想定以上にコストがかかっているクエリを発見するのに役立つ、汎用性の高い3つの主要クエリです。GitHubリポジトリには、Looker由来のクエリの抽出、特定クエリの実行回数、特定ラベル付きクエリのコストなど、より特定の用途に向けたクエリも用意しています。用途は限定的ですが、いずれも筆者やDoiTのチームメンバーが実際に使ってきたものなので、コミュニティに広く共有しています。

次に取り上げる大きなトピックは、必要以上にリソースを消費し、期待どおりのパフォーマンスを出していないクエリを見つけることです。多くの場合、ここで見つかるクエリは高コストなクエリと表裏一体で、共通するクエリも出てくるはずです。

このセクションでは「複雑度」という言葉が頻繁に登場します。複雑度を定義することは、まさに「複雑」なのですが、ここでは簡単のため「クエリ実行中に使用されたスロット数」と定義します。具体的には、処理に費やされたスロット時間の合計を、クエリ全体の実行時間で割った値です。JOBS_BY_PROJECT ビューでの計算例を以下に示します:

SELECT
 SAFE_DIVIDE(total_slot_ms, TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)) AS slotCount
FROM
 `<project-name>`.`<dataset-region>`.INFORMATION_SCHEMA.JOBS_BY_PROJECT

よく聞かれる質問にあらかじめ答えておくと、これはあくまで近似値です。BigQueryは残念ながら正確な値を返してくれず、この計算式はBigQuery UIが表示しているものと同じです。

どんなデータベースシステムでも、論理的に複雑なクエリは内部的な実行の複雑さと直接相関するのが一般的です。BigQueryも例外ではなく、さらにこの複雑さとクエリコストの間にも一般に直接的な相関があります。つまり、複雑さを下げればコストも下がるということです。

そのうえで、クエリの複雑度を判定するためにGitHubリポジトリから使う主なクエリが top_complex_queries.sql です。このクエリは、実行時間あたりに最も多くのスロットを使うクエリを返してくれるため、上位を取り出すことで、パフォーマンスに問題を抱えている可能性のあるクエリを特定できます。

複雑なクエリは複雑なまま正しく動かさざるを得ないことも多く、これは厳密な指標ではありませんが、問題を抱えていそうな候補を見つけるための有効な手段です。

クエリのパフォーマンス問題を特定するもう1つの方法が、最も実行時間の長いクエリを調べることです。これも先ほどと同様に厳密な指標ではありません。長時間かかるクエリは、単純にそれだけの時間が必要な場合も多いからです。

longest_running_queries.sql のクエリは、対象期間内のすべてのクエリを実行時間の長い順に並べて返します。これによって最も時間のかかるクエリが上位に並び、確認しやすくなります。このクエリのruntimeToBytesBilledRatio列は、クエリがミリ秒あたり何バイト処理したかを示す指標です。一般的にこの値が1を超えている場合は、処理データ量を減らせる最適化の余地がないか確認するとよいでしょう。

本シリーズの今後のパートでは、パフォーマンスチューニングをさらに深く取り上げ、BigQueryのパフォーマンスにまつわる見落とされがちな落とし穴と、その克服法を紹介していきます。

本パート最後のトピックは、GitHubリポジトリに収録された、ここまで紹介したものより汎用的な情報やメタデータを表示するクエリ群です。

ジョブにはクエリ、ロード、エクスポート、テーブルコピーなど複数の種類があります。なかでも圧倒的に多いのはクエリとロードのため、リポジトリにはこれらのジョブと有用なメタデータを返す load_job_information.sql および query_job_information.sql を用意しています。

さらに汎用的な版として general_job_information.sql もあり、すべての種類のジョブと一般的なメタデータを返します。

このセクションに入る前にお伝えしておきますが、Googleは2022年12月に、同時実行クエリに関するBigQueryの挙動を変更しました。以前はプロジェクトあたり同時実行クエリ数100件という固定の上限がありましたが、今後の変更やクエリキューの展開を見据え、利用可能なスロット数に応じてスケールする値に変わりました。

本記事執筆時点でこの仕様は完全には文書化されていませんが、サポートと既存ドキュメントから得た情報をもとに記載しています。

デフォルトではこの値はゼロに設定されており、BigQueryがプロジェクトに割り当て可能なリソースに応じて動的に同時実行数を決定します。フラットレート料金に切り替える場合は、予約に対して最大同時実行数(maximum concurrency)を設定し、それを同時実行数の上限としてリクエストできます。Googleはその値が必ず確保されることを保証してはいませんが、可能な限り達成するよう努めるとされています。公式ドキュメントはクエリキューのページのこちらで確認できます。

免責事項はここまでにして、同時実行クエリで起こり得る問題と、その特定方法を見ていきましょう。

クエリを並列で実行しすぎると、悪名高い503「Service Unavailable」、または新しい「Query was not admitted as the maximum concurrency has been reached」というメッセージが返ってくることがあります。これは同時実行レベルを超えてしまい、クエリがスケジュールされなくなった状態を意味します。当然ながら望ましくありません。

たとえ自社の組織がまだ一部のプロジェクトでこの上限に達していなくても、把握しておけば事前に回避できます。Googleが導入したばかりの変更を完全に分析・理解できた段階で、軽減策やそもそも発生させない設計について、本シリーズの今後のパートで取り上げます。

当面、分単位・秒単位の平均同時実行数を把握するには、concurrent_queries_by_minute.sql および concurrent_queries_by_seconds.sql のクエリを使用してください。これらは指定した期間内の各分(または秒)あたりの同時実行クエリの平均数を返します。

同じクエリがユーザーや何らかの自動化プロセスによって何度も実行されるケースは少なくありません。クエリには都度コストがかかるため、各クエリがどの程度の頻度で実行されているかを把握し、必要以上に走って余分なコストを生んでいないかを判断することが重要です。

query_counts.sql クエリは、対象期間内に各クエリが実行された回数を表示します。これにより、特定のクエリが必要以上に頻繁に実行されてコストがかさんでいないかを判断できます。指定期間中のクエリ合計コストも併せて表示されるため、実行頻度とコストが過剰になっていないかの判断に役立ちます。

これでBigQuery最適化に関する記事の最初の3パートが完了しました。次のパートでは、GitHubリポジトリの内容を一覧にした表を掲載し、新しいクエリが追加されるたびに各機能を文書化して更新していきます。

BigQueryは大きな変革期にあるため、本シリーズには今後さらにパートを追加し、新機能の展開に合わせて利用を最適に保つ方法を掘り下げていきます。