[TROCCO] BigQuery データマートのクエリにラベルが付与されるようになりました

概要

TROCCO データマート（BigQuery のみ）から発行されたクエリに、TROCCO 側の情報がラベルで付与されるようになりました。

この変更により、BigQuery に記録されたクエリがどの TROCCO データマートから発行されたか追跡したり、BigQuery 上で INFORMATION_SCHEMA を使って集計できるようになりました。

背景

TROCCO データマートを多数運用していると、以下のような場面に遭遇することがあります。

• BigQuery のコストが先月から大きく増えたので、どの TROCCO データマートが原因か確認したい
• TROCCO データマートのジョブ実行が遅いので、BigQuery 側の情報（例: スロット消費）と TROCCO 側の情報を突き合わせたい
• 特定の時間帯にスロットが枯渇したので、どの TROCCO データマートがスロットを消費しているのか確認したい

上記のような場面に遭遇した場合、BigQuery に記録されたクエリと TROCCO データマートに表示されているクエリをひとつひとつ見比べる必要があり、非常に骨の折れる作業となっていました。

付与されるラベル

TROCCO データマートから発行されたクエリに、以下のラベルが付与されます。

キー	値
trocco_job_type	datamart
trocco_statement_type	クエリの種類（後述）
trocco_job_id	TROCCO データマートジョブ ID

trocco_statement_type には、以下のいずれかの値が入ります。

値	内容
datamart_query	TROCCO データマート本体のクエリ
quality_check	データ品質チェック
before_load	「転送前に実行するクエリ」
schema_evolution	スキーマ自動追従モードによる ALTER 文

ラベルを利用する

ラベルが付いたことで、INFORMATION_SCHEMA.JOBS_BY_PROJECT から TROCCO データマートから発行されたクエリだけを抽出して集計できるようになりました。

実用的な例をいくつか紹介します。

重いクエリを見つける

直近 1 日に TROCCO データマートから発行されたクエリのうち、スロット消費量が多いものを上位 20 件抽出します。コスト増や実行遅延の原因となっているクエリを特定したい場合に利用できます。

SELECT
  (
    SELECT
      value
    FROM UNNEST(labels)
    WHERE
      key = 'trocco_job_id'
  ) AS trocco_job_id,
  TIMESTAMP_DIFF(end_time, start_time, SECOND) AS duration_sec,
  total_slot_ms,
  total_bytes_processed
-- リージョンは TROCCO データマートの出力先に合わせて変更してください（例: `region-asia-northeast1`）
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND job_type = 'QUERY'
  AND EXISTS (
    SELECT
      1
    FROM
      UNNEST(labels)
    WHERE
      key = 'trocco_job_type'
      AND value = 'datamart'
  )
ORDER BY
  total_slot_ms DESC
LIMIT
  20
;

時間帯別のスロット消費を確認する

直近 7 日間に TROCCO データマートから発行されたクエリについて、日本時間の時間帯ごとにスロット消費量（スロット時間）を集計します。スロットが枯渇しやすい時間帯を特定し、ジョブの実行スケジュールを見直したい場合に利用できます。

SELECT
  EXTRACT(HOUR FROM creation_time AT TIME ZONE 'Asia/Tokyo') AS hour_jst,
  SUM(total_slot_ms) / (1000 * 60 * 60) AS total_slot_hours
-- リージョンは TROCCO データマートの出力先に合わせて変更してください（例: `region-asia-northeast1`）
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND EXISTS (
    SELECT
      1
    FROM
      UNNEST(labels)
    WHERE
      key = 'trocco_job_type'
      AND value = 'datamart'
  )
GROUP BY
  hour_jst
ORDER BY
  hour_jst
;