JOBS ビュー

INFORMATION_SCHEMA.JOBS ビューには、現在のプロジェクト内の BigQuery ジョブすべてに関するニアリアルタイムのメタデータが含まれます。

必要なロール

INFORMATION_SCHEMA.JOBS ビューに対するクエリを実行するために必要な権限を取得するには、プロジェクトに対する BigQuery リソース閲覧者（roles/bigquery.resourceViewer）IAM ロールを付与するよう管理者に依頼してください。ロールの付与については、プロジェクト、フォルダ、組織へのアクセス権の管理をご覧ください。

この事前定義ロールには、INFORMATION_SCHEMA.JOBS ビューに対するクエリの実行に必要な bigquery.jobs.listAll 権限が含まれています。

カスタムロールや他の事前定義ロールを使用して、この権限を取得することもできます。

BigQuery の権限の詳細については、IAM でのアクセス制御をご覧ください。

スキーマ

基になるデータは creation_time 列で分割され、project_id と user_email でクラスタ化されます。query_info 列には、クエリジョブに関する追加情報が含まれます。

INFORMATION_SCHEMA.JOBS ビューのスキーマは次のとおりです。

列名	データの種類	値
`bi_engine_statistics`	`RECORD`	プロジェクトが BI Engine SQL インターフェースを使用するように構成されている場合、このフィールドには BiEngineStatistics が含まれます。それ以外の場合は `NULL` になります。
`cache_hit`	`BOOLEAN`	このジョブのクエリ結果がキャッシュから取得されたかどうか。マルチクエリステートメントジョブがある場合、親クエリの `cache_hit` は `NULL` です。
`creation_time`	`TIMESTAMP`	（パーティショニング列）このジョブの作成時間。パーティショニングは、このタイムスタンプの UTC 時間に基づきます。
`destination_table`	`RECORD`	結果の宛先テーブル（ある場合）。
`dml_statistics`	`RECORD`	ジョブが DML ステートメントを含むクエリの場合、値は次のフィールドを含むレコードです。 `inserted_row_count`: 挿入された行数。 `deleted_row_count`: 削除された行数。 `updated_row_count`: 更新された行数。それ以外のジョブの場合、値は `NULL` です。この列は、`INFORMATION_SCHEMA.JOBS_BY_USER` ビューと `INFORMATION_SCHEMA.JOBS_BY_PROJECT` ビューに存在します。
`end_time`	`TIMESTAMP`	このジョブの終了時間（エポックからのミリ秒）。このフィールドは、ジョブが `DONE` 状態になった時刻を表します。
`error_result`	`RECORD`	ErrorProto オブジェクトとしてのエラーの詳細。
`job_creation_reason.code`	`STRING`	ジョブが作成された大まかな理由を指定します。指定できる値は次のとおりです。 `REQUESTED`: ジョブの作成がリクエストされました。 `LONG_RUNNING`: `QueryRequest` の timeoutMs フィールドで指定されたシステム定義のタイムアウトを超えてクエリリクエストが実行されました。その結果、ジョブが作成された長時間実行オペレーションと見なされました。 `LARGE_RESULTS`: クエリの結果がインラインレスポンスに収まりません。 `OTHER`: クエリをジョブとして実行する必要があることがシステムによって判断されました。
`job_id`	`STRING`	ジョブが作成された場合のジョブの ID。ジョブの ID でない場合は、短いクエリモードを使用したクエリのクエリ ID。例: `bquxjob_1234`
`job_stages`	`RECORD`	ジョブのクエリステージ。注: 行レベルのアクセスポリシーが適用されたテーブルから読み取るクエリでは、この列の値が空になります。詳細については、BigQuery での行レベルのセキュリティに関するベストプラクティスをご覧ください。
`job_type`	`STRING`	ジョブのタイプ。`QUERY`、`LOAD`、`EXTRACT`、`COPY`、または `NULL` のいずれかです。`NULL` の値は、スクリプトジョブステートメントの評価やマテリアライズドビューの更新などの内部ジョブを示します。
`labels`	`RECORD`	Key-Value ペアとしてジョブに適用されるラベルの配列。
`parent_job_id`	`STRING`	親ジョブの ID（存在する場合）。
`priority`	`STRING`	このジョブの優先度。有効な値として、`INTERACTIVE`、`BATCH` があります。
`project_id`	`STRING`	（クラスタリング列）プロジェクトの ID。
`project_number`	`INTEGER`	プロジェクトの数。
`query`	`STRING`	SQL クエリテキスト。`JOBS_BY_PROJECT` ビューにのみクエリ列があります。
`referenced_tables`	`RECORD`	ジョブによって参照されるテーブルの配列。キャッシュヒットではないクエリジョブに対してのみデータが入力されます。
`reservation_id`	`STRING`	このジョブに割り当てられたプライマリ予約の名前（形式: `RESERVATION_ADMIN_PROJECT:RESERVATION_LOCATION.RESERVATION_NAME`）。この出力で: `RESERVATION_ADMIN_PROJECT`: 予約を管理する Google Cloud プロジェクトの名前 `RESERVATION_LOCATION`: 予約のロケーション `RESERVATION_NAME`: 予約の名前
`edition`	`STRING`	このジョブに割り当てられた予約に関連付けられているエディション。エディションの詳細については、BigQuery エディションの概要をご覧ください。
`session_info`	`RECORD`	ジョブが実行されているセッションの詳細（存在する場合）。
`start_time`	`TIMESTAMP`	このジョブの開始時間（エポックからのミリ秒）。このフィールドは、ジョブが `PENDING` 状態から `RUNNING` 状態または `DONE` 状態へ移行した時間を表します。
`state`	`STRING`	ジョブの実行状態。有効な状態は `PENDING`、`RUNNING`、`DONE` です。
`statement_type`	`STRING`	クエリステートメントのタイプ。たとえば、`DELETE`、`INSERT`、`SCRIPT`、`SELECT`、`UPDATE` です。有効な値の一覧については QueryStatementType をご覧ください。
`timeline`	`RECORD`	ジョブのクエリタイムライン。クエリ実行のスナップショットが格納されます。
`total_bytes_billed`	`INTEGER`	オンデマンド料金を使用するようにプロジェクトが構成されている場合、このフィールドにはジョブに対して課金された合計バイト数が含まれます。プロジェクトが定額料金を使用するように構成されている場合、バイト数は課金されず、このフィールドは情報提供のみを目的としています。注: 行レベルのアクセスポリシーが適用されたテーブルから読み取るクエリでは、この列の値が空になります。詳細については、BigQuery での行レベルのセキュリティに関するベストプラクティスをご覧ください。
`total_bytes_processed`	`INTEGER`	ジョブによって処理された合計バイト数。注: 行レベルのアクセスポリシーが適用されたテーブルから読み取るクエリでは、この列の値が空になります。詳細については、BigQuery での行レベルのセキュリティに関するベストプラクティスをご覧ください。
`total_modified_partitions`	`INTEGER`	ジョブによって変更されたパーティションの合計数。このフィールドは、`LOAD` ジョブと `QUERY` ジョブで入力されます。
`total_slot_ms`	`INTEGER`	`RUNNING` 状態（再試行を含む）のジョブの全期間におけるスロット（ミリ秒）。
`transaction_id`	`STRING`	このジョブが実行されたトランザクションの ID（存在する場合）。（プレビュー）
`user_email`	`STRING`	（クラスタリング列）ジョブを実行したユーザーのメールアドレスまたはサービスアカウント。
`query_info.resource_warning`	`STRING`	クエリ処理中のリソース使用量がシステムの内部しきい値を超えた場合に表示される警告メッセージ。クエリジョブが成功すると、`resource_warning` フィールドに値を入力できます。`resource_warning` を使用すると、`query_hashes` を使用してクエリを最適化し、同等のクエリセットのパフォーマンストレンドのモニタリングを設定するための追加のデータポイントを取得できます。
`query_info.query_hashes.normalized_literals`	`STRING`	クエリのハッシュが含まれます。`normalized_literals` は、コメント、パラメータ値、UDF、リテラルを無視する 16 進数の `STRING` ハッシュです。このフィールドは、キャッシュヒットではない GoogleSQL クエリが成功した場合に表示されます。
`query_info.performance_insights`	`RECORD`	ジョブのパフォーマンス分析情報。
`query_info.optimization_details`	`STRUCT`	ジョブの履歴ベースの最適化。
`transferred_bytes`	`INTEGER`	クロスクラウドクエリ（BigQuery Omni クロスクラウド転送ジョブなど）で転送された合計バイト数。
`materialized_view_statistics`	`RECORD`	クエリジョブで考慮されるマテリアライズドビューの統計。（プレビュー）

