创建和使用提取时间分区表

本文档介绍如何在 BigQuery 中创建和使用按数据提取时间分区的表（在下文中称为“提取时间分区表”）。如需了解日期/时间戳分区表，请参阅创建和使用日期/时间戳分区表。如需了解整数范围分区表，请参阅创建和使用整数范围分区表。

创建提取时间分区表后，您可以执行以下操作：

• 控制对表数据的访问权限
• 获取有关分区表的信息
• 列出数据集中的分区表
• 使用元表获取分区表元数据

如需详细了解如何管理分区表，包括更新分区表属性、复制分区表和删除分区表，请参阅管理分区表。

创建提取时间分区表

通过以下方法可以在 BigQuery 中创建提取时间分区表：

• 使用带有 partition_expression 的 DDL CREATE TABLE 语句
• 使用 Cloud Console 或经典版 BigQuery 网页界面手动创建
• 使用 bq 命令行工具的 bq mk 命令
• 调用 tables.insert API 方法以编程方式创建
• 使用客户端库
• 基于查询结果创建
• 在加载数据时创建
• 将日期分片表转换为分区表

表命名

在 BigQuery 中创建表时，数据集中每个表的名称都必须具有唯一性。表名称可：

• 包含最多 1024 个字符
• 包含字母（大写或小写）、数字和下划线

所需权限

如需创建表，您至少必须具有以下权限：

• bigquery.tables.create：创建表的权限
• bigquery.tables.updateData：使用加载作业、查询作业或复制作业向表中写入数据
• bigquery.jobs.create：用于运行向表中写入数据的查询作业、加载作业或复制作业

如需访问向表中写入的数据，可能还需要其他权限，例如 bigquery.tables.getData。

以下预定义 Cloud IAM 角色同时具有 bigquery.tables.create 和 bigquery.tables.updateData 权限：

• bigquery.dataEditor
• bigquery.dataOwner
• bigquery.admin

以下预定义的 Cloud IAM 角色具有 bigquery.jobs.create 权限：

• bigquery.user
• bigquery.jobUser
• bigquery.admin

此外，如果用户具有 bigquery.datasets.create 权限，则当该用户创建数据集时，系统会为其授予该数据集的 bigquery.dataOwner 访问权限。借助 bigquery.dataOwner 访问权限，用户可以在数据集内创建和更新表。

如需详细了解 BigQuery 中的 Cloud IAM 角色和权限，请参阅预定义的角色和权限。

创建具有架构定义的空提取时间分区表

在创建具有架构定义的空时间分区表时，可执行以下操作：

• 使用 Cloud Console 或经典版 BigQuery 网页界面输入架构
• 使用 bq 命令行工具以内嵌方式提供架构
• 使用 bq 命令行工具提交 JSON 架构文件
• 调用 API 的 tables.insert 方法时，在表资源中提供架构

如需详细了解如何指定表架构，请参阅指定架构。

创建时间分区表后，您可以执行以下操作：

• 向其中加载数据
• 向其中写入查询结果
• 向其中复制数据

如需创建具有架构定义的空时间分区表，请执行以下操作：

控制台

1. 从导航面板的资源部分选择数据集。 点击窗口右侧的创建表。
2. 在创建表页面的来源部分，选择创建空表。

3. 在创建表页面的目标部分，执行以下操作：

   • 在数据集名称部分，选择相应数据集。

   • 在表名称字段中，输入您要在 BigQuery 中创建的表的名称。

   • 确认表类型设置为原生表。

4. 在架构部分中，输入架构定义。

   • 通过以下方式，手动输入架构信息：

     • 启用以文本形式修改，并以 JSON 数组格式输入表架构。

       注意：您可以在 bq 命令行工具中输入以下命令，以 JSON 格式查看现有表架构：bq show --format=prettyjson dataset.table。

     • 使用添加字段手动输入架构。

5. 在分区和聚簇设置部分中，执行以下操作：

   • 在分区部分，点击不进行分区并选择按提取时间分区。

6. （可选）在分区过滤条件中，点击需要分区过滤条件框，以要求用户添加 WHERE 子句来指定要查询的分区。要求分区过滤条件有可能减少费用并提高性能。如需了解详情，请参阅查询分区表。

7. （可选）点击高级选项，然后在加密部分，点击客户管理的密钥以使用 Cloud Key Management Service 密钥。如果保留 Google 管理的密钥设置，BigQuery 将对静态数据进行加密。

8. 点击创建表。

注意：使用网页界面创建空的分区表时，不能添加标签、说明、表过期时间或分区过期时间。当您使用 bq 命令行工具或 API 创建表时，您可以添加这些可选属性。

创建表后，您可以更新分区表的表过期时间、说明和标签。在使用 BigQuery 网页界面创建表后，无法添加分区过期时间。

经典版界面

