数据仓库之拉链表设计

蔚落 2022-09-02 05:25 253阅读 0赞

*  [一、拉链表的使用场景][Link 1]
 *  [二、拉链表的设计和实现][Link 2]
    
     *  [1、数据需求][1]
     *  [2、拉链表设计说明][2]
 *  [三、在Hive中实现拉链表][Hive]
    
     *  [1、创建ods层和dw层表][1_ods_dw]
     *  [2、增量的sql实现][2_sql]
     *  [3、查询性能][3]
 *  [四、拉链表的sparkSQL整合hive实现][sparkSQL_hive]
    
     *  [0、拉链表的数据效果图][0]
     *  [1、拉链表设计具体步骤][1 1]
     *  [2、MySQL和ods层以及dw层数据说明][2_MySQL_ods_dw]
     *  [3、数据导入][3 1]

## 一、拉链表的使用场景 ##

在数据仓库的数据模型设计过程中，经常会遇到下面这种表的设计：

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

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

*  方案一：每天只留最新的一份，比如我们每天用Sqoop抽取最新的一份全量数据到Hive中。

这种方案实现起来很简单，每天drop掉前一天的数据，重新抽一份最新的。优点很明显，节省空间，一些普通的使用也很方便，不用在选择表的时候加一个时间分区什么的。缺点同样明显，没有历史数据，先翻翻旧账只能通过其它方式，比如从流水表里面抽。

*  方案二：每天保留一份全量的切片数据。

每天一份全量的切片是一种比较稳妥的方案，而且历史数据也在。缺点就是存储空间占用量太大太大了，如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费。当然我们也可以做一些取舍，比如只保留近一个月的数据？但是，需求是无耻的，数据的生命周期不是我们能完全左右的。

*  方案三：使用拉链表。

拉链表在使用上基本兼顾了我们的需求。首先它在空间上做了一个取舍，虽说不像方案一那样占用量那么小，但是它每日的增量可能只有方案二的千分之一甚至是万分之一。其实它能满足方案二所能满足的需求，既能获取最新的数据，也能添加筛选条件也获取历史的数据。所以我们还是很有必要来使用拉链表的。

## 二、拉链表的设计和实现 ##

1、数据需求

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

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

复制注册日期	    用户编号	手机号码
    2017-01-01	001	111111
    2017-01-01	002	222222
    2017-01-01	003	333333
    2017-01-01	004	444444

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

复制注册日期  	用户编号	手机号码  	备注
    2017-01-01	001	111111	
    2017-01-01	002	233333	（由222222变成233333）
    2017-01-01	003	333333	
    2017-01-01	004	432432	（由444444变成432432）
    2017-01-02	005	555555	（2017-01-02新增）

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

复制注册日期	    用户编号	手机号码	    备注
    2017-01-01	001	111111	
    2017-01-01	002	233333	
    2017-01-01	003	333333	
    2017-01-01	004	654321	（由432432变成654321）
    2017-01-02	005	115115	（由555555变成115115）
    2017-01-03	006	666666	（2017-01-03新增）

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

复制注册日期	    用户编号	手机号码	t_start_date	t_end_date
    2017-01-01	001	111111	2017-01-01	9999-12-31
    2017-01-01	002	222222	2017-01-01	2017-01-01
    2017-01-01	002	233333	2017-01-02	9999-12-31
    2017-01-01	003	333333	2017-01-01	9999-12-31
    2017-01-01	004	444444	2017-01-01	2017-01-01
    2017-01-01	004	432432	2017-01-02	2017-01-02
    2017-01-01	004	654321	2017-01-03	9999-12-31
    2017-01-02	005	555555	2017-01-02	2017-01-02
    2017-01-02	005	115115	2017-01-03	9999-12-31
    2017-01-03	006	666666	2017-01-03	9999-12-31

2、拉链表设计说明

*  **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的历史快照，判断生效时间小于等于2017-01-02，失效时间大于等于2017-01-02，则

复制select * from user where t_start_date <= ‘2017-01-02’ and t_end_date >= ‘2017-01-02’

## 三、在Hive中实现拉链表 ##

1、创建ods层和dw层表

*  ods层的ods.user表  
    以日期作为分区字段，压缩格式为ORC

复制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层的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';
    )

*  在dw层创建拉链表：dws.user\_his  
    在这张表中没有分区，但压缩格式依旧是ORC

复制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';
    )

2、增量的sql实现

假设我们已经已经初始化了2017-01-01的日期，然后需要更新2017-01-02那一天的数据  
  实现思路：（对需要修改的数据进行update） UNION ALL （新增的数据）

复制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 ALL
        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

3、查询性能

假如存放了5年的拉链数据，那么这张表势必会比较大，当查询的时候性能就比较低了，个人认为两个思路来解决：

