在Hive中合并列不匹配的多张表，有无更简洁的查询方式？

当需要合并大量列不匹配的表时，手动补NULL确实效率极低，以下是几种更高效的解决方法：

1. 利用元数据自动生成查询语句（推荐） #

Hive的元数据存储在information_schema中，我们可以通过查询元数据动态生成每个表的SELECT语句，自动补全缺失的列为NULL，无需手动编写每一列。

示例SQL #

假设你的表都在your_database库下，待合并的表为table_a、table_b、table_c，执行以下SQL会直接生成完整的UNION查询语句：

-- 获取所有待合并表的全量列
WITH all_columns AS (
  SELECT DISTINCT column_name
  FROM information_schema.columns
  WHERE table_schema = 'your_database'
    AND table_name IN ('table_a', 'table_b', 'table_c') -- 替换为你的实际表名
),
-- 为每个表生成对应的SELECT子句
table_queries AS (
  SELECT concat(
    'SELECT ',
    concat_ws(', ', collect_list(
      CASE WHEN EXISTS (
        SELECT 1 FROM information_schema.columns c
        WHERE c.table_schema = 'your_database'
          AND c.table_name = t.table_name
          AND c.column_name = ac.column_name
      ) THEN ac.column_name ELSE concat('NULL AS ', ac.column_name) END
    )),
    ' FROM ', t.table_name
  ) AS query
  FROM (SELECT DISTINCT table_name FROM information_schema.columns WHERE table_schema='your_database' AND table_name IN ('table_a','table_b','table_c')) t
  CROSS JOIN all_columns ac
  GROUP BY t.table_name
)
-- 拼接所有子查询，用UNION ALL连接（不需要去重则用UNION ALL，性能更优）
SELECT concat_ws(' UNION ALL ', collect_list(query)) AS full_union_query
FROM table_queries;

执行后会输出完整的查询语句，直接复制执行即可。如果你的Hive版本支持string_agg，可以替换concat_ws(', ', collect_list(...))为string_agg(..., ', ')，写法更简洁。

2. 创建视图统一结构 #

如果这些表需要频繁合并，可以为每个表创建视图，提前补全缺失的列，后续直接合并视图即可：

-- 为每个表创建视图
CREATE VIEW v_table_a AS SELECT col_a, NULL AS col_b, NULL AS col_c FROM table_a;
CREATE VIEW v_table_b AS SELECT NULL AS col_a, col_b, NULL AS col_c FROM table_b;
CREATE VIEW v_table_c AS SELECT NULL AS col_a, NULL AS col_b, col_c FROM table_c;

-- 合并视图
SELECT * FROM v_table_a UNION ALL SELECT * FROM v_table_b UNION ALL SELECT * FROM v_table_c;

这种方式适合长期复用的场景，一次性创建视图后，后续合并操作会非常简洁。

3. 用CTE临时统一结构（适合临时查询） #

如果只是临时查询，不想创建持久化视图，可以用CTE（公共表表达式）封装统一结构：

WITH unified_data AS (
  SELECT col_a, col_b, col_c FROM (
    SELECT col_a, NULL AS col_b, NULL AS col_c FROM table_a
    UNION ALL
    SELECT NULL AS col_a, col_b, NULL AS col_c FROM table_b
    UNION ALL
    SELECT NULL AS col_a, NULL AS col_b, col_c FROM table_c
  ) t
)
SELECT * FROM unified_data;

这种方式和你原本的写法类似，但通过CTE让结构更清晰，适合少量表的临时合并场景。

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