关于数据仓库:拉链表的原理及简单实现

​ 数新网络官网已全新上线，欢送点击拜访

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_his
SELECT 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_id
UNION 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;

而后就能够失去最终的后果了

数据如下：

​

编辑

最初就能够失去咱们文章结尾提到的表格了。

​