はじめに
BigQueryは好きですか？
私は、個人利用するほど便利で大好きなツールです。
しかし、自分がどれだけBigQueryを愛用しているかを伝えるのは意外と難しいと感じることがあります。そこで、どのような用途にどれくらい使っているかを可視化し、「プロフィール帳」をモチーフにした推し（BigQuery）のプロフィールを作成してみました。
参考：「プロフィール帳」イメージ：こちら
作ったもの
以下は完成したBigQueryプロフィールです。
以下リンクから、実際のレポートが確認できます。
https://lookerstudio.google.com/reporting/5f527169-138d-4f4b-b0e0-59572c3a1d41
右上で指定した期間を集計対象にして、以下の内容を可視化しています。
- ジョブの実行回数
- ジョブ実行全体における以下の詳細分類：
- (1) Looker Studioから呼び出された回数
- (2) データの追加（INSERT）回数
- (3) CSV、JSONなど外部データを取り込んだ回数（LOAD）
- 日次のジョブ実行回数ランキング
データ取得方法
INFORMATION_SCHEMA.JOB_BY_USER ビューを使用して、自分が実行したジョブ一覧を取得。ジョブの内容を判別するカラムをもとに、実行回数をカウントしました。
参考: JOBS_BY_USER ビュー (公式ドキュメント)
取得数値の定義
| 項目 | 項目定義 |
|---|---|
| ジョブの実行回数 | job_idのカウント |
| Looker Studioから呼び出し | labelsカラムでkey='requestor'かつvalue='looker_studio'に該当するjob_idのカウント |
| データの追加（INSERT） | statement_type='INSERT'に該当するjob_idのカウント |
| 外部データの取り込み（LOAD） | job_type='LOAD'に該当するjob_idのカウント |
| 日次のジョブ回数 | DATE(creation_time, 'Asia/Tokyo')ごとのjob_idのカウント |
データ取得用クエリ
WITH looker_studio_use AS (
SELECT DISTINCT
job_id
FROM
`region-us`.INFORMATION_SCHEMA.JOBS_BY_USER, UNNEST(labels) l
WHERE
l.key = 'requestor' AND l.value = 'looker_studio'
AND user_email = '[オーナーのメールアドレス]'
)
SELECT
DATE_TRUNC(DATE(creation_time, 'Asia/Tokyo'), MONTH) AS create_month,
DATE(creation_time, 'Asia/Tokyo') AS create_date,
job_type,
statement_type,
CASE
WHEN ls.job_id IS NOT NULL THEN 'Looker Studio'
ELSE 'BigQuery UI'
END AS label,
jb.job_id
FROM
`region-us`.INFORMATION_SCHEMA.JOBS_BY_USER jb
LEFT JOIN
looker_studio_use ls
ON jb.job_id = ls.job_id
WHERE
user_email = '[オーナーのメールアドレス]'
気づき
1. job_typeとstatement_typeで実行履歴の内容を判別可能
job_typeとstatement_typeカラムを使うと、以下のようなジョブ実行内容を詳細に判別できるようです。
-
job_type = 'QUERY'やstatement_type = 'SELECT'→ データ取得 -
statement_type = 'INSERT'→ データ追加 -
job_type = 'LOAD'→ データ取り込み - その他、
CTAS(CREATE TABLE AS SELECT)、DELETE、COPYなどのジョブも識別可能なようです。
この仕組みを使えば、データ作成や複製の履歴まで確認できる点が新たな発見でした。
2. 外部ツールからのジョブも判別可能
Looker Studioのような外部ツールから自動実行されたジョブを判別できそうな方法を見つけることができました。
labelsカラムを参照すると、key='requestor'かつvalue='looker_studio'が設定されているレコードが見つかり、実行されているクエリ内容は自動生成されているクエリのようであり、Looker Studioを利用したタイミングと概ね同じことから、Looker StudioからのBigQueryを呼び出しているジョブを一括して特定できていそうです。1点懸念点としては、発生しているjob_idをカウントすると1日当たり100を超える日もあることから、BigQueryを呼び出ししている回数が多いようで少し違和感がありました。こちらは、次回以降、調査してみようと思います。
labelsカラムをもとに判別する方法は以下ポストを参考にさせていただきました。
参考:https://x.com/syou6162/status/1696356634489217131
終わりに
今回、INFORMATION_SCHEMA.JOB_BY_USER ビューの基本的な使い方を学びつつ、BigQueryプロフィールを作成してみました。このビューには他にも色々なカラム、設定がされており色々なことができそうなので、さらに活用方法を模索し、BigQuery愛がより伝わるプロフィールにしていきたいです！