创建和使用分区表

本文档介绍如何创建和使用由 DATETIMESTAMP 列分区的表。如需了解提取时间分区表,请参阅创建和使用提取时间分区表

创建分区表后,您可以执行下列操作:

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

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

限制

分区表会受到下列限制的约束:

  • 分区列必须是标量 DATETIMESTAMP 列。虽然列模式可以是 REQUIREDNULLABLE,但不能是 REPEATED(基于数组)。
  • 此外,分区列必须是顶级字段。不能将 RECORD (STRUCT) 中的叶字段用作分区列。
  • 不能使用旧版 SQL 查询分区表,也不能将查询结果写入分区表中。

创建分区表

您可以通过下列方式在 BigQuery 中创建分区表:

表命名

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

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

所需权限

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

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

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

以下预定义的 Cloud IAM 角色包含 bigquery.tables.createbigquery.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 角色和权限,请参阅预定义的角色和权限

创建含架构定义的空分区表

您无法创建不含架构定义的空分区表。必须使用架构,才能确定用于创建分区的列。

创建含架构定义的空分区表时,您可以执行下列操作:

  • 使用 CLI 以内嵌方式提供架构
  • 使用 CLI 指定 JSON 架构文件
  • 调用 API 的 tables.insert 方法时,在表资源中提供架构。

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

创建分区表后,您可以执行下列操作:

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

如需创建带架构定义的空分区表,请按如下所述操作:

控制台

  1. 在导航面板的资源部分中,展开您的项目并选择数据集。

  2. 在窗口右侧的详细信息面板中,点击创建表

  3. 创建表面板的来源部分,执行以下操作:

    • 基于以下数据创建表部分,选择空表
  4. 目标部分中执行如下设置:

    • 对于数据集名称,选择相应的数据集,然后在表名称字段中输入要创建的表的名称。
    • 验证表类型是否设置为原生表
  5. 架构部分中,输入架构定义。

    • 通过以下方式,手动输入架构信息:

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

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

  6. 对于分区和聚簇设置,点击无分区,选择按字段分区 (Partition by field),然后选择 DATETIMESTAMP 列。如果架构不包含 DATETIMESTAMP 列,则此选项不可用。

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

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

  9. 点击创建表

DDL

借助数据定义语言 (DDL) 语句,您可以使用标准 SQL 查询语法创建和修改表和视图。

详细了解如何使用数据定义语言语句

如需在 GCP Console 中使用 DDL 语句创建分区表,请执行以下操作:

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

  2. 点击编写新查询

  3. 查询编辑器文本区域中,输入 CREATE TABLE DDL 语句

    以下查询会创建一个名为 transaction_date 的表,该表按 newtable DATE 列分区且分区过期时间设置为 3 天。

     CREATE TABLE
       mydataset.newtable (transaction_id INT64,
         transaction_date DATE)
     PARTITION BY
       transaction_date
     OPTIONS
       ( partition_expiration_days=3,
         description="a table partitioned by transaction_date" )

  4. 点击运行。查询完成后,该表将显示在资源窗格中。

经典版界面

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

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

  3. Create Table 页面的 Destination Table 部分,执行以下操作:

    • 对于 Table name,选择相应的数据集,然后在表名称字段中输入要创建的表的名称。
    • 确认 Table type 设置为 Native table
  4. Schema 部分中,手动输入架构定义

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

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

      • 使用 Add Field 输入架构。

  5. Options 部分,执行以下操作:

    • 对于 Partitioning Type,点击 None 并选择 Day
    • 对于 Partitioning Field,选择 TIMESTAMPDATE 列。默认值为 _PARTITIONTIME,此值将创建提取时间分区表。
    • (可选)点击 Require partition filter 框,以要求用户添加 WHERE 子句来指定要查询的分区。需要分区过滤条件有可能减少费用并提高性能。如需了解详情,请参阅查询分区表
    • (可选)对于 Destination encryption,选择 Customer-managed encryption,以使用 Cloud Key Management Service 密钥来加密表。如果保留 Default 设置,BigQuery 将使用 Google 管理的密钥对静态数据进行加密
  6. 点击 Create Table