INFORMATION_SCHEMA.JOBS にクエリを実行してクエリジョブのコストの概算を確認する場合は、SCRIPT ステートメントタイプを除外します。こうしないと、一部の値が 2 回カウントされます。SCRIPT 行には、このジョブの一部として実行されたすべての子ジョブの概要値が含まれます。

マルチステートメントクエリジョブ

マルチステートメントクエリジョブは、プロシージャ言語を使用するクエリジョブです。マルチステートメントクエリジョブでは、多くの場合、DECLARE で変数を定義するか、IF や WHILE などの制御フローステートメントを使用します。INFORMATION_SCHEMA.JOBS にクエリを実行する場合、マルチステートメントクエリジョブと他のジョブの違いを認識する必要がある場合があります。マルチステートメントクエリジョブには次の特徴があります。

statement_type = SCRIPT
reservation_id = NULL
子ジョブ。マルチステートメントクエリジョブの子ジョブには、マルチステートメントクエリジョブ自体を指す parent_job_id があります。これには、このジョブの一部として実行されたすべての子ジョブのサマリー値が含まれます。そのため、INFORMATION_SCHEMA.JOBS にクエリを実行してクエリジョブのコストの概算を確認する場合は、SCRIPT ステートメントタイプを除外する必要があります。こうしないと、total_slot_ms などの一部の値が 2 回カウントされる可能性があります。

