Hive Full Join多个表与Union All多个表

一.引言

假设有如下三张表，里面分别记录了三类不用用户的购买记录，现在想将下属记录合并为 => | 用户 | 果蔬购买量 | 饮品购买量 | 零食购买量 | ，没有购买则为 Null:

Table A：用户在超市近一个月购买果蔬的记录

Table B：用户在超市近一个月购买饮品的记录

Table C：用户在超市近一个月购买零食的记录

先建一个表供我们插入数据

hive -e "
create table if not exists user_act_of_buying (
    user string,
    fruits string,
    drinks string,
    snacks string
    )
    PARTITIONED BY(dt string)
    row format delimited fields terminated by '\t'
"

二.Full Join（适合两个表）

遇到上述场景的第一反应就是使用FullJoin，以用户名为聚合字段进行联合，这里采用套娃的方式，将Table A 和 Table B结合 再将结合的表与Table C结合得到最终结果，因为Hive没办法使用连等号： a.user = b.user = c.user，所以只能通过嵌套的方式。

hive -e "
select tmp.*,c.* from
(select user,fruit,drink from
    (select user,fruits from tableA where dt=$dt) a
    FULL OUTER JOIN
    (select user,drinks from tableB where dt=$dt) b
    on a.user = b.user
) tmp
FULL OUTER JOIN
(select user,snacks from tableC where dt=$dt) c
on tmp.user = c.user;
"

三.Union All（推荐👍 适合3个及以上表）

嵌套的方式可以解决问题，但是也有新的问题就是当需要Full Join的表太多时，需要写多层嵌套，不美观且容易出错。可以改变思路，先将全部的user Union在一起当做主表，然后依次LEFT OUTER JOIN Table A，Table B 和 Table C即可，即使还要结合用户购买的生活用品，电器等等，也不用担心嵌套太多不好写的问题，只需要将对应表的 user 添加到主表中，并把新增的表加入LEFT OUTER JOIN的队伍里即可。

hive -e "
insert overwrite table user_act_of_buying partition(dt=$dt)
select all.user,fruit.fruits,drinl.drinks,snack.snacks from
(
select user from (
    select user from
    (select user from tableA where dt=$dt) a
UNION ALL
    select user from
    (select user from tableB where dt=$dt) b
UNINO ALL
    select user from
    (select user from tableC where dt=$dt) c
) tmp
group by user
) all
LEFT OUTER JOIN
(select user,fruit from tableA where dt=$dt) fruit
on all.user = fruit.user
LEFT OUTER JOIN
(select user,drink from tableB where dt=$dr) drink
on all.user = drink.user
LEFT OUTER JOIN
(select user,snacks from tableC where dt=$dt) snack
on all.user = all.snack;
"

这里先通过tmp拿到所有表的user字段，然后再通过group by去重保证一人一条记录，最后LEFT JOIN即可。这里有几个坑要注意：

1.Sql支持Union 和 UnionAll，其中Union可以达到去重的效果，但Hive只支持Union ALL，所以这里有去重需求需要使用Group By。

2.Union All的时候每个语句返回的列的数量和名字必须一样，这也是为什么union all时在select语句外又重复了一条select user语句。

3.Union All的时候每个表使用别名，否则容易报 missing EOF ，找了一下午空格，很坑。。

4.Union all的每张表不支持limit、order by等函数，但是可以添加很多where过滤条件，这里只是限制了dt 。

5.这里第一种方案只是demo，拿到的是所有的数据，所以没有执行insert操作，但是Union方法可以确定拿到的每个字段，所以支持插入到对应表。

6.LEFT OUTER JOIN 和 LEFT JOIN ，FULL JOIN 和 FULL OUTER JOIN效果都相同。

7.FULL OUTER JOIN 其实就是把 LEFT OUTER JOIN 和 RIGHT OUTER JOIN的结果结合在一起。

4.总结

别用 Full Join