BigQuery アンチパターンレコメンデーションツールで、パフォーマンスの悪い SQL を使っていないか確認してみる。

みかみ

2024.06.12

こんにちは、みかみです。

本州も梅雨入りの季節になってまいりましたが、沖縄はそろそろ梅雨明けです！

やりたいこと

BigQuery で実行している SQL のパフォーマンスチューニングをしたい
BigQuery のアンチパターン SQL を使っていないか確認したい

どんな DWH を使うにしろ、SQL チューニングは避けて通れない道ではないかと思います。

実行計画確認して、データ処理エンジンの思想に思いを馳せて・・・。というのも、特に顕著な改善効果が得られた時にはこの上ない喜びを感じられる作業ですが、SQL が複雑だったり、大量の SQL を確認しないといけない場合は、心折れそうになる場合もあります。

BigQuery でも、クエリプランを確認しながら SQL をチューニングする必要はもちろんあると思いますが、その前段階として、アンチパターンの SQL はないか、一括で確認できたら便利では？！

ということで、BigQuery のアンチパターン SQL を確認できるツールがあるらしい、と、ずっと気になってたので、試しに使ってみました。

前提

本エントリでは、Cloud Shell を使用して、コマンドラインから SQL の検証を実行しています。が、ツールを Cloud Run Jobs にデプロイしたり、リモート関数（Cloud Functions）を作って SQL レイヤから実行したりもできるので、実行環境構築も合わせて検討すると、より利用の幅が広がるのではないかと思います。

Cloud Shell から実行してみる

README にも記載のある通り、Cloud Shell を使えば、たった 5 Step 5分たらずで、本当に Quick に始めることができました。

Step 1：Cloud Shell を起動

Google Cloud 管理コンソール、右上のリンクから、Cloud Shell を起動します。

cloud_shell_open

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リポジトリからも確認できます。

GoogleCloudPlatform/bigquery-antipattern-recognition/samples/queries/output /results.csv ｜ 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 の中身を確認してみます。

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 でした。

範囲指定など、コマンドで指定可能なオプションは以下の記載をご参照ください。

GoogleCloudPlatform/bigquery-antipattern-recognition/README.md ｜ GitHub

結果が格納された BigQuery テーブルデータも確認してみます。

infomation_schema_results

user_email 情報も格納されるので、誰が実行した SQL かも一目瞭然です（mikami が変な SQL 実行してますね。。

日々、SQL 警察業務でご苦労されてるみなさまの、一助になれば幸いです！

まとめ（所感）

コマンドラインからのワンショットの SQL 検証はもちろんのこと、INFORMATION_SCHEMA を参照して検証結果を BigQuery テーブルに格納するバッチジョブを作成しておけば、日々 SQL を検証・監視することが可能です。

クラウド環境ならではの豊富なリソースを利用できる BigQuery では特に、ついつい気にせず大量のデータを格納＆あまり気にせずアドホッククエリを実行してしまうこともあるかもしれませんが、BigQuery といえども、悲鳴をあげる（タイムアウトする）ケースが出てきます。 SQL を実行するユーザー側でアンチパターンクエリは投げないように心がける、またはツールを利用してアンチパターンの SQL を検出できれば、ユーザーにとっても、システムの運用チームにとっても、嬉しいことではないかと思いました。

BigQuery アンチパターンレコメンデーションツールで、パフォーマンスの悪い SQL を使っていないか確認してみる。

やりたいこと

前提

Cloud Shell から実行してみる

Step 1：Cloud Shell を起動

Step 2：GitHub リポジトリを Clone

Step 3：ディレクトリ移動

Step 4：Dockerイメージ作成

Step 5：ツール実行

SQL ファイル/フォルダを指定して実行してみる

GCS に格納した SQL ファイルのアンチパターンを検出してみる

実行結果を BigQuery テーブルに保存してみる

BigQuery の INFORMATION_SCHEMA から実行済みクエリを取得して、アンチパターンがないか確認してみる

まとめ（所感）

参考

イベント

EVENT【7/31（水）大阪】DevelopersIO 2024 OSAKA #devio2024

EVENT【8/2（金）リモート】フリーランスの持続可能性を高めるには ‐ フリーランストーク#12

EVENT【7/18（木）リモート】猫好きエンジニア向け会社説明会を開催します

EVENT【7/23（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【6/26リモート】クラスメソッドのフリーランスエンジニア会社説明会〜クラスメソッド案件特集〜開催します

EVENT【7/11（木）リモート】クラスメソッドの会社説明会を開催します

EVENT【7/5（金）札幌】DevelopersIO 2024 SAPPORO Business Lab #devio2024

EVENT【6/24（月）日比谷+オンライン】クラスメソッドとLINEヤフーが語る開発ノウハウ

EVENT【7/2（火）リモート】2025年新卒＆第二新卒向け営業職会社説明会を開催します！

EVENT【7/2（火）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

BigQuery アンチパターンレコメンデーションツールで、パフォーマンスの悪い SQL を使っていないか確認してみる。

やりたいこと

前提

Cloud Shell から実行してみる

Step 1：Cloud Shell を起動

Step 2：GitHub リポジトリを Clone

Step 3：ディレクトリ移動

Step 4：Dockerイメージ作成

Step 5：ツール実行

SQL ファイル/フォルダを指定して実行してみる

GCS に格納した SQL ファイルのアンチパターンを検出してみる

実行結果を BigQuery テーブルに保存してみる

BigQuery の INFORMATION_SCHEMA から実行済みクエリを取得して、アンチパターンがないか確認してみる

まとめ（所感）

参考

イベント

EVENT【7/31（水）大阪】DevelopersIO 2024 OSAKA #devio2024

EVENT【8/2（金）リモート】フリーランスの持続可能性を高めるには ‐ フリーランストーク#12

EVENT【7/18（木）リモート】猫好きエンジニア向け会社説明会を開催します

EVENT【7/23（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【6/26リモート】 クラスメソッドのフリーランスエンジニア会社説明会〜クラスメソッド案件特集 〜開催します

EVENT【7/11（木）リモート】クラスメソッドの会社説明会を開催します

EVENT【7/5（金）札幌】DevelopersIO 2024 SAPPORO Business Lab #devio2024

EVENT【6/24（月）日比谷+オンライン】 クラスメソッドとLINEヤフーが語る開発ノウハウ

EVENT【7/2（火）リモート】2025年新卒＆第二新卒向け営業職会社説明会を開催します！

EVENT【7/2（火）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

関連記事

Dataform CLI からテストコードを実行して、BigQuery UDF のユニットテストを実施してみる。

Cloud Tasksで複数のCloud Functionsを非同期に呼び出してみた

Cloud Run ジョブのジョブ実行キャンセルに関する権限が変更になります

Dataformワークフローをシェルスクリプトでポーリングしながら動かす

EVENT【6/26リモート】クラスメソッドのフリーランスエンジニア会社説明会〜クラスメソッド案件特集〜開催します

EVENT【6/24（月）日比谷+オンライン】クラスメソッドとLINEヤフーが語る開発ノウハウ