BigQuery分区表采样机制相关技术问题咨询

1. 查询表的块数量 #

通过BigQuery的INFORMATION_SCHEMA.TABLE_STORAGE视图可以查询表的块数，示例SQL如下：

SELECT total_logical_blocks, total_physical_blocks
FROM `region-your_region`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE table_catalog = '你的项目ID'
  AND table_schema = '你的数据集名称'
  AND table_name = '你的表名';

• total_logical_blocks：表的逻辑块数量（逻辑层面的块划分）
• total_physical_blocks：实际存储的物理块数量（考虑了压缩、重复数据删除等优化后的块数）
  根据需求选择对应字段即可。

2. 分区表的采样机制与执行顺序 #

• 采样机制：分区表的采样是按每个分区独立执行的，每个分区会被当作单独的数据集处理，同样遵循“至少扫描完整块”的规则，采样比例是针对单个分区内的数据计算的。
• 执行顺序：采样并不总是优先于WHERE子句：
  • 如果WHERE子句包含分区字段过滤（比如指定日期分区范围），BigQuery会先过滤出符合条件的分区，再对这些分区内的数据执行采样；
  • 如果WHERE子句是针对非分区字段的过滤，通常会先执行采样，再对采样后的结果应用过滤条件。

3. 日期分区表的低成本采样方案 #

完全可以用采样实现，而且是低成本获取特定分区样本的最优方式，推荐两种写法：

写法一：先过滤分区再采样 #

SELECT *
FROM `你的项目ID.你的数据集名称.你的表名`
WHERE date_partition = '2024-05-20' -- 指定目标日期分区
TABLESAMPLE SYSTEM (1 PERCENT); -- 按1%比例采样，可调整比例

这种方式会先筛选出目标分区，再在该分区内基于块进行采样，仅扫描该分区内的部分块（仍遵循完整块规则），扫描量远低于全分区扫描，成本大幅降低。

写法二：直接引用分区表的分区进行采样 #

BigQuery允许直接通过表名$分区值的格式引用单个分区，再添加采样：

SELECT *
FROM `你的项目ID.你的数据集名称.你的表名$20240520` -- 直接指定目标分区
TABLESAMPLE SYSTEM (1 PERCENT);

效果和写法一一致，同样只针对目标分区采样，成本可控。

注意：如果需要精确行数的样本，比如固定取1000条，可以用ORDER BY RAND() LIMIT 1000，但这种方式需要扫描全分区数据，成本远高于TABLESAMPLE，非必要不推荐。

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