1. 在导航窗格中，点击数据集名称旁边的向下箭头图标 ，然后点击 Create new table。

2. 在 Create Table 页面上的 Source Data 部分中，点击 Create empty table。

3. 在 Create Table 页面的 Destination Table 部分，执行以下操作：

   • 对于 Table name，选择相应的数据集，然后在表名称字段中输入要创建的表的名称。
   • 确认表类型设置为原生表。

4. 在 Schema 部分中，手动输入架构定义。

   • 可通过以下方式手动输入架构信息：

     • 点击 Edit as text，并以 JSON 数组格式输入表架构：

       

       注意：您可以在 bq 命令行工具中输入以下命令，以 JSON 格式查看现有表架构：bq show --schema
--format=prettyjson
project_id:dataset.table。

     • 使用 Add Field 输入架构：

       

5. 在 Options 部分，执行以下操作：

   • 对于 Partitioning，点击 None，并选择 Day。
   • 对于 Encryption Type，保留 Default 选项。此属性用于 CMEK（客户管理的加密密钥）。默认情况下，BigQuery 会对以静态方式存储的客户内容进行加密。

6. 点击创建表。

注意：使用网页界面创建空的分区表时，不能添加标签、说明、表过期时间或分区过期时间。当您使用 bq 命令行工具或 API 创建表时，您可以添加这些可选属性。

创建表后，您可以更新分区表的表过期时间、说明和标签。在使用 BigQuery 网页界面创建表后，无法添加分区过期时间。

bq

将 mk 命令与 --table 标志（或 -t 快捷方式）和 -- time_partitioning_type 标志（根据您的分区类型设置为 DAY 或 HOUR）结合使用。您可以内嵌方式或通过 JSON 架构文件提供表的架构定义。可选参数包括 --expiration、-- description、--time_partitioning_expiration、--destination_kms_key、--label。如果您要在非默认项目中创建表，请按以下格式将相应项目 ID 添加到数据集中：project_id:dataset。

本文未演示 --destination_kms_key。如需详细了解如何使用此标志，请参阅使用 Cloud KMS 密钥保护数据。

输入以下命令可使用架构定义来创建空的提取时间分区表：

bq mk --table \
--expiration integer1 \
--time_partitioning_type=unit_time \
--time_partitioning_expiration integer2 \
--description "description" \
-label key:value,key:value \
project_id:dataset.table \
schema

其中：

• integer1 是表的默认生命周期（以秒为单位）。 最小值为 3600 秒（一小时）。到期时间以当前世界协调时间 (UTC) 加上这个整数值为准。如果您在创建分区表时设置了该表的过期时间，则系统会忽略数据集的默认表过期时间设置。如果设置了此值，系统将在指定的时间后删除表和所有分区。
• unit_time 为 DAY 或 HOUR，具体取决于您的分区类型。如果未指定 time_partitioning_type，则默认为 DAY。
• integer2 是表分区的默认生命周期（以秒为单位）。它没有最小值。过期时间以分区的日期加上这个整数值为准。分区过期时间虽然与表的过期时间无关，但它不会替换表的过期时间。如果设置的分区过期时间超过了表的过期时间，则以表过期时间为准。
• description 是加英文引号的表说明。
• key:value 是代表标签的键值对。您可以使用英文逗号分隔列表输入多个标签。
• project_id 是您的项目 ID。
• dataset 是您的项目中的数据集。
• table 是您创建的时间分区表的名称。
• schema 是采用 column:data_type,column:data_type 格式的内嵌架构定义，或者是本地机器上 JSON 架构文件的路径。

使用命令行指定架构时，您不能添加 RECORD (STRUCT) 类型和列说明，也不能指定列的模式。所有模式均默认为 NULLABLE。如需添加说明、模式和 RECORD 类型，请改为提供 JSON 架构文件。

示例：

输入以下命令可在默认项目的 mydataset 中创建名为 mytable 的提取时间分区表。分区类型设置为 DAY，分区过期时间设置为 259200 秒（3 天），表过期时间设置为 2592000（1 个月/30 天），说明设置为 This is my time partitioned table，标签设置为 organization:development。该命令使用 -t 快捷方式代替 --table。该架构以内嵌方式指定为 qtr:STRING,sales:FLOAT,year:STRING。

bq mk -t \
--expiration 2592000 \
--time_partitioning_type=DAY \
--time_partitioning_expiration 259200 \
--description "This is my time partitioned table" \
--label organization:development \
mydataset.mytable \
qtr:STRING,sales:FLOAT,year:STRING

输入以下命令可在 myotherproject（非默认项目）中创建名为 mytable 的时间分区表。分区类型设置为 DAY，分区过期时间设置为 259200 秒（3 天），说明设置为 This is my time partitioned table，标签设置为 organization:development。该命令使用 -t 快捷方式代替 --table，且没有指定表过期时间。如果数据集具有默认的表过期时间，则系统会应用该过期时间。如果数据集没有默认的表过期时间，则该表将永不过期，但相应分区会在 3 天后过期。架构文件的路径为 /tmp/myschema.json。