*  在一些查询引擎中，我们对start\_date和end\_date做索引，这样能提高不少性能。
 *  保留部分历史数据，比如说我们一张表里面存放全量的拉链表数据，然后再对外暴露一张只提供近3个月数据的拉链表。

## 四、拉链表的sparkSQL整合hive实现 ##

现在介绍的这种设计方案与上面的方案大同小异，主要区别在于**数据的存储格式**，以**及分区的设计**，**增量抽取数据时增加了临时表作为存储**。

0、拉链表的数据效果图

![2381e5fada012e8cdf5a4cb443576d81.png][]  
![f0b2c721dfc055b4f4dae32c83a95d4c.png][]

![6da50a8c2f5d3f354c34eafb62f22e4d.png][]

1、拉链表设计具体步骤

*  1）第一次全量导入
    
     *  所有的ODS层数据全量导入到拉链历史记录表中
 *  2）增量导入（某天，例如：2021-07-21）
    
     *  增量导入某天的数据到ODS分区
     *  合并历史数据（通过连接查询的方式进行更新）

2、MySQL和ods层以及dw层数据说明

*  MySQL层数据和ods层数据保持一致
 *  在ods层使用dt作为日期，对每日增量数据进行分区存储，即每天都会增加一个分区

![8a9d9ac5b403e49ed640eb6d3a0ca62c.png][]

*  ods层数据都是用parquet格式存储，snappy压缩格式，对于sparkSQL的支持是最好的
 *  在dw层使用拉链表技术，不分区
 *  dw层每张表都有一张对应的临时中间表

![fb214fe3c9182e4eb601feab8156d0c7.png][]

3、数据导入

*  使用sparkSQL进行数据操作全量导入（数据日期是20190909号之前的）

![2fd7da3cca02c59fed1f7b6e9a0396d5.png][]  
![fa20f201113782fba085f21c2637c730.png][]

**modifyTime，**为数据的修改时间  
  **dw\_start\_date，**为数据的生效时间，只要当modifyTime不为空，就等于modifyTime的时间，否则就等于createTime创建时间的值  
  **dw\_end\_date，**为数据的失效时间，第一次全量导入时全部都初始化为"9999-12-31"，寓意永久不失效

*  增量导入（将20190910号的数据导入到拉链表中）

1）使用kettle将20190910的数据抽取到ods

![8882b93a4ea793ffac3f2ff2b3ea63e0.png][]

2）编写spark-sql更新历史数据（从ods层的20190910号分区获取数据）

![e3a8ed0e5a8abbc92a2d166690f8b89b.png][]  
![a6ea2e9aba006cc1e8b2f8e77284f655.png][]

**dw\_start\_date，**为数据的生效时间，此时不会做改变  
  **dw\_end\_date**，为数据的失效时间，如果dw\_end\_date依旧为"9999-12-31"，并且ods层的20190910号分区中goodsId（即两张表中都用此商品Id时），把dw\_end\_date更新为**昨天的日期**"2019-09-09"，否则日期不变

3）编写spark-sql获取当日数据

![c165f42236e318efef733e2c1a1db8b9.png][]  
![440635e9400e1adf766fb23d061c3a34.png][]

**dw\_start\_date，**为数据的生效时间，只要当modifyTime不为空，就等于modifyTime的时间，否则就等于createTime创建时间的值  
  **dw\_end\_date，**为数据的失效时间，此时不会做改变

4）先把数据合并到临时表中，第5步在把临时表中的数据插入到dw层的表中

![d6c97b33e34a8578e8591191d7f80ec6.png][]  
![8c0716bf3f36ea7d7fb9ebe34a28d9c4.png][]  
![771470b698bb5ff0fa3f606d3a26f23c.png][]

5）将历史数据和当日数据导入到历史拉链表中及查询数据

![2ccb6b5fcbf4851e352a1981503689a8.png][]

