日级别拉链表存储方案

发表于 更新于 分类于 阅读次数： Valine：

背景

当前数仓表的生命周期策略数据生命周期管理规范，除个别表外，无条件对所有表采用93天快照策略，数据重复存储，造成存储压力大。

现状：共xxx张表，占用xxx张空间，平均每张xxx存储。

基于合理利用存储，控制存储无序扩展，节约公司存储成本，需要该表快照策略，由快照策略改为拉链策略。

说明

本方案所说拉链，指的是替换快照方式日粒度的存储拉链，有别于基于业务流水数据设计的拉链。

方案比较

快照vs拉链

简洁版

比较项 快照 拉链
写入 无需加工 需要加工
使用 无成本 理解成本高
存储 126份副本 2~5份副本
计算 无 去重&排序
适用 所有 有主键&数据变更时间

明细版

比较项 快照 拉链
写入 快照直接按数据日期写入，简单易懂 拉链需要结合当天最新数据和历史拉链数据加工出最新数据，略微复杂
使用 快照关联历史直接按数据日期取多版本，简单易懂 拉链需要根据业务日期和开始时间及结束时间比较，确定唯一版本，使用成本高
存储 快照为93天周期+每月最后一天，以3年为例，共计126份副本 拉链只有在数据变更才会重复存储，根据数据量变更频繁程度，预估2~5份副本
计算 无计算 需要根据id和创建时间和结束时间进行去重和排序，进而计算出开始时间和结束时间
适用 所有 需要有主键&数据变更时间

总结

快照方案简单粗暴有效，拉链方案复杂优雅有效。

加工和使用成本上，可以认为拉链是快照方案的2~5倍

存储成本上，可以任务拉链是快照方案的 1/30 之一

前提条件

并非所有的表都可以做成拉链表来存储历史记录，业务表必须满足一下几点方可设计拉链

条件 解释 现状
有唯一主键id 业务库必须有唯一主键id用于标识同一条记录
这样才能对同一条记录进行版本区分		 开发规范里有明确必须有主键id
不排除个别表
有业务数据变更时间 如果数据字段发生变更，然而数据时间不进行变更会导致拉链表无法确认时间 开发规范里有明确edit_time需要自动更新
历史老表存在不更新现象，需要在mysql表结构中确认是否自动更新

设计

统一规范

1）无穷大的结束时间，统一采用 29991231 表示
2）无穷小的开始时间，统一采用 20000101表示
3）拉链表统一新增三个字段
之所以加上data前置，为了防止字段重名

字段名 解释
data_start_date 数据生效开始日期
data_end_date 数据生效结束日期
data_is_active 数据是否当前有效
4）拉链表分区统一使用三个字段进行分区
其中data_start_year和data_end_year是为了加快使用效率
dayid是为了获取昨天版本
字段名 解释
———– ———–
data_start_year 数据开始年份
data_end_year 数据结束年份
dayid 数据日期

5）data_start_date和data_end_date采用闭区间设计
6）拉链表的生命周期为2，即保留两份副本
7) 命名统一采用 xxx_fds命名
8）当前拉链表只针对dwd进行设计

设计举例

mysql 举例表结构

表名：test_a

字段 解释 其他
id 主键id pk
test_name 测试名称
create_time 创建时间
edit_time 编辑时间

mysql 举例表数据

20210701数据

日期 id test_name create_time edit_time
20210701 1 what’s your name 20210701 20210701
20210701 2 what’s your age 20210701 20210701
2021072 1 what’s wrong 20210701 20210702
20210702 2 what’s your age 20210701 20210701
20210710 1 whattttttttttt 20210701 20210710
20210710 2 what’s your age 20210701 20210701

拉链表代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nostrick;
 
-- 通用字段
set hivevar:com_columns=id,test_name,create_time,edit_time;
 
-- 原始dwd表名称
set hivevar:dwd_table_name=dwd_test_a_d;
 
-- fds全量拉量表名称
set hivevar:fds_table_name=dws_test_a_fds;
 
-- 主键id，分区表id会重复，注意
set hivevar:pk_id=id;
 
 
  
create table if not exists ${fds_table_name}
( 
data_start_date string comment '开始日期'
,data_end_date string comment '结束日期'
,id bigint COMMENT '自增主键'
,test_name string COMMENT '测试名称'
,create_time string COMMENT '表记录创建时间'
,edit_time string COMMENT '表记录修改时间')
partitioned by (dayid string comment '数据日期',data_is_active tinyint comment '数据是否当前最新版本'
,data_start_year string comment '起始年',data_end_year string comment '结束年')
stored as orc;
  
 
  
  
  
--删除旧分区，防止脏数据无法删除
ALTER TABLE ${fds_table_name} DROP if exists PARTITION (dayid='$v_date');
  
