数仓模型-增全量合并

ods层数据同步时经常会遇到增全量合并的模型，即T-1天增量表 + T-2全量表 = T-1全量表。可以通过full outer join脚本来完成合并，但是数据量很大时非常消耗资源。本文将为您介绍在做增量数据的增加、更新时如何通过full outer join改写left anti join来实现的最佳实践。

常规合并方法：

insert overwrite table tb_test partition(ds='${bizdate}')
select case when a.id is not null then a.id esle b.id end as id   
      ,if(a.name is not null, a.name, b.name) as name
      ,coalesce(a.age, b.age) as age 
      --这3种写法一样，都是优先取delta表的字段

from
(
   select * from tb_test_delta where ds=T-1
) a
full outer join
(
   select * from tb_test where ds=T-2
) b
on a.id =b.id;

hash分桶合并法

hive/odps表用full outer join时会有大量shuffle操作，如果我们能改变数据的分布，即通过预先一轮repartition，把数据提前分区好，就能避免shuffle。

分桶操作：

--建表时分桶
create table test_bucket_sorted (
id int comment 'ID', 
name string comment '名字'
)
comment '测试分桶'
clustered by(id) sorted by (id) into 4 buckets


--修改已有的表
alter table test_bucket1 clustered by(user_id) into 4 buckets;

假设有a和b两个表，a是增量表，b是全量表：

with 
 a as ( select * from values  (1,'111')
                             ,(2,'two')
                             ,(7,'777') as (id,name) ) --增量

,b as ( select * from values  (1,'')
                             ,(2,'222')
                             ,(3,'333')
                             ,(4,'444') as (id,name) )  --全量

场景1:只合并新增数据到全量表

left anti join相当于not in，增量not in全量,过滤后只剩下完全新增的id，对全量中已有的id不修改：

--查询完全新增的id
select * from a left anti join b on a.id=b.id ;
--结果如下
+------------+------+
| id         | name |
+------------+------+
| 7          | 777  |
+------------+------+

--完全新增的合并全量表
select * from  a --增量表
left anti join b on a.id=b.id  
union all 
select * from b  --全量表
--结果如下
+------------+------+
| id         | name |
+------------+------+
| 1          |      |
| 2          | 222  |
| 3          | 333  |
| 4          | 444  |
| 7          | 777  |
+------------+------+

场景2:合并新增数据到全量表，且更新历史数据

全量not in增量,过滤后只剩下历史的id，然后union all增量，既新增也修改

--查询历史全量数据
select * from b left anti join a on a.id=b.id;
--结果如下
+------------+------+
| id         | name |
+------------+------+
| 3          | 333  |
| 4          | 444  |
+------------+------+

--合并新增数据到全量表，且更新历史数据
select * from  b --全量表
left anti join a on a.id=b.id
union all 
select * from a ; --增量表
--结果如下
+------------+------+
| id         | name |
+------------+------+
| 1          | 111  |
| 2          | two  |
| 7          | 777  |
| 3          | 333  |
| 4          | 444  |
+------------+------+

这样一来，由于左表和右表每天的分区在罗盘的时候都提前按照key进行了分区，那么增全量merge的时候就没有reduce算子了，只有map算子。其DAG参考另外一篇：https://developer.aliyun.com/article/773653?utm_content=g_1000189187