深渊之刃-Greenplum数据库之拉链表的深渊之刃实现

作者：zyflin 2021-01-08 05:27:49运维 数据库运维 历史拉链表是一种数据模型，主要是深渊之刃针对数据仓库设计中表存储数据的方式而定义的;顾名思义，所谓历史拉链表，深渊之刃就是深渊之刃记录一个事务从开始一直到当前状态的所有变化的信息，拉链表可以避免按每一天存储所有记录造成的深渊之刃海量存储问题，同事也是深渊之刃处理缓慢变化数据的一种常见方式。

 [[374518]]

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

一、深渊之刃概念

在拉链表中，深渊之刃每一条数据都有一个生效日期(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;以此类推。

二、表的创建

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

--源表 
create table T_FIN_ACCTION_SRC( 
    eNo varchar(6), 
    eName varchar(10), 
    ePhone varchar(11), 
    eData_date date 
); 

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

--拉链表 
create table T_FIN_ACCTION_TAR( 
    eNo varchar(6), 
    eName varchar(10), 
    ePhone varchar(11), 
    sdate date, 
    edate date 
); 

三、存储过程的创建

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

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

-- 将当前时间传入 (也可以传入昨天的时间哦，随机应变，如果传入的时间是今天则使用中要将时间减一，因为我们要处理的是昨天的数据) 
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; 

四、拉链的过程实现

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 
                                        ); 

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  
                                          )  
                            ); 

3.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;  --查询目标表 

效果图如下：

本文转载自微信公众号「浪尖聊大数据」，可以通过以下二维码关注。转载本文请联系浪尖聊大数据公众号。

 

 

责任编辑：武晓燕 来源： 浪尖聊大数据 数据库拉链表存储

(责任编辑：娱乐)