BigQuery管理リソースグラフと同等のものをSQLで作る

背景

BigQuery管理リソースグラフって何?って人も多いと思いますが、BigQuery Reservationsを使っている場合に見える管理者用のBigQueryのリソース利用率の遷移が見れるグラフです。こういうやつです、主にスロット関係の統計量をよく見ます。

リアルタイムで見れたり、フィルタで条件をぽちぽち変えて見れたりして便利なのですが、たまに不便なことがあります。例えば

フィルタなしの状態
projectでグルーピングした状態
ユーザーでグルーピングした状態

を同時に画面に出すことはできません。パフォーマンスの調査をするときには色々並べた上で考えたいことも多いです。Data StudioなどのBIツールでこれと同等の内容が見れると調査がはかどりそうです。

やり方: INFORMATION_SCHEMA.JOBS_TIMELINE_BY_*を使う

...が、これと同等の内容のものをどうやって出せばいいのか最初は分かりませんでした。いつもお世話になるINFORMATION_SCHEMA.JOBS_BY_*は特定のジョブのことは分かるものの、ある時点でのリソース使用状況を状況を出す、というのはこれだと難しそうでした。ただ、最近勉強会で紹介したINFORMATION_SCHEMA.JOBS_TIMELINE_BY_*を使えば、特定時点(タイムスライス)毎のリソース使用状況が分かることに気付きました。勉強会はやる側が一番勉強になる...!

以下のSQLの結果をBIツールに投げてあげれば、冒頭で述べたようなグラフを同時に並べて見ることができます。

WITH slot_stats AS (
  SELECT
    DATETIME_TRUNC(DATETIME(period_start, "Asia/Tokyo"), HOUR) AS period_start_hour_jst,
    period_slot_ms / (1000 * 60 * 60) AS period_slot,
    *,
  FROM
    `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION
  WHERE
    job_creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 45 DAY) -- 期間は適当に選んでください
    AND statement_type != "SCRIPT"
    AND reservation_id IS NOT NULL
)
SELECT
  period_start_hour_jst,
  project_id,
  user_email,
  SUM(period_slot) AS period_slot,
FROM
  slot_stats
GROUP BY
  period_start_hour_jst,
  project_id,
  user_email
ORDER BY
  period_start_hour_jst DESC

INFORMATION_SCHEMA.JOBS_TIMELINE_BY_*を使って、BigQuery管理リソースグラフと同等の数字を出そうとすると多少の前提知識や調整が必要になったのでメモしておきます。

INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATIONは組織全体の全てのジョブのタイムスライスの情報を出すため、オンデマンドのジョブも入ってきてしまう
- reservation_id IS NOT NULLでフィルタしてやる
- 複数reservationを保持している場合、reservation_idでもGROUP BYしてやるとよさそう
period_slot_msはある地点(1秒単位で入ってきます)で使用したスロットのミリ秒を表わす
- period_slot_msを集計したい単位(今回の例ではHOUR)に合わせて割り算して辻褄を合わせる必要がある
最初、スロットのミリ秒って何か分からなかった...
- これを理解するには「スロット消費時間(下のスクショだとSlot time consumedと表示されているもの)」という概念を理解すればよい
- 例:
  - A: あるクエリを4000スロットを使って1秒で捌いた
  - B: あるクエリを2000スロットを使って2秒で捌いた
  - AとBはどちらも4000秒(スロット消費時間)となる
- つまり、period_slot_ms / (1000 * 60 * 60)はslot HOUR(スロット消費時間)、ということを意味する
- 詳しくはGoogle Cloudではじめる実践データエンジニアリング入門[業務で使えるデータ基盤構築]を読むとよいです