使われていないにも関わらず定期実行されてしまっているクエリ(in BigQuery)を検知するツールを作りました

TL;DR
背景
実装時の問題点
作った

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"
}