Hive on spark 多表full join 数据丢失大坑

上位跑路同事留下来的坑

多张表做 full join的时候，只是按照对应的关联字段关联了，中间也不过滤，也不分组聚合，其中两个看板都用到了其中一张表，其子查询都一致，但最终对比两个看板同个字段的数值不一致，看了前辈写的又臭又长的sql，也看不了问题，第二天才察觉。。。

我们在hive中会经常使用多表联查，也就是我们常做的join 或者 union 。但是在写完SQL后会发现往往实现不了业务需求，而我们却又找不到原因所在，下面是多表联查有关full join的经典的问题。

如果我们要查询的数据每一个表有一个字段进行连接，比如连接条件为on t1.id=t2.id，若要查询符合条件的所有数据势必使用full join，如果所有id对应每个表中都有数据，那么是不会出现问题的，但是通常不会所有id在任意表中都非空，这会出现一个问题。

就拿t1,t2,t3，3个表举例，每个表除了要查询id外，每个表各查询另一个字段datas（每个表的datas不同，这里只是泛指一个字段名而已），并取这三者的全部数据。当我们在查询完t1与t2表后需要进行full join，同样t2与t3也需要进行full join，就会出现下图的现象。

长方形表示各表id所属范围，t1可能有t2没有的id，同时t2也有可能有t1没有的id，当t1、t2两者full join on t1.id=t2.id 后，若再要对t3表进行full join可以，但是join条件为on t2.id=t3.id，或者说是on t1.id=t3.id的时候都会出现一个错误，因为如果单单用任意一个表的id对t3.id进行等值连接，则会缺少另一个表的等值连接条件，举例如果用t2.id=t3.id进行等值连接，则会出现以下情况。

select 
    t1.id,
    t1.data1,
    t2.data2,
    t3.data3
from t1
full join t2 on t1.id=t2.id
full join t3 on t2.id=t3.id

这样会造成原本能与t1表连接的①号部分独立了出来，成为单独的若干行。

单独的连接条件显然不行的情况下，我们能否使用连接条件的组合呢？我们通常这时会想到比较简单的or关键字，在这个例子中or是可行的，但是例子的表比较少，如果是大数据行业，要连接的表过多，可以使用coalesce()函数（因为函数里只有两个字段在这同样能使用nvl()函数），如果t1.id无法匹配t3.id，就看t2.id是否匹配，三者都不匹配的条件下的t3.id才会另起一行单独出来。

select 
    t1.id,
    t1.data1,
    t2.data2,
    t3.data3
from t1
full join t2 on t1.id=t2.id
full join t3 on coalesce(t1.id,t2.id)=t3.id  --此处coalesce函数能用nvl代替，仅限于两个字段

这样确实能够实现业务与需求，但是如果数据量庞大（上百万行级别那种），若有原始数据维度表有全部id的话，我们还能通过left join解决速度过慢的问题。上述例子多表full join过慢的原因并不是full join 的问题，而是每次都要运行一次coalesce函数进行判断，而通过获取源数据的全部id，可以避免使用coalesce函数（仅限于有源数据表的情况下使用，不用也没关系）

select 
    t0.id, --t0为原始数据表，包含所有的id
    data1,
    data2,
    data3
from t0
left join t1 on t0.id=t1.id
left join t1 on t0.id=t2.id
left join t1 on t0.id=t3.id
这样写不仅运行速度快，写起来也最为方便，前提是公司得有这个表。

转载

原文链接：https://blog.csdn.net/qq_40772429/article/details/120188145