数据仓库-拉链表

这篇具有很好参考价值的文章主要介绍了数据仓库-拉链表。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

在数据仓库中制作拉链表,可以按照以下步骤进行:

  1. 确定需求:首先明确需要使用拉链表的场景和需求。例如,可能需要记录历史数据的变化,以便进行时间序列分析等。
  2. 设计表结构:在数据仓库中,拉链表通常由两个表组成:当前表和历史表。当前表存储最新的数据,历史表存储过去的数据。两个表的结构应该相同,包含相同的字段。
  3. 建立当前表:创建一个当前表,用于存储最新的数据。该表应包含需要记录的所有字段,以及一个表示有效期的字段。有效期字段用于标识该条数据在何时失效。
  4. 建立历史表:创建一个历史表,用于存储过去的数据。该表的结构与当前表相同,但还应包含一个表示生效日期的字段。生效日期字段用于标识该条数据在何时生效。
  5. 数据插入与更新:当有新数据插入时,将其插入到当前表中,并设置相应的有效期。同时,将过期的数据从当前表中移动到历史表中,并更新它们的生效日期和有效期。
  6. 数据查询:在查询数据时,同时查询当前表和历史表。根据查询的时间范围,确定在哪个表中查找数据。
  7. 定期维护:定期对历史表进行维护,例如删除过期的数据、优化索引等,以确保拉链表的性能和存储空间的有效利用。

需要注意的是,拉链表在数据仓库中的应用可能因具体需求和场景而有所不同。因此,在实际制作拉链表时,建议根据数据仓库的设计规范、业务需求和数据特点进行调整和优化。同时,也要确保数据的完整性、一致性和准确性。

参考:漫谈数据仓库之拉链表(原理、设计以及在Hive中的实现)_拉链表实现方式_小强签名设计的博客-CSDN博客

二、什么是拉链表

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

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

数据仓库-拉链表,数据仓库

1. 拉链表的使用场景

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

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

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

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

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

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

三、拉链表的设计和实现
1. 如何设计一张拉链表

我们先看一下在 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’。(此处要好好理解,是拉链表比较重要的一块。 where条件筛选当前有效数据,开始日期小于等于当前日期并且结束日期大于等于当前日期,则为有效。
2. 在Hive中实现拉链表

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

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

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

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

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

  1. 我们可以监听 Mysql 数据的变化,比如说用 Canal,最后合并每日的变化,获取到最后的一个状态。
  2. 假设我们每天都会获得一份切片数据,我们可以通过取两天切片数据的不同来作为每日更新表,这种情况下我们可以对所有的字段先进行 concat,再取 md5,这样就 ok 了。
  3. 流水表!有每日的变更流水表。
  4. 通过 etl 工具对操作型数据库按照时间字段增量抽取到 ods 或者数据仓库(每天抽取前一天的数据),形成每天的增量数据(实际中使用最多的情形)
(1)拉链表实现方式一

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

现在我们假设我们已经已经初始化了 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
(2)拉链表实现方式二

操作型数据库的用户表结构:

CREATE EXTERNAL TABLE ods.user ( 
  user_num STRING COMMENT '用户编号', 
  mobile STRING COMMENT '手机号码', 
  reg_date STRING COMMENT '注册日期' ,
  last_modify_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';

每天增量抽取的用户表结构和抽取条件:

  1)表结构和上面的表结构保持一致,我们取表名为 ods.user_update

  2)增量抽取条件:select * from ods.user where last_modify_date = '$date'

拉链表:

  现在我们创建一张拉链表:

CREATE EXTERNAL TABLE dws.user_his ( 
  user_num STRING COMMENT '用户编号', 
  mobile STRING COMMENT '手机号码', 
  reg_date STRING COMMENT '用户编号',
  last_modify_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:

1)

merge into dws.user_his tar 
using
(
  select user_num,mobile from ods.user_update 
) sou on tar.user_num=sou.user_num 
and tar.mobile=sou.mobile 
and tar.t_start_date < '$date' 
and tar.t_end_date >  '$date' 
when matched then 
update set tar.t_end_date='9999-12-31';

按照主键筛选,在 dws.user_his 表中出现过的并且现在为有效数据的,全部更新为闭链数据。

INSERT  TABLE dws.user_his 
SELECT 
  C.user_num, 
        C.mobile, 
        C.reg_date, 
         c.last_modify_date
         '2017-01-02' AS t_start_time, 
         '9999-12-31' AS t_end_time 
  FROM ods.user_update AS C;

比如我们要1月2号的数据,取出来的数据为 select from user where t_start_date <= ‘2017-01-02’ and t_end_date >= ‘2017-01-02’

