TL;DR
- データ活用が進んでくると、使われていないにも関わらず定期実行されているクエリが増えてきます
- 無駄な利用料金、スロットを使っていることになるので、こういったクエリはなるべく減らしていきたいです
- こうしたクエリをさっと調べられるコマンドラインツールを作りました
- 監査ログから一気に出すと大量のログが出てきて見にくいため、見やすく加工しています
- クエリの文字列が微妙に違うクエリは、編集距離を使ってまとめ上げて出力します
背景
- データ活用が進んでくると、色んなところからクエリが定期実行されるようになります
- 例: スケジュールクエリ、CRON、社内の独自のクエリ実行基盤(GASなどで実装されることが多い)
- がんがんクエリが実行されてテーブルができるのはいいのですが、ダッシュボードが見られていないなどの理由から使われていないテーブルが段々増えることが多いです
- こういったクエリを放置しておくと、様々な面で無駄が発生してしまいます
- 例: BigQueryのオンデマンドだと、不要なお金がかかってしまう
- 例: BigQueryのフラットレートでも、不要なスロットを消費してしまい、社内の分析効率が段々落ちていく
- 落ちないように余計にスロットを買うことになったり...
- 例: 利用もされていないわけなので、ストレージの料金も無駄にかかってしまう
- 例: メンテナンスしないといけないクエリをメンテすることになり、工数面でも無駄が発生します
- いいことがほとんどないので、なるべくこういったクエリは減らしていきたい!
実装時の問題点
INFORMATION_SCHEMA.JOBS_BY_*
を使えばまあできるだろう...と思ったけど、そのままの結果だとめっちゃ見にくい- 数万行のログを見ても、どれから減らせばいいのか判断が付かない
- クエリの実行ツールなどでパラメータなどが付与されていて、大体同じなんだけど、びみょーーーに違う、というクエリがいるので、
GROUP BY
などでは対処しにくい- 似たようなクエリはまとめた上で
total_bytes_processed
の総量などの統計量が知りたい!
- 似たようなクエリはまとめた上で
作った
- そんなわけで、↑に対処できるツールを作りました
- ベースは
INFORMATION_SCHEMA.JOBS_BY_*
で、似たようなクエリは編集距離を使ってまとめ上げています- 編集距離でXX文字以内の差分だったら同一クエリと見なす、といった感じ
- 編集距離はUDFで頑張る手もあったけど、golangでがっと書いてしまった
goなので、インストールもお手軽です。
% go install github.com/syou6162/bigquery_needless_query_detector@latest
使い方はこんな感じ。
% ./bigquery_needlessness_query_detector \ --project my-project --region asia-northeast1 \ --min_distance_threshold 10 \ --creation_time 2021-02-07 --type PROJECT > out.json
各オプションの意味はこういう感じ。
--project
: テーブルを生成しているプロジェクト名--type PROJECT
を指定した場合、「生成しているテーブルを参照するクエリが存在するか」も--project
で指定したプロジェクトで判定されます
--region
: テーブル生成 / 参照をしているBigQueryのリージョン- このブログを読んでる人だと、
asia-northeast1
かus
を指定することが多そう
- このブログを読んでる人だと、
--min_distance_threshold
: 同一クエリと見なすのに使われる編集距離の閾値- この値以下だと同一のクエリとして束ねられます
- 0を指定すると、exactにクエリが一致している場合のみ同一のクエリと判定されるようになります
--creation_time
: テーブル生成 / 参照をしているクエリの開始点- これより後のクエリが対象となります
--type
: 参照しているクエリのスコープ。PROJECT
かORGANIZATION
のどちらかを指定しますPROJECT
の場合、テーブル生成のプロジェクトと同じプロジェクト内から発行されているクエリのみが対象となりますORGANIZATION
の場合、組織内で発行される全てのクエリが対象となります
実行するとこんな感じのJSONを吐きます。
[ { "count": 134, "total_bytes_processed": 1731945010, "query": "create or replace view `ml-news.mart.bigquery_usage_by_account` ...", "user_email": "ml-news-dataform@ml-news.iam.gserviceaccount.com", "destination_table": "ml-news:mart.bigquery_usage_by_account", "jobs": [ { "creation_time": "2021-08-23T00:01:13.691Z", "project_id": "ml-news", "project_number": 1061606843027, "user_email": "ml-news-dataform@ml-news.iam.gserviceaccount.com", "job_id": "dataform-70119c12-ff0f-4e56-b2d0-45769983a73b", "job_type": "QUERY", "statement_type": "CREATE_TABLE_AS_SELECT", "priority": "INTERACTIVE", "start_time": "2021-08-23T00:01:13.792Z", "end_time": "2021-08-23T00:01:15.896Z", "query": "create or replace table `ml-news.mart.google_bot_example_page` as \n\nWITH\n example_google_bot_visits_count ...", "destination_table": "ml-news:mart.google_bot_example_page", "state": "DONE", "reservation_id": null, "total_bytes_processed": 173194501, "total_slot_ms": 5345 }, ... ] }, ... ]
同一クエリとして判定されたjobs毎に結果がmapとして入っていて、まとめ上げられたクエリが何個あるか(count
)、まとめ上げられたクエリの総バイト処理数はいくつか(total_bytes_processed
)、クエリによって生成されたテーブルの場所(destination_table
)、実行者(user_email
)などが分かるようにしてます。まとめられた個別のjobのことも調べたくなるケースがあると思うので、それはjobs
に配列で入っています。
そのままの出力だとJSONが巨大になることが多いため、jqで適当に加工して使うことを想定して作っています。例えば、総処理バイト数が0より大きい & 大きい順に並べて表示するとかだとこんな感じです。以下は自分のプライベートで使っているプロジェクトの例ですが、dataformとdbtで動かしているクエリが無駄だったことが分かりました。
% cat out.json | \ jq -c 'sort_by(.total_bytes_processed) | reverse | .[] | select(.total_bytes_processed > 0) | {"total_bytes_processed": .total_bytes_processed, "query": .query[0:50], "count": .count, "user_email": .user_email, "destination_table": .destination_table}' | \ head -n 2 | \ jq . { "total_bytes_processed": 84113438269, "query": "create or replace table `ml-news.mart.google_bot_e", "count": 134, "user_email": "ml-news-dataform@ml-news.iam.gserviceaccount.com", "destination_table": "ml-news:mart.google_bot_example_page" } { "total_bytes_processed": 40423015212, "query": "/* {\"app\": \"dbt\", \"dbt_version\": \"0.20.0\", \"profil", "count": 361, "user_email": "dbt-runner@ml-news.iam.gserviceaccount.com", "destination_table": "ml-news:test_test.examples_with_tweet_stats_v_0_0_2" }
ツールの思想としては似たようなクエリをまとめ上げるところに焦点を置いているので、こんな感じで後段の処理でよしなに使ってもらえればと思います。