Databricks动态SQL：如何将查询列表中的所有查询UNION ALL？

核心思路 #

从参数表生成所有子查询语句，再用字符串聚合把它们用UNION ALL拼接成完整大查询，最后执行这个动态SQL。

具体步骤 #

1. 生成单条子查询语句
   根据DQ_FOM_SOURCE_SETUP里的参数字段（比如数据源表名、过滤条件、标识字段等），拼接出每个独立的可执行SQL。假设表中有source_table（数据源表）、filter_condition（过滤条件）、source_name（数据源标识）这几个字段，示例SQL如下：

   SELECT CONCAT('SELECT col1, col2, ''', source_name, ''' as source FROM ', source_table, ' WHERE ', filter_condition) AS sub_sql
   FROM DQ_FOM_SOURCE_SETUP
   

   你需要根据自己表的实际字段调整拼接逻辑，保证每条sub_sql都是完整合法的查询语句。

2. 聚合为带UNION ALL的大查询
   用Databricks支持的字符串聚合方法把所有子查询拼起来，两种常用方式：

   • 方法一：用STRING_AGG（Spark 3.0+ / Databricks SQL推荐）

     SELECT STRING_AGG(sub_sql, ' UNION ALL ') AS full_sql
     FROM (
         -- 替换成你生成sub_sql的查询逻辑
         SELECT CONCAT('SELECT col1, col2, ''', source_name, ''' as source FROM ', source_table, ' WHERE ', filter_condition) AS sub_sql
         FROM DQ_FOM_SOURCE_SETUP
     ) t
     

   • 方法二：用collect_list + concat_ws（兼容旧版本）

     SELECT CONCAT_WS(' UNION ALL ', COLLECT_LIST(sub_sql)) AS full_sql
     FROM (
         -- 替换成你生成sub_sql的查询逻辑
         SELECT CONCAT('SELECT col1, col2, ''', source_name, ''' as source FROM ', source_table, ' WHERE ', filter_condition) AS sub_sql
         FROM DQ_FOM_SOURCE_SETUP
     ) t
     

   如果需要格式化SQL方便查看，可以把拼接符改成' UNION ALL ' || CHAR(13)，加入换行符。

3. 执行动态生成的大查询
   在Databricks里直接用EXECUTE IMMEDIATE执行生成的完整SQL，两种实现方式：

   • Databricks SQL 变量方式

     DECLARE full_sql STRING;
     SET full_sql = (
         SELECT STRING_AGG(sub_sql, ' UNION ALL ')
         FROM (
             SELECT CONCAT('SELECT col1, col2, ''', source_name, ''' as source FROM ', source_table, ' WHERE ', filter_condition) AS sub_sql
             FROM DQ_FOM_SOURCE_SETUP
         ) t
     );
     EXECUTE IMMEDIATE :full_sql;
     

   • Notebook Python方式
     先获取完整SQL字符串，再执行：

     full_sql = spark.sql("""
         SELECT STRING_AGG(sub_sql, ' UNION ALL ') AS full_sql
         FROM (
             SELECT CONCAT('SELECT col1, col2, ''', source_name, ''' as source FROM ', source_table, ' WHERE ', filter_condition) AS sub_sql
             FROM DQ_FOM_SOURCE_SETUP
         ) t
     """).collect()[0]['full_sql']
     
     # 执行并展示结果
     spark.sql(full_sql).display()
     

避坑提示 #

• 所有子查询的字段数量、数据类型必须完全一致，否则UNION ALL会报错
• 如果参数里包含单引号，一定要用REPLACE转义，比如把source_name里的单引号替换成两个单引号：

  SELECT CONCAT('SELECT col1, col2, ''', REPLACE(source_name, '''', ''''''), ''' as source FROM ', source_table, ' WHERE ', filter_condition) AS sub_sql
  FROM DQ_FOM_SOURCE_SETUP
  

• 几百行参数的场景完全不用担心性能，字符串聚合的开销可以忽略，最终执行效率和手动写UNION ALL完全一致

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