JOBS_BY_* でジョブ警察

2020-02-14 に INFORMATION_SCHEMA.JOBS_BY_* ビューがベータになり、誰でも使えるようになりました（BigQuery release notes）。

Cloud Logging で見ていた BigQuery ジョブが BigQuery 単体で追えるようになるアップデートです。何ができるようになったのか見ていきましょう。 最新情報は、公式ドキュメント Getting jobs metadata using INFORMATION_SCHEMA をご確認ください。

INFORMATION_SCHEMA.JOBS_BY_* ビューで BigQuery ジョブの履歴を見る#

INFORMATION_SCHEMA.JOBS_BY_* ビューを使うと、実行中の BigQuery ジョブと過去 180 日間1の完了したジョブのメタデータを取得できます。 1: 180 日間とのことですが、私の手元で確認できたログは 2020-01-03 が最新でした。ログの保存開始日が 180 日前より最近である可能性があります。

スキーマは後ほど紹介します。まずはできることの例を見ていきましょう。

用途例 5 つ#

高価なクエリを摘発する#

今年のプロジェクト一課金されたクエリを発見します。 権限がない方が試したい場合には、JOBS_BY_PROJECT を JOBS_BY_USER に書き換えて、個人史上一課金されたクエリを見つけてみましょう。 JOBS_BY_ORGANIZATION には、query 列がないかもしれません。

SELECT
job_id,
user_email,
query,
total_bytes_processed
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE EXTRACT(YEAR FROM creation_time) = 2020
ORDER BY total_bytes_processed DESC
LIMIT 5
job_iduser_emailquerytotal_bytes_processed
bquxjob_549481d2_170433e2ef2abc@xyz.comSELECT ... FROM ...296581077362
bquxjob_1889199f_17037f62246def@xyz.comSELECT ... FROM ...296272073862
bquxjob_276128e9_17037f57be5ghi@xyz.comSELECT ... FROM ...296272062336
bquxjob_23f9c0e7_17037f2407bjkl@xyz.comSELECT ... FROM ...296272062336
bquxjob_254cda14_17037f422e5mno@xyz.comSELECT ... FROM ...296272062336

プロジェクト利用者を特定する#

特定のプロジェクトにジョブを送信したすべてのユーザーまたはサービスアカウントを一覧表示します。プロジェクトの棚卸し時に使えそうです。

SELECT
DISTINCT user_email
FROM
`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
user_email
abc@xyz.com
def@xyz.com

低速な野良クエリを摘発する#

予め保存されたクエリリストに属さない、スロットを大量消費するクエリを見つけます。スケジュールされたクエリを最適化する際に使えそうです。

SELECT
user_email,
query,
total_slot_ms
FROM
`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
query IN(
SELECT
query
FROM
<your_dataset>.query_list
)
ORDER BY total_slot_ms DESC
LIMIT 5
user_emailquerytotal_slot_ms
abc@xyz.comSELECT ... FROM ...5274048808
def@xyz.comSELECT ... FROM ...3384028972
ghi@xyz.comSELECT ... FROM ...3143715099
jkl@xyz.comSELECT ... FROM ...3095278727
mno@xyz.comSELECT ... FROM ...2370765019

使われていないデータセットを摘発する#

使われていないデータセットを発見し、棚卸しを行います。 ただし、JOBS_BY_ORGANIZATION は組織内のクエリのログですので、外部公開されたデータセットの場合には、使われていないかの判別に使うことはできません。

WITH
referenced_dataset AS(
SELECT
DISTINCT referenced_table.project_id,
referenced_table.dataset_id
FROM
`<your-project>.region-us.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION`
JOIN
UNNEST(referenced_tables)referenced_table)
SELECT
catalog_name,
schema_name
FROM
`<your-project>.INFORMATION_SCHEMA.SCHEMATA`
LEFT JOIN
referenced_dataset
ON
catalog_name=project_id
AND schema_name=dataset_id
WHERE
project_id IS NULL
catalog_nameschema_name
project-adataset_1
project-adataset_2

平均使用スロットの多い日を特定する#

特定のプロジェクトの平均使用スロットを表示します。BigQuery Reservations を使ったり、スケジュールされたクエリを使う場合には、スロット使用率の監視が重要になってきます。

SELECT
DATE(end_time) AS end_date,
SUM(total_slot_ms) / (1000*60*60*24) AS avg_slots
FROM
`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
GROUP BY
end_date
ORDER BY
end_date DESC
LIMIT
3
end_dateavg_slots
2020-02-16811.4027300231481
2020-02-1430.734837708333334
2020-02-1371.37354623842593

INFORMATIONSCHEMA.JOBS_BY* ビュー 3 種#

ここからはドキュメントベースの解説になります。

INFORMATION_SCHEMA.JOBS_BY_USER#

  • 現在のプロジェクトで実行ユーザーから送信されたジョブのみを返す
  • bigquery.jobs.list の権限が必要

INFORMATION_SCHEMA.JOBS_BY_PROJECT#

  • 現在のプロジェクトで送信されたすべてのジョブを返す
  • bigquery.jobs.listAll 権限が必要

INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION#

  • 現在のプロジェクトの組織で送信されたすべてのジョブを返す
  • 組織の bigquery.jobs.listAll の権限が必要

BigQuery ジョブのメタデータスキーマ#

公式ドキュメントから引用し、日本語を併記、特に重要そうなものを太字にします。

列名データタイプ値
creation_timeTIMESTAMPジョブの作成時間
project_idSTRINGプロジェクト ID
project_numberINTEGERプロジェクト番号
user_emailSTRINGジョブ実行者のメールアドレス
job_idSTRINGジョブ ID
job_typeSTRINGジョブタイプ（QUERY、LOAD、 EXTRACT、COPY、UNKNOWN）
statement_typeSTRINGクエリステートメントタイプ（SELECT、INSERT、UPDATE、DELETE）
start_timeTIMESTAMPジョブの開始時間
end_timeTIMESTAMPジョブの終了時間
querySTRINGSQLクエリ文字列
stateSTRING実行状態（PENDING、 RUNNING、DONE）
reservation_idSTRING割り当てられた reservation 名
total_bytes_processedINTEGER処理されたバイト数
total_slot_msINTEGERスロットミリ秒
error_resultRECORDエラー
cache_hitBOOLEANクエリ結果がキャッシュを返したか
destination_tableRECORD宛先テーブル
referenced_tablesRECORD参照されたテーブル
labelsRECORDラベルの配列
timelineRECORDタイムライン※クエリ実行進捗状況
job_stagesRECORDクエリステージ※BigQuery Web UI の実行の詳細から見れるもの

Cloud Logging と比較して#

優れるところ#

  • ログの保存期間が 180 日と長い
  • ログシンク不要で直ぐにクエリが叩ける
  • statement_type、referenced_tables など BigQuery の痒いところに手が届く

劣るところ#

BigQuery 以外の Google Cloud Platform のジョブを見ることはできないです。用途の違いなので使い分けが大事なようです。

まとめ#

INFORMATIONSCHEMA.JOBS_BY* を触って、思いつくユースケース 5 つを書き起こしてみました。 ログベースで色々な分析ができて、まだまだ色々な活用方法があるはずです。 ぜひ活用していきましょう。

Edit this page
Last updated on