但行好事
莫论前程❤

数据仓库初识 —- 数据表分类

  • 拉链表
  • 代码表 – 代码表包含一组基本值,可用于将数据分类。 (即由枚举类组成的数据表)
  • 流水表 – 对于表的每一个修改都会记录,可以用于反映实际记录的变更
  • 统计表 – 数据经整理后使之进一步表格化,便形成统计表。
  • 当前表 –
  • 追加流水表 –
  • 7天追加流水表 –
  • 全量表
  • 增量表
  • 快照表
  • 待续…

拉链表

拉链表是针对数据仓库设计中表存储数据的方式而定义的,顾名思义,所谓拉链,就是记录历史。记录一个事物从开始,一直到当前状态的所有变化的信息。

拉链表用来维护历史状态,以及最新状态数据的一种表,拉链表根据拉链粒度的不同,实际上相当于快照,只不过做了优化,去除了一部分不变的记录而已,通过拉链表可以很方便的还原出拉链时点的客户记录。

我们先看一个示例,这就是一张拉链表,存储的是用户的最基本信息以及每条记录的生命周期。我们可以使用这张表拿到最新的当天的最新数据以及之前的历史数据。

注册日期手机号码t_start_datet_end_date
2017-01-01001 1111112017-01-019999-12-31
2017-01-01002 2222222017-01-012017-01-01
2017-01-01002 2333332017-01-029999-12-31

我们暂且不对这张表做细致的讲解,后文会专门来阐述怎么来设计、实现和使用它。

拉链表的使用场景

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

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

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

为什么使用拉链表

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

方案一

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

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

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

方案二

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

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

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

拉链表

拉链表在使用上基本兼顾了我们的需求。

首先它在空间上做了一个取舍,虽说不像方案一那样占用量那么小,但是它每日的增量可能只有方案二的千分之一甚至是万分之一。

其实它能满足方案二所能满足的需求,既能获取最新的数据,也能添加筛选条件也获取历史的数据。

所以我们还是很有必要来使用拉链表的。

拉链表的设计和实现

我们先看一下在Mysql关系型数据库里的user表中信息变化。

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

注册日期用户编号手机号码
2017-01-01001111111
2017-01-01002222222
2017-01-01003333333
2017-01-01004444444

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

注册日期用户编号手机号码备注
2017-01-01001111111
2017-01-01002233333(由222222变成233333)
2017-01-01003333333
2017-01-01004432432(由444444变成432432)
2017-01-02005555555(2017-01-02新增)

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

注册日期用户编号手机号码备注
2017-01-01001111111
2017-01-01002233333
2017-01-01003333333
2017-01-01004654321(由432432变成654321)
2017-01-02005115115(由555555变成115115)
2017-01-03006666666(2017-01-03新增)

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

注册日期用户编号手机号码t_start_datet_end_date
2017-01-010011111112017-01-019999-12-31
2017-01-010022222222017-01-012017-01-01
2017-01-010022333332017-01-029999-12-31
2017-01-010033333332017-01-019999-12-31
2017-01-010044444442017-01-012017-01-01
2017-01-010044324322017-01-022017-01-02
2017-01-010046543212017-01-039999-12-31
2017-01-020055555552017-01-022017-01-02
2017-01-020051151152017-01-039999-12-31
2017-01-030066666662017-01-039999-12-31

说明

  • 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个状态变更,我们只取最后一个状态,这种天粒度的表其实已经能解决大部分的问题了。

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,
           CASE
                WHEN A.t_end_time = '9999-12-31' AND B.user_num IS NOT NULL THEN '2017-01-01'
                ELSE A.t_end_time
           END AS t_end_time
    FROM dws.user_his AS A
    LEFT JOIN ods.user_update AS B
    ON A.user_num = B.user_num
UNION
    SELECT C.user_num,
           C.mobile,
           C.reg_date,
           '2017-01-02' AS t_start_time,
           '9999-12-31' AS t_end_time
    FROM ods.user_update AS C
) AS T
赞(0) 打赏
未经允许不得转载:刘鹏博客 » 数据仓库初识 —- 数据表分类
分享到: 更多 (0)

相关推荐

  • 暂无文章

评论 抢沙发

评论前必须登录!

 

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