bq mk -t \
--time_partitioning_type=DAY \
--time_partitioning_expiration 259200 \
--description "This is my time partitioned table" \
--label organization:development \
myotherproject:mydataset.mytable \
/tmp/myschema.json

创建表后，您可以更新提取时间分区表的表过期时间、分区过期时间、说明和标签。

API

使用指定了 timePartitioning 属性和 schema 属性的已定义表资源调用 tables.insert 方法。

根据查询结果创建提取时间分区表

如需根据查询结果创建分区表，请将结果写入新目标表中。可通过查询分区表或非分区表来创建新的分区表。不能使用查询结果将现有表更改为分区表。

如需详细了解如何查询时间分区表，请参阅查询分区表。

控制台

使用 Console BigQuery 网页界面查询数据时，您无法为目标表指定分区选项。

经典版界面

使用经典版 BigQuery 网页界面查询数据时，您无法为目标表指定分区选项。

bq

输入 bq query 命令，指定 --destination_table 标志可基于查询结果创建一个永久表，指定 --time_partitioning_type 标志可创建提取时间分区目标表。DAY 和 HOUR 是受支持的 --time_partitioning_type 值，具体取决于分区的粒度。

指定 use_legacy_sql=false 标志可使用标准 SQL 语法。要将查询结果写入非默认项目中的某个表，请按以下格式将相应项目 ID 添加到数据集名称：project_id:dataset。

（可选）提供 --location 标志并将其值设置为您的位置。

输入以下命令可根据查询结果创建一个新的提取时间分区目标表：

bq --location=location query \
--destination_table project_id:dataset.table \
--time_partitioning_type=unit_time \
--use_legacy_sql=false \
'query'

其中：

• location 是位置的名称。--location 是可选标志。例如，如果您在东京地区使用 BigQuery，可将该标志的值设置为 asia-northeast1。您可以使用 .bigqueryrc 文件设置位置的默认值。
• project_id 是您的项目 ID。
• dataset 是将包含新时间分区表的数据集的名称。
• table 是您使用查询结果创建的时间分区表的名称。
  • unit_time 为 DAY 或 HOUR，具体取决于您的分区类型。如果未指定 time_partitioning_type，则默认为 DAY。
• query 是使用标准 SQL 语法的查询。

示例：

注意：以下示例可查询美国境内的公共数据集。由于公共数据集存储在美国的多地区位置，因此您的目标表所属的数据集也必须位于美国。您无法查询一个位置的数据集而将结果写入另一个位置的目标表。

输入以下命令可将查询结果写入 mydataset 中名为 mytable 的提取时间每日分区目标表。该数据集属于默认项目。该查询从非分区表（美国姓名数据公共数据集）中检索数据。

bq query \
--destination_table mydataset.mytable \
--time_partitioning_type=DAY \
--use_legacy_sql=false \
'SELECT
   name,
   number
 FROM
   `bigquery-public-data`.usa_names.usa_1910_current
 WHERE
   gender = "M"
 ORDER BY
   number DESC'

输入以下命令可将查询结果写入 mydataset 中名为 mytable 的提取时间分区目标表（按数据提取日期分区）。该数据集属于 myotherproject，而非默认项目。该查询从非分区表（美国姓名数据公共数据集）中检索数据。

bq query \
--destination_table myotherproject:mydataset.mytable \
--time_partitioning_type=DAY \
--use_legacy_sql=false \
'SELECT
   name,
   number
 FROM
   `bigquery-public-data`.usa_names.usa_1910_current
 WHERE
   gender = "M"
 ORDER BY
   number DESC'

API

如需将查询结果保存到永久提取时间分区表中，请调用 jobs.insert 方法，配置一项 query 作业，并为 destinationTable 和 timePartitioning 属性添加值。

在加载数据时创建提取时间分区表

在将数据加载到新表中时，可以通过指定分区选项来创建提取时间分区表。您无需在加载数据前专门创建空的分区表，而是可以同时创建分区表并加载数据。

在将数据加载到 BigQuery 中时，可以提供表架构，或者对于支持的数据格式，可以使用架构自动检测。

通过分区装饰器，您可以将数据加载到特定分区中。如需针对时区进行调整，请根据您的首选时区使用分区装饰器将数据加载到分区中。例如，如果您使用的是太平洋标准时间 (PST)，请使用以下相应分区装饰器将太平洋标准时间 2016 年 5 月 1 日生成的所有数据加载到该日期的分区中：

table_name$20160501

如需详细了解如何加载数据，请参阅将数据加载到 BigQuery 简介。

将日期分片表转换为提取时间分区表

