管理分区表数据

本文档介绍了如何管理 BigQuery 中的分区表数据。您可以通过以下方式处理分区表数据:

  • 将数据加载到分区表中
  • 浏览(或预览)分区表数据
  • 查询分区表数据
  • 对分区表数据执行附加或覆盖操作
  • 使用数据操纵语言语句修改分区表数据
  • 复制分区表数据
  • 将数据流式传输到分区表中
  • 导出分区表数据

如需了解如何管理表架构,请参阅修改表架构

将数据加载到分区表中

您可以在加载数据时创建分区表,也可以先创建空的分区表,后续再加载数据。在将数据加载到分区表时,您可以针对支持的数据格式使用架构自动检测功能,也可以指定架构

要详细了解如何加载数据,请参阅关于源数据格式和位置的文档:

浏览表数据

您可以通过以下方式浏览分区表数据:

  • 使用 GCP Console 或经典版 BigQuery 网页界面
  • 使用命令行工具的 bq head 命令
  • 调用 tabledata.list API 方法

所需权限

要在数据集级层浏览表数据,您需要对所浏览的分区表所属的数据集拥有 READER 访问权限。

您可以利用包含 bigquery.tables.getData 权限的 IAM 角色,而不使用数据集级层权限。所有预定义的项目级层 IAM 角色都拥有 bigquery.tables.getData 权限,但 bigquery.userbigquery.jobUserbigquery.metadataViewer 除外

此外,因为 bigquery.user 角色拥有 bigquery.datasets.create 权限,所以分配有 bigquery.user 角色的用户可读取其所创建任意数据集中任何表的数据。当分配有 bigquery.user 角色的用户创建数据集时,系统会授予该用户对该数据集的 OWNER 访问权限(如果未设置其他所有者)。凭借对数据集的 OWNER 访问权限,用户可以完全掌控该数据集以及其中的所有表。

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

浏览分区表数据

浏览分区表数据的方法如下:

Console

  1. 在 GCP Console 的导航窗格中,点击数据集以列出其表和视图。

  2. 点击列表中的分区表。您不能使用 BigQuery 网页界面预览各分区。

  3. 点击详细信息标签页。

    表详细信息

  4. 请注意行数中的值。使用 CLI 或 API 时,您可能需要此值来控制结果的起点。

    行数

  5. 点击预览标签页。此时会显示一组数据示例。

经典版界面

  1. 在 BigQuery 网页界面的导航窗格中,点击数据集左侧的蓝色箭头展开数据集,或双击数据集名称。系统会显示数据集中的表和视图。

  2. 点击列表中的分区表。您不能使用 BigQuery 网页界面预览各分区。

  3. 点击详情并记下行数中的值。使用 CLI 或 API 时,您可能需要此值来控制结果的起点。

  4. 点击预览。此时会显示一组数据示例。

    表预览

命令行

发出带 --max_rows 标志的 bq head 命令,即可列出特定表行数对应的所有字段。如果未指定 --max_rows,则默认值为 100。使用分区修饰器指定要浏览的分区,例如 $20180224

因为 bq head 命令不会创建查询作业,所以 bq head 命令不会显示在查询历史记录中,您也无需为此付费。

要浏览表中的字段子集(包括嵌套和重复字段),请使用 --selected_fields 标志以英文逗号分隔列表的形式输入字段。

要指定显示表数据之前跳过的行数,请使用 --start_row=[INTEGER] 标志(或 -s 快捷方式)。默认值为 0。要检索表中的行数,您可以使用 bq show 命令检索表信息

如果要浏览的表位于非默认项目中,请按以下格式将项目 ID 添加到命令:[PROJECT_ID]:[DATASET].[TABLE]

bq head --max_rows [INTEGER1] --start_row [INTEGER2] --selected_fields "[FIELDS]" [PROJECT_ID]:[DATASET].[TABLE]$[PARTITION]

其中:

  • [INTEGER1] 是要显示的行数。
  • [INTEGER2] 是显示数据之前要跳过的行数。
  • [FIELDS] 是英文逗号分隔的字段列表。
  • [PROJECT_ID] 是您的项目 ID。
  • [DATASET] 是包含该表的数据集的名称。
  • [TABLE] 是要浏览的表名称。
  • [PARTITION] 是分区装饰器。

示例:

输入以下命令以列出 "2018-02-24" 分区中 mydataset.mytable 内前 10 行的所有字段。mydataset 在您的默认项目中。

bq head --max_rows=10 mydataset.mytable$20180224

输入以下命令,列出 "2016-09-01" 分区中 mydataset.mytable 内前 100 行的所有字段。mydatasetmyotherproject 中,不在默认项目中。

bq head --format=prettyjson 'mydataset.mycolumntable2$20160901'

