写点什么

拉链表的原理及简单实现

  • 2023-06-29
    浙江
  • 本文字数:1617 字

    阅读完需:约 5 分钟

拉链表的原理及简单实现

 数新网络官网已全新上线,欢迎点击访问

www.datacyber.com 数新网络_让每个人享受数据的价值


1 什么是拉链表


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



编辑

上面就是一个简单的拉链表,记录了每个用户随着时间的变化其等级的变动情况。


2 拉链表的使用场景


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

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

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

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

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


3 拉链表的实现


创建 2020-5-1 的数据 

CREATE TABLE IF NOT EXISTS link_first(    user_id BIGINT     ,name  STRING   ,level   STRING    ,time  STRING    )COMMENT "link_first";insert into link_first values (1,'甲','A','2020-05-01'),(2,'乙','B','2020-05-01');SELECT * from link_first
复制代码

数据如下:



编辑

创建 2020-5-2 的数据 

CREATE TABLE IF NOT EXISTS link_second(   user_id BIGINT    ,name  STRING  ,level   STRING   ,time  STRING    )COMMENT "link_second";insert into link_second values (1,'甲','A','2020-05-02'),(2,'乙','A','2020-05-02'),(3,'丙','B','2020-05-02');SELECT * from link_second
复制代码

数据如下:



编辑

创建历史表存储 5 月 1 日数据,并对格式进行整理 

CREATE TABLE IF NOT EXISTS level_his(    user_id BIGINT     ,name  STRING   ,level   STRING    ,start_time  STRING     ,end_time  STRING  )COMMENT "level_his";INSERT OVERWRITE TABLE level_hisSELECT *FROM (    SELECT DISTINCT(user_id),name,level,'2020-05-01' as start_time,'9999-12-31' as end_time    FROM link_first    --WHERE id !='')t SELECT * from level_his;
复制代码

数据如下:



编辑

创建变动表,找到 5 月 2 日表里面变动和新增的 

CREATE TABLE IF NOT EXISTS level_update(    user_id BIGINT    ,name  STRING   ,level   STRING    ,time  STRING    )COMMENT 'level_update';INSERT OVERWRITE TABLE level_updateSELECT * FROM (SELECT a.*FROM     (SELECT DISTINCT(user_id),name,level,time FROM link_second )a    LEFT OUTER JOIN     (SELECT DISTINCT(user_id),name,level,time  FROM link_first )b ON     a.user_id = b.user_idWHERE a.level!=b.levelOR b.level IS NULL )t;SELECT * from level_update;
复制代码

数据如下:



最终结果 

  • level_his 为基础表

  • 把变动表的数据插入到 level_his 里面,并修改时间

INSERT OVERWRITE TABLE level_hisSELECT  *FROM(    SELECT a.user_id     , a.name    , a.level    , a.start_time    ,(CASE     WHEN a.end_time='9999-12-31' AND b.user_id IS NOT NULL THEN '2020-05-02' --在变动表中出现的,需要把时间改为5月2日(如果是实时表,就是最新时间)        ELSE a.end_time         END) AS  end_time    FROM level_his a    LEFT JOIN  level_update b  ON a.user_id = b.user_idUNION SELECT  c.user_id        ,c.name        ,c.level        ,'2020-05-02' as start_time        ,'9999-12-31' as end_time --变动表里面的数据需要把start_time改成5月2日(如果是实时表,就是最新时间);end_time改成'9999-12-31'FROM level_update  c )t;SELECT * from level_his ORDER BY user_id;
复制代码

然后就可以得到最终的结果了

数据如下:



最后就可以得到我们文章开头提到的表格了。


发布于: 刚刚阅读数: 5
用户头像

云数据智能操作系统领导者 2022-12-05 加入

浙江数新网络有限公司是一家拥抱开源,专注于云数据平台的大数据服务商,致力于结合全球云数仓先进理念,打造适合中国落地路径的云数仓体系。

评论

发布
暂无评论
拉链表的原理及简单实现_大数据_数新网络官方账号_InfoQ写作社区