为何无法使用时区转换后的列筛选按天分区的BigQuery表？

错误原因 #

你的物化视图依赖的AirView.FilteredData是分区表，BigQuery明确要求：基于分区表创建的物化视图，必须要么在查询中对原表的分区列添加筛选条件，要么在输出字段中保留原分区列。你当前的查询既未筛选原分区列，也未输出原UTC时间列，因此触发了分区筛选要求的报错。

解决方案 #

方案一：保留原UTC分区列，同时输出本地时区时间 #

这种方法最稳妥，既能满足BigQuery的分区要求，又能提供本地时区的时间用于可视化。修改后的SQL会保留原UTC时间列（作为原分区列），同时新增本地时区时间字段：

CREATE MATERIALIZED VIEW `AirView.HourAvg`
  PARTITION BY DATE(a.time)  -- 基于原UTC时间的日期分区，与原表逻辑一致
  OPTIONS (
    refresh_interval_minutes=60
) AS (
SELECT
  b.thing_name,
  b.city,
  b.device_type,
  a.thing_id,
  IF(b.device_type="Stationary", b.latitude, a.latitude) AS latitude,
  IF(b.device_type="Stationary", b.longitude, a.longitude) AS longitude,
  a.time AS utc_timestamp,  -- 保留原UTC分区列，满足BigQuery的分区要求
  DATETIME(DATETIME_TRUNC(a.time, MINUTE), "Asia/Kolkata") AS local_timestamp  -- 转换为本地时区的时间
FROM (
  SELECT
    *, (no2 * 1.88) AS _no2, (so2 * 2.62) AS _so2
  FROM
    `AirView.FilteredData`
  )a
JOIN
  `AirView.ThingsTable` b
ON
  b.thing_id = a.thing_id
GROUP BY 1,2,3,4,5,6,7,8  -- 新增了utc_timestamp，需更新GROUP BY序号
)

使用说明：

• 在Looker Studio中，直接用local_timestamp作为时间维度做可视化即可
• 当需要做分区筛选时，用utc_timestamp列，BigQuery会自动识别并应用分区过滤，避免全表扫描

方案二：在子查询中添加分区筛选（适合无需保留原UTC列的场景） #

如果不需要保留原UTC时间列，可以在子查询中对原表的分区列添加相对时间筛选，满足BigQuery的要求：

CREATE MATERIALIZED VIEW `AirView.HourAvg`
  PARTITION BY DATE(DATETIME(DATETIME_TRUNC(a.time, MINUTE), "Asia/Kolkata"))
  OPTIONS (
    refresh_interval_minutes=60
) AS (
SELECT
  b.thing_name,
  b.city,
  b.device_type,
  a.thing_id,
  IF(b.device_type="Stationary", b.latitude, a.latitude) AS latitude,
  IF(b.device_type="Stationary", b.longitude, a.longitude) AS longitude,
  DATETIME(DATETIME_TRUNC(a.time, MINUTE), "Asia/Kolkata") AS timestamp
FROM (
  SELECT
    *, (no2 * 1.88) AS _no2, (so2 * 2.62) AS _so2
  FROM
    `AirView.FilteredData`
  WHERE
    time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 365 DAY)  -- 对原分区列添加筛选条件
  )a
JOIN
  `AirView.ThingsTable` b
ON
  b.thing_id = a.thing_id
GROUP BY 1,2,3,4,5,6,7
)

注意：这种方法的物化视图仅包含最近365天的数据，若需要全量数据，建议使用方案一。

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