创建和使用分区表

本文介绍了如何在 BigQuery 中创建和使用分区表。要了解按提取时间分区的表,请参阅创建和使用按提取时间分区的表

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

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

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

限制

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

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

创建分区表

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

在 BigQuery 中创建基于列的时间分区表时,数据集中每个表的名称都必须具有唯一性。表名称符合下列特征:

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

所需权限

如需创建分区表,您必须具有数据集级的 WRITER 访问权限,或者必须拥有具备 bigquery.tables.create 权限的项目级层 IAM 角色。以下预定义的项目级层 IAM 角色具有 bigquery.tables.create 权限:

此外,由于 bigquery.user 角色具有 bigquery.datasets.create 权限,因此被分配了 bigquery.user 角色的用户可在其创建的任何数据集中创建分区表。在分配有 bigquery.user 角色的用户创建数据集后,系统将为该用户授予对该数据集的 OWNER 访问权限。凭借对数据集的 OWNER 访问权限,用户可以完全掌控该数据集以及其中的所有表。

如需详细了解 BigQuery 中的 IAM 角色和权限,请参阅访问权限控制。如需详细了解数据集级别的角色,请参阅数据集的初始角色

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

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

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

  • 使用命令行工具通过内嵌方式提供架构
  • 使用命令行工具指定 JSON 架构文件
  • 调用 API 的 tables.insert 方法时,在表资源中提供架构。

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

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

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

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

Console

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

  2. 点击编写新查询

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

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

     #standardSQL
     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. 点击更多,然后选择查询设置查询设置

  5. 处理位置部分,点击自动选择并选择数据的位置。如果数据位于 USEU 多区域位置,则您可以将处理位置设置为“未指定”。当您的数据位于 USEU 中时,系统会自动检测处理位置。 查询处理位置

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

经典版界面

  1. 转到 BigQuery 网页界面。

    转到 BigQuery 网页界面

  2. 点击编写查询

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

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

     #standardSQL
     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. 点击 Show Options

  5. 处理位置部分,点击未指定,然后选择数据的位置。如果数据位于 USEU 多区域位置,则可以将处理位置设置为“unspecified”。当您的数据位于 USEU 中时,系统会自动检测处理位置。

  6. 点击 Run Query。完成查询后,相应分区表将显示在导航窗格中。

命令行

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

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

如果您要在默认项目以外的某个项目中创建表,请按以下格式将相应项目 ID 添加到数据集内:[PROJECT_ID]:[DATASET]

此处未演示 --destination_kms_key。如需详细了解如何使用此标志,请参阅客户管理的加密密钥

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

bq mk --table --expiration [INTEGER1] --schema [SCHEMA] --time_partitioning_field [COLUMN] --time_partitioning_expiration [INTEGER2] --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] 是代表标签的键值对。可使用逗号分隔列表输入多个标签。
  • [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

架构通过内嵌方式指定为: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  --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

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

API

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

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 查询分区表或将查询结果写入分区表。

通过分区装饰器,您可以将查询结果写入特定分区。要针对时区进行调整,请根据您的首选时区使用分区装饰器将数据写入分区中。例如,如果您使用的是太平洋标准时间 (PST),请使用下面的相应分区装饰器将太平洋标准时间 2016 年 5 月 1 日生成的所有数据写入该日期的分区中:

[TABLE_NAME]$20160501

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

例如:

以下查询将数据写入到表 T 的 2018 年 2 月 1 日分区中。该表含有两列:一个名为 TSTIMESTAMP 列,以及另一个名为 aINT64 列。由于该查询将时间戳 2018-02-01 写入分区 $20180201 中,因此命令成功完成。该查询在 US 多区域中运行。

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

以下查询也尝试将数据写入表 T 中,但将时间戳 2018-01-31 写入分区 $20180201 中。此查询失败,因为您尝试写入的值不在相应分区的日期内。

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

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

Console

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

经典版界面

使用 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] 是位置的名称。如果数据位于 USEU 多区域位置,则 --location 标志为可选项。例如,如果您在东京区域使用 BigQuery,则将该标志的值设置为 asia-northeast1。您可以使用 .bigqueryrc 文件设置该位置的默认值。
  • [PROJECT_ID] 是项目 ID。
  • [DATASET] 是将包含新分区表的数据集的名称。
  • [TABLE] 是您基于查询结果创建的分区表的名称。
  • [QUERY] 是使用标准 SQL 语法的查询。目前,无法使用旧版 SQL 查询分区表,也无法将查询结果写入分区表中。

示例:

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