データの保持

このビューには、現在実行中のジョブと過去 180 日間のジョブの履歴が含まれます。

スコープと構文

このビューに対するクエリでは、リージョン修飾子を指定する必要があります。次の表に、このビューのリージョンスコープを示します。

ビュー名	リソーススコープ	リージョンスコープ
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.JOBS[_BY_PROJECT]	プロジェクトレベル	`REGION`

次のように置き換えます。

省略可: PROJECT_ID: Google Cloud プロジェクトの ID。指定しない場合は、デフォルトのプロジェクトが使用されます。

+ REGION: 任意のデータセットリージョン名。例: region-us。

注: INFORMATION_SCHEMA ビューに対してクエリを実行するには、リージョン修飾子を使用する必要があります。クエリ実行のロケーションは、INFORMATION_SCHEMA ビューのリージョンと一致する必要があります。

例

デフォルトプロジェクト以外のプロジェクトに対してクエリを実行するには、次の形式でプロジェクト ID を追加します。

`PROJECT_ID`.`region-REGION_NAME`.INFORMATION_SCHEMA.JOBS

次のように置き換えます。

PROJECT_ID: プロジェクトの ID。
REGION_NAME: プロジェクトのリージョン。

例: `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT

ジョブの使用状況と課金データを比較する

オンデマンド課金を使用するプロジェクトの場合、INFORMATION_SCHEMA.JOBS ビューを使用して、特定の期間のコンピューティング料金を確認できます。次のクエリは、請求される TiB とその結果の請求額の 1 日あたりの推定合計を生成します。制限事項セクションでは、これらの見積もりが請求額と一致しない可能性がある場合について説明しています。

この例では、次の追加変数を設定する必要があります。ここで編集して使いやすくすることができます。

START_DATE: 集計対象の最も早い日付（この値を含む）。
END_DATE: 集計対象の最終日（この値を含む）。
PRICE_PER_TIB: 請求見積もりに使用される TiB あたりのオンデマンド料金。

CREATE TEMP FUNCTION isBillable(error_result ANY TYPE)
AS (
  -- You aren't charged for queries that return an error.
  error_result IS NULL
  -- However, canceling a running query might incur charges.
  OR error_result.reason = 'stopped'
);

-- BigQuery hides the number of bytes billed on all queries against tables with
-- row-level security.
CREATE TEMP FUNCTION isMaybeUsingRowLevelSecurity(
  job_type STRING, tib_billed FLOAT64, error_result ANY TYPE)
AS (
  job_type = 'QUERY'
  AND tib_billed IS NULL
  AND isBillable(error_result)
);

WITH
  query_params AS (
    SELECT
      date 'START_DATE' AS start_date,  -- inclusive
      date 'END_DATE' AS end_date,  -- inclusive
  ),
  usage_with_multiplier AS (
    SELECT
      job_type,
      error_result,
      creation_time,
      -- Jobs are billed by end_time in PST8PDT timezone, regardless of where
      -- the job ran.
      EXTRACT(date FROM end_time AT TIME ZONE 'PST8PDT') billing_date,
      total_bytes_billed / 1024 / 1024 / 1024 / 1024 total_tib_billed,
      CASE statement_type
        WHEN 'SCRIPT' THEN 0
        WHEN 'CREATE_MODEL' THEN 50 * PRICE_PER_TIB
        ELSE PRICE_PER_TIB
        END AS multiplier,
    FROM `PROJECT_ID.region-REGION_NAME`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  )
SELECT
  billing_date,
  sum(total_tib_billed * multiplier) estimated_charge,
  sum(total_tib_billed) estimated_usage_in_tib,
  countif(isMaybeUsingRowLevelSecurity(job_type, total_tib_billed, error_result))
    AS jobs_using_row_level_security,
FROM usage_with_multiplier, query_params
WHERE
  1 = 1
  -- Filter by creation_time for partition pruning.
  AND date(creation_time) BETWEEN date_sub(start_date, INTERVAL 2 day) AND end_date
  AND billing_date BETWEEN start_date AND end_date
  AND isBillable(error_result)
GROUP BY billing_date
ORDER BY billing_date

制限事項

BigQuery では、行レベルのセキュリティが適用されたテーブルに対するクエリの一部の統計情報が非表示になります。指定されたクエリは、影響を受けるジョブの数を jobs_using_row_level_security としてカウントしますが、課金対象の使用量にはアクセスできません。
BigQuery ML のオンデマンドクエリの料金は、作成されるモデルのタイプによって異なります。INFORMATION_SCHEMA.JOBS は作成されたモデルのタイプを追跡しないため、指定されたクエリでは、すべての CREATE_MODEL ステートメントで料金の高いモデルタイプが作成されたと想定しています。
Apache Spark プロシージャは同様の料金モデルを使用しますが、請求は BigQuery Enterprise エディションの従量課金制 SKU として報告されます。INFORMATION_SCHEMA.JOBS は、この使用量を total_bytes_billed として追跡しますが、使用量がどの SKU を表しているかを特定することはできません。

スロットの平均使用率を計算する

次の例では、特定のプロジェクトの過去 7 日間のすべてのクエリの平均スロット使用率を計算します。この計算は、スロット使用率が 1 週間を通して一定しているプロジェクトで最も正確です。プロジェクトのスロット使用率が一定ではない場合、この数が想定よりも少なくなる可能性があります。

クエリを実行するには:

SELECT
  SUM(total_slot_ms) / (1000 * 60 * 60 * 24 * 7) AS avg_slots
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  -- Filter by the partition column first to limit the amount of data scanned.
  -- Eight days allows for jobs created before the 7 day end_time filter.
  creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 8 DAY) AND CURRENT_TIMESTAMP()
  AND job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
  AND end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP();

次のような結果になります。

+------------+
| avg_slots  |
+------------+
| 3879.1534  |
+------------+

特定の予約の使用状況は WHERE reservation_id = "…" で確認できます。これは一定期間の予約の使用率を判断するのに役立ちます。スクリプトジョブの場合、親ジョブは子ジョブからのスロットの合計使用状況も報告します。重複してカウントされないように、WHERE statement_type != "SCRIPT" を使用して親ジョブを除外します。

代わりに個々のジョブのスロットの平均使用率を確認する場合は、total_slot_ms / TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) を使用してください。

読み込みジョブの履歴

次の例では、特定のプロジェクトでバッチ読み込みジョブを送信したすべてのユーザーまたはサービスアカウントを一覧表示します。時間境界が指定されていないため、このクエリは使用可能なすべての履歴をスキャンします（たとえば、過去 30 日）。

SELECT
  DISTINCT(user_email) AS user
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  job_type = 'LOAD';

次のような結果になります。

+--------------+
| user         |
+--------------+
| abc@xyz.com  |
+--------------+
| def@xyz.com  |
+--------------+

読み込みジョブの数を取得して 1 日あたりのジョブ割り当ての使用量を確認する

次の例では、1 日あたりのジョブ割り当て量を判断できるように、日、データセット、テーブルごとのジョブ数を返します。

SELECT
    DATE(creation_time) as day,
    destination_table.project_id as project_id,
    destination_table.dataset_id as dataset_id,
    destination_table.table_id as table_id,
    COUNT(job_id) AS load_job_count
 FROM
   region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT
 WHERE
    creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 8 DAY) AND CURRENT_TIMESTAMP()
    AND job_type = "LOAD"
GROUP BY
    day,
    project_id,
    dataset_id,
    table_id
ORDER BY
    day DESC

最後に失敗した 10 件のジョブを取得する

次の例は、最後に失敗した 10 件のジョブを示しています。

SELECT
   job_id,
  creation_time,
  user_email,
   error_result
 FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
 WHERE error_result.reason != "Null"
ORDER BY creation_time DESC
LIMIT 10

結果は次のようになります。

+---------------+--------------------------+------------------+-----------------------------------------------------------+
| job_id        | creation_time            | user_email       | error_result                                              |
+---------------+--------------------------+------------------+-----------------------------------------------------------+
| examplejob_1  | 2020-10-10 00:00:00 UTC  | bob@example.com  | Column 'generate_metadata_snapshot' has mismatched type...|
| examplejob_2  | 2020-10-11 00:00:00 UTC  | bob@example.com  | Column 'generate_metadata_snapshot' has mismatched type...|
+---------------+--------------------------+------------------+-----------------------------------------------------------+

長時間実行ジョブのリストをクエリする

次の例は、30 分以上 RUNNING または PENDING 状態の長時間実行ジョブのリストを示しています。

SELECT
  job_id,
  job_type,
  state,
  creation_time,
  start_time,
  user_email
 FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
 WHERE
    state!="DONE" AND
    creation_time <= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 MINUTE)
ORDER BY creation_time ASC;

次のような結果になります。

+---------------+----------+---------+--------------------------------+--------------------------------+------------------+
| job_id        | job_type | state   | creation_time                  | start_time                     | user_email       |
+---------------+----------+---------+--------------------------------+--------------------------------+------------------+
| examplejob_1  | QUERY    | RUNNING | 2023-05-03 05:07:22.818000 UTC | 2023-05-03 05:07:22.905000 UTC | abc@example.com  |
| examplejob_2  | QUERY    | PENDING | 2023-05-01 02:05:47.925000 UTC | 2023-05-01 02:05:47.998000 UTC | xyz@example.com  |
+---------------+----------+---------+--------------------------------+--------------------------------+------------------+

短いクエリ最適化モードを使用するクエリ

次の例は、BigQuery がジョブを作成していない、短いクエリ最適化モードで実行されたクエリのリストを表示しています。

SELECT
 job_id,
FROM
 `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
 TIMESTAMP_TRUNC(creation_time, DAY) = '2024-06-12'
 AND job_creation_reason.code IS NULL