如果您以前创建了日期分片表，则可以使用 bq 命令行工具中的 partition 命令将整套相关表转换为单个提取时间分区表。日期分片表必须使用以下命名惯例：table_YYYYMMDD。 例如，mytable_20160101、…、mytable_20160331。

可选参数包括 --time_partitioning_expiration、--location、--time_partitioning_type。如果未指定 --time_partitioning_type，则默认值为 DAY。如果源表或目标表在非默认项目中，请按以下格式将相应项目 ID 添加到数据集名称中：project_id:dataset。

注意：partition 命令不支持 --label、--expiration、--description 标志。在创建分区表后，您可以为其添加标签、表过期时间和说明。

输入以下命令可将一系列日期分片表转换为单个提取时间分区表（按日期分区）：

bq --location=location partition \
--time_partitioning_type=DAY \
--time_partitioning_expiration integer \
project_id:dataset.source_table_ \
project_id:dataset.destination_table

其中：

• location 是位置的名称。--location 是可选标志。例如，如果您在东京地区使用 BigQuery，请将该标志的值设置为 asia-northeast1。您可以使用 .bigqueryrc 文件设置位置的默认值。
• integer 是表分区的默认生命周期（以秒为单位），它没有最小值。过期时间以分区的世界协调时间 (UTC) 日期加上这个整数值为准。分区过期时间虽然与表的过期时间无关，但它不会替换表的过期时间。如果设置的分区过期时间超过了表的过期时间，则以表过期时间为准。
• project_id 是您的项目 ID。
• dataset 是您的项目中的数据集。
• sourcetable 是日期分片表的前缀。
• destination_table 是您创建的分区表的名称。

示例：

输入以下命令可在默认项目的 mydataset 中创建名为 mytable_partitioned 的提取时间分区表。该表将按日期分区，分区过期时间设置为 259200 秒（3 天）。日期分片源表的前缀为 sourcetable_。源表也属于默认项目。

bq partition \
--time_partitioning_type=DAY \
--time_partitioning_expiration 259200 \
mydataset.sourcetable_ \
mydataset.mytable_partitioned

运行该命令时，系统会创建一个根据分片表生成分区的复制作业。例如，如果日期分片表为 sourcetable_20180126 和 sourcetable_20180127，则复制作业将创建以下分区：mydataset.mytable_partitioned$20180126 和 mydataset.mytable_partitioned$20180127。

输入以下命令可在 mydataset 中创建名为 mytable_partitioned 的提取时间分区表。mydataset 属于 myotherproject，而非默认项目。该表将按日期分区，分区过期时间设置为 86400 秒（1 天）。日期分片源表的前缀为 sourcetable_。源表属于默认项目。

bq partition \
--time_partitioning_type=DAY \
--time_partitioning_expiration 86400 \
mydataset.sourcetable_ \
myotherproject:mydataset.mytable_partitioned

输入以下命令可在默认项目的 mydataset 中创建名为 mytable_partitioned 的提取时间分区表。mydataset 创建于 asia-northeast1 地区。该表将按日期分区，分区过期时间设置为 259200 秒（3 天）。日期分片源表的前缀为 sourcetable_。源表也属于默认项目。

bq --location=asia-northeast1 partition \
--time_partitioning_type=DAY \
--time_partitioning_expiration 259200 \
mydataset.sourcetable_ \
mydataset.mytable_partitioned

控制对时间分区表的访问权限

如需配置对表和视图的访问权限，您可以在以下级层为实体授予 IAM 角色，这些级层按照允许的资源范围从大到小依次排列：

• Google Cloud 资源层次结构中的较高级层，例如项目、文件夹或组织级层
• 数据集级层
• 表/视图级层

对受 Cloud IAM 保护的任何资源的访问权限具有附加性。例如，如果某个实体没有项目等较高级层的访问权限，您可以在数据集级层向该实体授予访问权限，然后该实体便将有权访问该数据集中的表和视图。同样，如果实体没有较高级层或数据集级层的访问权限，您可以在表或视图级层向该实体授予访问权限。

在 Google Cloud 资源层次结构中的较高级层（如项目、文件夹或组织级层）授予 Cloud IAM 角色可为实体授予对一组广泛资源的访问权限。例如，在项目级层为实体授予角色会为该实体提供整个项目中所有数据集的访问权限。

在数据集级层授予角色可指定允许实体对该特定数据集中的表和视图执行的操作，即使实体没有更高级层的访问权限也可执行这些操作。如需详细了解如何配置数据集级层的访问权限控制，请参阅控制对数据集的访问权限。

在表或视图级层授予角色可指定允许实体对特定表和视图执行的操作，即使实体没有更高级层的访问权限也可执行这些操作。如需了解如何配置表级层的访问权限控制，请参阅控制对表和视图的访问权限。

