分区表简介

本页面简要介绍 BigQuery 中的分区表支持。

分区表是一种特殊的表,分成多个区段(称为分区),可让您更轻松地管理和查询数据。通过将大型表划分为较小的分区,可以改善查询性能;通过减少查询读取的字节数,可以控制费用。

您可以按以下方式对 BigQuery 表进行分区:

提取时间分区表

当您创建按提取时间分区的表时,BigQuery 会自动将数据加载到基于日期的每日分区中,这些分区反映了数据的提取或到达时间。通过伪列和后缀标识符,您可以重新组织(替换)数据并将其重定向到特定日期的分区。

提取时间分区表包含名为 _PARTITIONTIME 的伪列,该列包含基于日期的数据加载时间戳。对时间分区表的查询可以通过提供表示分区位置的 _PARTITIONTIME 过滤条件来限制读取的数据。查询会读取指定分区中的所有数据,但 _PARTITIONTIME 谓词过滤条件会限制扫描的分区数量。

创建提取时间分区表时,分区与表具有相同的架构定义。如果您需要将数据加载到其架构与表架构不同的分区中,则必须在加载数据之前更新表架构。或者,您可以在加载作业或查询作业中,使用架构更新选项更新表的架构。

日期、时间戳或日期时间分区表

BigQuery 还允许使用基于特定的 DATETIMESTAMPDATETIME 列的分区表。写入日期/时间戳/日期时间分区表的数据会根据分区列中指定的时间单位值(使用 UTC 来表示 TIMESTAMP)自动传送到相应的分区。

如果该表按 DATE 列分区,您可以按天、按月或按年创建分区。每个分区包含一个值范围,其中范围的起始值为一天、一月或一年的起始值,而范围的间隔是基于分区粒度的一天、一月或一年。如果表按 TIMESTAMPDATETIME 列分区,您可以使用任何时间单位粒度类型(包括 HOUR)创建分区。

日期/时间戳/日期时间分区表不需要 _PARTITIONTIME 伪列。针对日期/时间戳/日期时间分区表的查询可以根据分区列指定谓词过滤条件,以减少扫描的数据量。

当您创建日期/时间戳/日期时间分区表时,系统会创建两个特殊分区:

  • __NULL__ 分区:表示分区列中具有 NULL 值的行
  • __UNPARTITIONED__ 分区:表示日期在允许的日期范围之外的数据

__NULL____UNPARTITIONED__ 分区外,分区列中的所有数据都与分区标识符的日期匹配。这样做可让查询确定哪些分区不包含满足过滤条件的数据。用于过滤分区列中数据的查询可以限制值并完全排除不必要的分区。

日期/时间戳/日期时间分区与分片的比较

除了使用日期/时间戳/日期时间分区表之外,您也可以使用基于时间的命名方法(例如 [PREFIX]_YYYYMMDD)对表进行分片。这称为创建日期分片表。使用标准 SQL 或旧版 SQL,您都可以通过 UNION 运算符来指定查询,以限制查询扫描的表。

日期/时间戳/日期时间分区表的效果优于按日期分片的表。如果您创建以日期命名的表,BigQuery 必须为每个以日期命名的表保留架构和元数据副本。此外,使用以日期命名的表时,BigQuery 可能需要分别为每个要查询的表验证权限。该做法也会增加查询开销,影响查询性能。建议的最佳做法是使用日期/时间戳/日期时间分区表,而不是日期分片表。

比较分区选项

下表比较了分片表和日期/时间戳/日期时间分区表。

能力 分片表 提取时间分区表 分区表
分区方法 无:对表进行分片并使用 UNION 运算符查询可以模拟分区。 根据数据的提取或到达日期进行分区。可以使用伪列引用分区信息。 根据指定的 TIMESTAMPDATEDATETIME 列中的数据进行分区。

对于每小时分区表,分区仅支持 TIMESTAMPDATETIME 列。
分区标识符 您可以使用 0001-01-01 和 9999-12-31 之间的任何有效日期,但 DML 语句不能引用 1970-01-01 之前或 2159-12-31 之后的日期。 已绑定的 DATETIMESTAMPDATETIME 列中的有效输入。目前,1960-01-01 之前和 2159-12-31 之后的日期值会放在共享的 UNPARTITIONED 分区中。NULL 值位于显式 NULL 分区中。

分区标识符必须遵循以下格式:
  • yyyyMMddHH(对于每小时分区)。
  • yyyyMMdd(对于每日分区)。
  • yyyyMM(对于每月分区)。
  • yyyy(对于每年分区)。
