BigQuery动态视图创建咨询：自动整合逐年新增表数据

问题背景 #

你需要创建BigQuery视图，自动整合每年生成的dataset_source.table_name_prefix_YYYY_AGGREGATED格式年度表数据，且无法修改这些表。但同一数据集存在视图，导致通配符表功能无法使用（会匹配到视图报错），现有2022年的查询逻辑需要改成动态适配新表的方案。

最优方案1：存储过程+定时调度（推荐） #

普通视图无法动态识别新表，用存储过程自动生成视图定义，再通过定时调度自动更新，完全适配新表的自动生成逻辑。

步骤： #

1. 创建存储过程
   这个过程会自动扫描数据集里符合命名规则的实体表，拼接成UNION ALL查询逻辑，然后更新视图：

   CREATE OR REPLACE PROCEDURE dataset_source.refresh_dynamic_agg_view()
   BEGIN
     -- 提取所有符合规则的年度实体表（排除视图）
     DECLARE table_list STRING;
     SET table_list = (
       SELECT STRING_AGG(
         CONCAT('`', table_catalog, '.', table_schema, '.', table_name, '`'),
         ' UNION ALL '
       )
       FROM dataset_source.INFORMATION_SCHEMA.TABLES
       WHERE table_name LIKE 'table_name_prefix_%_AGGREGATED'
         AND table_type = 'BASE TABLE' -- 只取实体表，避开视图
     );
   
     -- 拼接完整的视图SQL
     DECLARE view_sql STRING;
     SET view_sql = CONCAT('
       WITH cte AS (
         SELECT 
           d.MULTIDIVISION_ZONE, 
           s.ISO_COUNTRIES[OFFSET(0)] Country, 
           d.DIVISION_CODE, 
           BRAND, 
           d.MULTIDIVISION_REGION,
           SUM(CASE WHEN PERIOD IN (''M01'', ''M02'', ''M03'') THEN VALUE_AVERAGE_RATE ELSE 0 END) YTD03,
           SUM(CASE WHEN PERIOD IN (''M01'', ''M02'', ''M03'', ''M04'', ''M05'', ''M06'') THEN VALUE_AVERAGE_RATE ELSE 0 END) YTD06,
           SUM(CASE WHEN PERIOD IN (''M01'', ''M02'', ''M03'', ''M04'', ''M05'', ''M06'', ''M07'', ''M08'', ''M09'') THEN VALUE_AVERAGE_RATE ELSE 0 END) YTD09,
           SUM(CASE WHEN PERIOD IN (''M01'', ''M02'', ''M03'', ''M04'', ''M05'', ''M06'', ''M07'', ''M08'', ''M09'', ''M10'', ''M11'', ''M12'') THEN VALUE_AVERAGE_RATE ELSE 0 END) YTD12
         FROM (', table_list, ') AS d
         JOIN `master_dataset.master_data_table` AS s
           ON s.MULTIDIVISION_CLUSTER_CODE = d.MULTIDIVISION_CLUSTER_CODE
         WHERE CODE = ''XXXXX''
         GROUP BY d.MULTIDIVISION_ZONE, Country, d.DIVISION_CODE, BRAND, d.MULTIDIVISION_REGION
       )
       SELECT MULTIDIVISION_ZONE AS perimeter, SUM(sales) AS value, quarter FROM cte  
       UNPIVOT(sales FOR quarter IN (YTD03, YTD06, YTD09, YTD12))
       GROUP BY MULTIDIVISION_ZONE, quarter
       UNION ALL
       SELECT brand, SUM(sales), quarter FROM cte  
       UNPIVOT(sales FOR quarter IN (YTD03, YTD06, YTD09, YTD12))
       GROUP BY brand, quarter
       UNION ALL
       SELECT MULTIDIVISION_REGION, SUM(sales), quarter FROM cte  
       UNPIVOT(sales FOR quarter IN (YTD03, YTD06, YTD09, YTD12))
       GROUP BY MULTIDIVISION_REGION, quarter
     ');
   
     -- 执行创建/更新视图
     EXECUTE IMMEDIATE view_sql;
   END;
   

2. 配置定时调度
   在BigQuery控制台找到这个存储过程，创建调度任务（比如每月执行一次，根据新表生成频率调整），新表生成后视图会自动同步数据。

方案2：分区表+数据传输服务（有权限时用） #

如果能配置数据传输，把年度表自动同步到分区表，视图直接查询分区表即可：

• 创建分区表dataset_source.aggregated_all_years，按year字段分区；
• 配置BigQuery数据传输服务，当新年度表创建时，自动把数据追加到对应分区；
• 修改原查询的FROM目标为这个分区表，视图就能直接获取所有年份的数据。

方案3：手动维护UNION ALL（无权限时备选） #

如果没法用存储过程或数据传输，只能每次新表生成后，手动修改视图SQL，把新表加到UNION ALL列表里：

FROM (
  `dataset_source.table_name_prefix_2022_AGGREGATED`
  UNION ALL
  `dataset_source.table_name_prefix_2023_AGGREGATED`
  -- 后续新表手动添加到这里
) AS d

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