BigQuery Editions環境でのスロット使用量を調べられるSQLを書いた

BigQueryの新プランの登場でBigQueryをOnDemandからEditionsに切り替える人も多いと思います。OnDemand環境下ではスキャンするデータ量を見ておけばよかったですが、Editionsではスロット消費量がベースになり課金額が決まります。

「課金額がどれくらいか」「スロット使用量が大きいユーザー / テーブルはどれか」を調べる際に使いそうなクエリを書いたのでまとめておきます。

組織内のプロジェクト毎のスロット使用量の推移を調べる

前提として、以下のことに気を付けましょう。

前提1: INFORMATION_SCHEMA.JOBSではなく、INFORMATION_SCHEMA.JOBS_TIMELINEを使う
前提2: 最短課金時間である 100 スロット/秒で切り上げる必要がある

以下のエントリになぜこうする必要があるかは書いてあるので、そちらを参照してください。

この前提を元に、組織内のプロジェクト毎のスロット使用量の推移を調べたい場合は以下のようなクエリを元にダッシュボードを作ればよさそうです。

WITH
  slot_in_second AS (
  SELECT
    project_id,
    period_start,
    CEIL(SUM(period_slot_ms) / 1000 / 100 ) * 100 AS total_slot_sec,
  FROM
    `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION
  WHERE
    DATE(job_creation_time, "Asia/Tokyo") >= PARSE_DATE("%Y%m%d", @DS_START_DATE) -- @DS_START_DATE はlooker studioのパラメータ。適当に必要なものに変えて使ってください
    AND DATE(job_creation_time, "Asia/Tokyo") <= PARSE_DATE("%Y%m%d", @DS_END_DATE)
  GROUP BY
    project_id, period_start)
SELECT
  project_id,
  DATE(period_start, 'Asia/Tokyo') AS period_start,
  ROUND(SUM(total_slot_sec) / 60 / 60, 2) AS total_slot_hour,
FROM
  slot_in_second
GROUP BY
  project_id, period_start

計算に使うのはperiod_startですが、WHERE句でフィルタするのはjob_creation_timeで行なっていることに注意してください。パーティショニング列になっているのはjob_creation_timeだからです(period_startだとfull scanが走る)。

スロット使用量が大きいユーザー / テーブルはどれかを調べる

同じ要領で「どのユーザーやテーブルがスロット使用量が多いか」を調べていけるようにしましょう。ベースになるのは引き続きINFORMATION_SCHEMA.JOBS_TIMELINEですが、これには生成したテーブルの情報(destination_table)が含まれていません。この情報はINFORMATION_SCHEMA.JOBSに含まれているので、JOINして必要な情報を補完するようにします。

この情報を調べる際、「前提2: 最短課金時間である 100 スロット/秒で切り上げる必要がある」を満たすのが案外難しかった*1ため、以下のクエリではこの前提を無視したクエリになっています。特にスロット使用量が小さく短時間で終わるようなクエリが大量にある場合、総和が全体のスロット使用量より低めに出てしまうことには注意しましょう。

しかし、一回のクエリが非常にスロットを消費する場合が改善したいケースであることが多いです。前述した前提を無視してはいるものの、それでも一定有用だと思います。総和が全体のスロット使用量より低めに出てしまう場合、クエリの実行回数の削減を主目的に探ってみるといいと思います。

以下のように実際のクエリを書いてみました。ユーザー毎、生成しているテーブル毎などにスロット使用量の推移を追うことができるため、コストの削減やパフォーマンスの改善に繋げられるかと思います。環境によってはreservation毎の推移などを出してもよいかもですね。

WITH
  jobs_by_timeline AS (
  SELECT
    job_id,
    DATE(period_start, "Asia/Tokyo") AS period_start,
    period_slot_ms,
    project_id,
    user_email,
  FROM
    `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION
  WHERE
    DATE(job_creation_time, "Asia/Tokyo") >= PARSE_DATE("%Y%m%d", @DS_START_DATE)
    AND DATE(job_creation_time, "Asia/Tokyo") <= PARSE_DATE("%Y%m%d", @DS_END_DATE)
    AND statement_type != "SCRIPT" ),
  jobs AS (
  SELECT
    job_id,
    destination_table,
  FROM
    `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
  WHERE
    DATE(creation_time, "Asia/Tokyo") >= PARSE_DATE('%Y%m%d', @DS_START_DATE)
    AND DATE(creation_time, "Asia/Tokyo") <= PARSE_DATE('%Y%m%d', @DS_END_DATE)),
  jobs_by_timeline_with_destination_table AS (
  SELECT
    jobs.destination_table.project_id AS destination_project_name,
    jobs.destination_table.dataset_id AS destination_dataset_name,
    jobs.destination_table.table_id AS destination_table_name,
    jobs.destination_table.project_id || "." || jobs.destination_table.dataset_id || "." || jobs.destination_table.table_id AS destination_table_fullname,
    jobs_by_timeline.job_id,
    jobs_by_timeline.period_start,
    jobs_by_timeline.period_slot_ms,
    jobs_by_timeline.project_id AS project_name,
    jobs_by_timeline.user_email,
  FROM
    jobs_by_timeline
  INNER JOIN
    jobs
  USING
    (job_id) )
SELECT
  destination_project_name,
  destination_dataset_name,
  destination_table_name,
  destination_table_fullname,
  period_start,
  project_name,
  user_email,
  SUM(period_slot_ms) / (1000 * 60 * 60) AS period_slot_hour
FROM
  jobs_by_timeline_with_destination_table
GROUP BY
  destination_project_name,
  destination_dataset_name,
  destination_table_name,
  destination_table_fullname,
  period_start,
  project_name,
  user_email