bq --location=US 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 --location=US 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 中时,可以提供表架构,或者,对于支持的数据格式,可以使用架构自动检测功能。

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

[TABLE_NAME]$20160501

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

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

API

如需在通过加载作业创建表时定义分区配置,您可以在 configuration.load.timePartitioning 消息中填充相关分区配置,包括用于让分区在一段时间后到期的 configuration.load.timePartitioning.expirationMs。填充的 configuration.load.timePartitioning.field 决定此表是按伪列还是按用户数据列进行分区。

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")
gcsRef := bigquery.NewGCSReference("gs://cloud-samples-data/bigquery/us-states/us-states-by-date.csv")
gcsRef.SkipLeadingRows = 1
gcsRef.Schema = bigquery.Schema{
	{Name: "name", Type: bigquery.StringFieldType},
	{Name: "post_abbr", Type: bigquery.StringFieldType},
	{Name: "date", Type: bigquery.DateFieldType},
}
loader := client.Dataset(destDatasetID).Table(destTableID).LoaderFrom(gcsRef)
loader.TimePartitioning = &bigquery.TimePartitioning{
	Field:      "date",
	Expiration: 90 * 24 * time.Hour,
}
loader.WriteDisposition = bigquery.WriteEmpty

job, err := loader.Run(ctx)
if err != nil {
	return err
}
status, err := job.Wait(ctx)
if err != nil {
	return err
}

if status.Err() != nil {
	return fmt.Errorf("Job completed with error: %v", status.Err())
}

Python

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

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'my_dataset'
table_id = "us_states_by_date"

dataset_ref = client.dataset(dataset_id)
job_config = bigquery.LoadJobConfig()
job_config.schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
    bigquery.SchemaField("date", "DATE"),
]
job_config.skip_leading_rows = 1
job_config.time_partitioning = bigquery.TimePartitioning(
    type_=bigquery.TimePartitioningType.DAY,
    field="date",  # name of column to use for partitioning
    expiration_ms=7776000000,
)  # 90 days
uri = "gs://cloud-samples-data/bigquery/us-states/us-states-by-date.csv"

load_job = client.load_table_from_uri(
    uri, dataset_ref.table(table_id), job_config=job_config
)  # API request

assert load_job.job_type == "load"

load_job.result()  # Waits for table load to complete.

table = client.get_table(dataset_ref.table(table_id))
print("Loaded {} rows to table {}".format(table.num_rows, table_id))

控制对分区表的访问权限

您不能直接分配对表或分区的访问权限控制,但可以通过在数据集级层或项目级层配置访问权限控制,控制表的访问权限。

数据集级层的访问权限控制指定用户、组和服务帐号可以对该特定数据集中的表执行的操作。如果仅分配数据集级层的权限,则还必须分配一个提供项目访问权限的初始或预定义项目级层角色,例如 bigquery.user

您可以分配预定义的项目级层 IAM 角色(这些角色可授予对项目中所有数据集内的所有表数据的权限),而不是授予对各个数据集的访问权限。

您还可以创建 IAM 自定义角色。如果创建自定义角色,则您授予的权限取决于您希望用户、群组或服务帐号能够执行哪些表操作。

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

使用分区表

获取有关分区表的信息

如需获取表的相关信息,您可以使用 GCP Console、经典版 BigQuery 网页界面或 bq show CLI 命令,也可以调用 tables.get API 方法。

所需权限

如需获取有关表的信息,您必须拥有数据集的 READER 角色,或必须拥有具备 bigquery.tables.get 权限的项目级层 IAM 角色。如果您拥有项目级层的 bigquery.tables.get 权限,则可获取项目中所有表的相关信息。除了 bigquery.userbigquery.jobUser 外,其他所有预定义的项目级层 IAM 角色都具有 bigquery.tables.get 权限。

此外,分配有 bigquery.user 角色的用户具有 bigquery.datasets.create 权限。这样一来,分配有 bigquery.user 角色的用户就可以获取该用户创建的任何数据集内的表的相关信息。在分配有 bigquery.user 角色的用户创建数据集后,该用户将获得对该数据集的 OWNER 访问权限。凭借对数据集的 OWNER 访问权限,用户可以完全掌控该数据集以及其中的所有表。

如需详细了解 BigQuery 中的 IAM 角色和权限,请参阅访问权限控制。如需详细了解数据集级别的角色,请参阅数据集的初始角色

获取分区表信息

要查看分区表的相关信息,请按如下所述操作:

Console

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

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

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

    表详细信息

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