LIMIT
 10

結果は次のようになります。

+------------------------------------------+
| job_id                                   |
+------------------------------------------+
| 2Lm09bHxDEsoVK8zwzWJomLHU_Ud%1910479b151 |
| j9_GVQf28jW2M1_RfTYGRPX1vq--!191047a135f | 
+------------------------------------------+

次の例は、BigQuery がジョブを作成していない、短いクエリ最適化モードで実行されたクエリに関する情報を示しています。

SELECT
 job_id,
 statement_type,
 priority,
 cache_hit,
 job_creation_reason.code AS job_creation_reason_code,
 total_bytes_billed,
 total_bytes_processed,
 total_slot_ms,
 state,
 error_result.message AS error_result_message,
FROM
 `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
 TIMESTAMP_TRUNC(creation_time, DAY) = '2024-06-12'
 AND job_id = '2Lm09bHxDEsoVK8zwzWJomLHU_Ud%1910479b151' -- queryId

注: このクエリに対してジョブが作成されていない場合、job_id フィールドにはクエリの queryId が含まれます。

結果は次のようになります。

+------------------------------------------+----------------+-------------+-----------+--------------------------+--------------------+-----------------------+---------------+-------+----------------------+
| job_id                                   | statement_type | priority    | cache_hit | job_creation_reason_code | total_bytes_billed | total_bytes_processed | total_slot_ms | state | error_result_message |
+------------------------------------------+----------------+-------------+-----------+--------------------------+--------------------+-----------------------+---------------+-------+----------------------+
| 2Lm09bHxDEsoVK8zwzWJomLHU_Ud%1910479b151 | SELECT         | INTERACTIVE | false     | null                     | 161480704          | 161164718             | 3106          | DONE  | null                 |
+------------------------------------------+----------------+-------------+-----------+--------------------------+--------------------+-----------------------+---------------+-------+----------------------+

次の例は、BigQuery がジョブを作成した、短いクエリ最適化モードで実行されたクエリのリストを表示しています。

SELECT
 job_id,
 job_creation_reason.code AS job_creation_reason_code
FROM
 `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
 TIMESTAMP_TRUNC(creation_time, DAY) = '2024-06-12'
 AND job_creation_reason.code IS NOT NULL
 AND job_creation_reason.code != 'REQUESTED'