您还可以创建 Cloud IAM 自定义角色。如果创建自定义角色，则您授予的权限取决于您希望实体能够执行的具体操作。

您无法对受 Cloud IAM 保护的任何资源设置“拒绝”权限。

如需详细了解角色和权限，请参阅：

• Cloud IAM 文档中的了解角色
• BigQuery 预定义的角色和权限
• 控制对数据集的访问
• 控制对表和视图的访问权限
• 通过 BigQuery 列级层安全性限制访问权限

使用提取时间分区表

获取有关提取时间分区表的信息

您可以通过以下方式来获取有关表的信息：

• 使用 Cloud Console 或经典版 BigQuery 网页界面
• 在 bq 命令行工具中使用 bq show 命令
• 调用 tables.get API 方法
• 使用客户端库

所需权限

如需获取有关表的信息，您至少必须获得 bigquery.tables.get 权限。以下预定义 Cloud IAM 角色可提供 bigquery.tables.get 权限：

• bigquery.metadataViewer
• bigquery.dataViewer
• bigquery.dataOwner
• bigquery.dataEditor
• bigquery.admin

此外，如果用户具有 bigquery.datasets.create 权限，则当该用户创建数据集时，系统会为其授予该数据集的 bigquery.dataOwner 访问权限。借助 bigquery.dataOwner 访问权限，用户可以获取数据集内表的信息。

如需详细了解 BigQuery 中的 Cloud IAM 角色和权限，请参阅预定义的角色和权限。

获取提取时间分区表信息

要查看有关提取时间分区表的信息，请执行以下操作：

控制台

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

2. 在导航面板的资源部分，展开项目并选择一个数据集，以列出其中包含的表和视图。

3. 点击表名称。

4. 点击详细信息。此标签页会显示表说明和表信息。

   

5. 点击架构标签页查看表的架构定义。请注意附加的伪列 _PARTITIONTIME，其中包含加载到表中的数据的时间戳（基于日期）。

经典版界面

1. 在导航窗格中，点击数据集左侧的向下箭头图标 以展开该数据集，或双击数据集名称。此时会显示数据集中的表和视图。

2. 点击表名称。

3. 点击 Details。Table Details 页面会显示表说明和表信息。

   

4. 点击架构标签页查看表的架构定义。请注意附加的伪列 _PARTITIONTIME，其中包含加载到表中的数据的时间戳（基于日期）。

   

bq

发出 bq show 命令可显示所有表信息。使用 --schema 标志可仅显示表的架构信息。--format 标志可用于控制输出。

如果您要获取非默认项目中的表信息，请按以下格式将相应项目 ID 添加到数据集：project_id:dataset。

bq show --schema --format=prettyjson <var>project_id:dataset.table</var>

其中：

• project_id 是您的项目 ID。
• dataset 是数据集的名称。
• table 是表的名称。

示例：

输入以下命令可显示 mydataset 中有关 mytable 的所有信息。mydataset 属于默认项目。

bq show --format=prettyjson mydataset.mytable

输入以下命令可显示 mydataset 中有关 mytable 的所有信息。mydataset 在 myotherproject 中，不在默认项目中。

bq show --format=prettyjson myotherproject:mydataset.mytable

输入以下命令可仅显示 mydataset 中有关 mytable 的架构信息。mydataset 属于 myotherproject，而非默认项目。

在显示时间分区表的架构信息时，不会显示 _PARTITIONTIME 伪列。

bq show --schema --format=prettyjson myotherproject:mydataset.mytable

API

调用 bigquery.tables.get 方法并提供所有相关参数。

列出数据集中的提取时间分区表

您可以使用 Cloud Console，经典版 BigQuery 网页界面或 bq 命令行工具中的 bq ls 命令列出数据集（包括分区表）中的表，或通过调用 tables.list API 方法。

所需权限

如需列出数据集中的表，您至少必须具有 bigquery.tables.list 权限。以下预定义 Cloud IAM 角色可提供 bigquery.tables.list 权限：

• bigquery.user
• bigquery.metadataViewer
• bigquery.dataViewer
• bigquery.dataOwner
• bigquery.dataEditor
• bigquery.admin

如需详细了解 BigQuery 中的 IAM 角色和权限，请参阅预定义的角色和权限。

列出分区表

如需列出数据集中的表（包括分区表），请执行以下操作：

控制台

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

2. 在导航面板的资源部分中，展开项目并点击数据集。

3. 滚动列表，查看数据集中的表。表、分区表、模型和视图由不同的图标进行标识。

经典版界面

1. 在网页界面的导航窗格中，点击数据集左侧的向下箭头图标 以展开该数据集，或双击数据集名称。此时会显示数据集中的表和视图。

2. 滚动列表，查看数据集中的表。表和视图由不同的图标进行标识。

   

bq

发出 bq ls 命令。--format 标志可用于控制输出。如果您要列出非默认项目中的表，请按以下格式将相应项目 ID 添加到数据集中：project_id:dataset。

