分区表简介

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

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

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

  • 时间单位列:按照表中的 TIMESTAMPDATEDATETIME 列对表进行分区。

  • 提取时间:按照 BigQuery 提取数据时的时间戳对表进行分区。

  • 整数范围:表按照整数列进行分区。

如果查询过滤分区列的值,BigQuery 可以扫描与过滤条件匹配的分区并跳过其余分区。此过程称为“删减”

时间单位列分区

您可以按照表的 DATETIMESTAMPDATETIME 列对表进行分区。当您将数据写入表时,BigQuery 会根据列中的值自动将数据放入正确的分区。

对于 TIMESTAMPDATETIME 列,分区可以具有每小时、每天、每月或每年的时间粒度。对于 DATE 列,分区可以具有每天、每月或每年的时间粒度。分区边界基于世界协调时间 (UTC)。

例如,假设您对包含 DATETIME 列的表按月分区。如果您将以下值插入表中,则行会写入以下分区:

列值 分区(每月)
DATETIME("2019-01-01") 201901
DATETIME("2019-01-15") 201901
DATETIME("2019-04-30") 201904

此外,系统会创建两个特殊分区:

  • __NULL__:包含分区列中具有 NULL 值的行。
  • __UNPARTITIONED__:包含分区列的值早于 1960-01-01 或晚于 2159-12-31 的行。

提取时间分区

当您创建按提取时间分区的表时,BigQuery 会根据 BigQuery 提取数据时的时间自动将行分配到分区。您可以为分区选择每小时、每天、每月或每年的时间粒度。分区边界基于世界协调时间 (UTC)。

注入时间分区表具有名为 _PARTITIONTIME 的伪列。此列的值是每行的提取时间,截断至分区边界(例如每小时或每天)。例如,假设您创建了一个每小时分区的提取时间分区表,并在以下时间发送数据。

提取时间 _PARTITIONTIME 分区(每小时)
2021-05-07 17:22:00 2021-05-07 17:00:00 2021050717
2021-05-07 17:40:00 2021-05-07 17:00:00 2021050717
2021-05-07 18:31:00 2021-05-07 18:00:00 2021050718

由于本例中的表使用每小时分区,因此 _PARTITIONTIME 的值被截断到小时边界。BigQuery 使用此值来确定数据的正确分区。

您还可以将数据写入特定分区。例如,您可能需要加载历史数据或根据时区进行调整。您可以使用 0001-01-01 和 9999-12-31 之间的任何有效日期。但是,DML 语句不能引用 1970-01-01 之前或 2159-12-31 之后的日期。如需了解详情,请参阅将数据写入特定分区

除了使用 _PARTITIONTIME,您还可以使用 _PARTITIONDATE_PARTITIONDATE 伪列包含与 _PARTITIONTIME 伪列中的值对应的世界协调时间 (UTC) 日期。

整数范围分区

您可以按照特定 INTEGER 列中的值范围对表进行分区。如需创建整数范围分区表,请提供以下各项:

  • 分区列。
  • 范围分区的起始值(含边界值)。
  • 范围分区的终止值(不含边界值)。
  • 分区中每个范围的间隔值。

例如,假设您使用以下规范创建了一个整数范围分区:

参数
列名 customer_id
开始 0
end 100
interval 10

表将按 customer_id 列进行范围分区,间隔值为 10。值 0 到 9 放入一个分区,值 10 到 19 放入下一个分区,以此类推,直到 99。此范围以外的值放入名为 __UNPARTITIONED__ 的分区。customer_idNULL 的所有行都会进入名为 __NULL__ 的分区。

选择每日、每小时、每月或每年分区。

按时间单位列或提取时间对表进行分区时,您可以选择分区是具有每日、每小时、每月还是每年的时间粒度。

  • 每日分区是默认分区类型。如果您的数据分布于多个日期范围内,或者如果数据随时间不断增加,则每日分区是一个不错的选择。

  • 如果您的表包含大量数据,且这些数据覆盖一个较短的日期范围(时间戳值通常小于六个月),请选择每小时分区。如果选择每小时分区,请确保分区数量不超出分区限制范围。

  • 如果您的表每天包含的数据量相对较少,但覆盖的日期范围很广,请选择每月或每年分区。如果您的工作流需要频繁更新或添加覆盖较广日期范围的行(例如超过 500 个日期),我们也建议您使用此选项。在这些场景中,请对分区列使用每月或每年分区以及聚簇操作,以获得最佳性能。如需了解详情,请参阅本页面上的分区与聚簇的比较

分区与聚簇的比较

分区和聚簇均可提高性能并降低查询费用。

在以下情况下使用聚簇:

  • 运行查询前无需严格的费用保证。
  • 您需要比分区单独允许更多的粒度。除了分区优势外,您还可以使用聚簇优势,还可以对分区和聚簇使用相同的列。
  • 您的查询通常对多个特定列使用过滤器或聚合。
  • 一列或多列中值数量的基数较大。

在以下情况下使用分区:

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

  • 您需要分区级层管理。例如,您需要设置分区到期时间,将数据加载到特定分区,或者删除分区。

  • 您想要指定数据的分区方式以及每个分区中的数据。例如,您想要定义时间粒度或定义为整数范围分区对表分区的范围。

在以下情况下,首选聚簇而非分区:

  • 分区会生成每个分区的少量数据(约为 1 GB)。
  • 分区会导致大量分区超出 分区表的限制
  • 分区会导致您的更改操作频繁修改表中的大多数分区(例如每隔几分钟)。

您还可以将分区与聚簇结合使用。数据首先进行分区,然后每个分区中的数据按聚簇列进行聚簇。

查询表时,分区根据分区删减设置查询费用的上限。由于聚簇删减操作,当查询实际运行时,可能还存在其他查询费用节省。

分区与分片的比较

表分片是使用 [PREFIX]_YYYYMMDD 等命名前缀将数据存储到多个表中的做法。

更推荐使用分区而不是表分片,因为分区表性能更佳。对于分片表,BigQuery 必须为每个表保留架构和元数据的副本。BigQuery 可能还需要分别为每个要查询的表验证权限。该做法也会增加查询开销,影响查询性能。

如果您之前创建了日期分片表,可以将其转换为提取时间分区表。如需了解详情,请参阅将日期分片表转换为提取时间分区表

限制

无法使用旧版 SQL 查询分区表,也无法将查询结果写入分区表中。

时间单位列分区表存在如下限制:

  • 分区列必须是标量 DATETIMESTAMPDATETIME 列。虽然列模式可以是 REQUIREDNULLABLE,但不能是 REPEATED(基于数组)。
  • 此外,分区列必须是顶级字段。不能将 RECORD (STRUCT) 中的叶字段用作分区列。

整数范围分区表存在如下限制:

  • 分区列必须为 INTEGER 列。虽然列模式可以是 REQUIREDNULLABLE,但不能是 REPEATED(基于数组)。
  • 此外,分区列必须是顶级字段。不能将 RECORD (STRUCT) 中的叶字段用作分区列。

分区表配额和限制

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

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

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

分区表价格

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

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

如需了解在 BigQuery 中控制费用的最佳实践,请参阅控制 BigQuery 费用

分区表安全性

分区表的访问权限控制与标准表的访问权限控制相同。如需了解详情,请参阅表访问权限控制简介

后续步骤