专栏首页暴走大数据漫谈数据仓库之拉链表(原理、设计以及在Hive中的实现)

漫谈数据仓库之拉链表(原理、设计以及在Hive中的实现)

0x00 前言

本文将会谈一谈在数据仓库中拉链表相关的内容,包括它的原理、设计、以及在我们大数据场景下的实现方式。

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

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

0x01 什么是拉链表

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

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

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

拉链表的使用场景

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

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

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

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

为什么使用拉链表

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

方案一

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

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

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

方案二

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

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

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

拉链表

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

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

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

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

0x02 拉链表的设计和实现

如何设计一张拉链表

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

我们用电商网站的例子,现在以用户的拉链表来说明。

我们先看一下在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。基于这个前提,我们来实现拉链表。

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

  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层用户表过来就行,我们写一下每日的更新语句。

现在我们假设我们已经已经初始化了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

0x03 补充

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

拉链表和流水表

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

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

查询性能

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

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

0xFF 总结

我们在这篇文章里面详细地分享了一下和拉链表相关的知识点,但是仍然会有一会遗漏。欢迎交流。

在后面的使用中又有了一些心得,补充进来:

  1. 使用拉链表的时候可以不加t_end_date,即失效日期,但是加上之后,能优化很多查询。
  2. 可以加上当前行状态标识,能快速定位到当前状态。
  3. 在拉链表的设计中可以加一些内容,因为我们每天保存一个状态,如果我们在这个状态里面加一个字段,比如如当天修改次数,那么拉链表的作用就会更大。
文章分享自微信公众号:
大数据真好玩

本文参与 腾讯云自媒体分享计划 ,欢迎热爱写作的你一起参与!

如有侵权,请联系 yunjia_community@tencent.com 删除。
登录 后参与评论
0 条评论

相关文章

  • 拉链表是什么

    木东居士
  • 系列 | 漫谈数仓第四篇NO.4 『数据应用』(BI&amp;OLAP)

    ☞ 03.OLAP引擎 [ Kylin Druid Presto Impala Kudu ADB ES .. ]

    Spark学习技巧
  • 大数据实战【千亿级数仓】项目总结

    前段时间做过一个大数据离线数仓的项目,前后花了有好几周的时间。一共是6个阶段,想关注阶段细节的朋友可以查看?大数据实战项目这个专栏。

    大数据梦想家
  • 用户画像 | 标签数据存储之Hive真实应用

    小伙伴们大家好呀,趁着年假的几天时间,我写了一篇 Elacticsearch 从0到1的“长篇大作”,现在还在排版,相信很快就会与大家见面了!关...

    大数据梦想家
  • 干货 | 百万QPS,秒级延迟,携程基于实时流的大数据基础层建设

    纪成,携程数据开发总监,负责金融数据基础组件及平台开发、数仓建设与治理相关的工作。对大数据领域开源技术框架有浓厚兴趣。

    携程技术
  • 用户画像 | 标签数据存储之Hive真实应用

    本期内容主要介绍使用Hive作为数据仓库的应用场景时,相应的库表结构如何设计。

    Spark学习技巧
  • 基于Hive数据仓库的标签画像实战

    建立用户画像首先需要建立数据仓库,用于存储用户标签数据。Hive是基于Hadoop的数据仓库工具,依赖于HDFS存储数据,提供的SQL语言可以查询存储在HDFS...

    王知无-import_bigdata
  • 什么样的大数据平台架构,才是最适合你的?

    技术最终为业务服务,没必要一定要追求先进性,各个企业应根据自己的实际情况去选择自己的技术路径。   它不一定具有通用性,但从一定程度讲,这个架构...

    BestSDK
  • 通俗易懂讲数据仓库之【缓慢变化维】

    本篇博客,博主为大家带来的是关于数据仓库中一个非常重要的知识点缓慢变化维的讲解!

    大数据梦想家
  • 2021数仓面试笔记

      并行执行模式   推测执行模式   数据倾斜时开启负载均衡模式   map缓冲区大小   溢写磁盘百分比   开启combanier提前预聚合 ...

    挽风
  • 大数据系列思考题----[持续更新]

    个人理解: hdfs启动流程 hdfs是Hadoop Distribute File System 的简称,即分布式文件系统,用于存储海量数据. hdfs...

    ChinaManor
  • 闲聊数据库和数据仓库的区别

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

    木东居士
  • 一种通用的数据仓库分层方法

    数据分层是数据仓库设计中十分重要的一个环节,优秀的分层设计能够让整个数据体系更易理解和使用。而目前网络中大部分可以被检索到相关文章只是简单地提及数据分层的设计,...

    木东居士
  • 大数据时代的争议:Spark 能替代 Hive 吗?

    学习 Spark 的面试者普遍认为 Spark 必然会替代 Hive 成为新的一代大数据仓库标准。

    崔庆才
  • 程序员在大数据面试时的争议:Spark能替代Hive?

    本文作者:曾就职传统通讯运营商,负责BI项目的开发;目前转型互联网公司,就职于某厂负责相关的大数据仓库建设工作。

    养码场
  • 最新数仓面试题_知行教育数仓项目

    包含: •项目做了什么 我们的教育大数据分析平台项目就是将大数据技术应用于教育行业,为企业经营提供数据支撑

    ChinaManor
  • 数据仓库问题总结

    1.在关系模型中,实现“关系中不允许出现相同的元组”的约束是通过 “主键” 完成的。

    曼路

扫码关注云+社区

领取腾讯云代金券