bq ls --format=pretty project_id:dataset

其中：

• project_id 是您的项目 ID。
• dataset 是数据集的名称。

运行该命令时，Type 字段会显示 TABLE 或 VIEW。对于提取时间分区表，Time Partitioning 字段会显示以毫秒为单位的 DAY 和分区过期时间（如果有）。

例如：

+-------------------------+-------+----------------------+--------------------------------+
|         tableId         | Type  |        Labels        | Time Partitioning              |
+-------------------------+-------+----------------------+--------------------------------+
| mytable                 | TABLE | department:shipping  |  DAY (expirationMs: 259200000) |
| myview                  | VIEW  |                      |                                |
+-------------------------+-------+----------------------+--------------------------------+

示例：

输入以下命令可列出默认项目的数据集 mydataset 中的表。

bq ls --format=pretty mydataset

输入以下命令可列出 myotherproject 的数据集 mydataset 中的表。

bq ls --format=pretty myotherproject:mydataset

API

如需使用 API 列出表，请调用 tables.list 方法。

在提取时间分区表中列出分区

您可以通过查询 _PARTITIONTIME 伪列（使用标准 SQL）或 __PARTITIONS_SUMMARY__ 元表（使用旧版 SQL）在提取时间分区表中列出分区。

如需运行查询，您可以使用 Cloud Console、经典版 BigQuery 网页界面或 bq query 命令，也可以调用 jobs.insert 方法并配置 query 作业。

所需权限

如需运行使用 __PARTITIONS_SUMMARY__ 元表或 _PARTITIONTIME 伪列的查询作业，您必须具有 bigquery.jobs.create 权限。以下预定义 Cloud IAM 角色可提供 bigquery.jobs.create 权限：

• bigquery.user
• bigquery.jobUser
• bigquery.admin

您还必须具有 bigquery.tables.getData 权限。以下预定义 Cloud IAM 角色可提供 bigquery.tables.getData 权限：

• bigquery.admin
• bigquery.dataEditor
• bigquery.dataOwner
• bigquery.dataViewer

如需详细了解 BigQuery 中的 Cloud IAM 角色和权限，请参阅预定义的角色和权限。

在提取时间分区表中列出分区

您可以使用标准 SQL（首选）或旧版 SQL 在提取时间分区表中列出分区。如需列出分区，请执行以下操作：

标准 SQL：

控制台

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

2. 点击编写新查询按钮。

3. 在查询编辑器框中输入以下文本，以查询 _PARTITIONTIME 伪列：

   SELECT
     _PARTITIONTIME as pt
   FROM
     `dataset.table`
   GROUP BY 1
   

   其中：

   • dataset 是包含表的数据集。
   • table 是表的名称。

4. （可选）点击更多，然后选择查询设置。

   

5. 点击运行。

经典版界面

1. 转到 BigQuery 网页界面。

   转到 BigQuery 网页界面

2. 点击 Compose query 按钮。

3. 在 New Query 框中输入以下文本，以查询某个表的 _PARTITIONTIME 伪列：

   #standardSQL
   SELECT
     _PARTITIONTIME as pt
   FROM
     `dataset.table`
   GROUP BY 1
   

   其中：

   • dataset 是包含表的数据集。
   • table 是表的名称。

4. 点击 Show Options。

5. （可选）在 Processing Location 部分中，点击 Unspecified 并选择数据的位置。

6. 点击 Run Query。

bq

使用 bq query 命令输入以下查询：

bq --location=location query --use_legacy_sql=false '
SELECT
  _PARTITIONTIME as pt
FROM
  `dataset.table`
GROUP BY 1'

其中：

• location 是位置的名称。--location 是可选标志。例如，如果您在东京地区使用 BigQuery，可将该标志的值设置为 asia-northeast1。您可以使用 .bigqueryrc 文件设置位置的默认值。
• dataset 是包含表的数据集。
• table 是表的名称。

API

调用 jobs.insert 方法，并配置一个用于查询表的 _PARTITIONTIME 伪列的 query 作业。

在作业资源 jobReference 部分的 location 属性中指定您的位置。

旧版 SQL：

控制台

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

2. 点击编写新查询按钮。

3. 在查询编辑器框中输入以下文本，以查询 __PARTITIONS_SUMMARY__ 元表：

   #legacySQL
   SELECT
     partition_id
   FROM
     [dataset.table$__PARTITIONS_SUMMARY__]
   

   其中：

   • dataset 是包含表的数据集。
   • table 是表的名称。

4. 点击更多，然后选择查询设置。

   

5. 在处理位置部分，点击自动选择并选择数据的位置。如果数据位于 US 或 EU 多地区位置，则您可以将处理位置保留设置为“自动选择”。当您的数据位于 US 或 EU 中时，系统会自动检测处理位置。

   

