greenplum--历史拉链表

历史拉链表

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

原理

在拉链表中，每一条数据都有一个生效日期(dw_beg_date)和失效日期(dw_end_date)。
以用户表为例，在2020年2月1日有新增的用户,此时需要将生效日期设置为2020年2月1日,失效日期设置为日期的最大值3000年12月31日。

用户	手机号	生效日期	失效日期
1001	132111111111	2020-02-01	3000-12-31
1002	132111111112	2020-02-01	3000-12-31

若在第二天需要删除用户1001时,将该用户的失效日期修改为2020年2月2日。

用户	手机号	生效日期	失效日期
1001	132111111111	2020-02-01	2020-02-02
1002	132111111112	2020-02-01	3000-12-31

若在第三天,修改用户1002的手机号,需要新增一条1002的记录,将原先的1002用户的失效日期修改为2020年2月3日,新增的1002记录,生效日期为2020年2月3日,失效日期为最大值3000年12月31日。

用户	手机号	生效日期	失效日期
1001	132111111111	2020-02-01	2020-02-02
1002	132111111112	2020-02-01	2020-02-03
1002	132111111113	2020-02-03	3000-12-31

若要查询用户的最新数据,只需要筛选出日期为3000年12月31日的数据;若要查看2020年2月2日的历史数据,则筛选生效时间<=2020-02-02并且失效时间>2020-02-02。

实现

相关表结构:

--事实表
create table public.member_fatdt0
(
	member_id varchar(64) --会员id
	,phoneno varchar(20) --电话号码
	,dw_beg_date date   --生效日期
	,dw_end_date date   --失效日期
	,dtype char(1)     --类型（历史数据、当前数据）
	,dw_status char(1)  --数据操作类型(I,D,U)
	,dw_ins_date date  --数据仓库插入日期
) with(appendonly=true,compresslevel=5)
distributed by (member_id)
partition by range(dw_end_date)
(
	partition p20200201 start(date'2020-02-01') inclusive,
	partition p20200202 start(date'2020-02-02') inclusive,
	partition p20200203 start(date'2020-02-03') inclusive,
	partition p30001231 start(date'3000-12-31') inclusive
	end (date'3001-01-01') exclusive
);
 --当天的数据库变更数据存储表
create table public.member_delta
(
	member_id varchar(64)  --会员号
	,phoneno varchar(20)  --电话号码
	,action char(1)  --类型（新增、删除、更新）
	,dw_ins_date date  --数据仓库插入日期
)with(appendonly=true,compresslevel=5)
distributed by(member_id)
;
--临时表,主要用于记录历史数据和当前数据的变更
create table public.member_tmp0
(
	member_id varchar(64)  --会员ID
	,phoneno varchar(20)   --电话号码
	,dw_beg_date date   --生效日期
	,dw_end_date date   --失效日期
	,dtype char(1)       --类型（历史数据H、当前数据C）
	,dw_status char(1)   --数据操作类型（I,D,U）
	,dw_ins_date date    --数据仓库插入日期
)with(appendonly=true,compresslevel=5)
distributed by(member_id)
partition by List(dtype)
(partition phis values('H'),
partition pcur values('C'),
default partition other
)
;

--临时表,用户交换分区
create table public.member_tmp1
(
	member_id varchar(64)
	,phoneno varchar(20)
	,dw_beg_date date 
	,dw_end_date date 
	,dtype char(1)
	,dw_status char(1)
	,dw_ins_date date
)with(appendonly=true,compresslevel=5)
distributed by(member_id);

2020年2月1日的数据(member_fatdt0):

2020年2月2日新增的数据(member_delta):

1. 关联member_fatdt0和member_delta表中更新,删除的数据进行左连接,关联上说明数据发生过变化,需要将该数据的生效时间更新为当天,并插入到临时表中的历史数据分区中,关联不上就插入到临时表的当前数据分区

--全量数据为2月1日,在2月3日刷新2月2日增量数据
truncate table public.member_tmp0;
insert into  public.member_tmp0
(
member_id
,phoneno
,dw_beg_date
,dw_end_date
,dtype
,dw_status
,dw_ins_date
)
select 
  a.member_id
  ,a.phoneno
  ,a.dw_beg_date
  ,case when b.member_id is null then a.dw_end_date
  else date'20200202' end as dw_end_date
  ,case when b.member_id is null then 'C'
  else 'H' end as dtype
  ,case when b.member_id is null then a.dw_status
  else b.action end as dw_status
  ,date'20200203'
from public.member_fatdt0 a
left join public.member_delta b
on a.member_id=b.member_id
and b.action in ('D','U')
where a.dw_beg_date<=date'20200202'-1
and a.dw_end_date>date'20200202'-1

2. 将member_delta的新增,更新数据插入到临时表(member_tmp0)表的当前数据分区中

insert into  public.member_tmp0
(
member_id
,phoneno
,dw_beg_date
,dw_end_date
,dtype
,dw_status
,dw_ins_date
)
select 
  member_id
  ,phoneno
  ,date'20200202'
  ,date'30001231'
  ,'C'
  ,action
  ,date'20200203'
from public.member_delta
where action in ('I','U');

3. 将member_fatdt0表中的对应分区与member_tmp0表的历史数据分区交换

truncate table public.member_tmp1;
alter table public.member_tmp0 exchange partition for('H') with table public.member_tmp1;
alter table public.member_fatdt0 exchange partition for('2020-02-02') with table public.member_tmp1;

4. 将member_fatdt0表中的对应分区与member_tmp0表的当前数据分区交换

alter table public.member_tmp0 exchange partition for('C') with table public.member_tmp1;
alter table public.member_fatdt0 exchange partition for('3000-12-31') with table public.member_tmp1;