限制扫描的数据 通过排除查询中不必要的列,仅引用所需分片并限制数据。 使用 _PARTITIONTIME 伪列排除分区。 对分区列使用谓词过滤条件。
分区数量 表的数量没有限制,但查询最多只能引用 1000 个表。 最多 4000 个分区。 最多 4000 个分区。
更新操作 每天最多只能更新 1000 次 单个操作可以提交到单个分区。最新分区(默认)或使用分区修饰器(如 [TABLE]$[DATE])指定的分区。 单个操作最多可以将数据提交到 2000 个不同的分区。
流式插入 您可以流式插入到任何分片表,但必须手动指定分片。 数据最初放在 UNPARTITIONED 分区中,然后默认提取到当前日期。通过使用分区后缀,您还可以根据当前 UTC 时间直接流式插入到过去 31 天和未来 16 天(相对于当前日期)内的分区中。 您可以流式插入过去 5 年和未来 1 年之间的数据。此范围之外的数据会被拒绝。此范围内的数据最初放在 UNPARTITIONED 分区中。当未分区的数据累积到足够多时,BigQuery 会自动对数据重新进行分区。
时区评估 由用户语义定义 世界协调时间 (UTC) 世界协调时间 (UTC)

整数范围分区表

BigQuery 允许使用基于特定的 INTEGER 列的分区表,您可以选择起始值、终止值和间隔值。针对整数范围分区表的查询可以根据分区列指定谓词过滤条件,以减少扫描的数据量。

如需创建整数范围分区表,请提供以下各项:

  • 用于创建整数范围分区的列
  • 范围分区的起始值(含边界值)
  • 范围分区的终止值(不含边界值)
  • 分区中每个范围的间隔值

超出表范围的值会进入 UNPARTITIONED 分区。

例如,如果您使用以下值创建整数范围分区:

参数
column name customer_id
start 0
end 100
interval 10

表将按 customer_id 列进行范围分区,间隔值为 10。值 0 到 9 位于一个分区中,值 10 到 19 位于另一个分区,…,最后的值 90 到 99 将位于另一个分区。0 到 99 之外的值(例如 -1 或 100)将位于 UNPARTITIONED 分区。Null 值将位于 NULL 分区。

当您创建整数范围分区表时,系统会创建两个特殊分区:

  • __NULL__ 分区:表示分区列中具有 NULL 值的行
  • __UNPARTITIONED__ 分区:表示在允许的整数起始值和间隔值范围之外的数据

__NULL____UNPARTITIONED__ 分区外,分区列中的所有数据都在整数起始值和间隔值范围之内。这样做可让查询确定哪些分区不包含满足过滤条件的数据。用于过滤分区列中数据的查询可以限制值并完全排除不必要的分区。

开始值和终止值之间可能范围的数量上限为 1 万。但是,由于每个范围都是一个分区,因此每个表的数据范围数量上限为 4000 个分区。

整数范围分区与聚簇的比较

整数范围分区和聚簇均可提高性能并降低查询费用。它们之间的主要差异和用例如下。

使用整数范围分区的情况:

  • 显式定义用于进行表分区的范围。您需要指定数据的分区方式以及每个分区中的数据。

  • 在查询运行之前,了解查询费用。分区删减操作是在查询运行之前执行的,因此您可以通过试运行了解分区删减后的查询费用。聚簇删减操作是在查询运行时执行的,因此只有查询结束后才能知道费用。

  • 处理某个分区(例如您希望将数据加载到特定分区时),或清除特定分区的数据。

使用聚簇的情况:

  • 您不在乎对数据划分聚簇的方式,只要可以提升性能和降低费用即可。BigQuery 会自动确定如何对数据划分聚簇以实现最佳性能并降低费用。

  • 需要超过 4000 个分区。对于分区表,BigQuery 的限制是 4000 个分区。对表中的聚簇数量没有限制。

注意,您可以对同一整数列进行分区和聚簇,从而享有两者的优势。数据将首先根据特定的整数范围进行分区。在每个范围内,如果数据量足够大,还将对这些数据划分聚簇。查询表时,分区根据分区删减设置查询费用的上限。当查询实际运行时,可能还存在其他查询费用节省。

使用 require_partitioning_filter

随着整数范围分区的发布,BigQuery 现支持多种分区类型:

  • 提取时间
  • 日期/时间戳/日期时间
  • 整数范围

为了简化 BigQuery API,我们将 require_partitioning_filter 参数从分区类型级层移到表级层。为实现日期/时间戳/日期时间分区的向后兼容性,分区级层仍支持 require_partitioning_filter。您也可以在表级层指定它。对于整数范围分区,只能在表级层指定 require_partitioning_filterbq 命令行工具已使用表级层选项,因此如何使用 bq 命令不会发生变化。如果您使用 BigQuery API,则需要在表级层使用 require_partitioning_filter 选项。

分区表配额和限制

分区表在 BigQuery 中有确定的限制

配额和限制也适用于针对分区表运行的不同类型的作业,其中包括:

如需详细了解所有配额和限制,请参阅配额和限制

分区表价格

在 BigQuery 中创建和使用分区表时,您的费用取决于分区中存储的数据量以及对数据运行的查询:

很多分区表操作都是免费的,包括将数据加载到分区、复制分区,以及从分区导出数据。这些操作虽然免费,但是受 BigQuery 的配额和限制约束。如需了解所有免费操作,请参阅价格页面上的免费操作

后续步骤