6. 点击运行。

经典版界面

1. 转到 BigQuery 网页界面。

   转到 BigQuery 网页界面

2. 点击 Compose query 按钮。

3. 在 New Query 框中输入以下文本，以查询某个表的 __PARTITIONS_SUMMARY__ 元表：

   #legacySQL
   SELECT
      partition_id
    FROM
      [dataset.table$__PARTITIONS_SUMMARY__]
   

   其中：

   • dataset 是包含表的数据集。
   • table 是表的名称。

4. 点击 Show Options。

5. （可选）在 Processing Location 部分中，点击 Unspecified 并选择数据的位置。

6. 点击 Run Query。

bq

使用 bq query 命令输入以下查询：

bq --location=location query --use_legacy_sql=true '
SELECT
   partition_id
 FROM
   [dataset.table$__PARTITIONS_SUMMARY__]'

其中：

• location 是位置的名称。--location 是可选标志。例如，如果您在东京地区使用 BigQuery，可将该标志的值设置为 asia-northeast1。您可以使用 .bigqueryrc 文件设置位置的默认值。
• dataset 是包含表的数据集。
• table 是表的名称。

API

调用 jobs.insert 方法，并配置一个用于查询 __PARTITIONS_SUMMARY__ 元表的 query 作业。

在作业资源 jobReference 部分的 location 属性中指定您的位置。

使用元表获取表元数据

您可以通过使用称为元表的特殊表来获取有关分区表的信息：元表包含元数据，例如数据集中的表和视图列表。元表是只读的。

使用元表获取分区元数据

__PARTITIONS_SUMMARY__ 元表是一个特殊的表，其内容表示时间分区表中关于分区的元数据。__PARTITIONS_SUMMARY__ 元表为只读表。

如需访问时间分区表中有关分区的元数据，请在查询的 SELECT 语句中使用 __PARTITIONS_SUMMARY__ 元表。您可以通过以下方式运行查询：

• 使用 Cloud Console 或经典版 BigQuery 网页界面
• 使用命令行工具的 bq query 命令
• 调用 jobs.insert API 方法并配置 query 作业
• 使用客户端库

目前，标准 SQL 不支持分区修饰器分隔符 ($)，因此，您不能使用标准 SQL 来查询 __PARTITIONS_SUMMARY__。使用 __PARTITIONS_SUMMARY__ 元表的旧版 SQL 查询如下所示：

SELECT
  column
FROM
  [dataset.table$__PARTITIONS_SUMMARY__]

其中：

• dataset 是您的数据集的名称。
• table 是时间分区表的名称。
• column 是以下值之一：

值	说明
project_id	项目名称。
dataset_id	数据集名称。
table_id	时间分区表的名称。
partition_id	分区的名称（日期）。
creation_time	创建分区的时间，以从 UTC 1970 年 1 月 1 日起计算的毫秒数表示。
last_modified_time	上次修改分区的时间，以从 UTC 1970 年 1 月 1 日起计算的毫秒数表示。

分区元表权限

如需运行使用 __PARTITIONS_SUMMARY__ 元表的查询作业，您至少必须具有 bigquery.jobs.create 权限。以下预定义的 Cloud IAM 角色包含 bigquery.jobs.create 权限：

• bigquery.user
• bigquery.jobUser
• bigquery.admin

您还必须具有 bigquery.tables.getData 权限。以下预定义 Cloud IAM 角色可提供 bigquery.tables.getData 权限：

• bigquery.dataViewer
• bigquery.dataEditor
• bigquery.dataOwner
• bigquery.admin

如需详细了解 BigQuery 中的 Cloud IAM 角色，请参阅访问权限控制。

分区元表示例

以下查询会检索名为 mydataset.mytable 的时间分区表的所有分区元数据。

控制台

#legacySQL
SELECT
  *
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]

经典版界面

#legacySQL
SELECT
  *
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]

bq

bq query --use_legacy_sql=true '
SELECT
  *
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]'

输出如下所示：

+----------------+------------+----------------+--------------+---------------+--------------------+
|   project_id   | dataset_id |    table_id    | partition_id | creation_time | last_modified_time |
+----------------+------------+----------------+--------------+---------------+--------------------+
| myproject      | mydataset  | mytable        | 20160314     | 1517190224120 | 1517190224997      |
| myproject      | mydataset  | mytable        | 20160315     | 1517190224120 | 1517190224997      |
+----------------+------------+----------------+--------------+---------------+--------------------+

以下查询会列出上次修改 mydataset.mytable 中的分区的时间。

控制台

#legacySQL
SELECT
  partition_id,
  last_modified_time
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]

经典版界面

#legacySQL
SELECT
  partition_id,
  last_modified_time
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]

bq

bq query --use_legacy_sql=true '
SELECT
  partition_id,
  last_modified_time
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]'

