DWD层渐变维之拉链表

背景

历史数据更新问题

例如：

• MySQL中有一张用户表：tb_user，每个用户注册完成以后，就会在用户表中新增该用户的信息，记录该用户的id、手机号码、用户名、性别、地址等信息。
• 每天都会有用户注册，产生新的用户信息
• 每天都需要将MySQL中的用户数据同步到Hive数据仓库中

• 需要对用户的信息做统计分析，例如统计新增用户的个数、用户性别分布、地区分布、运营商分布等指标
  当已经同步了的数据发生了改变

  
  image.png

---

解决方案

解决问题 主要思考的是历史数据要不要保留的问题,如果不保留可以采取
方案一,直接全覆盖,把mysql的新的数据直接覆盖掉hive表中的数据
方案二,每次数据改变，根据日期构建一份全量的快照表，每天一张表
方案三,拉链表,通过时间标记发生变化的数据的每种状态的时间周期

拉链表.png

设计

拉链表专门用于解决在数据仓库中数据发生变化如何实现数据存储的问题。
在建表的时候新建两个字段start_time end_time 用来记录开始时间和结束时间,如果结束时间是9999-12-31 表明这条记录现在有效是最新状态

拉链表如何设计实现.png

1.设计表时新增加两个字段start_time 和end_time,通常以9999-12-31为结束时间,表示数据有效
2.增量采集数据放入增量表中(增量表中的结果集1是最终结果集的一部分)
3.拉链表a left join 增量表b
4.如果b.user为null 表示原数据没有更新,如果b.user为null 且a.endtime为9999-12-31表示这条数据,发生了更新,需要把a.endtime 改为b.start_time的前一天) 查询结果集为2
5.两个结果集unionall 覆盖插入到拉链表

实现

--创建拉链表
create table test.dw_zipper(
                          userid string,
                          phone string,
                          nick string,
                          gender int,
                          addr string,
                          starttime string,
                          endtime string
) row format delimited fields terminated by '\t';

--创建增量采集表
create table test.ods_zipper_update(
                                  userid string,
                                  phone string,
                                  nick string,
                                  gender int,
                                  addr string,
                                  starttime string,
                                  endtime string
) row format delimited fields terminated by '\t';
--实现数据同步

insert overwrite table test.dw_zipper
select userid, phone, nick, gender, addr, starttime, endtime
from test.ods_zipper_update
union all
select a.userid,
       a.phone,
       a.nick,
       a.gender,
       a.addr,
       a.starttime,
       if(b.userid != 'null' and a.endtime == "9999-12-31",date_sub( b.starttime ,1),a.endtime   ) endtime
from test.dw_zipper a left join test.ods_zipper_update b on a.userid=b.userid order by userid;