数仓项目-拉链表技术精解

最新推荐文章于 2023-07-28 11:15:52 发布

yiluohan0307

最新推荐文章于 2023-07-28 11:15:52 发布

阅读量2.9k

点赞数

分类专栏： Linux系统文章标签： hive 拉链表缓慢变化维

本文链接：https://blog.csdn.net/yiluohan0307/article/details/121671694

版权

Linux系统专栏收录该内容

30 篇文章 0 订阅

订阅专栏

数仓项目-拉链表技术精解

一、拉链表的使用场景

在数据仓库的模型设计的过程中，通常我们会碰到那种非常大的业务基础信息表，如用户表；

假如一个用户表有10亿条记录，50个列，就算使用orc压缩，但张表的存储也会超过100G，如果同步到Hive中按HDFS的默认备份，那就是300G，这样对磁盘的消耗也是非常大的。

假设该表的某些字段在业务端会产生update操作，但是每次update的字段就那么1到2个，其它字段不变，那么这些变化不频繁的维度字段被称为缓慢渐变维，而且相同id的变update频率很小，每天update的记录只占全表记录的很小一部分，如1/20000。

这种场景的特点：

表中的部分字段会被update，例如：
- 用户的手机号码，用户地址，生日信息等等;
需要查看某一个时间点或者时间段的历史快照信息，例如：
- 查看某一个用户在历史某一时间点的手机号码
- 查看某一个用户在过去某一段时间内，更新过几次等等
变化的比例和频率不是很大，例如：
- 总共有1000万的用户，每天新增和发生变化的有10万左右

那么此时有3种同步方案：

每日全量覆盖的方式，特点：简单易操作，但是不支持保留历史数据；
每日全量切片的方式，特点：简单易操作，且能保证历史，但是会导致数仓存储大量的重复冗余数据，占用大量磁盘空间；
拉链表，特点：拉链表能保证每条记录的生命周期的start-date和end-date，既能保证历史数据，同时也优化了冗余存储。

二、一个简单的拉链表示例

假如业务那边有一个users表，表结构如下

2021-12-01的数据

用户id	注册日期	手机号码
001	2021-12-01	11111111
002	2021-12-01	22222222
003	2021-12-01	33333333

2021-12-02的数据

003 的电话号码作了修改，从 33333333 变成了 32323232 ，同时新增了一个用户 004

用户id	注册日期	手机号码
001	2021-12-01	11111111
002	2021-12-01	22222222
003	2021-12-01	32323232
004	2021-12-02	44444444

2020-07-03的数据

002 的电话号码作了修改，从 22222222 变成了 21212121 ，同时新增了一个用户 005

用户id	注册日期	手机号码
001	2021-12-01	11111111
002	2021-12-01	21212121
003	2021-12-01	32323232
004	2021-12-02	44444444
005	2021-12-03	55555555

那么假如我们按照T+1的方式同步数据，那么拉链表在hive中存储样式应该如下：

start_date 代表记录的生效起始时间，end_date 代表记录失效时间

假如我们需要查询 002在 cal_date 的对应的有效记录，可以使用 start_date <= cal_date and end_date > cal_date 进行限定。

如果我们需要查询当前的有效数据，那么我们只需要按照 where end_date = '9999-12-31' 来进行限定就okay了。

最后,我们希望得到的数据：

用户id	注册日期	手机号码	start_date	end_date
001	2021-12-01	11111111	2021-12-01	9999-12-31
002	2021-12-01	22222222	2021-12-01	2021-12-03
003	2021-12-01	33333333	2021-12-01	2021-12-02
003	2021-12-01	32323232	2021-12-02	9999-12-31
004	2021-12-02	44444444	2021-12-02	9999-12-31
002	2021-12-01	21212121	2021-12-03	9999-12-03
005	2021-12-03	55555555	2021-12-03	9999-12-31

三、拉链表存储历史快照代码实现方案

操作步骤：

在原有dw层表上，添加额外的两列
- 生效日期（start_date）
- 失效日期（end_date）
只同步当天修改的数据到ods层
拉链表算法实现
- 编写SQL处理dw层历史数据，重新计算之前的dw_end_date
- 编写SQL处理当天最新的数据（新添加的数据和修改过的数据）
拉链表的数据为：当天历史数据 UNION ALL 最新的数据

四、在Hive中实现拉链表

⚠️注意：在向大数据转型的趋势下，很多企业将Hive作为数据仓库的首选，但是Hive数据是基于HDFS的文件，只支持delete和insert操作，不支持update。

users
users_inc
users_his

整体思路：dw的新数据 = dw的旧数据+新增数据

1、数据准备