输出如下所示：

+--------------+--------------------+
| partition_id | last_modified_time |
+--------------+--------------------+
| 20160102     |      1471632556179 |
| 20160101     |      1471632538142 |
| 20160103     |      1471632570463 |
+--------------+--------------------+

为了以便于阅读的格式显示 last_modified_time 字段，请使用 FORMAT_UTC_USEC 函数。例如：

控制台

#legacySQL
SELECT
  partition_id,
  FORMAT_UTC_USEC(last_modified_time*1000) AS last_modified
FROM
  [mydataset.table1$__PARTITIONS_SUMMARY__]

经典版界面

#legacySQL
SELECT
  partition_id,
  FORMAT_UTC_USEC(last_modified_time*1000) AS last_modified
FROM
  [mydataset.table1$__PARTITIONS_SUMMARY__]

bq

bq query --use_legacy_sql=true '
SELECT
  partition_id,
  FORMAT_UTC_USEC(last_modified_time*1000) AS last_modified
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]'

输出如下所示：

+--------------+----------------------------+
| partition_id |       last_modified        |
+--------------+----------------------------+
| 20160103     | 2016-08-19 18:49:30.463000 |
| 20160102     | 2016-08-19 18:49:16.179000 |
| 20160101     | 2016-08-19 18:48:58.142000 |
+--------------+----------------------------+

示例：使用查询结果创建提取时间分区表

在此示例中，您将使用 bq 命令行工具创建提取时间分区表，并使用查询结果将数据添加到三个分区。该表将包含 2016 年最初三天的天气数据，这些数据按日期分区。

此示例查询 NOAA GSOD 天气数据集，以获取 2016 年前三天的温度，并将结果写入分区表。

注意：以下示例可查询美国境内的公共数据集。由于公共数据集存储在美国的多地区位置，因此您的目标表所属的数据集也必须位于美国。您无法查询一个位置的数据集而将结果写入另一个位置的目标表。

第 1 步：在名为 mydataset 的数据集中创建一个名为 temps 的空提取时间分区表（按日期分区）。无需为表指定架构。 查询结果中的架构用作表的架构定义。

bq mk --time_partitioning_type=DAY mydataset.temps

如需查看配置设置，请使用 bq show 命令：

bq show --format=prettyjson mydataset.temps

在 bq show 命令输出中查找 timePartitioning 条目：

{
  ...
  "timePartitioning": {
    "type": "DAY"
  },
  "type": "TABLE"
}

第 2 步：查询 NOAA GSOD 天气数据集，以获取 2016 年最初三天的温度，并将结果写入 temps 表中的相应分区。以下查询使用 --destination_table 选项和标准 SQL 语法将 100 行查询结果写入分区。

1. 运行以下查询，将 2016 年 1 月 1 日的温度写入分区 mydataset.temps$20160101：

   bq query --use_legacy_sql=false \
   --destination_table 'mydataset.temps$20160101' \
   'SELECT
      stn,
      temp
    FROM
      `bigquery-public-data`.noaa_gsod.gsod2016
    WHERE
      mo="01"
      AND da="01"
    LIMIT
      100'
   

2. 运行以下查询，将 2016 年 1 月 2 日的温度写入分区 mydataset.temps$20160102：

   bq --location=US query --use_legacy_sql=false \
   --destination_table 'mydataset.temps$20160102' \
   'SELECT
      stn,
      temp
    FROM
      `bigquery-public-data`.noaa_gsod.gsod2016
    WHERE
      mo="01"
      AND da="02"
    LIMIT
      100'
   

3. 运行以下查询，将 2016 年 1 月 3 日的温度写入分区 mydataset.temps$20160103：

   bq --location=US query --use_legacy_sql=false \
   --destination_table 'mydataset.temps$20160103' \
   'SELECT
      stn,
      temp
    FROM
      `bigquery-public-data`.noaa_gsod.gsod2016
    WHERE
      mo="01"
      AND da="03"
    LIMIT
      100'
   

第 3 步：通过使用 bq show 命令确认表中有 300 行。

bq show mydataset.temps

结果显示架构和总行数。

  Last modified        Schema       Total Rows   Total Bytes     Expiration      Time Partitioning   Labels   kmsKeyName
 ----------------- ---------------- ------------ ------------- ----------------- ------------------- -------- ------------
  28 Jan 15:03:45   |- stn: string   300          4800          29 Jan 15:00:32   DAY
                    |- temp: float

后续步骤

• 如需大致了解 BigQuery 中的分区表支持，请参阅分区表简介。
• 如需了解如何管理和更新分区表，请参阅管理分区表。
• 如需了解如何查询分区表，请参阅查询分区表。
• 如需了解日期/时间戳分区表，请参阅创建和使用日期/时间戳分区表。
• 如需了解整数范围分区表，请参阅创建和使用整数范围分区表。