LIMIT
 10

結果は次のようになります。

+----------------------------------+--------------------------+
| job_id                           | job_creation_reason_code |
+----------------------------------+--------------------------+
| job_LxOEwrJEffcOfjK7GBwWjO3RroOI | LARGE_RESULTS            |
+----------------------------------+--------------------------+

ユーザー ID ごとの処理バイト数

次の例は、クエリジョブに対してユーザーごとに課金される合計バイト数を示しています。

SELECT
  user_email,
  SUM(total_bytes_billed) AS bytes_billed
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
GROUP BY
  user_email;

注: JOBS ビューのスキーマドキュメントで total_bytes_billed 列に関する注意事項をご覧ください。

結果は次のようになります。

+---------------------+--------------+
| user_email          | bytes_billed |
+---------------------+--------------+
| bob@example.com     | 2847932416   |
| alice@example.com   | 1184890880   |
| charles@example.com | 10485760     |
+---------------------+--------------+

1 時間あたりの処理バイト数の内訳

次の例は、1 時間間隔のクエリジョブに対して課金される合計バイト数を示しています。

SELECT
  TIMESTAMP_TRUNC(end_time, HOUR) AS time_window,
  SUM(total_bytes_billed) AS bytes_billed
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
GROUP BY
  time_window
ORDER BY
  time_window DESC;

