你需要了解的全量表,增量表及拉链表

mysql测试数据准备

  • 第一天 9月10号数据

    1,待支付,2020-09-10 12:20:11,2020-09-10 12:20:11
    2,待支付,2020-09-10 14:20:11,2020-09-10 14:20:11
    3,待支付,2020-09-10 16:20:11,2020-09-10 16:20:11
  • 第二天 9月11号数据

    1,待支付,2020-09-10 12:20:11,2020-09-10 12:20:11
    2,已支付,2020-09-10 14:20:11,2020-09-11 14:21:11
    3,已支付,2020-09-10 16:20:11,2020-09-11 16:21:11
    4,待支付,2020-09-11 12:20:11,2020-09-11 12:20:11
    5,待支付,2020-09-11 14:20:11,2020-09-11 14:20:11

对比mysql第一天和第二天的数据发现,第二天新增了订单id为4和5这两条数据,并且订单id为2和3的状态更新为了已支付

全量表

每天所有的最新状态的数据,都要全量抽取到ods层,每个分区保留历史全量快照。
1、全量表,有无变化,都要报
2、每次上报的数据都是所有的数据(变化的 + 没有变化的)

9月10号全量抽取到ods层

create table ods_order_info_20200910(
 order_id     string    COMMENT '订单id'
,order_status string    COMMENT '订单状态'
,create_time  timestamp COMMENT '创建时间'
,update_time  timestamp COMMENT '更新时间'
) COMMENT '订单表'
row format delimited fields terminated by ','
;

你需要了解的全量表,增量表及拉链表插图

create table dwd_order_info_df(
 order_id     string    COMMENT '订单id'
,order_status string    COMMENT '订单状态'
,create_time  timestamp COMMENT '创建时间'
,update_time  timestamp COMMENT '更新时间'
) COMMENT '订单表'
partitioned by (date_id string)
row format delimited fields terminated by ','
;
# 把ods_order_info_20200910数据全量插到dwd层2020-09-10分区
insert overwrite table dwd_order_info_df partition(date_id = '2020-09-10')
select
order_id
,order_status
,create_time
,update_time
from ods_order_info_20200910;

9月11号全量抽取到ods层

你需要了解的全量表,增量表及拉链表插图1

create table ods_order_info_20200911(
 order_id     string    COMMENT '订单id'
,order_status string    COMMENT '订单状态'
,create_time  timestamp COMMENT '创建时间'
,update_time  timestamp COMMENT '更新时间'
) COMMENT '订单表'
row format delimited fields terminated by ',';

你需要了解的全量表,增量表及拉链表插图2

# 把ods_order_info_20200911数据全量插到dwd层2020-09-11分区
insert overwrite table dwd_order_info_df partition(date_id = '2020-09-11')
select
order_id
,order_status
,create_time
,update_time
from wedw_ods.order_info_20200911;

你需要了解的全量表,增量表及拉链表插图3

增量表

增量表:新增数据,增量数据是上次导出之后的新数据。

1、记录每次增加的量,而不是总量;
2、增量表,只报变化量,无变化不用报
3、业务库表中需有主键及创建时间,修改时间

9月10号全量抽取到ods层(全量初始化)

你需要了解的全量表,增量表及拉链表插图4

# 把ods——order_info_20200910数据全量插到dwd层2020-09-10分区
insert overwrite table dwd_order_info_di partition(date_id = '2020-09-10')
select
order_id
,order_status
,create_time
,update_time
from ods_order_info_20200910;

你需要了解的全量表,增量表及拉链表插图5

9月11号抽取更新的数据及当天新增的数据,即订单id为2,3,4,5的数据

你需要了解的全量表,增量表及拉链表插图6

dwd_order_info_di表9月10号的分区数据与ods_order_info_20200911增量抽取的数据合并,有2种方案:

a.两个表通过主键关联,dwd表存在并且ods表不存在的数据union all一下ods_order_info_20200911表所有的数据,即全量数据插入到dwd表的9月11号的分区

insert overwrite table dwd_order_info_di partition(date_id = '2020-09-11')
select
 t1.order_id
,t1.order_status
,t1.create_time
,t1.update_time
from
dwd_order_info_di t1
left join
ods_order_info_20200911 t2
on t1.order_id = t2.order_id
where t1.date_id = '2020-09-10'
and t2.order_id is null
union all
select 
 order_id
,order_status
,create_time
,update_time
from 
ods_order_info_20200911
;

你需要了解的全量表,增量表及拉链表插图7

b.两个表数据union all一下,再根据order_id去重(根据order分组,更新时间降序,取第一条)

insert overwrite table dwd_order_info_di partition(date_id = '2020-09-11')
select
 t2.order_id
,t2.order_status
,t2.create_time
,t2.update_time 
from
(
    select
     t1.order_id
    ,t1.order_status
    ,t1.create_time
    ,t1.update_time
    ,row_number() over(partition by order_id order by update_time desc) as rn
    from
    (
        select
         order_id
        ,order_status
        ,create_time
        ,update_time
        from
        dwd_order_info_di 
        where date_id = '2020-09-10'

        union all

        select 
         order_id
        ,order_status
        ,create_time
        ,update_time
        from 
        ods_order_info_20200911
    ) t1
) t2
where t2.rn = 1
;

你需要了解的全量表,增量表及拉链表插图8

特殊增量表:da表,每天的分区就是当天的数据,其数据特点就是数据产生后就不会发生变化,如日志表。

