Greenplum建模最佳实践之拉链表

拉链表是一种数据模型,主要是针对数据仓库设计中表存储数据的方式而定义的;顾名思义,所谓拉链表,就是记录历史,记录一个事务从开始一直到当前状态的所有变化的信息。
拉链表可以避免按每一天存储所有记录造成的海量存储问题,同时也是处理缓慢变化数据(SCD2)的一种常见方式。

一、概念原理

在拉链表中,每一条数据都有一个生效日期(sdate) 和 失效日期(edate)。假设在一个用户表中,在 2019年10月8日 新增了两个用户,则这两条记录的生效时间为当天,由于到 2019年10月8日 为止,这两条记录还没有被修改过,所以失效时间为无穷大,这里设置为数据库中的最大值(2999-12-31),如图所示:

第二天(2019-10-09),用户 1001 被删除,用户 1002 的电话号码被修改成 16500000006。为了保留历史状态,用户 1001 的失效时间被修改成 2019-10-09,用户 1002 则变成两条记录,如图所示:

第三天(2019-10-10),又新增了用户 1003,则用户表数据如图:

如果要查询最新的数据,那么只要查询失效时间为 2999-12-31 的数据即可,如果要查询 10月8号 的历史数据,则筛选生效时间 <= 2019-10-08 并且失效时间 > 2019-10-08 的数据即可;如果查询的是 10月9日的数据,那么筛选条件则是生效时间 <= 2019-10-09 并且失效时间 > 2019-10-09;以此类推

二、准备

2.1创建表

临时源表 T_FIN_ACCTION_SRC,接收其它数据库(如 oracle)表推送过来的数据 ,表结构和源数据库的表结构一致。

目标表 ( 即拉链表 ) T_FIN_ACCTION_TAR,这里注意的是:拉链表把源表的时间字段改成了生效时间失效时间哦。

2.2创建存储过程

在这里为了方便阅读以及代码的编写,先写出整体的存储过程架构,然后我们在一步一步添加代码:

-- 将当前时间传入 (也可以传入昨天的时间哦,随机应变,
-- 如果传入的时间是今天则使用中要将时间减1,因为我们要处理的是昨天的数据)

create or replace function My_FIN_GL_SUBJECT_PRO(IN P_TODAY VARCHAR)
    returns void
as $$
declare

begin   
            --1.目标表中没有此主键的则确定为新增  -  新增

            --2.源表中没有该ID则进行关链  -  删除

            --3.修改
            --3.1 闭链:目标表中有此主键的记录,状态值不同,更新结束日期为当天

            --3.2 开链:目标表中新增一条修改的数据,更新结束日期为无穷大

end;
$$
language plpgsql;

三、拉链实现过程

3.1新增 

目标表中没有此主键的则确定为新增 – 新增

insert into gplcydb.public.T_FIN_ACCTION_TAR(eNo,eName,ePhone,sdate,edate)
select s.eNo,s.eName,s.ePhone,s.eData_date,to_date('2999-12-31','yyyy-mm-dd')
  from gplcydb.public.T_FIN_ACCTION_SRC s
 where s.eData_date=(to_date(P_TODAY,'yyyy-mm-dd') - 1)
   and not exists(
                   select 1 
                     from gplcydb.public.T_FIN_ACCTION_TAR t
                    where s.eNo=t.eNo
                      and s.eName=t.eName
                      and s.ePhone=t.ePhone
                  );

3.2删除 

源表中没有该ID则进行关链 – 删除

update gplcydb.public.T_FIN_ACCTION_TAR a  
    set edate=(to_date(P_TODAY,'yyyy-mm-dd')-1)
where not exists(                  
                   select 1
                     from gplcydb.public.T_FIN_ACCTION_SRC s                   
                   where s.eNo=a.eNo
                     and a.edate=to_date('2999-12-31', 'yyyy-mm- 
                     dd')
                 );

3.3修改 

1)闭链:目标表中有此主键的记录,状态值不同,更新结束日期为当天

update gplcydb.public.T_FIN_ACCTION_TAR b
   set edate=(to_date(P_TODAY,'yyyy-mm-dd')-1)
 where b.edate=to_date('2999-12-31','yyyy-mm-dd')
   and exists(
               select 1 
                 from gplcydb.public.T_FIN_ACCTION_SRC s
                where s.eNo = b.eNo
                  and b.sdate < (to_date(P_TODAY,'yyyy-mm-dd')-1)
                  and ( s.eName <> b.eName or s.ePhone <> b.ePhone )
              )
;

2)开链:目标表中新增一条修改的数据,更新结束日期为无穷大

insert into gplcydb.public.T_FIN_ACCTION_TAR(eNo,eName,ePhone,sdate,edate)
select s.eNo,s.eName,s.ePhone,(to_date(P_TODAY,'yyyy-mm-dd') - 1),to_date('2999-12-31','yyyy-mm-dd')
  from gplcydb.public.T_FIN_ACCTION_SRC s
 where s.eData_date=(to_date(P_TODAY,'yyyy-mm-dd') - 1)
  and exists( -- 处理数据断链新增的情况
              select 1 
                from (
                        select eNo,sdate,max(edate) end_date
                          from gplcydb.public.T_FIN_ACCTION_TAR 
                         group by eNo,sdate
                      ) t
               where t.eNo=s.eNo
                 and s.eData_date = t.sdate
                 and t.end_date <= to_date(P_TODAY,'yyyy-mm-dd')
            )
;

四、测试

要测试拉链函数,首先我们必须要在原表中插入数据(模拟一天全量的数据):

insert into T_FIN_ACCTION_SRC values('1001','feiniu','18500000001','2019-10-10');

insert into T_FIN_ACCTION_SRC values('1002','beibei','18400000005','2019-10-10');

insert into T_FIN_ACCTION_SRC values('1003','yuyu','13800000005','2019-10-10'

调用函数进行拉链测试:


select My_FIN_GL_SUBJECT_PRO('2019-10-11');  -- 调用函数

select * from T_FIN_ACCTION_TAR;             -- 查询拉链表

测试结果如下图:

插入第二天全量数据,这些数据中有新增的数据,有源数据被删除,还有源数据被修改,完整的模拟sql语句如下:

delete from T_FIN_ACCTION_SRC where eno='1003';
insert into T_FIN_ACCTION_SRC values('1004','kongkong','13800000666','2019-10-11');
update T_FIN_ACCTION_SRC set ename='xiaofeifei' where eno='1001';

select * from T_FIN_ACCTION_SRC;

原表的效果图如下:

接下来执行拉链函数:

-- 执行拉链函数
select My_FIN_GL_SUBJECT_PRO('2019-10-12');

select * from T_FIN_ACCTION_TAR;  --查询目标表

效果图如下:

到此,我们的拉链实现就完成咯,当初还用了很多个临时表,琢磨了好久终于可以实现了,希望能帮助到你!

番外语

1.本文操作环境:Greenplum数据库。故采用了过程函数的方式insert/update实现拉链。

2.若Hive上,则需要采用full outer join实现拉链表。后面会有专篇文章介绍。

3.建议添加chain_status字段,分别记录拉链状态(active、expired、history)。


看这里,插播一条活动邀请

11月15日,Pivotal将联合DellEMC 在上海举办技术沙龙,为你详细剖析下一代大数据处理平台的核心技术。活动详情请戳这里

发表评论

电子邮件地址不会被公开。 必填项已用*标注