最近在做一个淘宝店铺数据的时候又重新疏通了一遍拉链表,我认为拉链表的使用是很完美的解决了增量数据与更新数据的问题。
我将数据分为三类:1、全量数据:是最好处理的数据,全部加载即可
2、增量数据:也不是太麻烦,筛选进行添加即可
3、有增量有修改的数据:这个是比较麻烦点的,看下文我的理解。
--- 全量数据
1、将数据加载到MySQL数据库中,在使用sqoop加载到ods层,再将ods层的数据insert into 到dw层,完成全量数据的加载
---- 增量数据
1、也是首先将数据加载到MySQL数据库中,然后按条件将新增的数据通过sqoop加载到ods层
--筛选条件
select id, name, address, create_date, update_date, '2022-09-01' as dt from tb_user where date(coalesce(update_date, create_date))='2022-09-01'
--开始导入
sqoop import \
--connect jdbc:mysql://hadoop01:3306/db_1_mysql \
--username root \
--password 123456 \
--query "select id, name, address, create_date, update_date, '2022-09-01' as dt from tb_user where date(coalesce(update_date, create_date))='2022-09-01' and \$CONDITIONS" \
--hcatalog-database db_ods \
--hcatalog-table tb_user_ods \
--fields-terminated-by '\t' \
-m 1
使用sqoop导入到ods层:
2、ods层导入时候新增了一个操作日期,即:dt,这一个字段代表了哪一天的新增数据 接下来就是将新增到ods层的数据导入到dw层,这时候会对end_date做修改
1、原表 t1 和 新增数据 t2 关联不上,此时t2.id is null这些数据的end_date记为 '9999-99-99'
2、原表 t1 和 新增数据 t2 关联的上且原表 t2.end_date = '9999-99-99',此时的数据为最新的历史数据,只需要把历史数据中的end_date 改为 前一天数据 (例:今天是9-4,想要处理昨天的历史数据,因此前天结束的历史数据(前提是关联的上且end_date为默认值'9999-99-99')将结束日期记为-1 day)
3、原表 t1 和 新增数据 t2 关联的上但原表t2.date_date != '9999-99-99',即:原表数据发生过修改,此时表中的end_date记为历史的修改数据,即:t2.end_date
with t2 as (
select * from ods.tb_user_ods where dt='2022-09-01'
)
insert into 拉链表的临时表 partition(start_date)
select
id, name, address, create_date, update_date,
case
-- id关联不上
when t2.id is null then t1.end_date
-- id关联的上但发生了历史数据的修改
when t2.id is not null and t1.end_date!='9999-99-99' then t1.end_date
-- id关联的上且没有发生历史数据的修改
when t2.id is not null and t1.end_date='9999-99-99' then date_add('2021-0-01', -1)
end as end_date,
start_date
from 旧拉链表 t1
left join t2 on t1.id=t2.id
union
select
id, name, address, create_date, update_date,
'9999-99-99' as end_date,
coa..(update_date, create_date) as start_date
from t2
3、将原始数据维护好了之后,与新增数据进行 union ,并将数据插入到临时表中(因为hive不支持数据的修改操作,临时表和正式表的结构完全相同)
4、将数据插入到了临时表中之后,将正式表删除(也可truncate,但是删除操作更安全),删除之后再新建(保证了表中无数据)
5、将临时表中的数据导入到正式表中,完成数据增加
6、再将临时表删除,再新建(保证清除了临时表中的内容)
拉链表_分析:https://kdocs.cn/l/cvIWXd9vtKgP