create database ods;
create database dw;
-- ods的原始切片表
CREATE TABLE `ods`.`users` (
reg_date STRING COMMENT '注册日期',
  user_id STRING COMMENT '用户编号',
  mobile 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的更新表，用来存储新增记录
CREATE TABLE `ods`.`users_inc` (
  reg_date STRING COMMENT '注册日期',
  user_id STRING COMMENT '用户编号',
  mobile STRING COMMENT '手机号码'
)
COMMENT '每日用户资料更新表'
PARTITIONED BY (dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS ORC;
--LOCATION '/ods/user_inc';


--dw的拉链表
CREATE TABLE `dw`.`users_his` (
  reg_date STRING COMMENT '用户编号',
  user_id STRING COMMENT '用户编号',
  mobile STRING COMMENT '手机号码',
  start_date STRING,
  end_date STRING
)
COMMENT '用户资料拉链表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS ORC;
--LOCATION '/dw/user_his';

insert into `ods`.`users` PARTITION (dt = '2021-12-01') values('2021-12-01',001,'11111111'),('2021-12-01',002,'22222222'),('2021-12-01',003,'33333333');

2、全量更新，users_his 表初始化

首先全量更新，我们先到 2021-12-01 为止的数据。

初始化，先把 2021-12-01 的数据初始化进去

INSERT overwrite TABLE `ods`.`users_inc` PARTITION (dt = '2021-12-01')
SELECT reg_date,user_id,mobile
FROM `ods`.`users`
WHERE reg_date < '2021-12-02' and reg_date <'2021-12-02';

刷到 dw 中

INSERT overwrite TABLE `dw`.`users_his`
SELECT reg_date,user_id,mobile,
reg_date AS start_date,
'9999-12-31' AS end_date
FROM `ods`.`users_inc`
WHERE dt = '2021-12-01';

dw.users_his 中的数据如下：

2021-12-01      001     11111111        2021-12-01      9999-12-31
2021-12-01      002     22222222        2021-12-01      9999-12-31
2021-12-01      003     33333333        2021-12-01      9999-12-31

3、剩余需要进行增量更新

--003的电话号码作了修改，从33333333变成了32323232，同时新增了一个用户004
truncate table `ods`.`users_inc`;
insert into `ods`.`users_inc` PARTITION (dt = '2021-12-02') values('2021-12-01',003,'32323232'),('2021-12-02',004,'44444444');

users_inc 中的增量数据

2021-12-02      002     21212121        2021-12-02
2021-12-02      003     32323232        2021-12-02
2021-12-02      004     44444444        2021-12-02

先放到增量表中，然后进行关联到一张临时表中，在插入到新表中

--此步，可以先创建一个users_his_tmp，然后再同步到users_his。为了方便直接同步。
--DROP TABLE IF EXISTS `dw`.`users_his_tmp`;
--CREATE TABLE `dw`.`users_his_tmp` AS 拉链表代码
--INSERT overwrite TABLE `dw`.`users_his` SELECT *  FROM `dw`.`users_his_tmp`;
INSERT overwrite TABLE `dw`.`users_his`
SELECT 
 * 
FROM(
 --将修改的数据进行生命周期的修改，
 SELECT
  a.reg_date,
  a.user_id,
  a.mobile,
  a.start_date,
  case 
   when a.end_date = '9999-12-31' and b.user_id is not null then '2021-12-02'
   else a.end_date
   end as end_date  --说明通过user_id关联上了，在user_inc表中也算今日的新增数据
 FROM `dw`.`users_his`  a
 LEFT JOIN `ods`.`users_inc` b
 ON a.user_id = b.user_id
 UNION 
  --将新增的数据直接union进来
 SELECT 
  reg_date,
  user_id,
  mobile,
  '2021-12-02' as start_date,
  '9999-12-31' as end_date
 FROM `ods`.`users_inc`
) x;

dw.users_his 中的数据如下：

2021-12-01      001     11111111        2021-12-01      9999-12-31
2021-12-01      002     22222222        2021-12-01      9999-12-31
2021-12-01      003     33333333        2021-12-01      2021-12-02
2021-12-02      003     32323232        2021-12-02      9999-12-31
2021-12-02      004     44444444        2021-12-02      9999-12-31

4、按照上面步骤3，把 2021-12-03 号的数据更新进去，最后结果如下

--002的电话号码作了修改，从22222222变成了21212121，同时新增了一个用户005
truncate table `ods`.`users_inc`;
insert into `ods`.`users_inc` PARTITION (dt = '2021-12-03') values('2021-12-01',002,'21212121'),('2021-12-03',005,'55555555');

INSERT overwrite TABLE `dw`.`users_his`
SELECT 
 * 
FROM(
 --将修改的数据进行生命周期的修改，
 SELECT
  a.reg_date,
  a.user_id,
  a.mobile,
  a.start_date,
  case 
   when a.end_date = '9999-12-31' and b.user_id is not null then '2021-12-03'
   else a.end_date
   end as end_date  --说明通过user_id关联上了，在user_inc表中也算今日的新增数据
 FROM `dw`.`users_his`  a
 LEFT JOIN `ods`.`users_inc` b
 ON a.user_id = b.user_id
 UNION 
  --将新增的数据直接union进来
 SELECT 
  reg_date,
  user_id,
  mobile,
  '2021-12-03' as start_date,
  '9999-12-31' as end_date
 FROM `ods`.`users_inc`
) x;

dw.users_his 中的数据如下：

2021-12-01      001     11111111        2021-12-01      9999-12-31
2021-12-01      002     21212121        2021-12-03      9999-12-31
2021-12-01      002     22222222        2021-12-01      2021-12-03
2021-12-01      003     32323232        2021-12-02      9999-12-31
2021-12-01      003     33333333        2021-12-01      2021-12-02
2021-12-02      004     44444444        2021-12-02      9999-12-31
2021-12-03      005     55555555        2021-12-03      9999-12-31

查询当前所有有效的记录

select * from users_his where end_date='9999-12-31';

查询 2021-12-01 的历史快照

select * from users_his where start_date <= '2021-12-01' and end_date >= '2021-12-01';

五、用MySQL来模拟hive的拉链表

由于MySQL中不支持Hive中的 insert overwrite ，所以用一张临时表进行代替，SQL如下：

create table if not exists user(reg_date  varchar(24),user_id int ,mobile varchar(24))  engine = innodb  default charset = utf8mb4;

create table if not exists user_update(reg_date  varchar(24),user_id int ,mobile varchar(24))  engine = innodb  default charset = utf8mb4;

create table if not exists user_his(reg_date  varchar(24),user_id int ,mobile varchar(24),start_date varchar(24) ,end_date varchar(24))  engine = innodb  default charset = utf8mb4;


######################2021-12-01###################

truncate table user;
truncate table user_update;
truncate table user_his;
insert into user values('2021-12-01',001,'11111111'),('2021-12-01',002,'22222222'),('2021-12-01',003,'33333333');
insert into user_update select * from user;

truncate table user_his;
insert into user_his
select 
*
from (
	select 
		a.reg_date,
		a.user_id,
		a.mobile,	
		a.start_date as start_date,
		if(b.user_id is not null and a.end_date ='9999-12-31','2021-12-01',a.end_date)
	from user_his a
	left join user_update b
	on a.user_id = b.user_id
	union 
	select 
		reg_date,
		user_id,
		mobile,
		'2021-12-01' as start_date,
		'9999-12-31' as end_date  
	from user_update
) tmp ;


######################2021-12-02###################
#模拟新增、修改数据，在新增表中，修改与新增都属于新增

delete from user_update where user_id= 001;
update  user_update set mobile = '21212121' where user_id = 002;
update  user_update set mobile = '32323232' where user_id = 003;
insert into user_update values('2021-12-02',004,'44444444');

drop table if exists user_his_temp;
create table if not exists user_his_temp as select * from user_his;

truncate table user_his;
insert into user_his
select 
	*
from (
	select 
		a.reg_date,
		a.user_id,
		a.mobile,	
		a.start_date as start_date,
		if(b.user_id is not null and a.end_date ='9999-12-31','2021-12-02',a.end_date)
	from user_his_temp a
	left join user_update b
	on a.user_id = b.user_id
	union 
	select 
		reg_date,
		user_id,
		mobile,
		'2021-12-02' as start_date,
		'9999-12-31' as end_date  
	from user_update
) tmp ;

参考

yiluohan0307

关注

0
点赞
踩
15

收藏

觉得还不错? 一键收藏
打赏
0
评论
数仓项目-拉链表技术精解

在数据仓库的模型设计的过程中，通常我们会碰到那种非常大的业务基础信息表，如用户表；假如一个用户表有10亿条记录，50个列，就算使用orc压缩，但张表的存储也会超过100G，如果同步到Hive中按HDFS的默认备份，那就是300G，这样对磁盘的消耗也是非常大的。假设该表的某些字段在业务端会产生update操作，但是每次update的字段就那么1到2个，其它字段不变，那么这些变化不频繁的维度字段被称为缓慢渐变维，而且相同id的变update频率很小，每天update的记录只占全表记录的很小一部分。
复制链接

扫一扫