CLI

使用带 --table 标志(或 -t 快捷方式)、--schema 标志和 --time_partitioning_field 标志的 mk 命令。您可以通过内嵌方式或通过 JSON 架构文件提供表的架构定义。

可选参数包括 --expiration--description--time_partitioning_expiration--destination_kms_key--require_partition_filter--labelDAY--time_partitioning_type 目前唯一支持的值,因此无需此标志。

如果您要在非默认项目中创建表,请按以下格式将相应项目 ID 添加到数据集中:project_id:dataset

此处未演示 --destination_kms_key。如需详细了解如何使用此标志,请参阅使用 Cloud Key Management Service 密钥保护数据

输入以下命令可创建带架构定义的空分区表:

bq mk --table \
--expiration integer1 \
--schema schema \
--time_partitioning_field column \
--time_partitioning_expiration integer2 \
--[no]require_partition_filter \
--description "description" \
--label key:value, key:value \
project_id:dataset.table

其中:

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

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

示例:

输入以下命令可在默认项目的 mydataset 中创建一个名为 mypartitionedtable 的分区表。分区的过期时间设置为 86400 秒(1 天),表的过期时间设置为 2592000(1 个 30 天的月份),说明设置为 This is my partitioned table,标签设置为 organization:development。该命令使用 -t 快捷方式代替 --table

--require_partition_filter 标志用于要求用户添加一个 WHERE 子句以指定要查询的分区。需要分区过滤条件有可能减少费用并提高性能。如需了解详情,请参阅查询分区表

架构通过内嵌方式指定为:ts:TIMESTAMP,column1:STRING,column2:INTEGER,coumn4:STRING。指定的 TIMESTAMP 字段 ts 用于创建分区。

bq mk -t \
--expiration 2592000 \
--schema 'ts:TIMESTAMP,column1:STRING,column2:INTEGER,coumn4:STRING' \
--time_partitioning_field ts \
--time_partitioning_expiration 86400  \
--require_partition_filter \
--description "This is my partitioned table" \
--label org:dev \
mydataset.mypartitionedtable

输入以下命令可在 myotherproject(而不是您的默认项目)中创建一个名为 mypartitionedtable 的分区表。分区的过期时间设置为 259200 秒(3 天),说明设置为 This is my partitioned table,标签设置为 organization:development。该命令使用 -t 快捷方式代替 --table。此命令没有指定表过期时间。如果相关数据集有默认的表过期时间,则系统会应用该过期时间。如果数据集没有默认的表过期时间,则该表将永不过期,但该分区会在 3 天后过期。

相应架构在如下本地 JSON 文件中指定:/tmp/myschema.json。该架构定义中包含一个名为 tsTIMESTAMP 字段,该字段用于创建分区。

bq mk -t \
--expiration 2592000 \
--schema /tmp/myschema.json \
--time_partitioning_field ts \
--time_partitioning_expiration 86400  \
--description "This is my partitioned table" \
--label org:dev \
myotherproject:mydataset.mypartitionedtable

创建分区表后,您可以使用 CLI 更新该表的表过期时间分区过期时间说明标签

API

调用 tables.insert 方法,并搭配指定了 timePartitioning 属性和 schema 属性的已定义表资源

Go

试用此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Go 设置说明进行操作。如需了解详情,请参阅 BigQuery Go API 参考文档

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
sampleSchema := bigquery.Schema{
	{Name: "name", Type: bigquery.StringFieldType},
	{Name: "post_abbr", Type: bigquery.IntegerFieldType},
	{Name: "date", Type: bigquery.DateFieldType},
}
metadata := &bigquery.TableMetadata{
	TimePartitioning: &bigquery.TimePartitioning{
		Field:      "date",
		Expiration: 90 * 24 * time.Hour,
	},
	Schema: sampleSchema,
}
tableRef := client.Dataset(datasetID).Table(tableID)
if err := tableRef.Create(ctx, metadata); err != nil {
	return err
}

