BigQuery中SQL自动生成表名 循环合并NOAA GSOD多年温度表

需求说明 #

需要合并BigQuery中存储的1929年-2022年NOAA GSOD气象数据集，提取各站点各月份的平均温度整合为单表。目前已实现手动编写UNION ALL合并少量年度表，但近百张表手动编写重复度过高；尝试通过字符串拼接动态构造表名的方案执行失败，需要实现表名自动生成、批量完成全量数据合并。

现有手动实现代码（3个年度合并示例） #

WITH avgtemptable1 AS (
    SELECT stn, year, mo, ROUND(AVG(temp)) AS avg_temp
    FROM `bigquery-public-data.noaa_gsod.gsod1955`
    GROUP BY stn, year, mo 
), avgtemptable2 AS (
    SELECT stn, year, mo, ROUND(AVG(temp)) AS avg_temp
    FROM `bigquery-public-data.noaa_gsod.gsod1956`
    GROUP BY stn, year, mo 
), avgtemptable3 AS (
    SELECT stn, year, mo, ROUND(AVG(temp)) AS avg_temp
    FROM `bigquery-public-data.noaa_gsod.gsod1957`
    GROUP BY stn, year, mo 
)
SELECT *
FROM avgtemptable1
UNION ALL
SELECT *
FROM avgtemptable2
UNION ALL
SELECT *
FROM avgtemptable3

失败的动态表名尝试代码 #

DECLARE name STRING DEFAULT 'bigquery-public-data.noaa_gsod.gsod';

SELECT stn,year,mo,temp,(SELECT CONCAT('`',name,'1955','`') AS name2)
FROM name2

最优实现方案 #

BigQuery原生提供*通配符表能力，专门适配同数据集下、按固定命名规则存储的同结构分表批量查询场景，不需要写循环、不需要动态拼接SQL，单段代码即可完成全量表的查询聚合：

SELECT 
  stn,
  year,
  mo,
  ROUND(AVG(temp)) AS avg_temp
FROM `bigquery-public-data.noaa_gsod.gsod*`
-- 通过_TABLE_SUFFIX伪列筛选需要查询的年份范围，对应gsod后的表名后缀
WHERE _TABLE_SUFFIX BETWEEN '1929' AND '2022'
GROUP BY stn, year, mo

方案说明 #

• 上述代码中gsod*会自动匹配所有表名以gsod为前缀的表，_TABLE_SUFFIX是通配符查询自带的伪列，值为通配符*匹配到的后缀内容，通过WHERE条件限定后缀范围即可精准筛选需要的年度表
• 该写法和手动编写近百个UNION ALL的逻辑效果完全一致，执行效率更高，后续如果需要调整查询的年份范围，只需要修改BETWEEN后的起止值即可，不需要维护大量重复代码
• 之前的动态拼接写法无法执行的核心原因是：BigQuery标准SQL的FROM子句不支持直接用变量、字符串表达式作为表名，表名必须是查询解析阶段可识别的标识符。如果一定要用动态拼接的方式实现，需要搭配EXECUTE IMMEDIATE执行动态生成的SQL，但对于当前按固定后缀命名的分表场景，通配符表是官方推荐的最优方案，稳定性和可维护性远高于循环拼接动态SQL的实现。

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