主机屋网站搭建设置,免费咨询问题的网站,多国语言网站模板,梧州网站推广外包服务前言 本文将会谈一谈在数据仓库中拉链表相关的内容#xff0c;包括它的原理、设计、以及在我们大数据场景下的实现方式。 全文由下面几个部分组成#xff1a;
先分享一下拉链表的用途、什么是拉链表。通过一些小的使用场景来对拉链表做近一步的阐释#xff0c;以及拉链表和…
前言 本文将会谈一谈在数据仓库中拉链表相关的内容包括它的原理、设计、以及在我们大数据场景下的实现方式。 全文由下面几个部分组成
先分享一下拉链表的用途、什么是拉链表。通过一些小的使用场景来对拉链表做近一步的阐释以及拉链表和常用的切片表的区别。举一个具体的应用场景来设计并实现一份拉链表最后并通过一些例子说明如何使用我们设计的这张表因为现在Hive的大规模使用我们会以Hive场景下的设计为例。分析一下拉链表的优缺点并对前面的提到的一些内容进行补充说明比如说拉链表和流水表的区别。 什么是拉链表
拉链表是针对数据仓库设计中表存储数据的方式而定义的顾名思义所谓拉链就是记录历史。记录一个事物从开始一直到当前状态的所有变化的信息。
我们先看一个示例这就是一张拉链表存储的是用户的最基本信息以及每条记录的生命周期。我们可以使用这张表拿到最新的当天的最新数据以及之前的历史数据。 我们暂且不对这张表做细致的讲解后文会专门来阐述怎么来设计、实现和使用它。
拉链表的使用场景
在数据仓库的数据模型设计过程中经常会遇到下面这种表的设计
有一些表的数据量很大比如一张用户表大约10亿条记录50个字段这种表即使使用ORC压缩单张表的存储也会超过100G在HDFS使用双备份或者三备份的话就更大一些。表中的部分字段会被update更新操作如用户联系方式产品的描述信息订单的状态等等。需要查看某一个时间点或者时间段的历史快照信息比如查看某一个订单在历史某一个时间点的状态。表中的记录变化的比例和频率不是很大比如总共有10亿的用户每天新增和发生变化的有200万左右变化的比例占的很小。
那么对于这种表我该如何设计呢下面有几种方案可选
方案一每天只留最新的一份比如我们每天用Sqoop抽取最新的一份全量数据到Hive中。方案二每天保留一份全量的切片数据。方案三使用拉链表。
为什么使用拉链表
现在我们对前面提到的三种进行逐个的分析。
方案一
这种方案就不用多说了实现起来很简单每天drop掉前一天的数据重新抽一份最新的。
优点很明显节省空间一些普通的使用也很方便不用在选择表的时候加一个时间分区什么的。
缺点同样明显没有历史数据先翻翻旧账只能通过其它方式比如从流水表里面抽。
方案二
每天一份全量的切片是一种比较稳妥的方案而且历史数据也在。
缺点就是存储空间占用量太大太大了如果对这边表每天都保留一份全量那么每次全量中会保存很多不变的信息对存储是极大的浪费这点我感触还是很深的…
当然我们也可以做一些取舍比如只保留近一个月的数据但是需求是无耻的数据的生命周期不是我们能完全左右的。
拉链表
拉链表在使用上基本兼顾了我们的需求。
首先它在空间上做了一个取舍虽说不像方案一那样占用量那么小但是它每日的增量可能只有方案二的千分之一甚至是万分之一。
其实它能满足方案二所能满足的需求既能获取最新的数据也能添加筛选条件也获取历史的数据。
所以我们还是很有必要来使用拉链表的。 拉表链的设计与实现
如何设计一张拉链表
下面我们来举个栗子详细看一下拉链表。
我们用电商网站的例子现在以用户的拉链表来说明。
我们先看一下在Mysql关系型数据库里的user表中信息变化。
在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中实现拉链表
在现在的大数据场景下大部分的公司都会选择以Hdfs和Hive为主的数据仓库架构。目前的Hdfs版本来讲其文件系统中的文件是不能做改变的也就是说Hive的表智能进行删除和添加操作而不能进行update。基于这个前提我们来实现拉链表。
还是以上面的用户表为例我们要实现用户的拉链表。在实现它之前我们需要先确定一下我们有哪些数据源可以用。
我们需要一张ODS层的用户全量表。至少需要用它来初始化。每日的用户更新表。
而且我们要确定拉链表的时间粒度比如说拉链表每天只取一个状态也就是说如果一天有3个状态变更我们只取最后一个状态这种天粒度的表其实已经能解决大部分的问题了。
另外补充一下每日的用户更新表该怎么获取据笔者的经验有3种方式拿到或者间接拿到每日的用户增量因为它比较重要所以详细说明
我们可以监听Mysql数据的变化比如说用Canal最后合并每日的变化获取到最后的一个状态。假设我们每天都会获得一份切片数据我们可以通过取两天切片数据的不同来作为每日更新表这种情况下我们可以对所有的字段先进行concat再取md5这样就ok了。流水表有每日的变更流水表。
ods层的user表
现在我们来看一下我们ods层的用户资料切片表的结构
CREATE EXTERNAL TABLE ods.user (user_num STRING COMMENT 用户编号,mobile STRING COMMENT 手机号码,reg_date STRING COMMENT 注册日期
COMMENT 用户资料表
PARTITIONED BY (dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t LINES TERMINATED BY \n
STORED AS ORC
LOCATION /ods/user;
)ods层的user_update表
然后我们还需要一张用户每日更新表前面已经分析过该如果得到这张表现在我们假设它已经存在。
CREATE EXTERNAL TABLE ods.user_update (user_num STRING COMMENT 用户编号,mobile STRING COMMENT 手机号码,reg_date STRING COMMENT 注册日期
COMMENT 每日用户资料更新表
PARTITIONED BY (dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t LINES TERMINATED BY \n
STORED AS ORC
LOCATION /ods/user_update;
)拉链表
现在我们创建一张拉链表
CREATE EXTERNAL TABLE dws.user_his (user_num STRING COMMENT 用户编号,mobile STRING COMMENT 手机号码,reg_date STRING COMMENT 用户编号,t_start_date ,t_end_date
COMMENT 用户资料拉链表
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t LINES TERMINATED BY \n
STORED AS ORC
LOCATION /dws/user_his;
)实现sql语句
然后初始化的sql就不写了其实就相当于是拿一天的ods层用户表过来就行我们写一下每日的更新语句。
现在我们假设我们已经已经初始化了2017-01-01的日期然后需要更新2017-01-02那一天的数据我们有了下面的Sql。
然后把两个日期设置为变量就可以了。
INSERT OVERWRITE TABLE dws.user_his
SELECT * FROM
(SELECT A.user_num,A.mobile,A.reg_date,A.t_start_time,CASEWHEN A.t_end_time 9999-12-31 AND B.user_num IS NOT NULL THEN 2017-01-01ELSE A.t_end_timeEND AS t_end_timeFROM dws.user_his AS ALEFT JOIN ods.user_update AS BON A.user_num B.user_num
UNIONSELECT C.user_num,C.mobile,C.reg_date,2017-01-02 AS t_start_time,9999-12-31 AS t_end_timeFROM ods.user_update AS C
) AS T补充
好了我们分析了拉链表的原理、设计思路、并且在Hive环境下实现了一份拉链表下面对拉链表做一些小的补充。
拉链表和流水表
流水表存放的是一个用户的变更记录比如在一张流水表中一天的数据中会存放一个用户的每条修改记录但是在拉链表中只有一条记录。
这是拉链表设计时需要注意的一个粒度问题。我们当然也可以设置的粒度更小一些一般按天就足够。
查询性能
拉链表当然也会遇到查询性能的问题比如说我们存放了5年的拉链数据那么这张表势必会比较大当查询的时候性能就比较低了个人认为两个思路来解决
在一些查询引擎中我们对start_date和end_date做索引这样能提高不少性能。保留部分历史数据比如说我们一张表里面存放全量的拉链表数据然后再对外暴露一张只提供近3个月数据的拉链表。 总结
我们在这篇文章里面详细地分享了一下和拉链表相关的知识点但是仍然会有一会遗漏。欢迎交流。
在后面的使用中又有了一些心得补充进来 使用拉链表的时候可以不加t_end_date即失效日期但是加上之后能优化很多查询。 可以加上当前行状态标识能快速定位到当前状态。 在拉链表的设计中可以加一些内容因为我们每天保存一个状态如果我们在这个状态里面加一个字段比如如当天修改次数那么拉链表的作用就会更大。
补充 学习 极限存储 摘取大数据之路 极限存储
上面的拉链表存储方式对于下游使用方存在一定的理解障碍特别是ODS 数据面向的下游用户包括数据分析师、前端开发人员等他们不怎么理解维度模型的概念因此会存在较高的解释成本。另外这种存储方式用start_dt 和end_dt 做分区随着时间的推移分区数量会极度膨胀而现行的数据库系统都有分区数量限制。
为了解决上述两个问题阿里巴巴提出来用极限存储的方式来处理。
1 . 透明化 底层的数据还是历史拉链存储但是上层做一个视图操作或者在 Hive 里做一个hook 通过分析语句的语法树把对极限存储前的表的 查询转换成对极限存储表的查询。对于下游用户来说极限存储表和全 量存储方式是一样的
Select * from A where ds 20160101等价于
Select * from A EXST where start dt 20160101 and end dt
20160101 ;2.分月做历史拉链表
假设用start_dt 和end一dt 做分区并且不做限制那么可以计算出 一年历史拉链表最多可能产生的分区数是365 × 36 4 /266 430 个。如果 在每个月月初重新开始做历史拉链表目录结构如下
[-- 2 01410/ 每月一个周期
一一20141001/ 201410 INFINITY 每月1 日的全量数据
一一20141001/20141002 # 1001 产生且1002 死亡记录
一一20141001/20141003 # 1001 产生且1003 死亡记录
一一 20141001/20141031 # 1001 产生且1031 死亡记录
一一 20141002/ 201410 INFINITY # 1002 产生新增记录
一一 20141002/20141003 # 1002 产生且1003 死亡记录
[---- 20141002 /20141031/
』 20141003/ 201410 INFINITY
# 1002 产生且1031 死亡记录
# 1003 产生新增记录
! ---- 20141031/ 201410 INFINITY
[ -- 2 01411/
# 1031 产生新增记录
...再计算一年最多可能产生的分区数是12 × 1 (3029)/2)5232 个。采用极限存储的处理方式极大地压缩了全量存储的成本又可以达到对下游用户透明的效果是一种比较理想的存储方式。但是其本身也有一定的局限性
首先其产出效率很低大部分极限存储通常需要t-2
其次对于变化频率高的数据并不能达到节约成本的效果。因此在实际生产中做极限存储需要进行一些额外的处理。
·在做极限存储前有一个全量存储表全量存储表仅保留最近一段时间的全量分区数据历史数据通过映射的方式关联到极限存储表。即用户只访问全量存储表所以对用户来说极限存储是不可见的。
·对于部分变化频率频繁的宇段需要过滤。例如用户表中存在用户积分宇段这种宇段的值每天都在发生变化如果不过滤的话极限存储就相当于每个分区存储一份全量数据起不到节约存储成本的效果。
