BigQuery分区裁剪未达预期 相同查询原表与重分区表扫描量差异巨大

问题描述 #

现有如下表：

该表以_date字段进行分区：

各分区的行数量分布较为均匀：

我在查询时尝试通过分区裁剪限制扫描的分区，发现查询处理的字节量远高于预期：

但当我将该表复制到新表，使用的SQL语句如下：

CREATE TABLE
  `analytics-310706.domain_events_tables_repartitioned.domain_user_UserRegistered__1838592482`
PARTITION BY
  _date AS
SELECT
  *
FROM
  `analytics-310706.domain_events_tables.domain_user_UserRegistered__1838592482`

对新生成的重分区表执行完全相同的查询，处理的字节量降低了数个数量级：

请问原表存在什么问题？原表的分区统计信息显示正常，原表查询的执行详情中也未找到根因。为什么相同查询在原表上需要扫描166MB，在重分区后的表上仅需扫描0.75MB？

注：两者的差异不仅是预估值的区别，实际处理的字节数同样存在上述巨大差异。

问题原因 #

原表存在分区存储碎片化问题，这是分区表在频繁小批量写入、流式写入或者多次MERGE/UPDATE操作后非常常见的问题：

1. 原表在历史写入过程中产生了大量小尺寸的存储文件。BigQuery这类OLAP引擎的存储底层以固定大小的块为单位进行扫描，大量小文件会导致块空间被严重浪费，同时即使做了分区裁剪，也需要遍历所有小文件的元数据、读取每个文件的有效内容，额外开销会被成倍放大。
2. 部分非对齐写入场景下，虽然元数据显示分区规则正常，但实际部分存储文件中会混杂多个分区的_date值，分区裁剪无法直接跳过整个文件，必须扫描文件内容过滤不符合条件的数据，进一步提升了扫描量。

你执行重分区操作的过程，本质是将全表数据按_date分区键重新整理写入，每个分区的数据会被合并为少量连续的大文件，且文件内容完全和分区键对齐，此时分区裁剪可以直接跳过所有不匹配的分区文件，仅扫描目标分区的少量大文件，扫描量自然会出现数个数量级的下降。

验证与优化建议 #

• 你可以通过查看两张表的存储详情，对比每个分区对应的文件数量，原表的单分区文件数会远高于重分区后的表，即可验证碎片化问题。
• 后续避免该问题可以调整写入策略，增大单次写入的批量大小减少小文件生成，也可以定期对分区表执行全量重分区操作，或者开启存储引擎的自动文件合并能力。

内容的提问来源于stack exchange，提问作者Tobias Hermann