BigQuery アンチパターンレコメンデーションツールで、パフォーマンスの悪い SQL を使っていないか確認してみる。
こんにちは、みかみです。
本州も梅雨入りの季節になってまいりましたが、沖縄はそろそろ梅雨明けです!
やりたいこと
- BigQuery で実行している SQL のパフォーマンスチューニングをしたい
- BigQuery のアンチパターン SQL を使っていないか確認したい
どんな DWH を使うにしろ、SQL チューニングは避けて通れない道ではないかと思います。
実行計画確認して、データ処理エンジンの思想に思いを馳せて・・・。 というのも、特に顕著な改善効果が得られた時にはこの上ない喜びを感じられる作業ですが、SQL が複雑だったり、大量の SQL を確認しないといけない場合は、心折れそうになる場合もあります。
BigQuery でも、クエリプランを確認しながら SQL をチューニングする必要はもちろんあると思いますが、その前段階として、アンチパターンの SQL はないか、一括で確認できたら便利では?!
ということで、BigQuery のアンチパターン SQL を確認できるツールがあるらしい、と、ずっと気になってたので、試しに使ってみました。
- BigQuery でアンチパターン認識ツールを使用してクエリ パフォーマンスの向上と費用の最適化を図る
- GoogleCloudPlatform/bigquery-antipattern-recognition | GitHub
前提
本エントリでは、Cloud Shell を使用して、コマンドラインから SQL の検証を実行しています。 が、ツールを Cloud Run Jobs にデプロイしたり、リモート関数(Cloud Functions)を作って SQL レイヤから実行したりもできるので、実行環境構築も合わせて検討すると、より利用の幅が広がるのではないかと思います。
- Cloud Shell の使用 | Cloud Shell ドキュメント
- GoogleCloudPlatform/bigquery-antipattern-recognition/README.md | GitHub
Cloud Shell から実行してみる
README にも記載のある通り、Cloud Shell を使えば、たった 5 Step 5分たらずで、本当に Quick に始めることができました。
Step 1:Cloud Shell を起動
Google Cloud 管理コンソール、右上のリンクから、Cloud Shell を起動します。
Step 2:GitHub リポジトリを Clone
Cloud Shell から以下のコマンドを実行し、bigquery-antipattern-recognition
リポジトリをcloneします。
git clone https://github.com/GoogleCloudPlatform/bigquery-antipattern-recognition.git
Step 3:ディレクトリ移動
以下のコマンドで、clone したリポジトリに移動します。
cd bigquery-antipattern-recognition
Step 4:Dockerイメージ作成
以下のコマンドで、ツールの Docker イメージを Build します。
mvn clean package jib:dockerBuild -DskipTests
待つこと約2分。 イメージの作成が完了しました。
(省略) Downloaded from central: https://repo.maven.apache.org/maven2/org/codehaus/groovy/groovy/3.0.3/groovy-3.0.3.jar (7.9 MB at 22 MB/s) [INFO] [INFO] --- jib:3.3.2:dockerBuild (default-cli) @ bigquery-antipattern-recognition-tool --- [INFO] Skipping containerization because packaging is 'pom'... [INFO] ------------------------------------------------------------------------ [INFO] Reactor Summary for bigquery-antipattern-recognition-tool 1.0.0: [INFO] [INFO] bigquery-antipattern-recognition ................... SUCCESS [01:37 min] [INFO] bigquery-antipattern-recognition-tool .............. SUCCESS [ 3.124 s] [INFO] ------------------------------------------------------------------------ [INFO] BUILD SUCCESS [INFO] ------------------------------------------------------------------------ [INFO] Total time: 01:51 min [INFO] Finished at: 2024-06-11T08:52:28Z [INFO] ------------------------------------------------------------------------
Step 5:ツール実行
以下の README 記載のサンプルコマンドで、検証対象の SQL をターミナルから直接指定して、アンチパターンチェックを実行します。
docker run \ -i bigquery-antipattern-recognition \ --query "SELECT * FROM \`project.dataset.table1\`"
実行結果は以下です。
$ docker run \ -i bigquery-antipattern-recognition \ --query "SELECT * FROM \`project.dataset.table1\`" 2024-06-11 08:53:34:684 [main] INFO com.google.zetasql.toolkit.antipattern.cmd.AntiPatternCommandParser - Running anti pattern tool for args:--query SELECT * FROM `project.dataset.table1` (省略) 2024-06-11 08:53:49:033 [main] INFO com.google.zetasql.toolkit.antipattern.output.LogOutputWriter - -------------------------------------------------- Recommendations for query: query provided by cli: * SimpleSelectStar: SELECT * on table: project.dataset.table1. Check that all columns are needed. -------------------------------------------------- 2024-06-11 08:53:49:042 [main] INFO com.google.zetasql.toolkit.antipattern.Main - * Queries read: 1 * Queries with anti patterns: 1
アンチパターンの SQL の場合には、Recommendations for query:
として非推奨の理由が表示されます。
なお、問題ないクエリを指定した場合には、以下のように Queries with anti patterns:
部分が 0
になります。
$ docker run \ -i bigquery-antipattern-recognition \ --query "SELECT col_1 FROM \`project.dataset.table1\`" 2024-06-11 10:39:54:740 [main] INFO com.google.zetasql.toolkit.antipattern.cmd.AntiPatternCommandParser - Running anti pattern tool for args:--query SELECT col_1 FROM `project.dataset.table1` (省略) 2024-06-11 10:40:06:136 [main] INFO com.google.zetasql.toolkit.antipattern.Main - * Queries read: 1 * Queries with anti patterns: 0
SQL ファイル/フォルダを指定して実行してみる
先ほどは検証対象の SQL をコマンド内でターミナルから直接指定しましたが、実際にシステムで流している SQL は 1行で終わるはずもなく、いちいちコマンドで指定していたら実用に耐えません。。
なのでもちろん、ファイルやフォルダを指定してアンチパターンを検出することもできます。
以下のコマンドで、SQLファイルを指定して実行しました。
export INPUT_FOLDER=$(pwd)/../ export INPUT_FILE_NAME=sample.sql docker run \ -v $INPUT_FOLDER:$INPUT_FOLDER \ -i bigquery-antipattern-recognition \ --input_file_path $INPUT_FOLDER/$INPUT_FILE_NAME
sample.sql
ファイルの中身は、先ほどターミナルから指定したのと同じ、以下のSQLです。
SELECT * FROM project.dataset.table1
INPUT_FOLDER
の最後のスラッシュが不要だった気がしますが、結果、ちゃんとアンチパターン検出してくれました。
$ export INPUT_FOLDER=$(pwd)/../ $ export INPUT_FILE_NAME=sample.sql $ docker run \ > -v $INPUT_FOLDER:$INPUT_FOLDER \ > -i bigquery-antipattern-recognition \ > --input_file_path $INPUT_FOLDER/$INPUT_FILE_NAME 2024-06-11 10:46:39:493 [main] INFO com.google.zetasql.toolkit.antipattern.cmd.AntiPatternCommandParser - Running anti pattern tool for args:--input_file_path /home/mikami_yuki/20240611/bigquery-antipattern-recognition/..//sample.sql (省略) 2024-06-11 10:46:43:554 [main] INFO com.google.zetasql.toolkit.antipattern.output.LogOutputWriter - -------------------------------------------------- Recommendations for query: /home/mikami_yuki/20240611/bigquery-antipattern-recognition/..//sample.sql * SimpleSelectStar: SELECT * on table: project. Check that all columns are needed. -------------------------------------------------- 2024-06-11 10:46:43:559 [main] INFO com.google.zetasql.toolkit.antipattern.Main - * Queries read: 1 * Queries with anti patterns: 1
さらに、検証対象の複数の SQL ファイルを格納したフォルダを指定して実行してみます。 また、検証結果も、ターミナル出力だけだと不便なので、ファイル出力してみます。
export INPUT_FOLDER=$(pwd)/sample export OUTPUT_FOLDER=$(pwd)/sample/output export OUTPUT_FILENAME=results.csv docker run \ -v $INPUT_FOLDER:$INPUT_FOLDER \ -v $OUTPUT_FOLDER:$OUTPUT_FOLDER \ -i bigquery-antipattern-recognition \ --input_folder_path $INPUT_FOLDER \ --output_file_path $OUTPUT_FOLDER/$OUTPUT_FILENAME
サンプルフォルダの下には、先ほどと同じアンチパターンとアンチパターンではない、以下の2つの SQL ファイルを格納しました。
SELECT * FROM project.dataset.table1
SELECT col_1 FROM project.dataset.table1
実行結果は以下です。
$ export INPUT_FOLDER=$(pwd)/sample export OUTPUT_FOLDER=$(pwd)/sample/output export OUTPUT_FILENAME=results.csv docker run \ -v $INPUT_FOLDER:$INPUT_FOLDER \ -v $OUTPUT_FOLDER:$OUTPUT_FOLDER \ -i bigquery-antipattern-recognition \ --input_folder_path $INPUT_FOLDER \ --output_file_path $OUTPUT_FOLDER/$OUTPUT_FILENAME 2024-06-11 11:07:07:726 [main] INFO com.google.zetasql.toolkit.antipattern.cmd.AntiPatternCommandParser - Running anti pattern tool for args:--input_folder_path /home/mikami_yuki/20240611/bigquery-antipattern-recognition/sample --output_file_path /home/mikami_yuki/20240611/bigquery-antipattern-recognition/sample/output/results.csv (省略) 2024-06-11 11:07:11:411 [main] INFO com.google.zetasql.toolkit.antipattern.Main - Parsing query: /home/mikami_yuki/20240611/bigquery-antipattern-recognition/sample/query_antipattern.sql (省略) 2024-06-11 11:07:11:797 [main] INFO com.google.zetasql.toolkit.antipattern.output.LocalCsvOutputWriter - Writing recommendation for query: /home/mikami_yuki/20240611/bigquery-antipattern-recognition/sample/query_antipattern.sql to local file: /home/mikami_yuki/20240611/bigquery-antipattern-recognition/sample/output/results.csv 2024-06-11 11:07:11:800 [main] INFO com.google.zetasql.toolkit.antipattern.Main - Parsing query: /home/mikami_yuki/20240611/bigquery-antipattern-recognition/sample/query_ok.sql (省略) 2024-06-11 11:07:11:868 [main] INFO com.google.zetasql.toolkit.antipattern.Main - * Queries read: 2 * Queries with anti patterns: 1
2つのクエリを read し、そのうち 1つのクエリでアンチパターンが検出されました。
出力された results.csv
ファイルの中身は以下です。
id,recommendation /home/mikami_yuki/20240611/bigquery-antipattern-recognition/sample/query_antipattern.sql,"SimpleSelectStar: SELECT * on table: project. Check that all columns are needed."
検出されるアンチパターンと検出結果のサンプルファイルは、以下 GitHubリポジトリからも確認できます。
GCS に格納した SQL ファイルのアンチパターンを検出してみる
local フォルダ配下の SQL が検証できるので、SQL ファイルを GCS 上のフォルダに格納しても検証実行可能です。
以下のコマンドで gs://test-mikami/20240611
配下の SQL ファイルを対象に、アンチパターンを検出してみます。
docker run \ -i bigquery-antipattern-recognition \ --input_folder_path gs://test-mikami/20240611
指定した GCS フォルダには先ほどの local フォルダと同じ、アンチパターンとアンチパターンではない SQL 2ファイルを格納しました。
$ docker run \ -i bigquery-antipattern-recognition \ --input_folder_path gs://test-mikami/20240611 2024-06-11 13:53:51:619 [main] INFO com.google.zetasql.toolkit.antipattern.cmd.AntiPatternCommandParser - Running anti pattern tool for args:--input_folder_path gs://test-mikami/20240611 (省略) 2024-06-11 13:53:55:410 [main] INFO com.google.zetasql.toolkit.antipattern.cmd.AntiPatternCommandParser - Reading input folder from GCS 2024-06-11 13:53:57:063 [main] INFO com.google.zetasql.toolkit.antipattern.Main - Parsing query: gs://test-mikami/20240611/query_antipattern.sql (省略) 2024-06-11 13:53:57:413 [main] INFO com.google.zetasql.toolkit.antipattern.output.LogOutputWriter - -------------------------------------------------- Recommendations for query: gs://test-mikami/20240611/query_antipattern.sql * SimpleSelectStar: SELECT * on table: project. Check that all columns are needed. -------------------------------------------------- 2024-06-11 13:53:58:001 [main] INFO com.google.zetasql.toolkit.antipattern.Main - Parsing query: gs://test-mikami/20240611/query_ok.sql (省略) 2024-06-11 13:53:58:084 [main] INFO com.google.zetasql.toolkit.antipattern.Main - * Queries read: 2 * Queries with anti patterns: 1
local フォルダを指定した場合同様、2ファイル中1ファイルで、アンチパターン検出できました。
実行結果を BigQuery テーブルに保存してみる
検証結果はファイルだけではなく、BigQuery に出力することもできます。
以下の SQL を実行して、BigQueey に結果格納用のテーブルを作成しておきます。
CREATE OR REPLACE TABLE temp.antipattern_output_table ( job_id STRING, user_email STRING, query STRING, recommendation ARRAY<STRUCT<name STRING, description STRING>>, slot_hours FLOAT64, optimized_sql STRING, process_timestamp TIMESTAMP );
Cloud Shell から以下のコマンドを実行し、local フォルダ配下の SQL のアンチパターン検証結果を、先ほど作成した BigQuery テーブルに出力します。
※プロジェクトIDは伏字に変更しています。
export INPUT_FOLDER=$(pwd)/temp/sample docker run \ -v $INPUT_FOLDER:$INPUT_FOLDER \ -i bigquery-antipattern-recognition \ --input_folder_path $INPUT_FOLDER \ --output_table <my-project>.temp.antipattern_output_table
コマンド実行完了後、BigQuery の antipattern_output_table
の中身を確認してみます。
検証対象のクエリや検証実行日時などの情報も合わせて出力されました。
BigQuery の INFORMATION_SCHEMA から実行済みクエリを取得して、アンチパターンがないか確認してみる
以下のコマンドで、INFORMATION_SCHEMA.JOBS ビューに格納されている、2024-06-11 11:00:00 UTC
から 2024-06-11 13:00:00 UTC
の間に実行済みの SQL を対象に、アンチパターンの SQL が実行されていなかったか、確認することもできます。
※プロジェクトIDは伏字に変更しています。
docker run \ -v ~/.config:/root/.config \ -i bigquery-antipattern-recognition \ --read_from_info_schema \ --processing_project_id <my-project> \ --output_table <my-project>.temp.antipattern_output_table \ --info_schema_region asia-northeast1 \ --read_from_info_schema_start_time "2024-06-11 11:00:00 UTC" \ --read_from_info_schema_end_time "2024-06-11 13:00:00 UTC" \ --info_schema_top_n_percentage_of_jobs 1.0
$ docker run \ -v ~/.config:/root/.config \ -i bigquery-antipattern-recognition \ --read_from_info_schema \ --processing_project_id <my-project> \ --output_table <my-project>.temp.antipattern_output_table \ --info_schema_region asia-northeast1 \ --read_from_info_schema_start_time "2024-06-11 11:00:00 UTC" \ --read_from_info_schema_end_time "2024-06-11 13:00:00 UTC" \ --info_schema_top_n_percentage_of_jobs 1.0 2024-06-11 15:57:33:057 [main] INFO com.google.zetasql.toolkit.antipattern.cmd.AntiPatternCommandParser - Running anti pattern tool for args:--read_from_info_schema --processing_project_id <my-project> --output_table <my-project>.temp.antipattern_output_table --info_schema_region asia-northeast1 --read_from_info_schema_start_time 2024-06-11 11:00:00 UTC --read_from_info_schema_end_time 2024-06-11 13:00:00 UTC --info_schema_top_n_percentage_of_jobs 1.0 (省略) 2024-06-11 15:57:37:734 [main] INFO com.google.zetasql.toolkit.antipattern.util.BigQueryHelper - Reading from INFORMATION_SCHEMA: SELECT project_id, CONCAT(project_id, ":ASIA-NORTHEAST1.", job_id) job_id, query, total_slot_ms / (1000 * 60 * 60 ) AS slot_hours, user_email, PERCENT_RANK() OVER(ORDER BY total_slot_ms desc) perc_rnk FROM `<my-project>.region-asia-northeast1`.INFORMATION_SCHEMA.JOBS WHERE creation_time BETWEEN '2024-06-11 11:00:00 UTC' AND '2024-06-11 13:00:00 UTC' AND total_slot_ms > 0 AND (statement_type != "SCRIPT" OR statement_type IS NULL) AND (reservation_id != 'default-pipeline' or reservation_id IS NULL) AND query not like '%INFORMATION_SCHEMA%' QUALIFY perc_rnk < 1.0 ORDER BY project_id, start_time desc 2024-06-11 15:57:40:252 [main] INFO com.google.zetasql.toolkit.antipattern.Main - Parsing query: <my-project>:ASIA-NORTHEAST1.bquxjob_e9ece90_1900733c270 (省略) 2024-06-11 15:57:41:352 [main] INFO com.google.zetasql.toolkit.antipattern.Main - * Queries read: 3 * Queries with anti patterns: 1
INFORMATION_SCHEMA から検証対象クエリを抽出する SQL も確認できるので便利です。 指定した時間帯では 3本の検証対象クエリがあり、そのうち1本がアンチパターンの SQL でした。
範囲指定など、コマンドで指定可能なオプションは以下の記載をご参照ください。
結果が格納された BigQuery テーブルデータも確認してみます。
user_email
情報も格納されるので、誰が実行した SQL かも一目瞭然です(mikami が変な SQL 実行してますね。。
日々、SQL 警察業務でご苦労されてるみなさまの、一助になれば幸いです!
まとめ(所感)
コマンドラインからのワンショットの SQL 検証はもちろんのこと、INFORMATION_SCHEMA を参照して検証結果を BigQuery テーブルに格納するバッチジョブを作成しておけば、日々 SQL を検証・監視することが可能です。
クラウド環境ならではの豊富なリソースを利用できる BigQuery では特に、ついつい気にせず大量のデータを格納&あまり気にせずアドホッククエリを実行してしまうこともあるかもしれませんが、BigQuery といえども、悲鳴をあげる(タイムアウトする)ケースが出てきます。 SQL を実行するユーザー側でアンチパターンクエリは投げないように心がける、またはツールを利用してアンチパターンの SQL を検出できれば、ユーザーにとっても、システムの運用チームにとっても、嬉しいことではないかと思いました。