数仓模型-数据同步模型

数仓模型中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 是否过滤删除