[Link 1]: https://www.cnblogs.com/nthforsth/p/15088772.html#%E4%B8%80%E3%80%81%E6%8B%89%E9%93%BE%E8%A1%A8%E7%9A%84%E4%BD%BF%E7%94%A8%E5%9C%BA%E6%99%AF
[Link 2]: https://www.cnblogs.com/nthforsth/p/15088772.html#%E4%BA%8C%E3%80%81%E6%8B%89%E9%93%BE%E8%A1%A8%E7%9A%84%E8%AE%BE%E8%AE%A1%E5%92%8C%E5%AE%9E%E7%8E%B0
[1]: https://www.cnblogs.com/nthforsth/p/15088772.html#1%E3%80%81%E6%95%B0%E6%8D%AE%E9%9C%80%E6%B1%82
[2]: https://www.cnblogs.com/nthforsth/p/15088772.html#2%E3%80%81%E6%8B%89%E9%93%BE%E8%A1%A8%E8%AE%BE%E8%AE%A1%E8%AF%B4%E6%98%8E
[Hive]: https://www.cnblogs.com/nthforsth/p/15088772.html#%E4%B8%89%E3%80%81%E5%9C%A8hive%E4%B8%AD%E5%AE%9E%E7%8E%B0%E6%8B%89%E9%93%BE%E8%A1%A8
[1_ods_dw]: https://www.cnblogs.com/nthforsth/p/15088772.html#1%E3%80%81%E5%88%9B%E5%BB%BAods%E5%B1%82%E5%92%8Cdw%E5%B1%82%E8%A1%A8
[2_sql]: https://www.cnblogs.com/nthforsth/p/15088772.html#2%E3%80%81%E5%A2%9E%E9%87%8F%E7%9A%84sql%E5%AE%9E%E7%8E%B0
[3]: https://www.cnblogs.com/nthforsth/p/15088772.html#3%E3%80%81%E6%9F%A5%E8%AF%A2%E6%80%A7%E8%83%BD
[sparkSQL_hive]: https://www.cnblogs.com/nthforsth/p/15088772.html#%E5%9B%9B%E3%80%81%E6%8B%89%E9%93%BE%E8%A1%A8%E7%9A%84sparksql%E6%95%B4%E5%90%88hive%E5%AE%9E%E7%8E%B0
[0]: https://www.cnblogs.com/nthforsth/p/15088772.html#0%E3%80%81%E6%8B%89%E9%93%BE%E8%A1%A8%E7%9A%84%E6%95%B0%E6%8D%AE%E6%95%88%E6%9E%9C%E5%9B%BE
[1 1]: https://www.cnblogs.com/nthforsth/p/15088772.html#1%E3%80%81%E6%8B%89%E9%93%BE%E8%A1%A8%E8%AE%BE%E8%AE%A1%E5%85%B7%E4%BD%93%E6%AD%A5%E9%AA%A4
[2_MySQL_ods_dw]: https://www.cnblogs.com/nthforsth/p/15088772.html#2%E3%80%81mysql%E5%92%8Cods%E5%B1%82%E4%BB%A5%E5%8F%8Adw%E5%B1%82%E6%95%B0%E6%8D%AE%E8%AF%B4%E6%98%8E
[3 1]: https://www.cnblogs.com/nthforsth/p/15088772.html#3%E3%80%81%E6%95%B0%E6%8D%AE%E5%AF%BC%E5%85%A5
[2381e5fada012e8cdf5a4cb443576d81.png]: /images/20220829/9ebc6fdf4ac44ec9a835b1797c35b78b.png
[f0b2c721dfc055b4f4dae32c83a95d4c.png]: /images/20220829/d3c647fa2fb041e388fd0cc90eab0902.png
[6da50a8c2f5d3f354c34eafb62f22e4d.png]: /images/20220829/976a6cc03a31490c894ec7733d615ee5.png
[8a9d9ac5b403e49ed640eb6d3a0ca62c.png]: /images/20220829/4711f64159aa47fa97f9fd3f9ba55ed8.png
[fb214fe3c9182e4eb601feab8156d0c7.png]: /images/20220829/ef49de7a4d1c45b6b229cfdea8e5f15f.png
[2fd7da3cca02c59fed1f7b6e9a0396d5.png]: /images/20220829/12ae8cea2e9b49988313ff26ddeac42a.png
[fa20f201113782fba085f21c2637c730.png]: /images/20220829/c66d15e08f7a48f887355078b9356303.png
[8882b93a4ea793ffac3f2ff2b3ea63e0.png]: /images/20220829/31ba000ab6974939a02d95b1f4b9be57.png
[e3a8ed0e5a8abbc92a2d166690f8b89b.png]: /images/20220829/ec97139a5ea140d5a8d1a099ea478ca3.png
[a6ea2e9aba006cc1e8b2f8e77284f655.png]: /images/20220829/0149b3d97bc749578d1d9bf5ad0d5de5.png
[c165f42236e318efef733e2c1a1db8b9.png]: /images/20220829/3119ec8c50e0447cb9d89ef01a320c4b.png
[440635e9400e1adf766fb23d061c3a34.png]: /images/20220829/636d716b64724d2e8e161d054c781194.png
[d6c97b33e34a8578e8591191d7f80ec6.png]: /images/20220829/c32eebedeb1c4091b7f9f68595161682.png
[8c0716bf3f36ea7d7fb9ebe34a28d9c4.png]: /images/20220829/2429fb0e51604b7fa456cde77de4b5e5.png
[771470b698bb5ff0fa3f606d3a26f23c.png]: /images/20220829/c1769952ff734181ad0d54c5ab3564de.png
[2ccb6b5fcbf4851e352a1981503689a8.png]: /images/20220829/de47aeed7aea450abc31294678b63b63.png