数据仓库（什么是拉链表）

04-11 1536阅读

中文名 ：企业数据仓库

外文名 ：Enterprise Data Warehouse

简称 ：EDW

数据仓库(DW)概念的创始人W. H.Inmon对数据仓库下了这样的定义：“数据仓库是一个面向主题的、集成的、非易失的且随时间变化的数据集合，用来支持管理人员的决策。”数据仓库将大量用于事物处理的传统数据库数据进行清理、抽取和转换，使原始数据发生了质的变化，转化为适合分析的导出型数据，并按照决策主题的需要进行重新组织。

一、数据库和数据仓库的区别

1、概念上的区别

  1. 数据库，简而言之可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据进行新增、截取、更新、删除等操作。 ————百度百科
  2. 数据仓库，英文名称为Data Warehouse，可简写为DW或DWH。数据仓库，是为企业所有级别的决策制定过程，提供所有类型数据支持的战略集合。它是单个数据存储，出于分析性报告和决策支持目的而创建。 为需要业务智能的企业，提供指导业务流程改进、监视时间、成本、质量以及控制。 ————百度百科

        直观上理解：相同点是两者都是存储数据。不同点是数据库主要是基本的、日常的事务处理，例如银行交易；数据仓库，支持复杂的分析操作，侧重决策支持。

2、举个例子

举个最常见的例子，以我们常举例的电商来讲，我们侧重于从没有数据仓库到有数据仓库的演变阶段：

第一阶段：无分析需求阶段

电商早期，基本不需要太多数据分析，先跑起来系统就行，这时候买一套电商系统，搞点服务器，加一两个研发就能跑起来了。这时候对数据的需求就是只需要有个数据库就行。最多就是看看营业额就够，不需要数据仓库。

第二阶段：简单统计需求阶段

网站做大后流量来了，客户和订单都多起来了，普通查询已经有压力了，这个时候就需要升级架构变成多台服务器和多个业务数据库（量大+分库分表），这个阶段的业务数字和指标还可以勉强从业务数据库里查询。

此时仍不太需数据仓库，数据库勉强够用，定时从从库里面统计数据就可以。

第三阶段：复杂统计需求阶段

随着业务指数级的增长，数据量的会陡增，数据来源也越来越多样，这时已经不单单是交易类数据了，用户点击、和图片等数据都多了起来。

同时公司角色也开始多了起来，开始有了 各种老板，各种运营、市场、产品的同学，大家需要面临的问题越来越复杂，越来越深入，对数据的需求也越来越复杂。而复杂的分析类计算势必会对线上的数据库造成影响。

因为，业务数据库中的数据结构主要是为了完成交易而设计的，不是为了而查询和分析的便利设计的。业务数据库大多是读写优化的，即又要读，也要写。因此对于大量数据的读操作和复杂计算是支持不足。

而怎么解决这个问题，此时我们就需要建立一个数据仓库了。

3、技术上的区别

有了上面的分析，大家可能感觉还是比较虚，那我们举一些现实工作中遇到的技术，来看一下数据库和数据仓库的区别：

流行的数据库：MySQL、Oracle、SqlServer等

流行的数据仓库：Hive、Impala、Greenplum等

划分并不绝对，比如很多公司也会用Oracle来做数据仓库，但是基本没有公司用Hive来当作业务库来使用。

4、总结一下

数据库是面向事务的设计，数据仓库是面向主题设计的。

数据库一般服务于业务系统的，数据仓库一般是服务于分析系统的。

数据库一般存储在线交易数据，数据仓库存储的一般是历史数据。

数据库设计是尽量避免冗余，数据仓库在设计是有意引入冗余。

数据库是为捕获数据而设计，数据仓库是为分析数据而设计。

数据库一般会对数据进行增删改查，数据仓库一般只对进行增和查，基本不会修改数据。

当然，数据仓库不仅仅指的是一个存储引擎，而是一套完整的数据建设的方法论。

二、一种通用的数据仓库分层方法

1、一种通用的数据分层设计

为了满足前面提到数据分层带来的好处，我们将数据模型分为三层：数据运营层（ ODS ）、数据仓库层（DW）和数据应用层（APP）。如下图所示。简单来讲，我们可以理解为：**ODS层存放的是接入的原始数据，DW层是存放我们要重点设计的数据仓库中间层数据，APP是面向业务定制的应用数据。**下面详细介绍这三层的设计。

数据仓库（什么是拉链表）

2、存储

既然谈到了数据分层，那不同的层次中会用到什么计算引擎和存储系统呢，本节来简单分享一下。

数据层的存储一般如下：

