关于hive3多表leftjoin导致数据丢失问题及解决方案

最近业务场景需要将一张大表通过name名字关联多个小表去获取他们的id,大表数据9000w,小表数据最大180w,最小30w,我以主表leftjoin的时候发现了数据丢失问题

代码如下

select eid, concat_ws(',', hat_name1, hat_name2, hat_name3, hat_name4, hat_name5, hat_name6) as hat_name
from (select t1.eid,
             if(t1.name = t2.name, hat_name1, null)  as hat_name1,
             if(t1.name = t3.name, hat_name2, null)      as hat_name2,
             if(t1.name = t4.name, t4.hat_name, null) as hat_name3,
             if(t1.name = t5.name, hat_name4, null) as hat_name4,
             if(t1.name = t6.name, t6.hat_name, null) as hat_name5,      
            if(t1.eid = t7.eid, t7.hat_name, null) as hat_name6
      from temp_a t1
               left join temp_a t2 on t1.format_name = t2.name
               left join temp_b t3 on t1.credit_no = t3.code
               left join temp_c t4 on t1.format_name = t4.name
               left join temp_d t5 on t1.format_name = t5.name
               left join temp_e = t6.eid
               left join temp_f t7 on t1.eid = t7.eid) t;

结果显示数据我t7的数据由180w剩下9w,发生了严重的数据丢失,在别的表也有不同程度的丢失问题.

最后发现这个问题主要是hive3版本底层mr的缺陷,是由于 HIVE 2 与 HIVE 3 在 JOIN 操作时使用了不同的 Hash 算法，导致同样的值在关联时被不同的 Hash 算法映射成了不同的值，而这些不同的 Hash 值在进行关联时无法被相互匹配。最终导致本来该被关联在一起的数据由于 Hash 值得不同未能被关联在一起。而决定到底应用哪套 Hash 值算法则是根据 bucketing_version 的值来进行评判的。

特别地，在进行多表关联时，即使相同 bucketing_version 的 Hive 表，由于其关联的中间过程所产生的中间表，在源代码中 bucketing_version 值会被置为 -1，因此该中间表再与第三张乃至更多的表关联时会直接导致 Hash 算法的混乱计算。

因此，为了保障关联的数据准确性，必须要确保 bucketing_version 在进行多表关联或者多版本表关联时的稳定。即，保障 bucketing_version 的稳定性就是保证 Hive 3 数据关联时的准确性。

此外，HIVE 社区已经针对 bucketing_version 不稳定的问题进行了集中的问题汇总和修改建议指导。可以通过查看 JIRA: HIVE-21304 了解系统性的 bucketing_version 稳定性提高方法，此处不做过多赘述。此处引出之前看到文章:
https://blog.csdn.net/weixin_38070561/article/details/126895259

此外,除了上面通过源码的修改来解决之类问题,我也从sql上考虑解决该问题的可行性,如果多表leftjoin可能造成数据丢失,那我们是否可以尽量的少去关联它呢?

因为我的业务是为了获取主表的id,所以我采用了unionall的方法,将各个小表以临时表的方式unionall一起,然后跟主表left去获取他的id,这是我就从6表关联变成了1表关联,代码如下:

select eid, name, hat_name, hat_id
from (select eid, name, hat_name, hat_id
      from temp_a
      union all
      select eid, name, hat_name, hat_id
      from temp_b
      union all
      select eid, name, hat_name, hat_id
      from temp_c
      union all
      select '' as eid, name, hat_name, hat_id
      from temp_d
      union all
      select eid, name, hat_name, hat_id
      from temp_e
      union all
      select '' as eid, name, hat_name, hat_id
      from temp_f
     ) t1
group by eid, name, hat_name, hat_id

因为考虑数据安全,代码仅发表部分,大概就是这个意思,上面的数据插入临时表后与大表关联也可以有效防止数据丢失,当然这个办法只能说是治标不治本的办法,因为业务比较急才走的捷径,归根到底还是得从根源上解决问题