数据仓库-拉链表,数据仓库

与1月2号数据完全一致。

1. 拉链表和流水表

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

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

2. 查询性能

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

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

  可以采用备份的方案,保证无误和可行。(保存增量数据,并对 t_dw_orders_his 表每个月备份一次全量数据。如需回滚,最多重跑30天数据即可)

六、总结

  在后面的使用中又有了一些心得,补充进来:文章来源地址https://www.toymoban.com/news/detail-735629.html

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

到了这里,关于数据仓库-拉链表的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

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

    本文将会谈一谈在数据仓库中拉链表相关的内容,包括它的原理、设计、以及在我们大数据场景下的实现方式。 全文由下面几个部分组成: 先分享一下拉链表的用途、什么是拉链表。 通过一些小的使用场景来对拉链表做近一步的阐释,以及拉链表和常用的切片表的区别。

    2024年04月12日
    浏览(37)
  • 详解数据仓库之拉链表(原理、设计以及在Hive中的实现)

    最近发现一本好书,读完感觉讲的非常好,首先安利给大家,国内第一本系统讲解数据血缘的书!点赞!近几天也会安排朋友圈点赞赠书活动(ง•̀_•́)ง 0x00 前言 本文将会谈一谈在数据仓库中拉链表相关的内容,包括它的原理、设计、以及在我们大数据场景下的实现方式

    2024年02月05日
    浏览(54)
  • 【大数据hive】hive 拉链表设计与实现

    目录 一、前言 二、拉链表业务背景 2.1 数据同步引发的问题 2.1.1 解决方案1

    2024年02月09日
    浏览(35)
  • 通俗易懂:什么是拉链表

    拉链表是数据仓库中一种重要的模型,相信很多数据工作者都接触过,面试也是经常考察的点。 但是很多人第一次接触“拉链表”这个词,难免会产生疑惑:拉链表是什么? 按照度娘的解释:“拉链表是一种针对数据仓库设计中表存储数据的方式而定义的数据模型,它有点

    2024年02月08日
    浏览(39)
  • Hive---拉链表

    拉链表是一种数据模型,主要是针对数据仓库设计中表存储数据的方式而定义的,顾名思义,所谓拉链,就是记录历史。记录一个事物从开始,一直到当前状态的所有变化的信息。拉链表可以避免按每一天存储所有记录造成的海量存储问题,同时也是处理缓慢变化数据(SCD

    2024年02月10日
    浏览(71)
  • 拉链表概述

    数据仓库中有一种数据存储模型,用于维护历史状态以及最新状态,反映某一Key的历史变化演进过程,该种模型叫做缓慢变化维,简称SCD(Slowly Changing Dimensions),通常所说的拉链表其实属于缓慢变化维的一种实现方式,拉链表通过增加一列维度信息维护拉链状态,他们之间

    2024年02月12日
    浏览(28)
  • 拉链表详解

    目录 一、拉链表概念 二、拉链表对应的业务需求 三、代码实现 3.1 数据初始化:  3.2 创建ods层增量表:  3.3 创建dwd层拉链表  3.4 数据更新 ,将数据日期为2023-3-4的日期添加到拉链表中  3.4.1 先追加数据到ods层表 3.4.2 更新dwd层表数据         拉链表是一种数据模型,主要

    2024年01月17日
    浏览(31)
  • Hive 拉链表详解及实例

    版本迭代:hive 0.14 slowly changing dimension = hive 2.6.0 merge 事务管理 原来采用分区表,用户分区存储历史增量数据,缺点是重复数据太多 定义:数仓用于解决持续增长且存在一定时间时间范围内重复的数据 存储:创建拉链表时使用 列式存储ORC : 不能使用load加载数据 压缩比高

    2024年01月23日
    浏览(32)
  • Flink实时数仓同步:拉链表实战详解

    在大数据领域,业务数据通常最初存储在关系型数据库,例如MySQL。然而,为了满足日常分析和报表等需求,大数据平台会采用多种不同的存储方式来容纳这些业务数据。这些存储方式包括离线仓库、实时仓库等,根据不同的业务需求和数据特性进行选择。 举例来说,假设业

    2024年01月20日
    浏览(56)
  • doris - 数仓 拉链表 按天全量打宽表性能优化

    1、 业务历史数据可以变更 2、拉链表按天打宽 3、拉链表模型分区字段设计不合理,通用的过滤字段没有作为分区分桶字段 4、拉链表表数据量略大、模型数据分区不合理和服务器资源限制,计算任务执行超时【3-4年,用户数:132W】 5、基于拉链表打宽后的天表行转列【 最多

    2024年02月14日
    浏览(54)

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

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包