Hive中合并arch与noarch表获取全量数据视图的SQL查询咨询

你之前用左连接无法得到全量结果的原因是：左连接是横向拼接两张表的字段，适合需要把两张表同一个res_id的不同字段合并到一行的场景，而你的需求是纵向合并两张表的所有数据行，所以用UNION ALL是正确的方向。

基础全量查询SQL #

UNION ALL要求上下查询的字段数量、字段类型完全对齐，所以需要给字段更少的arch表补全noarch独有的created_at、updated_at字段，缺省值可根据业务需求调整：

SELECT 
  tr_id,
  res_id,
  info_json,
  created_at,
  updated_at
FROM noarch

UNION ALL

SELECT 
  tr_id,
  res_id,
  info_json,
  NULL AS created_at, -- 无对应数据时默认返回空，也可替换为自定义默认值，如'1970-01-01 00:00:00'
  NULL AS updated_at
FROM arch;

特定res_id过滤查询SQL #

如果仅需要查询指定res_id的结果，可直接在子查询中分别加过滤条件，避免全表扫描提升查询性能：

SELECT * FROM (
  SELECT 
    tr_id,
    res_id,
    info_json,
    created_at,
    updated_at
  FROM noarch
  WHERE res_id IN ('111','222','333','444')

  UNION ALL

  SELECT 
    tr_id,
    res_id,
    info_json,
    NULL AS created_at,
    NULL AS updated_at
  FROM arch
  WHERE res_id IN ('111','222','333','444')
) merged_data;

持久化为视图（可选） #

如果需要长期使用该统一视图，可创建Hive视图，后续直接查询视图过滤数据即可：

CREATE VIEW IF NOT EXISTS full_arch_noarch_view AS
SELECT 
  tr_id,
  res_id,
  info_json,
  created_at,
  updated_at
FROM noarch
UNION ALL
SELECT 
  tr_id,
  res_id,
  info_json,
  NULL AS created_at,
  NULL AS updated_at
FROM arch;

-- 后续查询示例
SELECT * FROM full_arch_noarch_view WHERE res_id = '111';

注意事项 #

• 没有去重需求时优先用UNION ALL，相比UNION不会触发额外的去重shuffle操作，Hive查询性能提升明显
• 若两张表存在同一个res_id的重复数据需要去重，可把UNION ALL替换为UNION，或者在外层加DISTINCT逻辑

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