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