动态生成Union All语句创建Hive合并视图并保证字段位置与类型一致

核心思路 #

先拉取所有待合并表的全量字段+对应数据类型，固定最终视图的字段顺序，再逐表补全缺失字段的空值，最后拼接成完整的建视图语句。

具体操作步骤 #

1. 拉取全量字段元数据固定字段顺序 #

直接查Hive自带的信息 schema 就能拿到所有表的字段和类型，不用碰底层元数据库：

SELECT DISTINCT column_name, data_type 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_schema = '你的Hive库名' 
-- 把要合并的10张表名填到IN条件里
AND table_name IN ('tab1','tab2','tab3','tab4','tab5','tab6','tab7','tab8','tab9','tab10')
-- 这里可以自定义排序规则，固定最终视图的字段顺序
ORDER BY column_name;

把查询结果保存下来，就是最终合并视图的完整字段列表，以及每个字段对应的数据类型。

2. 生成单表的SELECT子查询 #

对每一张待合并的表，遍历上面拿到的全量字段列表：

• 字段在当前表存在：直接写字段名
• 字段在当前表不存在：写CAST(NULL AS 对应字段的数据类型) AS 字段名
  这里必须加CAST强转类型，否则NULL默认会被识别为STRING，和实际字段类型不一致会触发UNION ALL的类型不匹配错误

举个例子：全量字段为col11(STRING)、col12(INT)、col13(BIGINT)，tab1只有col11和col12，那tab1的子查询就是：

SELECT col11, col12, CAST(NULL AS BIGINT) AS col13 FROM tab1

tab2只有col11和col13，子查询就是：

SELECT col11, CAST(NULL AS INT) AS col12, col13 FROM tab2

3. 拼接成完整建视图语句 #

把所有单表子查询用UNION ALL拼接，外层套上建视图的语法即可：

CREATE VIEW comb_view AS
-- 外层直接写固定顺序的全量字段列表
SELECT col11, col12, col13, ......
FROM (
    SELECT col11, col12, CAST(NULL AS BIGINT) AS col13 FROM tab1
    UNION ALL
    SELECT col11, CAST(NULL AS INT) AS col12, col13 FROM tab2
    UNION ALL
    -- 剩余8张表的子查询依次拼接
    ......
) c;

自动化优化方案 #

如果表字段变动频繁，可以写个简单的Python/Shell脚本自动完成上面的流程：

• 脚本连接Hive执行第一步的元数据查询
• 自动遍历每个表生成对应子查询
• 直接执行拼接好的建视图语句
  每次字段变动后跑一遍脚本即可，不用手动改SQL。

注意事项 #

• 遇到同名字段在不同表数据类型不一致的情况，要先在第一步统一类型，比如统一转成STRING或者精度更高的数值类型，再写入子查询的CAST逻辑
• 字段顺序完全由第一步的排序规则决定，不会因为子表的字段顺序变动导致视图字段错位
• 不需要合并的字段可以在第一步查询后手动剔除，不会出现在最终视图里

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