本页面简要介绍 BigQuery 中的分区表支持。
分区表是一种特殊的表,分成多个区段(称为分区),可让您更轻松地管理和查询数据。通过将大型表划分为较小的分区,可以改善查询性能;通过减少查询读取的字节数,可以控制费用。
您可以按以下方式对 BigQuery 表进行分区:
提取时间分区表
当您创建按提取时间分区的表时,BigQuery 会自动将数据加载到基于日期的每日分区中,这些分区反映了数据的提取或到达时间。通过伪列和后缀标识符,您可以重新组织(替换)数据并将其重定向到特定日期的分区。
提取时间分区表包含名为 _PARTITIONTIME
的伪列,该列包含基于日期的数据加载时间戳。对时间分区表的查询可以通过提供表示分区位置的 _PARTITIONTIME
过滤条件来限制读取的数据。查询会读取指定分区中的所有数据,但 _PARTITIONTIME
谓词过滤条件会限制扫描的分区数量。
创建提取时间分区表时,分区与表具有相同的架构定义。如果您需要将数据加载到其架构与表架构不同的分区中,则必须在加载数据之前更新表架构。或者,您可以在加载作业或查询作业中,使用架构更新选项更新表的架构。
日期、时间戳或日期时间分区表
BigQuery 还允许使用基于特定的 DATE
、TIMESTAMP
或 DATETIME
列的分区表。写入日期/时间戳/日期时间分区表的数据会根据分区列中指定的时间单位值(使用 UTC 来表示 TIMESTAMP
)自动传送到相应的分区。
如果该表按 DATE
列分区,您可以按天、按月或按年创建分区。每个分区包含一个值范围,其中范围的起始值为一天、一月或一年的起始值,而范围的间隔是基于分区粒度的一天、一月或一年。如果表按 TIMESTAMP
或 DATETIME
列分区,您可以使用任何时间单位粒度类型(包括 HOUR
)创建分区。
日期/时间戳/日期时间分区表不需要 _PARTITIONTIME
伪列。针对日期/时间戳/日期时间分区表的查询可以根据分区列指定谓词过滤条件,以减少扫描的数据量。
当您创建日期/时间戳/日期时间分区表时,系统会创建两个特殊分区:
__NULL__
分区:表示分区列中具有NULL
值的行__UNPARTITIONED__
分区:表示日期在允许的日期范围之外的数据
除 __NULL__
和 __UNPARTITIONED__
分区外,分区列中的所有数据都与分区标识符的日期匹配。这样做可让查询确定哪些分区不包含满足过滤条件的数据。用于过滤分区列中数据的查询可以限制值并完全排除不必要的分区。
日期/时间戳/日期时间分区与分片的比较
除了使用日期/时间戳/日期时间分区表之外,您也可以使用基于时间的命名方法(例如 [PREFIX]_YYYYMMDD
)对表进行分片。这称为创建日期分片表。使用标准 SQL 或旧版 SQL,您都可以通过 UNION
运算符来指定查询,以限制查询扫描的表。
日期/时间戳/日期时间分区表的效果优于按日期分片的表。如果您创建以日期命名的表,BigQuery 必须为每个以日期命名的表保留架构和元数据副本。此外,使用以日期命名的表时,BigQuery 可能需要分别为每个要查询的表验证权限。该做法也会增加查询开销,影响查询性能。建议的最佳做法是使用日期/时间戳/日期时间分区表,而不是日期分片表。
比较分区选项
下表比较了分片表和日期/时间戳/日期时间分区表。
能力 | 分片表 | 提取时间分区表 | 分区表 |
---|---|---|---|
分区方法 | 无:对表进行分片并使用 UNION 运算符查询可以模拟分区。 |
根据数据的提取或到达日期进行分区。可以使用伪列引用分区信息。 | 根据指定的 TIMESTAMP 、DATE 或 DATETIME 列中的数据进行分区。对于每小时分区表,分区仅支持 TIMESTAMP 和 DATETIME 列。 |
分区标识符 | 无 | 您可以使用 0001-01-01 和 9999-12-31 之间的任何有效日期,但 DML 语句不能引用 1970-01-01 之前或 2159-12-31 之后的日期。 |
已绑定的 DATE 、TIMESTAMP 或 DATETIME 列中的有效输入。目前,1960-01-01 之前和 2159-12-31 之后的日期值会放在共享的 UNPARTITIONED 分区中。NULL 值位于显式 NULL 分区中。分区标识符必须遵循以下格式:
|
限制扫描的数据 | 通过排除查询中不必要的列,仅引用所需分片并限制数据。 | 使用 _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_filter
。bq
命令行工具已使用表级层选项,因此如何使用 bq
命令不会发生变化。如果您使用 BigQuery API,则需要在表级层使用 require_partitioning_filter
选项。
分区表配额和限制
分区表在 BigQuery 中有确定的限制。
配额和限制也适用于针对分区表运行的不同类型的作业,其中包括:
要详细了解所有配额和限制,请参阅配额和限制。
分区表价格
在 BigQuery 中创建和使用分区表时,您的费用取决于分区中存储的数据量以及对数据运行的查询:
很多分区表操作都是免费的,包括将数据加载到分区、复制分区,以及从分区导出数据。这些操作虽然免费,但是受 BigQuery 的配额和限制约束。如需了解所有免费操作,请参阅价格页面上的免费操作。
后续步骤
- 要了解如何创建和使用提取时间分区表,请参阅创建和使用提取时间分区表。
- 如需了解如何创建和使用基于列的时间分区表,请参阅创建和使用日期/时间戳/日期时间分区表。
- 如需了解如何创建和使用整数范围分区表,请参阅创建和使用整数范围分区表。
- 如需了解如何管理和更新分区表,请参阅管理分区表。
- 如需了解如何查询分区表,请参阅查询分区表。