次のような結果になります。

+-------------------------+--------------+
| time_window             | bytes_billed |
+-------------------------+--------------+
| 2022-05-17 20:00:00 UTC | 1967128576   |
| 2022-05-10 21:00:00 UTC | 0            |
| 2022-04-15 20:00:00 UTC | 10485760     |
| 2022-04-15 17:00:00 UTC | 41943040     |
+-------------------------+--------------+

テーブルあたりのクエリジョブ数

次の例は、my_project でクエリされた各テーブルがクエリジョブで参照された回数を示しています。

SELECT
  t.project_id,
  t.dataset_id,
  t.table_id,
  COUNT(*) AS num_references
FROM
  my_project.`region-us`.INFORMATION_SCHEMA.JOBS, UNNEST(referenced_tables) AS t
GROUP BY
  t.project_id,
  t.dataset_id,
  t.table_id
ORDER BY
  num_references DESC;

次のような結果になります。

+------------+------------+----------+----------------+
| project_id | dataset_id | table_id | num_references |
+------------+------------+----------+----------------+
| my_project | dataset1   | orders   | 58             |
| my_project | dataset1   | products | 40             |
| my_project | dataset2   | sales    | 30             |
| other_proj | dataset1   | accounts | 12             |
+------------+------------+----------+----------------+

テーブルあたりのクエリジョブと読み込みジョブによって変更されるパーティション数