输入以下命令,仅显示 "2016-09-01" 分区中 mydataset.mytable 内的 field1field2。该命令使用 --start_row 标志跳转到第 100 行。mydataset.mytable 在默认项目中。

bq head --format=prettyjson --start_row 100 --selected_fields "state_number" 'mydataset.mycolumntable2$20160901'

API

通过调用 tabledata.list 来浏览表数据。在 tableId 参数中指定表和分区修饰器的名称。

配置以下可选参数来控制输出:

  • maxResults - 要返回的结果数上限
  • selectedFields - 以英文逗号分隔的待返回字段列表;如果未指定此参数,则返回所有字段
  • startIndex - 要读取的起始行的索引(从零开始)

返回的值会封装在一个 JSON 对象中,您必须对此对象进行解析,具体请参阅 tabledata.list 参考文档。

查询分区表数据

将数据加载到 BigQuery 中后,您可以查询表中的数据。BigQuery 支持两种类型的查询:

默认情况下,BigQuery 运行交互式查询,也就是说查询会尽快执行。

BigQuery 也可运行批量查询。BigQuery 会代表您将每个批量查询排成队列,并在有闲置资源可用时尽快开始查询,这通常需要几分钟的时间。

您可以通过以下方式运行交互式查询和批量查询:

如需了解详情,请参阅查询分区表

对分区表数据执行附加和覆盖操作

您可以使用加载或查询操作覆盖分区表数据。您可以通过执行加载附加操作或附加查询结果的操作,将额外的数据附加到现有分区表。

所需权限

要覆盖现有分区或将数据附加到现有分区,您必须具有数据集级层的 WRITER 访问权限,或者必须分配有包含 bigquery.tables.updateData 权限的项目级层 IAM 角色。以下预定义的项目级层 IAM 角色包含 bigquery.tables.updateData 权限:

此外,由于 bigquery.user 角色具有 bigquery.datasets.create 权限,因此分配有 bigquery.user 角色的用户可以覆盖或附加该用户在数据集中创建的任意表内的任何分区。在分配有 bigquery.user 角色的用户创建数据集时,系统会授予该用户对该数据集的 OWNER 访问权限(如果未指定其他所有者)。凭借对数据集的 OWNER 访问权限,用户可以完全控制该数据集、其中的所有表以及所有表分区。

必须获得 bigquery.jobs.create 权限才能运行加载作业或查询作业。以下预定义的项目级层 IAM 角色包含 bigquery.jobs.create 权限:

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

使用加载作业

要对分区执行附加或覆盖操作,您可以使用 bq load 命令,或者调用 jobs.insert 方法并配置一个加载作业。GCP Console 和经典版 BigQuery 网页界面不支持在加载作业中对分区执行附加或覆盖操作。

使用加载作业对特定分区执行附加数据或覆盖数据操作时,请注意以下事项:

  • 从 Cloud Storage 加载数据时,存储分区必须位于与 BigQuery 数据集相同的位置
  • 您要加载的数据必须符合表格的分区架构。所有写入分区的行都应具有属于分区日期的值。
  • 由于分区表中的分区共享表架构,所以替换某个分区中的数据不会替换表架构。实际情况恰好与此相反,也就是说新数据的架构必须与表架构兼容。有关在加载作业中更新表架构的信息,请参阅管理表架构
  • 如果要将数据附加到提取时间分区表,并且未指定分区修饰器,则使用当前分区。

要使用加载作业对分区表数据执行覆盖或附加操作,请指定目标表和分区修饰器,并将写入处置标志设置为以下某项:

CLI 选项 API 选项 说明
--noreplace WRITE_APPEND 将数据附加到现有分区。如果未指定写入处置选项,则默认操作是将数据附加到分区。
--replace WRITE_TRUNCATE 覆盖(重新组织)分区。

分区修饰器表示一个特定的日期,其形式如下:

$YYYYMMDD

例如,以下命令会替换名为 mydataset.table1 的分区表 2016 年 1 月 1 日 (20160101) 对应的整个分区内的数据。系统会从 Cloud Storage 存储分区中加载 JSON 数据。Cloud Storage 存储分区和 BigQuery 数据集均创建于 asia-northeast1 区域。

bq --location=asia-northeast1 load --replace --source_format=NEWLINE_DELIMITED_JSON 'mydataset.table1$20160101' gs://mybucket/myfile.json

要详细了解如何使用加载作业附加或覆盖数据,请参阅您的源数据格式对应的文档:

使用查询作业

要对分区执行附加或覆盖操作,您可以使用 bq query 命令,或者调用 jobs.insert 方法并配置一个查询作业。GCP Console 和经典版 BigQuery 网页界面不支持在查询作业中对分区执行附加或覆盖操作。

