背景

 

拉链表是针对数据仓库设计中表存储数据的方式而定义的,主要是维护历史状态,以及最新状态数据的一种表,拉链表根据拉链粒度的不同,实际上相当于快照,只不过做了优化,去除了一部分不变的记录,通过拉链表可以很方便的还原出拉链时点的客户记录。

拉链表相对来说应用场景比较有限,一般使用hive的分区表、或者全量、增量更新表或者存储快照方便之后查询。

全量表:每天的所有的最新状态的数据;

增量表:每天的新增数据;

拉链表:维护历史状态,以及最新状态数据;

流水表:对于表中的每一个修改都会记录,可以用于反映实际记录的变更;

拉链历史表,既能满足反应数据的历史状态,又可以最大程度的节省存储。

 

应用场景

 

  • 有一些表的数据量很大,比如一张用户表,大约10亿条记录,50个字段,这种表,即使使用ORC压缩,单张表的存储也会超过100G,在HDFS使用双备份或者三备份的话就更大一些。
  • 表中的部分字段会被update更新操作,如用户联系方式,产品的描述信息,订单的状态等等。
  • 需要查看某一个时间点或者时间段的历史快照信息,比如,查看某一个订单在历史某一个时间点的状态。
  • 表中的记录变化的比例和频率不是很大,比如,总共有10亿的用户,每天新增和发生变化的有200万左右,变化的比例占的很小。

那么对于这种表我该如何设计呢?下面有几种方案可选:

  • 方案一:每天只留最新的一份,比如抽取最新的一份全量数据到Hive中。
  • 方案二:每天保留一份全量的切片数据。
  • 方案三:使用拉链表。

现在我们对前面提到的三种进行逐个的分析。

方案一

这种方案就不用多说了,实现起来很简单,每天drop掉前一天的数据,重新抽一份最新的。

优点很明显,节省空间,一些普通的使用也很方便,不用在选择表的时候加一个时间分区什么的。

缺点同样明显,没有历史数据,先翻翻旧账只能通过其它方式,比如从流水表里面抽。

方案二

每天一份全量的切片是一种比较稳妥的方案,而且历史数据也在。

缺点就是存储空间占用量太大太大了,如果对这边表每天都保留一份全量,那么每次全量中会保存很多不变的信息,对存储是极大的浪费。

当然我们也可以做一些取舍,比如只保留近一个月的数据?但是,需求是无耻的,数据的生命周期不是我们能完全左右的。

此时可以考虑使用拉链表。首先它在空间上做了一个取舍,虽说不像方案一那样占用量那么小,但是它每日的增量可能只有方案二的千分之一甚至是万分之一。其次它能满足方案二所能满足的需求,既能获取最新的数据,也能添加筛选条件也获取历史的数据。

拉链表的设计和实现

有一张用户表

在2017-01-01这一天表中的数据是:

在2017-01-02这一天表中的数据是, 用户002和004资料进行了修改,005是新增用户:

在2017-01-03这一天表中的数据是, 用户004和005资料进行了修改,006是新增用户:

如果在数据仓库中设计成历史拉链表保存该表,则会有下面这样一张表,这是最新一天(即2017-01-03)的数据:

说明

  • t_start_date表示该条记录的生命周期开始时间,t_end_date表示该条记录的生命周期结束时间。
  • t_end_date = '9999-12-31'表示该条记录目前处于有效状态。
  • 如果查询当前所有有效的记录,则select * from user where t_end_date = '9999-12-31'。
  • 如果查询2017-01-02的历史快照,则select * from user where t_start_date <= '2017-01-02' and t_end_date >= '2017-01-02'。(此处要好好理解,是拉链表比较重要的一块)

HIVE实例

数据基本上都是每天全量同步,那么怎么来实现拉链表呢。

ods层的店铺资料表的结构如下(没有快照,只存一份,所以一旦查历史的快照很麻烦):

CREATE EXTERNAL TABLE IF NOT EXISTS db_business.ods_shp_target_dd
( 
`id` int comment '主键',
`shop_code` string comment '店铺code',
`shop_name` string comment '店铺名称',
`date_create` timestamp comment '创建时间',
`date_update` timestamp comment '更新时间',
`date_delete` timestamp comment '删除时间'
)
COMMENT ''
ROW FORMAT DELIMITED
NULL DEFINED AS ""
STORED AS TEXTFILE
LOCATION 'hdfs://NameNodeHACluster/apps/hive/warehouse/db_business.db/ods_shp_target_dd'

创建拉链表

CREATE EXTERNAL TABLE IF NOT EXISTS db_business.ods_shp_zipper_target_dd
( 
id int comment '主键',
shop_code string comment '店铺code',
shop_name string comment '店铺名称',
date_create timestamp comment '创建时间',
date_update timestamp comment '更新时间',
date_delete timestamp comment '删除时间',
t_start string comment '该条记录的生命周期开始时间',
t_end string comment '该条记录的生命周期开始时间'
)
COMMENT '店铺的健康目标拉链表--tes'
ROW FORMAT DELIMITED
NULL DEFINED AS ""
STORED AS TEXTFILE
LOCATION 'hdfs://NameNodeHACluster/apps/hive/warehouse/db_business.db/ods_shp_zipper_target_dd';

INSERT OVERWRITE TABLE db_business.ods_shp_zipper_target_dd
select id,
shop_code,
shop_name,
date_create,
date_update,
date_delete,
'#yesterday_ds#'  AS t_start,
'9999-12-31' AS t_end
from db_business.ods_shp_target_dd

代码实现逻辑

1、查询拉链表中的数据在ods表中哪些被删除了,将所有删除的数据t_end时间改为当天。
2、拉链表去除删除的数据,在union已经将删除的数据t_end时间改为当天的数据。
3、在union ods表后distinct 变成一张新表。
4、对这张新表查询t_start is null就为每天有变化的数据,将t_end改为9999-12-31。
5、在判断哪些数据是新增的,哪些是更新的,将更新的数据 中的历史数据t_end改为当天。

流程图如下:

这个实现的逻辑是基于Hive表来实现的,相对来说比较繁琐,正常的业务逻辑可以通过mysql事务去更新这类数据。

最后修改于 2021-07-21 17:08:22
上一篇