資料準備

第一天 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的狀態更新為了已支付

全量表

每天的所有的最新狀態的資料。

1、全量表，有無變化，都要報

2、每次上報的資料都是所有的資料（變化的 + 沒有變化的）

9月10號全量抽取到ods層

create table wedw_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 wedw_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 ','
;

# 把wedw_ods.order_info_20200910資料全量插到dwd層2020-09-10分割槽
insert overwrite table wedw_dwd.order_info_df
 partition(date_id = '2020-09-10')
select
order_id
,order_status
,create_time
,update_time
from wedw_ods.order_info_20200910
;

9月11號全量抽取到ods層

create table wedw_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 ','
;

# 把wedw_ods.order_info_20200911資料全量插到dwd層2020-09-11分割槽
insert overwrite table wedw_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
;

全量抽取，每個分割槽保留歷史全量快照。

增量表

增量表：新增資料，增量資料是上次匯出之後的新資料。

1、記錄每次增加的量，而不是總量；

2、增量表，只報變化量，無變化不用報

3、業務庫表中需有主鍵及建立時間，修改時間

9月10號全量抽取到ods層(全量初始化)

# 把wedw_ods.order_info_20200910資料全量插到dwd層2020-09-10分割槽
insert overwrite table wedw_dwd.order_info_di
 partition(date_id = '2020-09-10')
select
order_id
,order_status
,create_time
,update_time
from wedw_ods.order_info_20200910
;

9月11號抽取更新的資料及當天新增的資料，即訂單id為2,3,4,5的資料

wedw_dwd.order_info_di表9月10號的分割槽資料與wedw_ods.order_info_20200911增量抽取的資料合併，有2種方案

a.兩個表通過主鍵關聯，dwd表存在並且ods表不存在的資料

union all 一下wedw_ods.order_info_20200911表所有的資料，即全量資料插入到dwd表的9月11號的分割槽

insert overwrite table wedw_dwd.order_info_di 
partition(date_id = '2020-09-11')
select
 t1.order_id
,t1.order_status
,t1.create_time
,t1.update_time
from
wedw_dwd.order_info_di t1
left join
wedw_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 
wedw_ods.order_info_20200911
;

b.兩個表資料union all一下，再根據order_id去重(根據order分組，更新時間降序，取第一條)

insert overwrite table wedw_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
        wedw_dwd.order_info_di 
        where date_id = '2020-09-10'
 
        union all
 
        select 
         order_id
        ,order_status
        ,create_time
        ,update_time
        from 
        wedw_ods.order_info_20200911
    ) t1
) t2
where t2.rn = 1
;

特殊增量表：da表，每天的分割槽就是當天的資料，其資料特點就是資料產生後就不會發生變化，如日誌表。

拉鍊表

維護歷史狀態，以及最新狀態資料

適用情況：

1.資料量比較大

2.表中的部分欄位會被更新

3.需要檢視某一個時間點或者時間段的歷史快照資訊
檢視某一個訂單在歷史某一個時間點的狀態
某一個使用者在過去某一段時間，下單次數

4.更新的比例和頻率不是很大

如果表中資訊變化不是很大，每天都保留一份全量，那麼每次全量中會儲存很多不變的資訊，對儲存是極大的浪費

優點

1、滿足反應資料的歷史狀態

2、最大程度節省儲存

9月10號全量抽取到ods層

create table wedw_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 ','
;

建立dwd層拉鍊表
增加兩個欄位：
start_dt(表示該條記錄的生命週期開始時間——週期快照時的狀態)
end_dt(該條記錄的生命週期結束時間)

end_dt= ‘9999-12-31’ 表示該條記錄目前處於有效狀態

create table wedw_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 wedw_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
wedw_ods.order_info_20200910
;

9月11號抽取更新的資料及當天新增的資料到ods層，即訂單id為2,3,4,5的資料

insert overwrite table wedw_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
wedw_dwd.order_info_dz t1
left join wedw_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 wedw_ods.order_info_20200911 t1
;

查詢當前的所有有效記錄：

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

查詢9月10號歷史快照:

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

查詢9月11號歷史快照:

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