--去重后的全量数据
with full_data as (
  select dayid
  ,${com_columns} --字符串会被替换，需要转义
  from
    (select  dayid
     ,${com_columns}
    from  ${dwd_table_name} --获取当天最新数据
    where dayid='$v_date'
    and substr(create_time,1,8)<='$v_date'
      
    union all
  
    select data_start_date as dayid
    ,\${com_columns}
    from \${fds_table_name} -- 从历史拉链表获取历史所有数据
    where substr(create_time,1,8)<='$v_date'
    and dayid=date_format(date_sub(to_date('$v_date','yyyyMMdd'),1),'yyyyMMdd')
    ) a
  group by dayid,\${com_columns}
)
--清洗数据，使数据合规，我们做的是天粒度的拉链，同一个主键id一天取最后一条记录
--之所以要清洗，理论上同一个id一天是会存在多条记录的，比如记录在1号03分变更,然后在1号10分抽取，然后在1号19：08分变更，然后在2号10分抽取
,legal_data as (
  select *
  from
    (select *
     ,row_number() over(partition by ${pk_id},substr(edit_time,1,8) order by edit_time desc,dayid desc) as data_rank -- 加入dayid排序，为修改值，但是eidt未改变的值，取最新的数据
     from full_data
    ) a
  where data_rank=1 --同一个主键id一天取最后一条记录
)
--日粒度的拉链
,date_data_ds as (
  select if(lag_edit_date is null,substr(create_time,1,8),substr(edit_time,1,8)) as data_start_date --如果上一条编辑时间为空，说明是第一条，采用创建日期作为开始日期，不然采用本条编辑时间作为开始时间
  ,case when lead_edit_date is null and dayid='$v_date' then '29991231' --如果下一条编辑时间为空，且该条记录分区时间为最新分区，说明是最后一条，采用默认29991231作为结束时间，不然采用下一条编辑时间作为结束时间
        when lead_edit_date is null and dayid<'$v_date' then if(substr(edit_time,1,8)>=dayid,substr(edit_time,1,8),dayid) -- 如果下一条编辑时间为空，该表记录分区不是最新分区，说明该记录在最新分区中已被删除，该记录的修改时间和分区时间取最大值。  该判断为兼容记录物理删除骚操作，取最大值为防止dwd重刷导致的数据错乱
        else lead_edit_date -- 下一条记录存在，采用下一条编辑时间作为结束时间
        end as data_end_date
  ,if(lead_edit_date is null and dayid='$v_date',1,0) as data_is_active --没有下一条，说明是最新的一条
  ,\${com_columns}
  from
    (select *
    ,lead(date_format(date_add(to_date(substr(edit_time,1,8),'yyyyMMdd'),-1),'yyyyMMdd'),1,null) over (partition by ${pk_id} order by create_time,edit_time) as lead_edit_date --下一条记录的编辑日期
    ,lag(substr(edit_time,1,8),1,null) over (partition by ${pk_id} order by create_time,edit_time) as lag_edit_date --上一条记录的编辑时间,由于开始时间和结束时间是区间包含，edit是作为结束时间的闭区间，因此需要上一条日期+1作为开始时间
    --,last_value(edit_time) over (partition by id order by create_time,edit_time) as last_edit_time --分组内最后一条记录编辑日期
    from legal_data
    ) a
  
)
  
  
insert overwrite table ${fds_table_name} partition (dayid,data_is_active,data_start_year,data_end_year)
select data_start_date
,data_end_date
,${com_columns}
,dayid
,data_is_active
,data_start_year
,data_end_year
from
  (select  data_start_date
,data_end_date
,data_is_active
,${com_columns}
,substr(data_start_date,1,4) as data_start_year
,substr(data_end_date,1,4) as data_end_year
,'$v_date' as dayid
from date_data_ds
 ) a
;

拉链表数据效果

|数据日期| data_start_date| data_end_date| data_is_active| id| test_name| create_time| edit_time| data_start_year| data_end_year|
|—–| —–| —–| —–| —–| —–| —–| —–| —–| —–|
|20210701| 20210701| 29991231| 1| 1| what’s your name| 20210701| 20210701| 2021| 2999|
|20210701|20210701 |29991231 |1 |2 |what’s your age| 20210701| 20210701| 2021| 2999|
|20210702| 20210701| 20210701| 0| 1| what’s your name| 20210701| 20210701| 2021| 2021|
|20210702|20210702| 29991231| 1| 1| what’s wrong| 20210701| 20210702| 2021| 2999|
|20210702|20210701| 29991231| 1| 2| what’s your age| 20210701| 20210701| 2021| 2999|
|20210710| 20210701| 20210701| 0| 1| what’s your name| 20210701| 20210701| 2021| 2021|
|20210710|20210702| 20210709| 0| 1| what’s wrong| 20210701| 20210702| 2021| 2021|
|20210710|20210710| 29991231| 1| 1| whattttttttttt| 20210701| 20210710 |2021| 2999|
|20210710|20210701 |29991231 |1| 2| what’s your age| 20210701| 20210701| 2021| 2999|

拉链表的使用

获取当前最新数据

1）优先使用最新表而非快照表

2）一定要用快照： sql select * from dwd_test_a_fds where dayid='cur_time' and data_is_active=1

获取某一天的状态

1
2
3
4
5
6
select * from dwd_test_a_fds
where dayid='cur_time'
and data_start_year<=substr('$v_date',1,4) --索引查询
and data_end_year>=substr('$v_date',1,4)
and data_start_date<='$v_date'
and data_end_date>='$v_date

根据业务字段字段进行匹配

1
2
3
4
5
6
7
8
select a.*
from
  (select * from test_test where dayid='$v_date') a
left join
  (select * from dwd_test_a_fds where dayid='cur_time') b
on a.biz_id=b.id
and substr(a.biz_time,1,8)>=b.data_start_time
and substr(a.biz_time,1,8)<=b.data_end_time

效果

待测试，暂定dwd_order_shop_full_d 表

流程