使用 INFORMATION_SCHEMA 获取作业元数据

INFORMATION_SCHEMA 是一系列视图,可让您访问数据集、例程、表、视图、作业和预留的相关元数据。

您可以查询 INFORMATION_SCHEMA.JOBS_BY_* 视图以检索有关 BigQuery 作业的实时元数据。此视图包含当前正在运行的作业,以及最近 180 天的已完成作业历史记录。

所需权限

使用 INFORMATION_SCHEMA 表检索作业元数据需要适当范围的权限:

  • JOBS_BY_USER 需要项目的 bigquery.jobs.list 权限(Project ViewerBigQuery User 角色具备此权限)。
  • JOBS_BY_PROJECT 需要项目的 bigquery.jobs.listAll 权限(Project OwnerBigQuery Admin 角色具备此权限)。
  • JOBS_BY_ORGANIZATION 需要组织的 bigquery.jobs.listAll 权限(Organization OwnerOrganization Admin 角色具备此权限)。请注意,JOBS_BY_ORGANIZATION 仅由已指定 Google Cloud 组织的用户使用。

架构

查询 INFORMATION_SCHEMA.JOBS_BY_* 视图时,每个 BigQuery 作业都会有一行对应的查询结果。

  • INFORMATION_SCHEMA.JOBS_BY_USER 仅返回当前项目当前用户提交的作业。
  • INFORMATION_SCHEMA.JOBS_BY_PROJECT 返回当前项目中提交的所有作业。
  • INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION 返回在与当前项目关联的组织中提交的所有作业。

INFORMATION_SCHEMA.JOBS_BY_* 视图具有如下架构:

列名 数据类型
creation_time TIMESTAMP (分区列)此作业的创建时间
project_id STRING (聚簇列)项目的 ID。
project_number INTEGER 项目编号
user_email STRING (聚簇列)运行作业的用户的电子邮件地址或服务帐号。
job_id STRING 作业的 ID。例如:“bqxjob_1234”
job_type STRING 作业的类型。可以是 QUERYLOADEXTRACTCOPYUNKNOWN
statement_type STRING 查询语句(如果有效的话)的类型。例如 SELECTINSERTUPDATEDELETE
start_time TIMESTAMP 此作业的开始时间
end_time TIMESTAMP 此作业的结束时间
query STRING SQL 查询文本。注意:JOBS_BY_ORGANIZATION 视图没有 query 列。
state STRING 作业的运行状态。有效状态包括 PENDINGRUNNINGDONE
reservation_id STRING 分配给此作业的主要预留的名称(如果适用的话)。
total_bytes_processed INTEGER 作业处理的字节总数
total_slot_ms INTEGER 整个作业持续时间内的的槽毫秒数。
error_result RECORD 错误详情(如果有的话)。
cache_hit BOOLEAN 查询结果是否已缓存。
destination_table RECORD 结果的目标表(如果有的话)。
referenced_tables RECORD 作业引用的表数组。
labels RECORD 应用于作业的标签数组。
timeline RECORD 作业的查询时间轴。包含查询执行情况的快照。
job_stages RECORD 作业的查询阶段

数据保留

目前,只有最近 180 天的作业历史记录会保留在 INFORMATION_SCHEMA 作业视图中。

区域性

BigQuery 的 INFORMATION_SCHEMA 作业视图已区域化。如需查询这些视图,您必须添加以下格式的相关区域名称作为前缀:`region-region_name`.INFORMATION_SCHEMA.view

例如:

  • 如需查询“美国多区域”中的数据,请使用 `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  • 如需查询“欧盟多区域”中的数据,请使用 `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  • 如需查询 asia-northeast1 区域中的数据,请使用 `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT

您可以在此处找到可用区域的列表

示例

示例 1:平均槽利用率

以下示例会计算指定项目在过去 7 天内所有查询的平均槽利用率。

如需对非默认项目运行查询,请按 `project_id`.`region-region_name`.INFORMATION_SCHEMA.view 格式添加项目 ID,例如 `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT

要运行查询,请执行以下操作:

控制台

  1. 在 Cloud Console 中,打开 BigQuery 网页界面。

    转到 Cloud Console

  2. 查询编辑器框中,输入以下标准 SQL 查询。 INFORMATION_SCHEMA 要求使用标准 SQL 语法。标准 SQL 是 Cloud Console 中的默认语法。

    SELECT
     SUM(total_slot_ms) / (1000*60*60*24*7) AS avg_slots
    FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
    WHERE
     job_type = "QUERY"
     AND end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP()
    
  3. 点击运行

CLI

使用 query 命令并通过 --nouse_legacy_sql--use_legacy_sql=false 标志指定标准 SQL 语法。INFORMATION_SCHEMA 查询要求使用标准 SQL 语法。

如需运行查询,请输入以下命令:

bq query --nouse_legacy_sql \
'SELECT
   SUM(total_slot_ms) / (1000*60*60*24*7) AS avg_slots
 FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
 WHERE
   job_type = "QUERY"
   AND end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP()'

结果应如下所示:

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

您可以使用 WHERE reservation_id = "…" 检查特定预留的使用率。这可能有助于确定预留在一段时间内的使用百分比。

示例 2:加载作业历史记录

以下示例会列出针对指定项目提交批量加载作业的所有用户或服务帐号。由于未指定时间边界,因此该查询会扫描所有可用历史记录(例如,过去 30 天)。