如果您使用查询作业对分区执行附加或覆盖操作,请注意以下事项:

  • 您查询的表必须与要附加或覆盖的表处于相同的位置
  • 在对提取时间分区表中的分区执行附加或覆盖操作时,您可以使用旧版 SQL 或标准 SQL 语法。
  • 对分区表中的一个分区执行附加或覆盖操作时,您的查询必须使用标准 SQL 语法。目前,查询分区表或将查询结果写入分区表时不支持使用旧版 SQL。
  • 将查询结果写入分区时,写入分区的数据必须符合表的分区架构。所有写入分区的行都应具有属于分区日期的值。
  • 如果要将数据附加到提取时间分区表,并且未指定分区修饰器,则使用当前分区。

要使用查询结果覆盖或附加到分区,请使用分区修饰器指定目标表,并将写入处置设为以下某项:

CLI 选项 API 选项 说明
--append_table WRITE_APPEND 将查询结果附加到现有分区。
--replace WRITE_TRUNCATE 使用查询结果覆盖(重新组织)分区。

例如,以下命令会使用查询结果,重新组织 table1 中 2016 年 3 月 1 日 (20160301) 分区的数据。目标表和查询的表均位于 US 多区域位置。

bq --location=US query --use_legacy_sql=false --replace --destination_table 'mydataset.table1$20160301' 'SELECT column1, column2 FROM mydataset.mytable'

如果目标表存在,并且未进行分区,系统会返回以下错误:BigQuery error in query operation: Error processing job '[PROJECT][JOB_ID]' Incompatible table partitioning specification. Expects partitioning specification interval (type:day), but input partitioning specification is none

如需详细了解如何使用查询结果对表执行数据附加或覆盖操作,请参阅编写查询结果

使用 DML 语句修改分区表数据

您可以使用标准 SQL 方言中的 DML 语句修改分区的表中的数据。DML 语句允许您执行批量行更新、插入和删除操作。要查看将 DML 与分区表一起使用的示例,请参阅使用 DML 语句更新分区表数据

旧版 SQL 方言不支持 DML 语句。要使用旧版 SQL 更新或删除数据,您必须先删除分区表,然后使用新数据重新创建该分区表。您也可以通过编写查询修改数据,并将查询结果写入新的分区表。

复制分区表数据

您可以通过以下方式复制分区表:

  • 使用 GCP Console 或经典版 BigQuery 网页界面
  • 使用命令行工具的 bq cp 命令
  • 调用 jobs.insert API 方法并配置复制作业

要详细了解如何复制表,请参阅复制表

要复制一个或多个分区,您可以使用命令行工具的 bq cp 命令,或者调用 jobs.insert API 方法并配置一个复制作业。目前,GCP Console 或经典版 BigQuery 网页界面不支持复制分区。

要详细了解如何复制分区,请参阅复制分区

将数据流式传输到分区表中

要将数据流式传输到特定分区,请在指定作为流式传输目标的表的 tableId 时使用分区修饰器。例如,以下命令会将对应于 2017 年 1 月 1 日 ($20170101) 的一个分区内的一行流式传输到一个名为 mydataset.mytable 的分区表中:

echo '{"a":1, "b":2}' | bq insert 'mydataset.mytable$20170101'

此命令演示了如何使用分区修饰器。bq insert 命令仅用于测试。要将数据流式传输到 BigQuery 中,请使用 API 的 tabledata.insertAll 方法。如需详细了解如何将数据流式传输到分区中,请参阅流式传输到分区表

在使用分区装饰器进行流式传输时,您可在相对于当前日期(基于当前世界协调时间 (UTC))的过去 30 天内及未来 5 天内流式传输到分区。要为这些允许范围以外的日期写入分区,您可以使用加载或查询作业。

如果在流式传输数据时将按时间分区的表指定为目标表,则每个分区都有一个流式缓冲区。如果您执行加载、查询或复制作业,并且通过将 writeDisposition 属性设置为 WRITE_TRUNCATE 覆盖了一个分区,则流式缓冲区会得到保留。如果您想移除流式缓冲区,请对该分区调用 tables.get,以验证流式缓冲区是否为空。

导出表数据

从分区表导出数据的方法与从非分区表导出数据的方法相同。目前,您不能导出单个分区。如需了解详情,请参阅导出表数据

您可以采用 CSV、JSON 或 Avro 格式导出分区表数据。目前,数据必须导出到 Cloud Storage 存储分区。不支持导出到本地机器;但是,您可以使用 GCP Console 或经典版 BigQuery 网页界面下载和保存查询结果

后续步骤

要详细了解如何使用分区表,请参阅:

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

发送以下问题的反馈:

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