BigQueryで高額課金が発生しているクエリの呼び出し元を特定する

こんにちは

突然ですが、BigQueryで負の遺産を大量に抱えていたりしませんか?

自由に簡単に使い始めることができることで人気のBigQueryですが、長年使っていると、ふと

「あれ、なんでこんなに課金されてるんだ?」

と思ったりしないでしょうか

今回は、

BigQueryの課金が多いことはわかったけど、具体的にどれが要因かわからない

クエリはわかったけど、どこから呼ばれているのかわからない

そんな時にBigQueryで呼ばれているクエリと呼び出し元を特定する方法をまとめます

※BigQuery Analyticsの課金額が多いことを特定するまでは省略します

■実行されているクエリとスキャン量を出す

BigQueryでこちらのクエリを流します

SELECT query,
       user_email,
       ROUND(SUM(total_bytes_billed) / POWER(1024, 4), 3) AS billed_tera_bytes
FROM INFORMATION_SCHEMA.JOBS
WHERE TIMESTAMP_TRUNC(creation_time, MONTH) = TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), MONTH)
  AND cache_hit = FALSE
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 10

INFORMATION_SCHEMA.JOBS を見ます

このクエリにより、実行クエリの内容、実行アカウント、サーチ量をサーチ量順（=課金量）順に確認することができます。

当月の実行を対象としていますが、期間を絞りたいときはcreation_timeで期間を絞り込んでください。

これで、課金額が高いクエリと呼び出しアカウントがわかります

■サービスアカウントで呼ばれているが、どこでサービスアカウントが使われているかわからない

クエリはわかりました
呼んでるアカウントもわかりました
アカウントは共通のサービスアカウントでした
このサービスアカウント、どこで使われているの?

ということで、呼び出し元を探ってみましょう

先ほどのクエリを少し修正します

SELECT query,
       user_email,
       ROUND(SUM(total_bytes_billed) / POWER(1024, 4), 3) AS billed_tera_bytes,
       ANY_VALUE(job_id) job_id
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE TIMESTAMP_TRUNC(creation_time, MONTH) = TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), MONTH)
  AND cache_hit = FALSE
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 10

実行すると、job_idが出力されます

Cloud Loggingのログエクスプローラに、対象のjob_idを入力して検索します

期間はデフォルトで直近の1時間になっているので、適切に設定します