使用 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_FOLDER 需要父级文件夹的 bigquery.jobs.listAll 权限（文件夹的 Folder Admin 和 BigQuery Admin 具备此权限）。
JOBS_BY_ORGANIZATION 需要组织的 bigquery.jobs.listAll 权限（Organization bigquery.resourceAdmin、Organization Owner 和 Organization Admin 角色具备此权限）。请注意，JOBS_BY_ORGANIZATION 仅由已指定 Google Cloud 组织的用户使用。

如需详细了解具体的 BigQuery 权限，请参阅角色和权限。

架构

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

INFORMATION_SCHEMA.JOBS_BY_USER 仅返回当前项目当前用户提交的作业。
INFORMATION_SCHEMA.JOBS_BY_PROJECT 返回当前项目中提交的所有作业。
INFORMATION_SCHEMA.JOBS_BY_FOLDER 返回当前项目父文件夹中提交的所有作业，包括其子文件夹中的作业。
INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION 返回在与当前项目关联的组织中提交的所有作业。

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

列名	数据类型	值
`creation_time`	`TIMESTAMP`	（分区列）此作业的创建时间。分区基于此时间戳的世界协调时间 (UTC)。
`project_id`	`STRING`	（聚簇列）项目的 ID。
`project_number`	`INTEGER`	项目编号。
`folder_numbers`	`RECORD`	项目祖先实体中文件夹（这些文件夹从距离项目最近的叶级文件夹开始依次排列）的 Google 帐号和 ID 管理 (GAIA) ID。此列仅在 `JOBS_BY_FOLDER` 中填充。
`user_email`	`STRING`	（聚簇列）运行作业的用户的电子邮件地址或服务帐号。
`job_id`	`STRING`	作业的 ID。例如 `bquxjob_1234`。
`job_type`	`STRING`	作业的类型。可以是 `QUERY`、`LOAD`、`EXTRACT`、`COPY` 或 `null`。作业类型 `null` 表示内部作业，例如脚本作业语句评估或具体化视图刷新。
`statement_type`	`STRING`	查询语句（如果有效的话）的类型。例如 `SELECT`、`INSERT`、`UPDATE` 或 `DELETE`。
`priority`	`STRING`	此作业的优先级。
`start_time`	`TIMESTAMP`	此作业的开始时间。
`end_time`	`TIMESTAMP`	此作业的结束时间。
`query`	`STRING`	SQL 查询文本。注意：只有 `JOBS_BY_PROJECT` 视图具有 `query` 列。
`state`	`STRING`	作业的运行状态。有效状态包括 `PENDING`、`RUNNING`、`DONE`。
`reservation_id`	`STRING`	分配给此作业的主要预留的名称（如果适用的话）。如果作业在已分配给预留的项目中运行，其采用以下格式：`reservation-admin-project:reservation-location.reservation-name`。
`total_bytes_processed`	`INTEGER`	作业处理的字节总数。
`total_slot_ms`	`INTEGER`	整个作业持续时间内的的槽毫秒数。
`error_result`	`RECORD`	错误的详细信息（如有），为 `ErrorProto`。
`cache_hit`	`BOOLEAN`	查询此作业的查询结果是否来自缓存。
`destination_table`	`RECORD`	结果的目标表（如有）。
`referenced_tables`	`RECORD`	作业引用的表数组。
`labels`	`RECORD`	作为 `key` 和 `value` 字符串应用于作业的标签数组。
`timeline`	`RECORD`	作业的查询时间轴。包含查询执行情况的快照。
`job_stages`	`RECORD`	作业的查询阶段。
`total_bytes_billed`	`INTEGER`	如果项目配置为使用按需价格，则此字段包含作业的总计费字节数。如果项目配置为使用固定价格，则您无需为字节付费，并且此字段仅供参考。

数据保留

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

区域性

BigQuery 的 INFORMATION_SCHEMA 作业视图已地区化。如需查询这些视图，您必须使用地区限定符。

示例

示例 1：平均槽利用率

以下示例会计算指定项目在过去 7 天内所有查询的平均槽利用率。请注意，此计算对于在一周内槽使用量一致的项目最为准确。如果项目的槽使用量不一致，则此数字可能会低于预期。

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

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

控制台

在 Cloud Console 中，打开 BigQuery 页面。

转到 BigQuery 页面

在查询编辑器框中，输入以下标准 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
 -- 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 end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP()

点击运行。

bq

使用 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
   -- 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)

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

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

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

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

控制台

在 Cloud Console 中，打开 BigQuery 页面。

转到 BigQuery 页面
在查询编辑器框中输入以下标准 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 视图名称区分大小写。
点击运行。

bq

使用 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 页面。

转到 BigQuery 页面
在查询编辑器框中，输入以下标准 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 视图名称区分大小写。
点击运行。

bq

使用 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 页面。

转到 BigQuery 页面
在查询编辑器框中，输入以下标准 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 视图名称区分大小写。
点击运行。

bq

使用 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 获取数据集元数据。
了解如何使用 INFORMATION_SCHEMA 获取表元数据。
了解如何使用 INFORMATION_SCHEMA 获取视图元数据。