BigQueryのテーブル利用状況を可視化してみる

方針

• BigQueryのINFORMATION_SCHEMAを利用してジョブメタデータを取得し、テーブルごとのジョブ実行回数を集計する。

直近1ヶ月のジョブ実行回数を取得するクエリ

SELECT 
  referenced_tables.project_id
  , referenced_tables.dataset_id
  , referenced_tables.table_id
  , COUNT(job_id) AS count_job
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT -- BigQuery の INFORMATION_SCHEMA ジョブビューはリージョン化されています。これらのビューに対してクエリを実行するには、リージョン修飾子を使用する必要があります。
  , UNNEST(referenced_tables) AS referenced_tables
WHERE 
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 1 MONTH) -- creation_timeは時間単位の列パーティションになっている
  AND job_type = 'QUERY' -- ジョブのタイプ
  AND statement_type = 'SELECT' -- クエリステートメントのタイプ
  AND state = "DONE" -- ジョブの実行状態
GROUP BY
  1,2,3
ORDER BY
  count_job DESC

ポイント

• RECORD型のreferenced_tablesカラムに、クエリが参照したプロジェクト、データセット、テーブル情報が格納されている。(めっちゃありがたい！)
• なので、UNNESTでフラット化した上で、job_idカラムをCOUNTしてあげればOK。

応用：ジョブが実行されていないテーブルを取得するクエリ

WITH 
  count_jobs AS (
SELECT 
  referenced_tables.project_id
  , referenced_tables.dataset_id
  , referenced_tables.table_id
  , COUNT(job_id) AS count_job
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT -- BigQuery の INFORMATION_SCHEMA ジョブビューはリージョン化されています。これらのビューに対してクエリを実行するには、リージョン修飾子を使用する必要があります。
  , UNNEST(referenced_tables) AS referenced_tables
WHERE 
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 1 MONTH) -- creation_timeは時間単位の列パーティションになっている
  AND job_type = 'QUERY' -- ジョブのタイプ
  AND statement_type = 'SELECT' -- クエリステートメントのタイプ
  AND state = "DONE" -- ジョブの実行状態
GROUP BY
  1,2,3
  )

SELECT
  tables.table_catalog AS project_id
  , tables.table_schema AS dataset_id
  , tables.table_name AS table_id
  , count_jobs.count_job
FROM 
  `region-us`.INFORMATION_SCHEMA.TABLES AS tables -- BigQuery の INFORMATION_SCHEMA ジョブビューはリージョン化されています。これらのビューに対してクエリを実行するには、リージョン修飾子を使用する必要があります。
LEFT JOIN 
  count_jobs
ON 
  tables.table_catalog = count_jobs.project_id
  AND tables.table_schema = count_jobs.dataset_id 
  AND tables.table_name = count_jobs.table_id
ORDER BY 
  count_jobs.count_job, tables.table_catalog, tables.table_schema, tables.table_name

ポイント

• INFOMARTION_SCHEMA.TABLESに、テーブルのメタデータが格納されている。
• 冒頭で記載したクエリの結果を結合することで、ジョブが発行されていないテーブルも含めて取得している。

まとめ

• 今回の件で初めてINFORMATION_SCHEMAの存在を知りました。
• start_timeカラム(ジョブの開始時間)、end_timeカラム(ジョブの終了時間)もあるので、パフォーマンスの悪いクエリの抽出もできそう。