hive中多表full join主键重复问题

0. 相关文章链接

开发随笔文章汇总

1. 问题描述

    在Hive中（其他类似SQL，比如PostgreSQL可能也存在此问题），当对多张表（3张及以上）进行full join时，会存在每张表的主键都是唯一，但当full join后，会发现主键可能有重复。

2. 问题复现

2.1. 建表语句

create table realtime_dw.table_a
(
    id int,
    name int
);

create table realtime_dw.table_b
(
    id int,
    name int
);

create table realtime_dw.table_c
(
    id int,
    name int
);

2.2. 插入数据

-- a表      -- b表      -- c表
1,111       1,111       1,111
2,222       3,333       4,444
3,333       4,444       5,555

2.3. 查询SQL以及问题

select
    coalesce(a.id, b.id, c.id) as id
    , a.name
    , b.name
    , c.name
from table_a as a

full join table_b as b
on a.id = b.id

full join table_c as c
on a.id = c.id
;

当执行如上查询SQL时，会发现其中 id = 4 的数据有重复，如下图所示：

3. 问题原因

    之所以会出现这样的问题，是因为是以a表为主表，但a表中只有 id 为 1、2、3 的数据，但在b表中有id为4，c表中也有id为4，此时full join时，a表会以空值和b表、c表中id为4的数据join，这样关联后的表中就会出现2条id为4的数据。

4. 问题解决

    在后续的表full join时，不单单使用第一张表的主键full join（因为是full join，所以肯定会存在第一张表为null，而其他表有值的数据），而使用 coalesce 方法对所有前面已经full join了的主键进行条件关联，如下代码：

select
    coalesce(a.id, b.id, c.id) as id
    , a.name
    , b.name
    , c.name
from table_a as a

full join table_b as b
on a.id = b.id

full join table_c as c
on coalesce(a.id, b.id) = c.id
;

此时，结果就不会有主键重复，如下查询结果所示：

注：其他相关文章链接由此进 -> 开发随笔文章汇总