Hive全外连接结果存在重复行问题排查求助

在Hive中执行full outer join时，查询结果出现重复行，已确认两张表都通过行键完成去重校验。但将子查询tb2的数据存入中间表后再执行连接查询，结果恢复正常。

执行的SQL #

select count(*)
from
    dw_rk.f_t_rk_dzxx tb1 full
                              outer join (
        select
            s1.gmsfhm as sfzhm,
            s1.sjjzd_dzbm,
            s1.qxqc,
            s1.xzjd,
            s1.qxc,
            s1.pcs,
            s1.jws,
            s1.dm,
            s1.mph,
            s1.xqqc,
            s1.lfmc,
            s1.dy,
            s1.fh,
            s1.dzqc,
            from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:dd:ss') as d_timestamp,
            'gaj' as d_deptname,
            'f_s_zw_gaj_ldzh_syrk_rkjbxxb' as d_tabname
        from
            (
                select
                    t0.gmsfhm,
                    t0.sjjzd_dzbm,
                    case
                        when t2.qxmc is null then t1.qxmc
                        else t2.qxmc
                        end as qxqc,
                    t1.xzjd as xzjd,
                    case
                        when t3.qxc is null then t1.qxc
                        else t3.qxc
                        end as qxc,
                    t1.pcs as pcs,
                    t1.jwqmc as jws,
                    t1.dm as dm,
                    t1.mph as mph,
                    t1.xqmc as xqqc,
                    t1.lfmc as lfmc,
                    t1.dy as dy,
                    t1.fh as fh,
                    t1.dzqc as dzqc,
                    row_number() over (
                        partition by t0.gmsfhm
                        order by
                            t0.gxsj,
                            t0.rksj desc
                        ) as num
                from
                    dws.f_s_zw_gaj_ldzh_syrk_rkjbxxb t0
                        left join dws.f_s_zw_ldzh_bzdz_dzjbxxb t1 on t0.sjjzd_dzbm = t1.dzbh
                        left join (
                        select
                            distinct ssxqbm,
                                     qxmc
                        from
                            dws.f_s_zw_ldzh_bzdz_dzjbxxb
                    ) t2 on t0.sjjzd_ssxqdm = t2.ssxqbm
                        left join (
                        select
                            distinct qxcbh,
                                     qxc
                        from
                            dws.f_s_zw_ldzh_bzdz_dzjbxxb
                    ) t3 on t0.sjjzd_sqjcwhdm = t3.qxcbh
                where
                    t0.gmsfhm is not null
            ) s1
        where
                s1.num = 1
    ) tb2 on tb1.sfzhm = tb2.sfzhm;

执行计划（explain） #

STAGE DEPENDENCIES:
  Stage-7 is a root stage
"  Stage-12 depends on stages: Stage-7, Stage-13 , consists of Stage-15, Stage-3"
  Stage-15 has a backup stage: Stage-3
  Stage-11 depends on stages: Stage-15
"  Stage-10 depends on stages: Stage-3, Stage-8, Stage-11 , consists of Stage-14, Stage-4"
  Stage-14 has a backup stage: Stage-4
  Stage-9 depends on stages: Stage-14
"  Stage-5 depends on stages: Stage-4, Stage-9"
  Stage-1 depends on stages: Stage-5
  Stage-4
  Stage-3
  Stage-8 is a root stage
  Stage-16 is a root stage
  Stage-13 depends on stages: Stage-16
  Stage-0 depends on stages: Stage-1

原因分析 #

• Hive优化器逻辑偏差：当子查询作为连接右表时，Hive优化器可能未正确保留子查询的去重逻辑（即row_number() over(partition by t0.gmsfhm)... num=1的过滤），导致子查询的去重操作在连接前未完全生效，出现重复的sfzhm行，进而引发连接后的重复结果。
• 多阶段并行处理的一致性问题：从执行计划的Stage依赖关系来看，存在多阶段并行和备份Stage的情况，并行处理过程中，子查询的分区去重逻辑可能被重复计算，导致临时输出中出现重复数据。
• 临时数据与持久化数据的差异：子查询作为临时数据集时，Hive可能在内存或临时存储中处理，未做严格的持久化去重校验；而存入中间表时，数据会被落地存储，完整执行去重逻辑，确保每个sfzhm仅对应一行数据。

解决办法 #

• 强制子查询物化：在连接语句中添加Hive提示，强制先物化子查询结果，确保去重逻辑生效后再执行连接。例如：

  select count(*)
  from
      dw_rk.f_t_rk_dzxx tb1 full
                                outer join /*+ MATERIALIZE(tb2) */ (
          -- 原tb2子查询内容
      ) tb2 on tb1.sfzhm = tb2.sfzhm;
  

• 显式使用中间表：延续当前有效的方法，先将tb2的查询结果插入到临时中间表，再基于中间表执行full outer join，确保数据经过持久化去重。
• 校验连接键一致性：确认tb1.sfzhm和tb2.sfzhm的数据类型、长度完全一致，避免因隐式类型转换导致的异常匹配。
• 调整Hive优化参数：关闭可能干扰子查询逻辑的自动优化，例如设置：

  set hive.auto.convert.join=false;
  set hive.optimize.skewjoin=false;
  

  强制Hive按照SQL编写的逻辑顺序执行，先完成子查询的去重再进行连接操作。

内容的提问来源于stack exchange，提问作者X-Hadrain