使用 INFORMATION_SCHEMA 获取作业元数据

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

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

所需权限

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

JOBS_BY_USER 需要项目的 bigquery.jobs.list 权限（Project Viewer 和 BigQuery User 角色具备此权限）。
JOBS_BY_PROJECT 需要项目的 bigquery.jobs.listAll 权限（Project Owner 和 BigQuery Admin 角色具备此权限）。
JOBS_BY_ORGANIZATION 需要组织的 bigquery.jobs.listAll 权限（Organization Owner 和 Organization 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`	作业的类型。可以是 `QUERY`、`LOAD`、`EXTRACT`、`COPY` 或 `UNKNOWN`。
`statement_type`	`STRING`	查询语句（如果有效的话）的类型。例如 `SELECT`、`INSERT`、`UPDATE` 或 `DELETE`。
`start_time`	`TIMESTAMP`	此作业的开始时间
`end_time`	`TIMESTAMP`	此作业的结束时间
`query`	`STRING`	SQL 查询文本。注意：`JOBS_BY_ORGANIZATION` 视图没有 `query` 列。
`state`	`STRING`	作业的运行状态。有效状态包括 `PENDING`、`RUNNING`、`DONE`。
`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。

要运行查询，请执行以下操作：

控制台

在 Cloud Console 中，打开 BigQuery 网页界面。

转到 Cloud Console

在查询编辑器框中，输入以下标准 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()

点击运行。

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。

要运行查询，请执行以下操作：

控制台

在 Cloud Console 中，打开 BigQuery 网页界面。

转到 Cloud Console
在查询编辑器框中输入以下标准 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"
```
注意：INFORMATION_SCHEMA 视图名称区分大小写。
点击运行。

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。

要运行查询，请执行以下操作：

控制台

在 Cloud Console 中，打开 BigQuery 网页界面。

转到 Cloud Console
在查询编辑器框中，输入以下标准 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
```
注意：INFORMATION_SCHEMA 视图名称区分大小写。
点击运行。

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。

要运行查询，请执行以下操作：

控制台

在 Cloud Console 中，打开 BigQuery 网页界面。

转到 Cloud Console
在查询编辑器框中，输入以下标准 SQL 查询。 INFORMATION_SCHEMA 要求使用标准 SQL 语法。标准 SQL 是 Cloud Console 中的默认语法。
```
SELECT
 job_id,
 creation_time,
 query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
WHERE state != "DONE"
```
注意：INFORMATION_SCHEMA 视图名称区分大小写。
点击运行。

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 |
  +--------------+--------------+----------------------------------------------+

后续步骤

如需查看 INFORMATION_SCHEMA 的概览，请转到 BigQuery INFORMATION_SCHEMA 简介。
了解如何使用 INFORMATION_SCHEMA 获取数据集元数据。
了解如何使用 INFORMATION_SCHEMA 获取表元数据。
了解如何使用 INFORMATION_SCHEMA 获取视图元数据。