Python

试用此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('my_dataset')

table_ref = dataset_ref.table("my_partitioned_table")
schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
    bigquery.SchemaField("date", "DATE"),
]
table = bigquery.Table(table_ref, schema=schema)
table.time_partitioning = bigquery.TimePartitioning(
    type_=bigquery.TimePartitioningType.DAY,
    field="date",  # name of column to use for partitioning
    expiration_ms=7776000000,
)  # 90 days

table = client.create_table(table)

print(
    "Created table {}, partitioned on column {}".format(
        table.table_id, table.time_partitioning.field
    )
)

基于查询结果创建分区表

如需基于查询结果创建分区表,请将结果写入新目标表中。您可以通过查询分区表或非分区表来创建分区表。不能使用查询结果将现有标准表更改为分区表。

基于查询结果创建分区表时,必须使用标准 SQL。目前,无法使用旧版 SQL 查询分区表或将查询结果写入分区表。

通过分区装饰器,您可以将查询结果写入特定分区。例如,如需将结果写入 2016 年 5 月 1 日的分区,请使用以下分区装饰器:

table_name$20160501

使用分区装饰器将查询结果写入特定分区时,被写入分区的数据必须与相应表的分区架构相符。所有写入到分区的行均应包含相应分区日期内的值。

例如:

以下查询检索自 2018 年 2 月 1 日起的数据,并将数据写入表 mytable$20180201 分区。该表含有两列 - 一个名为 TSTIMESTAMP 列和一个名为 aINT64 列。

bq query \
--nouse_legacy_sql  \
--destination_table=mytable$20180201 \
'SELECT
   TIMESTAMP("2018-02-01") AS TS,
   2 AS a'

以下查询检索自 2018 年 1 月 31 日起的数据,并将数据写入 mytable$20180201 分区。此查询失败,因为您尝试写入的数据不在分区的日期范围内。

bq query \
--nouse_legacy_sql  \
--destination_table=T$20180201 \
'SELECT
   TIMESTAMP("2018-01-31") as TS,
   2 as a'

如需了解如何在分区表中附加或重新组织(替换)数据,请参阅附加和覆盖时间分区表数据。如需详细了解如何查询分区表,请参阅查询分区表

基于查询结果创建分区表

如需基于查询结果创建分区表,请执行以下操作:

控制台

使用 GCP Console 查询数据时,您无法为目标表指定分区选项。

经典版界面

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

CLI

输入 bq query 命令,指定 --destination_table 标志可基于查询结果创建一个永久表,指定 --time_partitioning_field 标志可创建分区目标表。DAY--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_field column \
--use_legacy_sql=false \
'query'

其中:

  • location 是您位置的名称--location 是可选标志。例如,如果您在东京区域使用 BigQuery,则将该标志的值设置为 asia-northeast1。您可以使用 .bigqueryrc 文件设置该位置的默认值。
  • project_id 是您的项目 ID
  • dataset 是将包含新分区表的数据集的名称
  • table 是您基于查询结果创建的分区表的名称
  • query 是使用标准 SQL 语法的查询。目前,无法使用旧版 SQL 查询分区表,也无法将查询结果写入分区表中。

示例:

输入以下命令可将查询结果写入 mydataset 中名为 mypartitionedtable 的分区目标表中。mydataset 在您的默认项目中。该查询从非分区表(NHTSA 交通事故伤亡公开数据集)中检索数据。该表的 timestamp_of_crash TIMESTAMP 列用于创建分区。

bq query \
--destination_table mydataset.mypartitionedtable \
--time_partitioning_field timestamp_of_crash \
--use_legacy_sql=false \
'SELECT
   state_number,
   state_name,
   day_of_crash,
   month_of_crash,
   year_of_crash,
   latitude,
   longitude,
   manner_of_collision,
   number_of_fatalities,
   timestamp_of_crash
 FROM
   `bigquery-public-data`.nhtsa_traffic_fatalities.accident_2016
 LIMIT
   100'