次の例は、DML ステートメントを含むクエリと読み込みジョブによって変更されたパーティション数をテーブルごとに示しています。このクエリには、コピージョブの total_modified_partitions は表示されません。

SELECT
  destination_table.table_id,
  SUM(total_modified_partitions) AS total_modified_partitions
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  DATE(creation_time, "America/Los_Angeles") = CURRENT_DATE()
GROUP BY
  table_id
ORDER BY
  total_modified_partitions DESC

最も費用のかかるクエリ（プロジェクト別）

次の例では、my_project で最も費用の高いクエリをスロットの使用時間別に一覧表示しています。

SELECT
 job_id,
 query,
 user_email,
 total_slot_ms
FROM `my_project`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE EXTRACT(DATE FROM  creation_time) = current_date()
ORDER BY total_slot_ms DESC
LIMIT 4

以下の例では、処理されたデータごとに最も費用のかかるクエリを一覧表示できます。

SELECT
 job_id,
 query,
 user_email,
 total_bytes_processed
FROM `my_project`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE EXTRACT(DATE FROM  creation_time) = current_date()
ORDER BY total_bytes_processed DESC
LIMIT 4

どちらの例でも、結果は次のようになります。

+--------------+---------------------------------+-----------------------+---------------+
| job_id       | query                           | user_email            | total_slot_ms |
+--------------+---------------------------------+--------------------------+------------+
| examplejob_1 | SELECT ... FROM dataset.table1  | bob@example.com       | 80,000        |
| examplejob_2 | SELECT ... FROM dataset.table2  | alice@example.com     | 78,000        |
| examplejob_3 | SELECT ... FROM dataset.table3  | charles@example.com   | 75,000        |
| examplejob_4 | SELECT ... FROM dataset.table4  | tina@example.com      | 72,000        |
+--------------+---------------------------------+-----------------------+---------------+

リソース警告の詳細を取得する

「リソース超過」というエラーメッセージが表示された場合は、特定の時間枠内のクエリについて問い合わせることができます。

SELECT
  query,
  query_info.resource_warning
FROM
  `user_project.region-us`.INFORMATION_SCHEMA.JOBS
WHERE
 creation_time BETWEEN TIMESTAMP("2022-12-01")
 AND TIMESTAMP("2022-12-08")
 AND query_info.resource_warning IS NOT NULL
LIMIT
 50;

日付別にグループ化したリソース警告をモニタリングする

「リソース超過」というエラーメッセージが表示された場合は、日付別にグループ化されたリソース警告の合計数をモニタリングして、ワークロードに変更があったかどうかを確認できます。

WITH resource_warnings AS (
  SELECT
    EXTRACT(DATE FROM creation_time) AS creation_date
  FROM
    `user_project.region-us`.INFORMATION_SCHEMA.JOBS
  WHERE
    creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY)
    AND query_info.resource_warning IS NOT NULL
)
SELECT
  creation_date,
  COUNT(1) AS warning_counts
FROM
  resource_warnings
GROUP BY creation_date
ORDER BY creation_date DESC;

クエリのスロットの使用量と費用を見積もる

以下は、estimated_runnable_units を使用して各ジョブの平均スロットと最大スロットを計算した例です。

予約がない場合、reservation_id は NULL です。

SELECT
  project_id,
  job_id,
  reservation_id,
  EXTRACT(DATE FROM creation_time) AS creation_date,
  TIMESTAMP_DIFF(end_time, start_time, SECOND) AS job_duration_seconds,
  job_type,
  user_email,
  total_bytes_billed,

  -- Average slot utilization per job is calculated by dividing total_slot_ms by the millisecond duration of the job

  SAFE_DIVIDE(job.total_slot_ms,(TIMESTAMP_DIFF(job.end_time, job.start_time, MILLISECOND))) AS job_avg_slots,
  query,

  -- Determine the max number of slots used at ANY stage in the query.
  -- The average slots might be 55. But a single stage might spike to 2000 slots.
  -- This is important to know when estimating number of slots to purchase.

  MAX(SAFE_DIVIDE(unnest_job_stages.slot_ms,unnest_job_stages.end_ms - unnest_job_stages.start_ms)) AS jobstage_max_slots,

  -- Check if there's a job that requests more units of works (slots). If so you need more slots.
  -- estimated_runnable_units = Units of work that can be scheduled immediately.
  -- Providing additional slots for these units of work accelerates the query,
  -- if no other query in the reservation needs additional slots.

  MAX(unnest_timeline.estimated_runnable_units) AS estimated_runnable_units
