遲到的事實(shí)概述
- 數(shù)據(jù)倉庫通常建立于一種理想的假設(shè)情況下， 這就是數(shù)據(jù)倉庫的度量（事實(shí)記錄） 與度量的環(huán)境（維度記錄） 同時(shí)出現(xiàn)在數(shù)據(jù)倉庫中。 當(dāng)同時(shí)擁有事實(shí)記錄和正確的當(dāng)前維度行時(shí)窃这， 就能夠從容地首先維護(hù)維度鍵， 然后在對應(yīng)的事實(shí)表行中使用這些最新的鍵。 然而须喂， 各種各樣的原因會(huì)導(dǎo)致需要ETL系統(tǒng)處理遲到的事實(shí)數(shù)據(jù)匹涮。 例如天试， 某些線下的業(yè)務(wù)， 數(shù)據(jù)進(jìn)入操作型系統(tǒng)的時(shí)間會(huì)滯后于事務(wù)發(fā)生的時(shí)間然低。 再或者出現(xiàn)某些極端情況喜每， 如源數(shù)據(jù)庫系統(tǒng)出現(xiàn)故障， 直到恢復(fù)后才能補(bǔ)上故障期間產(chǎn)生的數(shù)據(jù)雳攘。
- 在銷售訂單示例中带兜， 晚于訂單日期進(jìn)入源數(shù)據(jù)的銷售訂單可以看作是一個(gè)遲到事實(shí)的例子。 銷售訂單數(shù)據(jù)被裝載進(jìn)其對應(yīng)的事實(shí)表時(shí)吨灭， 裝載日期晚于銷售訂單產(chǎn)生的日期刚照， 因此是一個(gè)遲到的事實(shí)。 本例中因?yàn)槎ㄆ谘b載的是前一天的數(shù)據(jù)喧兄， 所以這里的“晚于”指的是事務(wù)數(shù)據(jù)延遲兩天及其以上才到達(dá)ETL系統(tǒng)无畔。
- 必須對標(biāo)準(zhǔn)的ETL過程進(jìn)行特殊修改以處理遲到的事實(shí)。 首先吠冤， 當(dāng)遲到度量事件出現(xiàn)時(shí)浑彰， 不得不反向搜索維度表歷史記錄， 以確定事務(wù)發(fā)生時(shí)間點(diǎn)的有效的維度代理鍵拯辙， 因?yàn)楫?dāng)前的維度內(nèi)容無法匹配輸入行的情況郭变。 此外， 還需要調(diào)整后續(xù)事實(shí)行中的所有半可加度量涯保， 例如饵较， 由于遲到的事實(shí)導(dǎo)致客戶當(dāng)前余額的改變。 遲到事實(shí)可能還會(huì)引起周期快照事實(shí)表的數(shù)據(jù)更新遭赂。 例如 數(shù)倉--DW--Hadoop數(shù)倉實(shí)踐Case-13-周期快照事實(shí)表節(jié)討論的月銷售周期快照表循诉， 如果2016年6月的銷售訂單金額已經(jīng)計(jì)算并存儲在month_end_sale_order_fact快照表中， 這時(shí)一個(gè)遲到的6月訂單在7月某天被裝載撇他， 那么2016年6月的快照金額必須因遲到事實(shí)而重新計(jì)算茄猫。
- 下面就以銷售訂單數(shù)據(jù)倉庫為例， 說明如何處理遲到的事實(shí)困肩。
修改數(shù)據(jù)倉庫模式
- 借助 數(shù)倉--DW--Hadoop數(shù)倉實(shí)踐Case-13-周期快照事實(shí)表中建立的月度周期快照表划纽，其數(shù)據(jù)源自已經(jīng)處理過的銷售訂單事務(wù)事實(shí)表。 因此為了確定事實(shí)表中的一條銷售訂單記錄是否是遲到的锌畸， 需要把源數(shù)據(jù)中的登記日期列裝載進(jìn)銷售訂單事實(shí)表勇劣。 為此要在銷售訂單事實(shí)表上添加登記日期代理鍵列。 為了獲取登記日期代理鍵的值， 還要使用維度角色扮演技術(shù)添加登記日期維度表比默。
- 執(zhí)行下面的腳本在銷售訂單事實(shí)表里添加名為entry_date_sk的日期代理鍵列幻捏， 并且從日期維度表創(chuàng)建一個(gè)叫做entry_date_dim的數(shù)據(jù)庫視圖。
use dw;
-- 在事務(wù)事實(shí)表中添加登記日期代理鍵列
alter table
sales_order_fact rename to sales_order_fact_old;
-- 創(chuàng)建表
create table
sales_order_fact
(
order_number int comment'order number',
customer_sk int comment'customer SK',
customer_zip_code_sk int comment'customer zip code SK',
shipping_zip_code_sk int comment'shipping zip code SK',
product_sk int comment'product SK',
sales_order_attribute_sk int comment'sales order attribute SK',
order_date_sk int comment'order date SK',
entry_date_sk int comment 'entry date SK',
allocate_date_sk int comment'allocate date SK',
allocate_quantity int comment'allocate quantity',
packing_date_sk int comment'packing date SK',
packing_quantity int comment'packing quantity',
ship_date_sk int comment'ship date SK',
ship_quantity int comment'ship quantity',
receive_date_sk int comment'receive date SK',
receive_quantity int comment'receive quantity',
request_delivery_date_sk int comment'request delivery date SK',
order_amount decimal(10,2) comment'order amount',
order_quantity int comment'order quantity'
)
clustered by(order_number) into8 buckets
stored as
orc tblproperties ('transactional'='true');
-- 將數(shù)據(jù)插入到表中
insert into
sales_order_fact
select
order_number,
customer_sk,
customer_zip_code_sk,
shipping_zip_code_sk,
product_sk,
sales_order_attribute_sk,
order_date_sk,
null,
allocate_date_sk,
allocate_quantity,
packing_date_sk,
packing_quantity,
ship_date_sk,
ship_quantity,
receive_date_sk,
receive_quantity,
request_delivery_date_sk,
order_amount,
order_quantity
fromsales_order_fact_old;
drop table
sales_order_fact_old;
-- 建立登記日期維度視圖
create view
entry_date_dim
(entry_date_sk, entry_date, month_name, month
, quarter, year
)
as select
date_sk, date, month_name, month, quarter, year
from
date_dim
;
修改定期裝載腳本
- 在創(chuàng)建了登記日期維度視圖命咐， 并給銷售訂單事實(shí)表添加了登記日期代理鍵列以后篡九， 需要修改數(shù)據(jù)倉庫定期裝載腳本來裝載登記日期。 下面顯示了修改后的regular_etl.sql定期裝載腳本（只列出修改的部分） 醋奠。 注意sale_order源數(shù)據(jù)表及其對應(yīng)的過渡表中都已經(jīng)含有登記日期榛臼， 只是以前沒有將其裝載進(jìn)數(shù)據(jù)倉庫。
-- 修改定期裝載腳本
insert into
sales_order_fact
select
a.order_number,
c.customer_sk,
i.customer_zip_code_sk,
j.shipping_zip_code_sk,
d.product_sk,
g.sales_order_attribute_sk,
e.order_date_sk,
h.entry_date_sk,
null, null, null, null, null, null, null, null,
f.request_delivery_date_sk,
order_amount,
quantity
from
rds.sales_order a,
customer_dim c,
product_dim d,
order_date_dim e,
request_delivery_date_dim f,
sales_order_attribute_dim g,
customer_zip_code_dim i,
shipping_zip_code_dim j,
entry_date_dim h,
rds.customer k,
rds.cdc_time l
where
a.order_status = 'N'
and
a.customer_number = c.customer_number
and
a.status_date >= c.effective_date
and
a.status_date < c.expiry_date
and
a.customer_number = k.customer_number
and
k.customer_zip_code = i.customer_zip_code
and
a.status_date >= i.effective_date
and
a.status_date <= i.expiry_date
and
k.shipping_zip_code = j.shipping_zip_code
and
a.status_date >= j.effective_date
and
a.status_date <= j.expiry_date
and
a.product_code = d.product_code
and
a.status_date >= d.effective_date
and
a.status_date < d.expiry_date
and to_date
(a.status_date) = e.order_date
and
to_date(a.entry_date) = h.entry_date
and
to_date(a.request_delivery_date) = f.request_delivery_date
and
a.verification_ind = g.verification_ind
and
a.credit_check_flag = g.credit_check_flag
and
a.new_customer_ind = g.new_customer_ind
and
a.web_order_flag = g.web_order_flag
and
a.entry_date >= l.last_load
and a.entry_date < l.current_load ;
-- 更新分配庫房窜司、 打包沛善、 配送、 收貨4種訂單狀態(tài)的時(shí)間代理鍵和度量塞祈，
-- 也要加上entry_date_sk
- 本節(jié)開頭曾經(jīng)提到路呜， 需要為遲到的事實(shí)行獲取事務(wù)發(fā)生時(shí)間點(diǎn)的有效的維度代理鍵。 在裝載腳本中使用銷售訂單過渡表的狀態(tài)日期字段限定當(dāng)時(shí)的維度代理鍵织咧。 例如胀葱， 為了獲取事務(wù)發(fā)生時(shí)的客戶代理鍵， 篩選條件為：
status_date >= customer_dim.effective_date
and
status_date < customer_dim.expiry_date
- 之所以可以這樣做笙蒙， 原因在于本示例滿足以下兩個(gè)前提條件： 在最初源數(shù)據(jù)庫的銷售訂單表中抵屿， status_date存儲的是狀態(tài)發(fā)生時(shí)的時(shí)間； 維度的生效時(shí)間與過期時(shí)間構(gòu)成一條連續(xù)且不重疊的時(shí)間軸捅位， 任意status_date日期只能落到唯一的生效時(shí)間轧葛、 過期時(shí)間區(qū)間內(nèi)。
修改裝載月度周期快照事實(shí)表
-
數(shù)倉--DW--Hadoop數(shù)倉實(shí)踐Case-13-周期快照事實(shí)表創(chuàng)建的month_sum.sql腳本文件用于裝載月銷售周期快照事實(shí)表艇搀。 遲到的事實(shí)記錄會(huì)對周期快照中已經(jīng)生成的月銷售匯總數(shù)據(jù)產(chǎn)生影響尿扯， 因此必須做適當(dāng)?shù)男薷摹?/li>
- 月銷售周期快照表存儲的是某月某產(chǎn)品匯總的銷售數(shù)量和銷售金額， 表中有月份代理鍵焰雕、 產(chǎn)品代理鍵衷笋、 銷售金額、 銷售數(shù)量4個(gè)字段矩屁。 由于遲到事實(shí)的出現(xiàn)辟宗， 需要將事務(wù)事實(shí)表中的數(shù)據(jù)劃分為三類： 非遲到的事實(shí)記錄； 遲到的事實(shí)吝秕， 但周期快照表中尚不存在相關(guān)記錄泊脐； 遲到的事實(shí)， 并且周期快照表中已經(jīng)存在相關(guān)記錄烁峭。 對這三類事實(shí)數(shù)據(jù)的處理邏輯各不相同容客， 前兩類數(shù)據(jù)需要匯總后插入快照表秕铛， 而第三種情況需要更新快照表中的現(xiàn)有數(shù)據(jù)。 下面我們對修改后的month_sum.sql文件分解說明缩挑。
drop table if exists tmp;
create table tmp as
select a.order_month_sk order_month_sk,
a.product_sk product_sk,
a.month_order_amount + b.order_amount month_order_amount,
a.month_order_quantity + b.order_quantity month_order_quantity
from month_end_sales_order_fact a,
(select d.month_sk month_sk,
a.product_sk product_sk,
sum(order_amount) order_amount,
sum(order_quantity) order_quantity
from sales_order_fact a,
order_date_dim b,
entry_date_dim c,
month_dim d
where a.order_date_sk = b.order_date_sk
and a.entry_date_sk = c.entry_date_sk
and c.month = month(${hivevar:pre_month_date})
and c.year = year(${hivevar:pre_month_date})
and b.month = d.month
and b.year = d.year
and b.order_date <> c.entry_date
group by d.month_sk , a.product_sk) b
where a.product_sk = b.product_sk
and a.order_month_sk = b.month_sk;
delete from month_end_sales_order_fact
where exists
(select 1
from tmp t2
where month_end_sales_order_fact.order_month_sk = t2.order_month_sk
and month_end_sales_order_fact.product_sk = t2.product_sk);
insert into month_end_sales_order_fact select * from tmp;
- 按事務(wù)發(fā)生時(shí)間的先后順序但两， 我們先處理第三種情況。 為了更新周期快照表數(shù)據(jù)调煎， 需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表镜遣。 子查詢用于從銷售訂單事實(shí)表中獲取所有上個(gè)月錄入的己肮， 并且是遲到的數(shù)據(jù)行的匯總士袄， 用b.order_date <> c.entry_date作為判斷遲到的條件。 本示例中實(shí)際可以去掉這條判斷語句谎僻， 因?yàn)橹挥羞t到事實(shí)會(huì)對已有的快照數(shù)據(jù)造成影響娄柳。 外層查詢把具有相同產(chǎn)品代理鍵和月份代理鍵的遲到事實(shí)的匯總數(shù)據(jù)加到已有的快照數(shù)據(jù)行上， 臨時(shí)表中存儲這個(gè)查詢的結(jié)果艘绍。 注意產(chǎn)品代理鍵和月份代理鍵共同構(gòu)成了周期快照表的邏輯主鍵赤拒， 可以唯一標(biāo)識一條記錄； 之后使用先刪除再插入的方式更新周期快照表诱鞠。 從周期快照表刪除數(shù)據(jù)的操作也是以邏輯主鍵匹配作為過濾條件挎挖。
insert into
month_end_sales_order_fact
select
d.month_sk, a.product_sk, sum(order_amount), sum(order_quantity)
from
sales_order_fact a,
order_date_dim b,
entry_date_dim c,month_dim d
where
a.order_date_sk = b.order_date_sk
and
a.entry_date_sk = c.entry_date_sk
and
c.month= month(${hivevar:pre_month_date})
and
c.year= year(${hivevar:pre_month_date})
and
b.month= d.month
and
b.year= d.year
and
not exists
(select
1
from
month_end_sales_order_fact p
where
p.order_month_sk = d.month_sk
and
p.product_sk = a.product_sk)
group by
d.month_sk , a.product_sk
;
- 上面這條語句將第一、 二類數(shù)據(jù)統(tǒng)一處理航夺。 使用相關(guān)子查詢獲取所有上個(gè)月新錄入的蕉朵， 并且在周期快照事實(shí)表中尚未存在的產(chǎn)品銷售月匯總數(shù)據(jù)， 插入到周期快照表中阳掐。 銷售訂單事實(shí)表的粒度是每天始衅， 而周期快照事實(shí)表的粒度是每月， 因此必須使用訂單日期代理鍵對應(yīng)的月份代理鍵進(jìn)行比較缭保。
遲到的事實(shí)總結(jié)
- 遲到的事實(shí)似乎是針對周期性快照事實(shí)表才需要處理的汛闸？
- 這個(gè)遲到的事實(shí)需要在模型建立的時(shí)候考慮進(jìn)去，模型建立之初需要事實(shí)考慮的艺骂。
- 數(shù)倉模型的建立很考驗(yàn)實(shí)力啊诸老。
- 還是對模型多思考才行。
