管理分区表数据

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

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

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

将数据加载到分区表中

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

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

浏览表数据

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

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

所需权限

如需浏览表和分区数据,您至少必须具有 bigquery.tables.getData 权限。以下预定义的 Cloud IAM 角色包含 bigquery.tables.getData 权限:

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

此外,如果用户具有 bigquery.datasets.create 权限,则当该用户创建数据集时,系统会为其授予该数据集的 bigquery.dataOwner 权限。 借助 bigquery.dataOwner 权限,用户可以浏览数据集的表和分区中的数据。

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

浏览分区表数据

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

控制台

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

  2. 点击列表中的分区表。

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

    表详细信息

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

    行数

  5. 点击预览标签页。此时会显示一组数据示例。请注意,您无法使用 GCP Console 预览各分区。

经典版界面

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

  2. 点击列表中的分区表。

  3. 点击 Details 并记下 Number of Rows 中的值。使用 CLI 或 API 时,您可能需要此值来控制结果的起点。

  4. 点击 Preview。此时会显示一组数据示例。请注意,您无法使用 BigQuery 网页界面预览各分区。

    表预览

CLI

发出带 --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 会代表您将每一批查询排成队列,等到有闲置资源可用便会立即开始查询,这通常需要几分钟的时间。

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

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

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

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

所需权限

如需覆盖现有分区或将数据附加到现有分区,您至少必须获得以下权限:

  • bigquery.tables.create
  • bigquery.tables.updateData
  • bigquery.jobs.create

如需访问用于附加或覆盖的数据,可能还需要其他权限,例如 bigquery.tables.getData

以下预定义的 Cloud IAM 角色包含 bigquery.tables.updateDatabigquery.tables.create 权限:

  • 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 角色和权限,请参阅预定义的角色和权限

使用加载作业

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

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

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

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

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

分区修饰器表示一个特定的日期,使用以下格式:

$YYYYMMDD

例如,以下命令会替换名为 mydataset.table1 的分区表中日期为 2016 年 1 月 1 日 (20160101) 的整个分区内的数据。系统会从 Cloud Storage 存储分区中加载 JSON 数据。

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

使用查询作业

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

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

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

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

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

例如,以下命令会使用查询结果,重报 table1 的 2016 年 3 月 1 日 (20160301) 分区中的数据。

bq 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_id 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 方法并配置一个 copy 作业
  • 使用客户端库

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

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

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

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

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

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

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

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

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

导出表数据

从分区表导出全部数据的方法与从非分区表导出数据的方法相同。如需了解详情,请参阅导出表数据。要导出单个分区中的数据,请将分区修饰器 $date 附加到表名称。例如:mytable$20160201

此外,您还可以通过将分区名称附加到表名称来从 __NULL____UNPARTITIONED__ 分区导出数据。例如,mytable$__NULL__mytable$__UNPARTITIONED__

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

后续步骤

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

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

发送以下问题的反馈:

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