输入以下命令可将查询结果写入 mydataset 中名为 mypartitionedtable 的分区目标表中。mydatasetmyotherproject 中,不在您的默认项目中。该查询从非分区表(NHTSA 交通事故伤亡公开数据集)中检索数据。该表的 timestamp_of_crash TIMESTAMP 列用于创建分区。

bq query \
--destination_table myotherproject:mydataset.mypartitionedtable \
--time_partitioning_field timestamp_of_crash \
--use_legacy_sql=false \
'SELECT
   state_number,
   state_name,
   day_of_crash,
   month_of_crash,
   year_of_crash,
   latitude,
   longitude,
   manner_of_collision,
   number_of_fatalities,
   timestamp_of_crash
 FROM
   `bigquery-public-data`.nhtsa_traffic_fatalities.accident_2016
 LIMIT
   100'

API

如需将查询结果保存到永久分区表中,请调用 jobs.insert 方法,配置 query 作业,并为 destinationTabletimePartitioning 属性添加值。

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

在加载数据时创建分区表

在将数据加载到新表的过程中,可以通过指定分区选项来创建分区表。无需创建空的分区表即可加载数据。可以同时创建分区表和加载数据。

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

通过分区装饰器,您可以将数据加载到特定分区。例如,如需将 2016 年5 月 1 日生成的所有数据加载到 20160501 分区,请使用以下分区装饰器:

table_name$20160501

使用分区装饰器将数据加载到特定分区时,加载到分区的数据必须与相应表的分区架构相符。所有写入到分区的行均应包含相应分区日期内的值。

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

控制对分区表的访问权限

您不能直接分配对表或视图的访问权限控制,数据集级层是您能够授予访问权限的最低级层 BigQuery 资源。如需配置对表和视图的访问权限,请在数据集级层或更高级层为实体授予 Cloud IAM 角色。

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

您还可以在 Google Cloud Platform 资源层次结构中的更高级层(例如项目、文件夹或组织级层)授予 Cloud IAM 角色。在更高级层授予角色后,实体可以访问更多资源。例如,在项目级层为实体授予角色会为该实体提供整个项目中所有数据集的访问权限。如需详细了解如何授予对资源的访问权限,请参阅 Cloud IAM 文档中的授予、更改和撤消对资源的访问权限

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

如需详细了解角色和权限,请参阅:

使用分区表

获取有关分区表的信息

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

  • 使用 GCP Console 或经典版 BigQuery 网页界面
  • 使用 bq show CLI 命令
  • 调用 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. 在 GCP Console 中打开 BigQuery 网页界面。
    转到 BigQuery 网页界面

  2. 在导航面板的资源部分中,展开项目和数据集,然后点击列表中的表名称。

  3. 点击查询编辑器下方的详细信息。此标签页会显示表说明和表信息。

    表详细信息

  4. 点击架构标签页查看表的架构定义。请注意,分区表不包含 _PARTITIONTIME 伪列。

经典版界面

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

  2. 点击表名称。

  3. 点击 DetailsTable Details 页面会显示表的说明和相关信息。

  4. 点击 Schema 标签查看表的架构定义。请注意,分区表不包含 _PARTITIONTIME 伪列。

CLI

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

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

bq show --schema --format=prettyjson project_id:dataset.table

其中:

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

示例:

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

bq show --format=prettyjson mydataset.mytable

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

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

输出应如下所示:

{
  "creationTime": "1563236533535",
  "description": "This is my partitioned table",
  "etag": "/ABcDEo7f8GHijKL2mnOpQr==",
  "expirationTime": "1565828533000",
  "id": "myproject:mydataset.mypartitionedtable",
  "kind": "bigquery#table",
  "labels": {
    "org": "dev"
  },
  "lastModifiedTime": "1563236533576",
  "location": "US",
  "numBytes": "0",
  "numLongTermBytes": "0",
  "numRows": "0",
  "requirePartitionFilter": true,
  "schema": {
    "fields": [
      {
        "name": "ts",
        "type": "TIMESTAMP"
      },
      {
        "name": "column1",
        "type": "STRING"
      },
      {
        "name": "column2",
        "type": "INTEGER"
      },
      {
        "name": "column3",
        "type": "STRING"
      }
    ]
  },
  "selfLink": "https://bigquery.googleapis.com/bigquery/v2/projects/myproject/datasets/mydataset/tables/mypartitionedtable",
  "tableReference": {
    "datasetId": "mydataset",
    "projectId": "myproject",
    "tableId": "mypartitionedtable"
  },
  "timePartitioning": {
    "expirationMs": "86400000",
    "field": "ts",
    "requirePartitionFilter": true,
    "type": "DAY"
  },
  "type": "TABLE"
}

输入以下命令可仅显示有关 mydataset 中的 mytable 的架构信息。mydatasetmyotherproject 中,不在默认项目中。

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

输出应如下所示:

[
  {
    "name": "ts",
    "type": "TIMESTAMP"
  },
  {
    "name": "column1",
    "type": "STRING"
  },
  {
    "name": "column2",
    "type": "INTEGER"
  },
  {
    "name": "column3",
    "type": "STRING"
  }
]

API

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

列出某个数据集内的分区表

您可以通过以下方式列出数据集中的表(包括分区表):

  • 使用 GCP Console 或经典版 BigQuery 网页界面
  • 使用 bq ls CLI 命令
  • 调用 tables.list API 方法
  • 使用客户端库

所需权限

如需列出数据集中的视图,您至少必须拥有 bigquery.tables.list 权限。以下预定义的 Cloud IAM 角色具有 bigquery.tables.list 权限:

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

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

列出分区表

如需列出数据集中的表(包括分区表),请按如下所述操作:

控制台

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

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

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

经典版界面

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

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

    查看表

CLI

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

bq ls --format=pretty project_id:dataset

其中:

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

运行该命令时,Type 字段会显示 TABLEVIEW。对于分区表,Time Partitioning 字段会显示 DAY、用于创建分区的列以及以毫秒为单位的分区过期时间(如果已指定)。

例如:

+-------------------------+-------+----------------------+---------------------------------------------------+
|         tableId         | Type  |        Labels        | Time Partitioning                                 |
+-------------------------+-------+----------------------+---------------------------------------------------+
| mytable                 | TABLE | department:shipping  |  DAY (field: source_date, expirationMs: 86400000) |
| myview                  | VIEW  |                      |                                                   |
+-------------------------+-------+----------------------+---------------------------------------------------+

示例:

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

bq ls --format=pretty mydataset

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

bq ls --format=pretty myotherproject:mydataset

API

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

列出分区表中的分区

如需列出分区表中的分区,您可以使用旧版 SQL 查询 __PARTITIONS_SUMMARY__ 元表。

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

所需权限

如需运行使用 __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 角色,请参阅访问权限控制

列出某个分区表中的分区

您可以使用旧版 SQL 来列出某个分区表中的分区。如需列出分区表中的分区,请按如下所述操作:

控制台

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

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

  3. 查询编辑器框中输入以下文本,以查询 __PARTITIONS_SUMMARY__ 元表:

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

    其中:

    • dataset 是包含表的数据集
    • table 是表的名称
  4. 点击运行

经典版界面

  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. 点击 Run query

CLI

使用 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 是包含表的数据集
  • dataset.table 是表的名称

API

调用 jobs.insert 方法,并配置 query 作业来查询表的 __PARTITIONS_SUMMARY__ 元表。

使用元表获取分区表元数据

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

目前,您无法使用 INFORMATION_SCHEMA 服务来获取分区表元数据。

使用元表获取分区元数据

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

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

  • 使用 GCP 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__]

CLI

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__]

CLI

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__]

CLI

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

后续步骤

此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

此网页
需要帮助?请访问我们的支持页面