对于缓慢变化维-拉链表的实践

如果编辑有什么问题或有什么需要交流的可以留言。

公司有张用户表,全表千万量级，增量10w以内，且用户信息会进行更新
用户行为日志表关联用户表补全用户属性，由于用户信息会进行更新，因此关联时要关联当日的用户表
目前方案是每天全量抽取，分区存储全量快照 即每天一个全量快照
改善方案：增量同步+(全量快照or拉链表)

- mysql数据库中用户表 其中 
    status_x 字段可能会缓慢变化
    update_time 数据插入或者更新 这个字段都会修改  即数据修改的时间 有索引，能够进行抽取
create table user_mysql(
    uid primary key ,
    status_1
    status_2
    status_n
    update_time index default currnettime or update 
);

- 数仓中增量表
create table user_inc(
    uid ,
    status_1
    status_2
    status_n
    update_time 
)partitioned by (pt);

增量抽取 每天 0:30 执行
insert overwrite table user_inc partition(pt = pt)
select 
* 
from user_mysql 
where update_time between 'pt 00:00:00' and 'pt 23:59:59' ;


- 数仓中拉链表
create table user_zip(
    uid,
    status_1
    status_2
    status_n
    update_time
    start_date '生效日期'
)partitioned by (end_date  comment '失效日期')
增加两个字段 start_date end_date
end_date=20990101表示当前生效数据

- 拉链操作 用到了user_zip 20990101分区 及 user_inc的pt分区数据
with t_zip as (
    select 
    * 
    from user_zip
    where end_date = '20990101'
),
t_inc as (
    select 
    * 
    from user_inc 
    where pt = 'pt'  
)

insert overwrite table user_zip partition(end_date) 
select
t1.`(end_date)?+.+`, -- 取t1表除end_date外其他字段
if(t2.id is null,t1.end_date,'pt') as end_date -- 只修改这个字段
from  user_zip t1 left join user_inc t2 on t1.id = t2.id
union all 
select
`(pt)?+.+`,
'pt' as start_date,  -- 所有增量抽取的start_date=pt end_date=20990101
'20990101' as end_date 
from user_inc
;


-- 在使用时，需要遍历当前日期及后面所有分区的数据 效率不高
比如看6.18的数据 要看20200618到20990101所有分区数据 然后再根据start_date进行过滤
explain
select 
*
from user_zip
where end_date > '20220618' and start_date <= '20220618'
比如看4.15的数据 要看20200415到20990101所有分区数据 然后再根据start_date进行过滤
explain
select 
*
from user_zip
where end_date > '20220415' and start_date <= '20220415'

如果修改分区键 即将end_date作为分区键 改为 partitioned by (end_date,start_date)  这样取数据会少点 但分区数又太多了


采用全量快照 每天存一个千万级别的快照 使用效率最高；
如果拉链 end_date作为分区键 20990101分区是正在生效的全量快照 其他分区基本每天10w以内数据 但使用效率不高
如果拉链 end_date,start_date作为联合分区键 具体没做 但能想到分区数会很多但取数会变少 但效率和存储都会降低很多


-- 结论
-- 1.使用增量同步+全量快照的方式 节省同步同时 用存储换计算
-- 2.后续如果数据量太大了 再改成拉链表(有每日快照的话 还是能改的)


再说一个拉链表的回退问题
比如今天告知数据出了问题 需要重跑 拉链表由于依赖上个周期的任务 需要先回退 
-- 拉链表的回退 
-- 只有 当日分区和生效分区 这两个分区数据会受到影响
-- 当日分区 ：将失效日期改成20990101
-- 20990101：将start_date = '${pt}' 的 全部删除 
insert overwrite table user_zip partition (end_date)
select
`(pt)?+.+`,
'20990101'
from user_zip 
where end_date = 'pt'
union ALL 
select 
*
from user_zip 
where end_date = '20990101' and start_date <> '${pt}';  

alter table user_zip drop partition (end_date = '${pt}');