FROM `region-us`.INFORMATION_SCHEMA.JOBS AS job
  CROSS JOIN UNNEST(job_stages) as unnest_job_stages
  CROSS JOIN UNNEST(timeline) AS unnest_timeline
WHERE project_id = 'my_project'
  AND statement_type != 'SCRIPT'
  AND DATE(creation_time) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE()
GROUP BY 1,2,3,4,5,6,7,8,9,10
ORDER BY job_id;

クエリのパフォーマンス分析結果を表示する

次の例では、過去 30 日間のプロジェクトのパフォーマンス分析情報があるすべてのクエリジョブと、Google Cloud コンソールでクエリ実行グラフにリンクする URL を返します。

SELECT
  `bigquery-public-data`.persistent_udfs.job_url(
    project_id || ':us.' || job_id) AS job_url,
  query_info.performance_insights
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  DATE(creation_time) >= CURRENT_DATE - 30 -- scan 30 days of query history
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND error_result IS NULL
  AND statement_type != 'SCRIPT'
  AND EXISTS ( -- Only include queries which had performance insights
    SELECT 1
    FROM UNNEST(
      query_info.performance_insights.stage_performance_standalone_insights
    )
    WHERE slot_contention OR insufficient_shuffle_quota
    UNION ALL
    SELECT 1
    FROM UNNEST(
      query_info.performance_insights.stage_performance_change_insights
    )
    WHERE input_data_change.records_read_diff_percentage IS NOT NULL
  );

メタデータ更新ジョブを表示する

次の例では、メタデータ更新ジョブを一覧表示します。

SELECT
 *
FROM
 `region-aws-us-east-1.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
WHERE
 job_id LIKE '%metadata_cache_refresh%'
 AND creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 6 HOUR)
ORDER BY start_time desc
LIMIT 10;

同一クエリのパフォーマンスの推移を分析する

次の例では、過去 7 日間に同じクエリを実行した最も低速の 10 件のジョブが返されます。

DECLARE querytext STRING DEFAULT(
  SELECT query
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE job_id = 'JOB_ID'
  LIMIT 1
);

SELECT
  start_time,
  end_time,
  project_id,
  job_id,
  TIMESTAMP_DIFF(end_time, start_time, SECOND) AS run_secs,
  total_bytes_processed / POW(1024, 3) AS total_gigabytes_processed,
  query
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  query = querytext
  AND total_bytes_processed > 0
  AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
ORDER BY 5 DESC
LIMIT 10;

JOB_ID は、分析中のクエリを実行した job_id に置き換えます。

管理リソースグラフからスロット使用状況を照合する

管理リソースグラフの情報と同様のスロット使用状況を調べるには、INFORMATION_SCHEMA.JOBS_TIMELINE ビューにクエリを実行します。

JOBS ビュー

必要なロール

スキーマ

マルチステートメント クエリジョブ

データの保持

スコープと構文

例

ジョブの使用状況と課金データを比較する

制限事項

スロットの平均使用率を計算する

読み込みジョブの履歴

読み込みジョブの数を取得して 1 日あたりのジョブ割り当ての使用量を確認する

最後に失敗した 10 件のジョブを取得する

長時間実行ジョブのリストをクエリする

短いクエリ最適化モードを使用するクエリ

ユーザー ID ごとの処理バイト数

1 時間あたりの処理バイト数の内訳

テーブルあたりのクエリジョブ数

テーブルあたりのクエリジョブと読み込みジョブによって変更されるパーティション数

最も費用のかかるクエリ（プロジェクト別）

リソース警告の詳細を取得する

日付別にグループ化したリソース警告をモニタリングする

クエリのスロットの使用量と費用を見積もる

クエリのパフォーマンス分析結果を表示する

メタデータ更新ジョブを表示する

同一クエリのパフォーマンスの推移を分析する

管理リソースグラフからスロット使用状況を照合する

マルチステートメントクエリジョブ