hive库存拉链表的实现过程 oracle拉链表原理

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

user_id	name	level	start_time	end_time
1	甲	A	2020-05-01	9999-12-31
2	乙	B	2020-05-01	2020-05-02
2	乙	A	2020-05-02	9999-12-31
3	丙	B	2020-05-02	9999-12-31

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

拉链表的使用场景

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

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

拉链表的实现

• 1、创建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

数据如下

user_id	name	level	time
1	甲	A	2020-05-01
2	乙	B	2020-05-01

• 2、创建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

数据如下

user_id	name	level	time
1	甲	A	2020-05-02
2	乙	A	2020-05-02
3	丙	B	2020-05-02

• 3、创建历史表存储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;

数据如下

user_id	name	level	start_time	end_time
1	甲	A	2020-05-01	9999-12-31
2	乙	B	2020-05-01	9999-12-31

• 4、创建变动表，找到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_update
SELECT * 
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_id
WHERE a.level!=b.level
OR b.level IS NULL 

)t;
SELECT * from level_update;

数据如下

user_id	name	level	time
3	丙	B	2020-05-02
2	乙	A	2020-05-02

• 5最终结果

• level_his为基础表
• 把变动表的数据插入到level_his里面，并修改时间

INSERT OVERWRITE TABLE level_his
SELECT  *
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;

就可以得到最终的结果了

如下：

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