如需对非默认项目运行查询,请按 `project_id`.`region-region_name`.INFORMATION_SCHEMA.view 格式添加项目 ID,例如 `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT

要运行查询,请执行以下操作:

控制台

  1. 在 Cloud Console 中,打开 BigQuery 网页界面。

    转到 Cloud Console

  2. 查询编辑器框中输入以下标准 SQL 查询。 INFORMATION_SCHEMA 要求使用标准 SQL 语法。标准 SQL 是 Cloud Console 中的默认语法。

    SELECT
     DISTINCT(user_email) AS user
    FROM
     `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
    WHERE
     job_type = "LOAD"
    
  3. 点击运行

CLI

使用 query 命令并通过 --nouse_legacy_sql--use_legacy_sql=false 标志指定标准 SQL 语法。INFORMATION_SCHEMA 查询要求使用标准 SQL 语法。

如需运行查询,请输入以下命令:

bq query --nouse_legacy_sql \
'SELECT
   DISTINCT(user_email) AS user
 FROM
   `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
 WHERE
   job_type = "LOAD"'

结果应如下所示:

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

示例 3:费用最高的作业

以下示例演示了如何查找当天在组织中扫描字节数排名前五的作业。您可以根据 statement_type 进一步过滤,以查询其他信息,例如加载、导出和查询。

如需对非默认项目运行查询,请按 `project_id`.`region-region_name`.INFORMATION_SCHEMA.view 格式添加项目 ID,例如 `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT

要运行查询,请执行以下操作:

控制台

  1. 在 Cloud Console 中,打开 BigQuery 网页界面。

    转到 Cloud Console

  2. 查询编辑器框中,输入以下标准 SQL 查询。 INFORMATION_SCHEMA 要求使用标准 SQL 语法。标准 SQL 是 Cloud Console 中的默认语法。

    SELECT
     job_id,
     user_email,
     total_bytes_processed
    FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
    WHERE EXTRACT(DATE FROM  creation_time) = current_date()
    ORDER BY total_bytes_processed DESC
    LIMIT 5
    
  3. 点击运行

CLI

使用 query 命令并通过 --nouse_legacy_sql--use_legacy_sql=false 标志指定标准 SQL 语法。INFORMATION_SCHEMA 查询要求使用标准 SQL 语法。

如需运行查询,请输入以下命令:

bq query --nouse_legacy_sql \
'SELECT
   job_id,
   user_email,
   total_bytes_processed
 FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
 WHERE EXTRACT(DATE FROM  creation_time) = current_date()
 ORDER BY total_bytes_processed DESC
 LIMIT 5'

结果应如下所示:

  +--------------+--------------+---------------------------+
  | job_id       |  user_email  |  total_bytes_processed    |
  +--------------+--------------+---------------------------+
  | bquxjob_1    |  abc@xyz.com |    999999                 |
  +--------------+--------------+---------------------------+
  | bquxjob_2    |  def@xyz.com |    888888                 |
  +--------------+--------------+---------------------------+
  | bquxjob_3    |  ghi@xyz.com |    777777                 |
  +--------------+--------------+---------------------------+
  | bquxjob_4    |  jkl@xyz.com |    666666                 |
  +--------------+--------------+---------------------------+
  | bquxjob_5    |  mno@xyz.com |    555555                 |
  +--------------+--------------+---------------------------+
  

示例 4:待处理和正在运行的作业

以下示例列出了由当前用户启动且处于待处理或正在运行状态的作业。

如需对非默认项目运行查询,请按 `project_id`.`region-region_name`.INFORMATION_SCHEMA.view 格式添加项目 ID,例如 `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT

要运行查询,请执行以下操作:

控制台

  1. 在 Cloud Console 中,打开 BigQuery 网页界面。

    转到 Cloud Console

  2. 查询编辑器框中,输入以下标准 SQL 查询。 INFORMATION_SCHEMA 要求使用标准 SQL 语法。标准 SQL 是 Cloud Console 中的默认语法。

    SELECT
     job_id,
     creation_time,
     query
    FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
    WHERE state != "DONE"
    
  3. 点击运行

CLI

使用 query 命令并通过 --nouse_legacy_sql--use_legacy_sql=false 标志指定标准 SQL 语法。INFORMATION_SCHEMA 查询要求使用标准 SQL 语法。

如需运行查询,请输入以下命令:

bq query --nouse_legacy_sql \
'SELECT
   job_id,
   creation_time,
   query
 FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
 WHERE state != "DONE"'

结果应如下所示:

  +--------------+--------------+----------------------------------------------+
  | job_id       |  creation_time            |  query                          |
  +--------------+--------------+----------------------------------------------+
  | bquxjob_1    |  2019-10-10 00:00:00 UTC  |  SELECT ... FROM dataset.table1 |
  +--------------+--------------+----------------------------------------------+
  | bquxjob_2    |  2019-10-10 00:00:01 UTC  |  SELECT ... FROM dataset.table2 |
  +--------------+--------------+----------------------------------------------+
  | bquxjob_3    |  2019-10-10 00:00:02 UTC  |  SELECT ... FROM dataset.table3 |
  +--------------+--------------+----------------------------------------------+
  | bquxjob_4    |  2019-10-10 00:00:03 UTC  |  SELECT ... FROM dataset.table4 |
  +--------------+--------------+----------------------------------------------+
  | bquxjob_5    |  2019-10-10 00:00:04 UTC  |  SELECT ... FROM dataset.table5 |
  +--------------+--------------+----------------------------------------------+
  

后续步骤