管理分区表数据

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

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

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

将数据加载到分区表中

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

如需在将数据加载到分区表时使用架构自动检测功能,请使用 Cloud Console、bq 命令行工具或 API。

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

Java

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

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobId;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.LoadJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TimePartitioning;
import java.time.Duration;
import java.time.temporal.ChronoUnit;
import java.util.UUID;

public class LoadPartitionedTable {

  public static void runLoadPartitionedTable() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "/path/to/file.csv";
    loadPartitionedTable(datasetName, tableName, sourceUri);
  }

  public static void loadPartitionedTable(String datasetName, String tableName, String sourceUri)
      throws Exception {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, tableName);

      Schema schema =
          Schema.of(
              Field.of("name", StandardSQLTypeName.STRING),
              Field.of("post_abbr", StandardSQLTypeName.STRING),
              Field.of("date", StandardSQLTypeName.DATE));

      // Configure time partitioning. For full list of options, see:
      // https://cloud.google.com/bigquery/docs/reference/rest/v2/tables#TimePartitioning
      TimePartitioning partitioning =
          TimePartitioning.newBuilder(TimePartitioning.Type.DAY)
              .setField("date")
              .setExpirationMs(Duration.of(90, ChronoUnit.DAYS).toMillis())
              .build();

      LoadJobConfiguration loadJobConfig =
          LoadJobConfiguration.builder(tableId, sourceUri)
              .setFormatOptions(FormatOptions.csv())
              .setSchema(schema)
              .setTimePartitioning(partitioning)
              .build();

      // Create a job ID so that we can safely retry.
      JobId jobId = JobId.of(UUID.randomUUID().toString());
      Job loadJob = bigquery.create(JobInfo.newBuilder(loadJobConfig).setJobId(jobId).build());

      // Load data from a GCS parquet file into the table
      // Blocks until this load table job completes its execution, either failing or succeeding.
      Job completedJob = loadJob.waitFor();

      // Check for errors
      if (completedJob == null) {
        throw new Exception("Job not executed since it no longer exists.");
      } else if (completedJob.getStatus().getError() != null) {
        // You can also look at queryJob.getStatus().getExecutionErrors() for all
        // errors, not just the latest one.
        throw new Exception(
            "BigQuery was unable to load into the table due to an error: \n"
                + loadJob.getStatus().getError());
      }
      System.out.println("Data successfully loaded into time partitioned table during load job");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println(
          "Data not loaded into time partitioned table during load job \n" + e.toString());
    }
  }
}

浏览表数据

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

  • 使用 Cloud Console
  • 使用 bq 命令行工具的 bq head 命令
  • 调用 tabledata.list API 方法
  • 使用客户端库

所需权限

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

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

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

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

浏览分区表数据

如需浏览分区表数据,请执行以下操作:

控制台

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

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

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

    表详细信息

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

    行数

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

bq

发出 bq head 命令并使用 --max_rows 标志,即可列出特定数量的表行中的所有字段。如果未指定 --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 行中的所有字段。输出采用 JSON 格式。此示例将 myotherproject 指定作为项目,而不是使用默认项目。

bq head --format=prettyjson 'myotherproject:mydataset.mytable$20160901'

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

bq head \
--format=prettyjson \
--start_row 100 \
--selected_fields "field1,field2" \
'mydataset.mytable$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

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

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

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

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

此外,如果用户具有 bigquery.datasets.create 权限,则当该用户创建数据集时,系统会为其授予该数据集的 bigquery.dataOwner 访问权限。借助 bigquery.dataOwner 访问权限,用户可以对数据集中的表和分区执行附加和覆盖操作。

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

使用加载作业

如需对分区执行附加或覆盖操作,您可以使用 bq load 命令,或者调用 jobs.insert 方法并配置 load 作业。Cloud Console 不支持在加载作业中对分区执行附加或覆盖操作。

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

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

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

命令行选项 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 作业。Cloud Console 不支持在查询作业中对分区执行附加或覆盖操作。

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

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

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

命令行选项 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 更新或删除数据,您必须先删除分区表,然后使用新数据重新创建该分区表。您也可以通过编写查询修改数据,并将查询结果写入新的分区表。

复制分区表数据

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

  • 使用 Cloud Console
  • 使用 bq 命令行工具的 bq cp 命令
  • 调用 jobs.insert API 方法并配置复制作业
  • 使用客户端库

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

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

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

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

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

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

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

使用分区修饰器流式传输到每日分区表时,您可将数据流式传输到相对于当前日期(基于当前世界协调时间 (UTC))的过去 31 天到未来 16 天内的分区。如需写入日期在这此允许范围以外的分区,您可以使用加载或查询作业。对于每小时分区表,不支持使用分区修饰器进行流式传输,因此必须改为流式传输到基表。

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

导出表数据

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

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

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

后续步骤

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