经典版界面

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

  2. 点击表名称。

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

    分区表详细信息

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

命令行

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

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

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

API

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

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

如需列出数据集中的表(包括分区表),您可以使用 GCP Console、经典版 BigQuery 网页界面或 bq ls CLI 命令,也可以调用 tables.list API 方法。

所需权限

如需列出某个数据集中的表,您必须拥有数据集的 READER 角色,或者必须拥有具备 bigquery.tables.list 权限的项目级层 IAM 角色。如果您拥有项目级层的 bigquery.tables.list 权限,则可列出项目所含任何数据集中的表。除了 bigquery.jobUser 外,其他所有预定义的项目级层 IAM 角色都具有 bigquery.tables.list 权限。

如需详细了解 BigQuery 中的 IAM 角色和权限,请参阅访问权限控制。如需详细了解数据集级别的角色,请参阅数据集的初始角色

列出分区表

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

Console

  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 权限。以下预定义的项目级层 IAM 角色具有 bigquery.jobs.create 权限:

您还必须拥有数据集级别的 READER 角色,或者必须拥有具备 bigquery.tables.getData 权限的项目级层 IAM 角色。除了 bigquery.userbigquery.jobUserbigquery.metadataViewer 外,其他所有预定义的项目级层 IAM 角色都具有 bigquery.tables.getData 权限。

如需详细了解 BigQuery 中的 IAM 角色和权限,请参阅访问权限控制。如需详细了解数据集级别的角色,请参阅数据集的初始角色

列出某个分区表中的分区

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

Console

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

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

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

    #legacySQL
    SELECT
      partition_id
    FROM
      [[DATASET].[TABLE]$__PARTITIONS_SUMMARY__]
    

    其中:

    • [DATASET] 是包含表的数据集。
    • [TABLE] 是表的名称。
  4. 点击更多,然后选择查询设置

    查询设置

  5. 处理位置部分,点击自动选择并选择数据的位置。如果数据位于 USEU 多区域位置,则您可以将处理位置设置为“自动选择”。当您的数据位于 USEU 中时,系统会自动检测处理位置。

    查询处理位置

  6. 点击运行

经典版界面

  1. 转到 BigQuery 网页界面。

    转到 BigQuery 网页界面

  2. 点击 Compose query 按钮。

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

    #legacySQL
    SELECT
      partition_id
    FROM
      [[DATASET].[TABLE]$__PARTITIONS_SUMMARY__]
    

    其中:

    • [DATASET] 是包含表的数据集。
    • [TABLE] 是表的名称。
  4. 点击 Show Options

  5. 处理位置部分,点击未指定,然后选择数据的位置。如果数据位于 USEU 多区域位置,则可以将处理位置设置为“unspecified”。当您的数据位于 USEU 中时,系统会自动检测处理位置。

  6. 点击 Run Query

CLI

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

bq --location=[LOCATION] query --use_legacy_sql=true '
SELECT
  partition_id
FROM
  [[DATASET].[TABLE]$__PARTITIONS_SUMMARY__]'

Where:

  + `[LOCATION]` is the name of your location. The `--location` flag is
    optional if your data is in the `US` or the `EU` multi-region
    location. For example, if you are using BigQuery in
    the Tokyo region, set the flag's value to `asia-northeast1`. You can
    set a default value for the location using the [.bigqueryrc file](/bigquery/docs/bq-command-line-tool#setting_default_values_for_command-line_flags).
  + `[DATASET]` is the dataset that contains the table.
  + `[TABLE]` is the name of the table.

API

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

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

使用元表获取表元数据

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

使用元表获取分区元数据

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

如需访问时间分区表中有关分区的元数据,请在查询的 SELECT 语句中使用 __PARTITIONS_SUMMARY__ 元表。如需运行查询,您可以使用 Console、经典版 BigQuery 网页界面或命令行工具的 bq query 命令,也可以调用 jobs.insert API 方法并配置查询作业

目前,标准 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 权限。以下预定义的项目级层 IAM 角色具有 bigquery.jobs.create 权限:

您还必须拥有数据集级别的 READER 角色,或者必须拥有具备 bigquery.tables.getData 权限的项目级层 IAM 角色。除了 bigquery.userbigquery.jobUserbigquery.metadataViewer 外,其他所有预定义的项目级层 IAM 角色都具有 bigquery.tables.getData 权限。

分区元表示例

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

Console

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

经典版界面

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

命令行

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 中分区的时间。

Console

#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 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 函数。例如:

Console

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

后续步骤

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

发送以下问题的反馈:

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