Data Source：数据源一般是业务库和埋点，当然也会有第三方购买数据等多种数据来源方式。业务库的存储一般是Mysql 和 PostgreSql。

  1. ODS 层：ODS 的数据量一般非常大，所以大多数公司会选择存在HDFS上，即Hive或者Hbase，Hive居多。
  2. DW 层：一般和 ODS 的存储一致，但是为了满足更多的需求，也会有存放在 PG 和 ES 中的情况。
  3. APP 层：应用层的数据，一般都要求比较快的响应速度，因此一般是放在 Mysql、PG、Redis中。

计算引擎的话，可以简单参考图中所列就行。目前大数据相关的技术更新迭代比较快，本节所列仅为简单参考。

数据仓库（什么是拉链表）

三、数据仓库之拉链表（原理、设计以及在Hive中的实现）

全文由下面几个部分组成：

  1. 先分享一下拉链表的用途、什么是拉链表。
  2. 通过一些小的使用场景来对拉链表做近一步的阐释，以及拉链表和常用的切片表的区别。
  3. 举一个具体的应用场景，来设计并实现一份拉链表，最后并通过一些例子说明如何使用我们设计的这张表（因为现在Hive的大规模使用，我们会以Hive场景下的设计为例）。
  4. 分析一下拉链表的优缺点，并对前面的提到的一些内容进行补充说明，比如说拉链表和流水表的区别。

1、什么是拉链表？

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

数据仓库（什么是拉链表）

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

数据仓库（什么是拉链表）

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

2、拉链表的使用场景

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

有一些表的数据量很大，比如一张用户表，大约10亿条记录，50个字段，这种表，即使使用ORC压缩，单张表的存储也会超过100G，在HDFS使用双备份或者三备份的话就更大一些。

表中的部分字段会被update更新操作，如用户联系方式，产品的描述信息，订单的状态等等。

需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态。

表中的记录变化的比例和频率不是很大，比如，总共有10亿的用户，每天新增和发生变化的有200万左右，变化的比例占的很小。

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

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

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

方案三：使用拉链表。

3、为什么使用拉链表？

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

方案一：

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

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

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

方案二:

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

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

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

方案二:拉链表

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

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

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

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

4、拉链表的设计和实现

如何设计一张拉链表

下面我们来举个栗子详细看一下拉链表。

以用户的拉链表来说明，我们先看一下在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’。（此处要好好理解，是拉链表比较重要的一块。）

    5、在Hive中实现拉链表

    传视频

    hive（数据仓库工具）

    hive是基于Hadoop的一个数据仓库工具，用来进行数据提取、转化、加载，这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。hive数据仓库工具能将结构化的数据文件映射为一张数据库表，并提供SQL查询功能，能将SQL语句转变成MapReduce任务来执行。Hive的优点是学习成本低，可以通过类似SQL语句实现快速MapReduce统计，使MapReduce变得更加简单，而不必开发专门的MapReduce应用程序。hive十分适合对数据仓库进行统计分析。

    在现在的大数据场景下，大部分的公司都会选择以Hdfs和Hive为主的数据仓库架构。目前的Hdfs版本来讲，其文件系统中的文件是不能做改变的，也就是说Hive的表智能进行删除和添加操作，而不能进行update。基于这个前提，我们来实现拉链表。

    还是以上面的用户表为例，我们要实现用户的拉链表。在实现它之前，我们需要先确定一下我们有哪些数据源可以用。

    1. 我们需要一张ODS层的用户全量表。至少需要用它来初始化。
    2. 每日的用户更新表。

    而且我们要确定拉链表的时间粒度，比如说拉链表每天只取一个状态，也就是说如果一天有3个状态变更，我们只取最后一个状态，这种天粒度的表其实已经能解决大部分的问题了。

    另外，补充一下每日的用户更新表该怎么获取，据笔者的经验，有3种方式拿到或者间接拿到每日的用户增量，因为它比较重要，所以详细说明：

    1. 我们可以监听Mysql数据的变化，比如说用Canal，最后合并每日的变化，获取到最后的一个状态。
    2. 假设我们每天都会获得一份切片数据，我们可以通过取两天切片数据的不同来作为每日更新表，这种情况下我们可以对所有的字段先进行concat，再取md5，这样就ok了。
    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层user_update表过来就行，我们写一下每日的更新语句。现在我们假设我们已经已经初始化了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

    6、补充

    好了，我们分析了拉链表的原理、设计思路、并且在Hive环境下实现了一份拉链表，下面对拉链表做一些小的补充。

    拉链表和流水表

    流水表存放的是一个用户的变更记录，比如在一张流水表中，一天的数据中，会存放一个用户的每条修改记录，但是在拉链表中只有一条记录。

    这是拉链表设计时需要注意的一个粒度问题。我们当然也可以设置的粒度更小一些，一般按天就足够。

    查询性能

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

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

VPS购买请点击我

文章版权声明：除非注明，否则均为主机测评原创文章，转载或复制请以超链接形式并注明出处。

相关阅读

目录[+]