BigQuery获取最新分区查询报错：需分区列过滤用于分区消除

原查询方案 #

原本使用以下查询获取数据，但由于分区可能存在延迟，无法确保拿到目标数据：

SELECT DISTINCT
  *
FROM
  `project.dataset.table` t
WHERE DATE(_PARTITIONTIME) >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) 

尝试的两种替代查询 #

方案一：子查询获取最近30天内的最大分区日期 #

SELECT DISTINCT
  *
FROM
  `project.dataset.table` t
WHERE DATE(_PARTITIONTIME) IN 
(
   SELECT 
     MAX(DATE(_PARTITIONTIME)) AS max_partition
   FROM `project.dataset.table`
   WHERE DATE(_PARTITIONTIME) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
)

方案二：通过INFORMATION_SCHEMA获取最新分区 #

SELECT DISTINCT
  *
FROM
  `project.dataset.table` t
WHERE TIMESTAMP(DATE(_PARTITIONTIME)) IN 
(
  SELECT parse_timestamp("%Y%m%d", MAX(partition_id))
  FROM `project.dataset.INFORMATION_SCHEMA.PARTITIONS`
  WHERE table_name = 'table'
)

报错信息 #

Cannot query over table 'project.dataset.table' without a filter over
column(s) '_PARTITION_LOAD_TIME', '_PARTITIONDATE', '_PARTITIONTIME'
that can be used for partition elimination.

中文翻译：

无法查询表 'project.dataset.table'，因为未对可用于分区消除的列 '_PARTITION_LOAD_TIME'、'_PARTITIONDATE'、'_PARTITIONTIME' 设置过滤条件。

问题原因与解决思路 #

BigQuery对分区表查询有硬性要求：必须包含能触发分区消除的过滤条件，也就是过滤条件需是直接作用在分区列上的常量或可提前计算的表达式。而你用的子查询结果在查询优化阶段无法被识别为确定值，所以无法触发分区消除，导致报错。

可行解决方法 #

1. 用脚本变量提前获取最大分区值
   先查询得到最新分区日期，再将其作为变量传入主查询，确保过滤条件是明确的常量，触发分区消除：

DECLARE max_partition_date DATE;

SET max_partition_date = (
  SELECT MAX(DATE(_PARTITIONTIME))
  FROM `project.dataset.table`
  WHERE DATE(_PARTITIONTIME) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
);

SELECT DISTINCT *
FROM `project.dataset.table` t
WHERE DATE(_PARTITIONTIME) = max_partition_date;

2. 组合逻辑：优先取昨日分区，不存在则取最近最大分区
   如果可以接受“优先尝试昨日分区，失败则取最近30天内最大分区”的逻辑，可用COALESCE实现：

SELECT DISTINCT *
FROM `project.dataset.table` t
WHERE DATE(_PARTITIONTIME) = COALESCE(
  -- 先尝试取昨天的分区
  (SELECT DATE(_PARTITIONTIME) FROM `project.dataset.table` WHERE DATE(_PARTITIONTIME) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) LIMIT 1),
  -- 若不存在则取最近30天内的最大分区
  (SELECT MAX(DATE(_PARTITIONTIME)) FROM `project.dataset.table` WHERE DATE(_PARTITIONTIME) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
);

3. 动态生成查询（客户端/脚本场景）
   先查询INFORMATION_SCHEMA拿到最新分区ID，再拼接成主查询执行：

-- 第一步：获取最新分区日期
SELECT parse_date("%Y%m%d", MAX(partition_id)) AS max_partition
FROM `project.dataset.INFORMATION_SCHEMA.PARTITIONS`
WHERE table_name = 'table';

-- 第二步：将上面得到的日期替换到下面的查询中执行
SELECT DISTINCT *
FROM `project.dataset.table` t
WHERE DATE(_PARTITIONTIME) = 'YYYY-MM-DD';

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