在数据仓库中拉链表的使用

使用场景：

在数据仓库中，拉链表可以用于处理维度表的历史时间变化
优点：使用拉链表的优势在于能够有效地追踪维度属性的历史变化

使用方法：

设计拉链表结构：在维度表中添加拉链表所需字段，如开始日期，结束日期，这些字段用来表示维度属性历史变化情况
初始化拉链表：将维度表的当前数据拷贝到拉链表中，将结束日期设定一个特定的未来日期，表示数据当前有效
新数据插入：当有新数据插入时，更新原始数据结束日期变化为变化前的日期，同时，新增的起始日期变为更新日期

举例说明

全量操作

目标：mysql->ods->dwd
背景：假设今天是2022年6月1号，处理2022年6月1号之前的数据
原始数据：

mysql 这是2022-06-01 的用户表t1
id	name	address	create_date	update_date
1003	张三	北京	2022-05-30	2022-05-30
1004	李四	上海	2022-05-01	2022-05-15
1005	王五	广州	2022-04-01	2022-04-26
1006	赵六	深圳	2022-06-01	2022-06-01

select *,'2022-5-31'  as dt from t1 where coalesce(update_date,create_date)<'2022-6-1'
然后把这个筛选结果用sqoop导入ods层

hive.ods.tb_user_ods 表t2
id	name	address	create_date	update_date	dt
1003	张三	北京	2022-05-30	2022-05-30	2022-05-31
1004	李四	上海	2022-05-01	2022-05-15	2022-05-31
1005	王五	广州	2022-04-01	2022-04-26	2022-05-31
导入dwd层

insert into t3 partition(start_date)
--在这里，通过partition(start_date)指定了按照start_date列进行分区。也就是说，新插入的数据将根据start_date的值来确定所属的分区。
select id,name,address,create_date,update_date,'9999-99-99' as end_date,coalesce(update_date,create_date) as start_date from t2

hive.dwd.tb_user_dwd表t3
id	name	address	create_date	update_date	start_date	end_date
1003	张三	北京	2022-05-30	2022-05-30	2022-05-30	9999-99-99
1004	李四	上海	2022-05-01	2022-05-15	2022-05-15	9999-99-99
1005	王五	广州	2022-04-01	2022-04-26	2022-04-26	9999-99-99

增量操作

目标：mysql->ods->dwd
背景：假设今天是2022年6月2号，处理2022年6月1号的数据
原始数据：

mysql 这是2022-06-02 的用户表
id	name	address	create_date	update_date
1003	张三	北京	2022-05-30	2022-05-30
1004	李四	苏州	2022-05-01	2022-06-01
1005	王五	广州	2022-04-01	2022-04-26
1006	赵六	深圳	2022-06-01	2022-06-01

查询上一天增量数据
select *,'2022-06-01' as dt from 用户表 where coalesce(update_date,create_date)='2022-06-01'

id	name	address	create_date	update_date	dt
1004	李四	苏州	2022-05-01	2022-06-01	2022-06-01
1006	赵六	深圳	2022-06-01	2022-06-01	2022-06-01
把这个结果用sqoop导入ods
hive.ods.tb_user_ods
--------------------	----	-------	-----------	-----------	----------
id	name	address	create_date	update_date	dt
1003	张三	北京	2022-05-30	2022-05-30	2022-05-31
1004	李四	上海	2022-05-01	2022-05-15	2022-05-31
1005	王五	广州	2022-04-01	2022-04-26	2022-05-31
1004	李四	苏州	2022-05-01	2022-06-01	2022-06-01
1006	赵六	深圳	2022-06-01	2022-06-01	2022-06-01
处理增量数据 t1

select id,name,address,create_date,update_date,'9999=99-99'as end_date,
coalesce(update_date,create_date) as start_date from tb_user_ods
where dt='2022-06-01'

处理旧拉链表

 select
    id,name,address,create_date,update_date,
    case
      when b.id is null then a.end_date -- 没有修改
      when b.id is not null and a.end_date!='9999-99-99' then a.end_date -- 修改的 无效的
      when b.id is not null and a.end_date='9999-99-99' then date_add('2022-06-01', -1) -- 修改的 有效的      
      end as end_date,
      a.start_date
    from 旧拉链表 a left join t1 b on a.id=b.id

在6月2号处理6月1号数据中，修改无效情况没有体现，下面用6月3号处理6月2号数据的拉链表

将两个表union

新 hive.dwd.tb_user_dwd
id	name	address	create_date	update_date	start_date	end_date
1003	张三	北京	2022-05-30	2022-05-30	2022-05-30	9999-99-99
1004	李四	上海	2022-05-01	2022-05-15	2022-05-15	2022-05-31
1005	王五	广州	2022-04-01	2022-04-26	2022-04-26	9999-99-99
1004	李四	苏州	2022-05-01	2022-06-01	2022-06-01	9999-99-99
1006	赵六	深圳	2022-06-01	2022-06-01	2022-06-01	9999-99-99

将两个结果放到临时表
清空拉链表数据
将临时表放到拉链表
清空临时表数据