数仓模型中ods层最容易忽略的就是数据删除操作,本文将介绍数据同步过程中对于删除数据的几种处理方法。
首先假定我们的数据都是从mysql binlog同步到数仓的ods层。那么拉取binlog有两种方式(增量和全量),如果再细分的话就是每天增量或全量同步,和一次性增量或全量同步。
如果是增量同步,则还需要和历史全量进行merge,在merge的时候要处理的问题有:
1.如何处理insert、update、delete数据?
2.如何处理数据飘移问题?
先假设mysql表的结构如下:
--mysql创建DDL--
create table stu (
id bigint unsigned not null auto_increment comment '主键id',
gmt_create datetime default current_timestamp comment '创建时间',
gmt_modified datetime default current_timestamp on update current_timestamp comment '修改时间',
name varchar(63) collate utf8mb4_general_ci not null comment '用户名',
primary key (id)
) engine = innodb auto_increment = 5 default charset = utf8mb4 collate = utf8mb4_general_ci comment = '用户信息表'
这里最重要的字段就是gmt_create和gmt_modified,这个就是数据同步最重要的参考时间。但是我们通常选择按照gmt_modified去同步。
即 mysql_db.stu => hive_ods.stu_delta, 分区字段ds=T-1
1.如果选择按照gmt_create,则只能把今天新增的数据拉到hive_ods.stu_delta的今天的分区, gmt_create=20210329 -> ds=20210329
2.如果选择按照gmt_modified,只有今天修改过的数据都能拉到hive_ods.stu_delta的今天的分区,gmt_modified=20210329 -> ds=20210329
这个背景介绍完之后,回到上面的两个问题:
假设stu通过binlog同步到hive时的日志表为s_stu,那么
1.如何处理insert、update、delete数据?
通过binlog的数据操作类型operate_type来判断,枚举值为:insert、update、delete
有两种过滤删除的办法:对于一个id,(1)只过滤最后一条删除数据;(2)过滤最后一条删除的数据及之前的所有数据;(3)不做任何过滤,但是业务库清除历史数据时候,会有大量删除的脏数据被同步过来。
insert overwrite table stu_delta partition(date = '${date}')
select id,gmt_create,gmt_modified,user_name
from
(
select *,row_number(partition by id order by gmt_modified desc) as rn
from
(
select split(row,0) as operate_type
,split(row,1) as id
,split(row,2) as gmt_create
,split(row,3) as gmt_modified
,split(row,4) as user_name
from s_stu
)
where operate_type<>'delete' --对于一个id,只过滤最后一条删除数据
)
where rn=1
insert overwrite table stu_delta partition(date = '${date}')
select id,gmt_create,gmt_modified,user_name
from
(
select *,row_number(partition by id order by gmt_modified desc) as rn
from
(
select split(row,0) as operate_type
,split(row,1) as id
,split(row,2) as gmt_create
,split(row,3) as gmt_modified
,split(row,4) as user_name
from s_stu
)
)
where rn=1
and operate_type<>'delete' --对于一个id,过滤最后一条删除的数据及之前的所有数据
2.如何处理数据飘移问题?
主要通过分区和gmt_modified来限定时间。比如因为同步binlog是15分钟同步一次,落到一个分区,一个小时共有4个分区(mm=00,15,30,45)。
如果要完整解析'${date}' =8号整天的数据,那么则要考虑数据在0点跨天的时候可能会出现延迟落盘,即8号的数据可能提前落在了7号23点45这个分区,也可能落在了9号0点0分这个分区。
而我们调度任务应该是9号0点15分之后开始运行,才能不会漏掉数据,因此逻辑如下:
select *
from s_stu
where
(
(ds='${date-1}' and hh='23' and mm='45' ) and
(ds='${date}' ) and
(ds='${date+1}' and hh='00' and mm='00' )
)
and substr(gmt_modified,1,10)='date' -- yyyy-MM-dd
3.如何把历史全量中的数据也删除?todo
业务库今天删了一部分数据,那么可以通过1.中的方法去过滤删除,但是昨天之前的数据也要同步删除,那就要通过增全量合并的方法去删除。
数据同步分为:
(1)全量同步 X 是否过滤删除
(2)增量同步 X 是否过滤删除