拉链表

拉链表维护历史状态,以及最新状态数据适用一下情况:

  1. 数据量比较大
  2. 表中的部分字段会被更新
  3. 需要查看某一个时间点或者时间段的历史快照信息
  4. 更新的比例和频率不是很大,如果表中信息变化不是很大,每天都保留一份全量,那么每次全量中会保存很多不变的信息,对存储是极大的浪费优点

9月10号全量抽取到ods层

create table ods_order_info_20200910(
 order_id     string    COMMENT '订单id'
,order_status string    COMMENT '订单状态'
,create_time  timestamp COMMENT '创建时间'
,update_time  timestamp COMMENT '更新时间'
) COMMENT '订单表'
row format delimited fields terminated by ',';

你需要了解的全量表,增量表及拉链表插图9

建立dwd层拉链表

增加两个字段:

  • start_dt(表示该条记录的生命周期开始时间周期快照时的状态)
  • end_dt(该条记录的生命周期结束时间)end_dt= ‘9999-12-31’ 表示该条记录目前处于有效状态
create table dwd_order_info_dz(
 order_id     string    COMMENT '订单id'
,order_status string    COMMENT '订单状态'
,create_time  timestamp COMMENT '创建时间'
,update_time  timestamp COMMENT '更新时间'
,start_dt     date      COMMENT '开始生效日期'
,end_dt       date      COMMENT '结束生效日期'
) COMMENT '订单表'
partitioned by (date_id string)
row format delimited fields terminated by ',';

注:第一次加工的时候需要初始化所有数据,start_time设置为数据日期2020-09-10,end_time设置为9999-12-31

insert overwrite table dwd_order_info_dz partition(date_id = '2020-09-10')
select
 order_id    
,order_status
,create_time 
,update_time 
,to_date(update_time) as start_dt   
,'9999-12-31' as end_dt  
from
ods_order_info_20200910;

你需要了解的全量表,增量表及拉链表插图10

9月11号抽取更新的数据及当天新增的数据到ods层,即订单id为2,3,4,5的数据

你需要了解的全量表,增量表及拉链表插图11

insert overwrite table dwd_order_info_dz partition(date_id = '2020-09-11')
select
 t1.order_id    
,t1.order_status
,t1.create_time 
,t1.update_time
,t1.start_dt
,case when t1.end_dt = '9999-12-31' and t2.order_id is not null then t1.date_id else t1.end_dt end as end_dt
from
dwd_order_info_dz t1
left join ods_order_info_20200911 t2
on t1.order_id = t2.order_id
where t1.date_id = '2020-09-10'
union all
SELECT
 t1.order_id    
,t1.order_status
,t1.create_time 
,t1.update_time
,to_date(update_time) as start_dt
,'9999-12-31' as end_dt
FROM ods_order_info_20200911 t1
;

你需要了解的全量表,增量表及拉链表插图12

查询当前的所有有效记录

select 
* 
from 
dwd_order_info_dz 
where 
date_id = '2020-09-11'
and end_dt ='9999-12-31';

你需要了解的全量表,增量表及拉链表插图13

查询9月10号历史快照

select 
* 
from 
dwd_order_info_dz 
where 
date_id = '2020-09-10' 
and start_dt <= '2020-09-10' 
and end_dt >='2020-09-10';

你需要了解的全量表,增量表及拉链表插图14

查询9月11号历史快照

select 
* 
from 
dwd_order_info_dz 
where 
date_id = '2020-09-11' 
and start_dt <= '2020-09-11' 
and end_dt >='2020-09-11';

你需要了解的全量表,增量表及拉链表插图15

总结

1、如果数据量不是很大(不超过20W)且预估后续增长的非常慢,可以考虑全量表抽取,这是最简便的方法
2、如果数据量目前来说不是很大,但是业务发展很快,数据量一段时间后就会上来,建议增量抽取
3、目前数据量本身就非常大,肯定是需要增量抽取的,比如现在有10亿数据,如果你每天全量抽取一遍,相信我,你会抽哭的
4、对于历史状态需要保存的,这个时候就需要使用拉链表了,实际工作中,使用拉链表的场景并不会太多,比如订单表,保存订单历史状态,维表(缓慢变化维的处理)

数据仓库

你真的了解数仓元数据吗,数据地图你又知道多少?

2022-12-14 1:03:17

数据仓库

指标与标签的区别

2022-12-14 1:09:35

3 条回复 A文章作者 M管理员
  1. 请教一下,看了您的介绍,还是有点不太理解的是增量表的讲述上,dwd_order_info_di,抽取方法是取出增量部分和历史合并,再插入新的分区,这样每个分区仍是保留了最新状态的全量数据,并不是增量的存储,这种增量表,和每次从ods抽取全量数据放到新的分区,这里的区别是?

    还是想表达的是,ods_order_info_20200911及以后的日期是 从mysql增量抽取并存储的,而dwd_order_info_di每个分区是全量的,但这个di的i是又是增量表意思?

    • 是的,dwd_order_info_di其实是全量表,ods_order_info_20200911代表的是每日新增的数据。

      每日取dwd_order_info_di,t-2的分区数据,与ods_order_info_20200911,ods临时表的t-1日变更数据合并,产出到dwd_order_info_di,t-1的分区全量数据。

  2. 测试下啊啊啊啊

购物车
优惠劵
搜索