无法使用_TABLE_SUFFIX时，如何用BigQuery视图实现BI工具增量加载？

方案1：添加存储生成列（最直接） #

如果有权限修改原分区表，可新增一个存储类型的日期列，直接映射_TABLE_SUFFIX，让QuickSight能直接基于该列做增量刷新，同时保证BigQuery的分区修剪生效：

ALTER TABLE `your-project.your-dataset.your-partitioned-table`
ADD COLUMN partition_date DATE 
GENERATED ALWAYS AS PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) STORED;

• 该列为存储类型，不会额外增加查询计算量
• QuickSight配置增量刷新时，直接选择partition_date作为日期字段，设置刷新范围（比如仅加载上次刷新后的日期）
• BigQuery会自动将WHERE partition_date >= '2024-04-02'这类条件转换为_TABLE_SUFFIX >= '20240402'，实现分区修剪，不会扫描全部分区

方案2：参数化视图（无表修改权限时用） #

创建接受日期参数的视图，将日期参数转换为_TABLE_SUFFIX的整数格式，确保分区过滤逻辑能被BigQuery识别：

CREATE OR REPLACE VIEW `your-project.your-dataset.incremental-view`
AS
SELECT *
FROM `your-project.your-dataset.your-partitioned-table`
WHERE _TABLE_SUFFIX BETWEEN 
  FORMAT_DATE('%Y%m%d', @start_date) 
  AND FORMAT_DATE('%Y%m%d', @end_date);

• 在QuickSight中连接该视图时，创建start_date和end_date参数，绑定到增量刷新的时间范围（比如start_date设为上次刷新时间，end_date设为当前时间）
• 每次刷新时，QuickSight会传递参数值，BigQuery仅扫描对应_TABLE_SUFFIX的分区，避免全量扫描

方案3：普通视图+谓词下推验证 #

如果不想用参数，可创建仅转换日期的视图，然后在QuickSight数据集里添加过滤条件，确保BigQuery能将日期条件下推到分区：

CREATE OR REPLACE VIEW `your-project.your-dataset.date-converted-view`
AS
SELECT *, 
  PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) AS partition_date
FROM `your-project.your-dataset.your-partitioned-table`;

• 在QuickSight数据集的筛选条件中添加partition_date >= {last_refresh_timestamp}
• 验证分区修剪：在BigQuery中执行QuickSight生成的查询（可从QuickSight的查询日志中获取），用EXPLAIN查看计划，如果出现Partition filter: _TABLE_SUFFIX >= 'xxxxxx'则说明下推生效，不会扫全表

关键注意点 #

• 避免在视图中使用复杂函数转换_TABLE_SUFFIX（比如自定义UDF），会导致BigQuery无法识别分区过滤条件，从而扫描全部分区
• 测试时可通过BigQuery的Query Details查看扫描的分区数，确认增量加载的效率

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