前導讀：數倉架構（ods-dwd-dws-ads）每一層之前我們已經分享過很多干貨了，但dwd卻還沒有。今天這篇文章主要講解數倉dwd層業務事實數據的搭建、解析、加載與案例實踐。言往期干貨：數倉建模—指標體系數倉建模分層理論數倉建模—寬表的設計數倉建模方法論數倉架構發展史

一、DWD層結構DWD層是對用戶的日志行為事實進行解析，以及對交易業務數據采用維度模型的方式重新建模（即維度退化）。

1、回顧DWD層概念我們在來回顧一下對DWD層（Data Warehouse Detail）的定義：“明細粒度事實層：是以業務過程來作為建模驅動，基于每個具體的業務過程特點，構建最細粒度的明細層事實表（注意是最細粒度）。需要結合企業的數據使用特點，將明細事實表的某些重要維度屬性字段做適當冗余，即寬表化處理。明細粒度事實層的表通常也被稱為邏輯事實表。”

2、DWD層建模4步驟DWD層是事實建模層，這層建模主要做的4個步驟：我們目前已經完成了：

2.1、選擇業務過程選擇了事實表，比如：訂單事實表、支付事實表等；

2.2、聲明粒度即確認每一行數據是什么，要保證事實表的最小粒度。

2.3、確認維度在前面兩節中我們確定了6個維度；比如時間、用戶、地點、商品、優惠券、活動這6個維度。思路是其他ODS層表的維度需要向這6個維度進行退化到DIM層，這樣做的母的是減少后期的大量表之間的join操作。6個維度表的退化操作其實我們在前面的第十二章節已經做了即DIM層。除了第3張表即商品維度表是5個表退化到1張表上，其他都是1-2張表退化到1張表上，相對比較簡單。

2.4、確認事實就是確認事實表的每張事實表的度量值。下面我們根據事實表的加載方式來選擇幾個實戰操作一下。

二、DWD層-事務型事實表關于事實表分類，我們在數倉關系建模和維度建模，里面說過，分為6類事實表。

1、事務型事實表的概念適用于不會發生變化的業務。業務表的同步策略是增量同步。以每個事務或事件為單位，例如一個銷售訂單記錄，一筆支付記錄等，作為事實表里的一行數據。一旦事務被提交，事實表數據被插入，數據就不再進行更改，其更新方式為增量更新。8張表里面包含：支付事實表、評價事實表、退款事實表、訂單明細(詳情)事實表

2、解析思路根據事實表（行），選擇不同的維度（列）來建表。

3、支付事實表（事務型事實表）需要時間、用戶、地區三個維度，查看ODS層表ods_payment_info，發現沒有地區維度字段。所以通過ods_order_info表關聯做join獲取該字段。

3.1、建表語句 drop table if exists dwd_fact_payment_info;create external table dwd_fact_payment_info ( `id` string COMMENT 'id', `out_trade_no` string COMMENT '對外業務編號', `order_id` string COMMENT '訂單編號', `user_id` string COMMENT '用戶編號', `alipay_trade_no` string COMMENT '支付寶交易流水編號', `payment_amount` decimal(16,2) COMMENT '支付金額', `subject` string COMMENT '交易內容', `payment_type` string COMMENT '支付類型', `payment_time` string COMMENT '支付時間', `province_id` string COMMENT '省份ID') COMMENT '支付事實表表'PARTITIONED BY (`dt` string)stored as parquetlocation '/warehouse/gmall/dwd/dwd_fact_payment_info/'tblproperties?("parquet.compression"="lzo");

3.2、裝載語句province_id省份ID這個字段通過?ods_order_info表做join獲取 SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;insert?overwrite?table?dwd_fact_payment_info?partition(dt='2021-05-03')select pi.id, pi.out_trade_no, pi.order_id, pi.user_id, pi.alipay_trade_no, pi.total_amount, pi.subject, pi.payment_type, pi.payment_time, oi.province_idfrom(????select?*?from?ods_payment_info?where?dt='2021-05-03')pijoin( select id, province_id from ods_order_info where dt='2021-05-03')oion?pi.order_id?=?oi.id;4、退款事實表（事務型事實表）需要時間、用戶、商品三個維度，查看ODS層表ods_order_refund_info，所有字段都有，那么直接取數裝載。

4.1、創建表 drop table if exists dwd_fact_order_refund_info;create external table dwd_fact_order_refund_info( `id` string COMMENT '編號', `user_id` string COMMENT '用戶ID', `order_id` string COMMENT '訂單ID', `sku_id` string COMMENT '商品ID', `refund_type` string COMMENT '退款類型', `refund_num` bigint COMMENT '退款件數', `refund_amount` decimal(16,2) COMMENT '退款金額', `refund_reason_type` string COMMENT '退款原因類型', `create_time` string COMMENT '退款時間') COMMENT '退款事實表'PARTITIONED BY (`dt` string)stored as parquetlocation '/warehouse/gmall/dwd/dwd_fact_order_refund_info/'tblproperties?("parquet.compression"="lzo");

4.2、裝載時間直接從ODS層查到數據后裝載。 insert overwrite table dwd_fact_order_refund_info partition(dt='2021-05-03')select id, user_id, order_id, sku_id, refund_type, refund_num, refund_amount, refund_reason_type, create_timefrom ods_order_refund_infowhere?dt='2021-05-03';

5、評價事實表、訂單明細事實表（事務型事實表）都和上面“退款事實表”處理方法一樣，并且所有字段均從ODS層ods_comment_info直接獲取。你是否可以自己創建呢？

三、DW層-周期型快照事實表

1、周期型快照事實表的概念周期型快照事實表，表中不會保留所有數據，只保留固定時間間隔的數據，例如每天或者每月的銷售額或每月的賬戶余額等。例如購物車，有加減商品，隨時都有可能變化，但是我們更關心每天結束時這里面有多少商品，方便我們后期統計分析。相當于每天一個全量快照，業務表的同步策略是全量同步。

2、解析思路每天做一次快照，導入的數據是全量，區別于事務型事實表是每天導入新增。存儲的數據比較講究時效性，時間太久了的意義不大，可以刪除以前的數據。

3、加購事實表（周期型快照事實表）

3.1、創建表結構所有字段ODS層，fact_cart_info表都有。 drop table if exists dwd_fact_cart_info;create external table dwd_fact_cart_info( `id` string COMMENT '編號', `user_id` string COMMENT '用戶id', `sku_id` string COMMENT 'skuid', `cart_price` string COMMENT '放入購物車時價格', `sku_num` string COMMENT '數量', `sku_name` string COMMENT 'sku名稱 (冗余)', `create_time` string COMMENT '創建時間', `operate_time` string COMMENT '修改時間', `is_ordered` string COMMENT '是否已經下單。1為已下單;0為未下單', `order_time` string COMMENT '下單時間', `source_type` string COMMENT '來源類型', `srouce_id` string COMMENT '來源編號') COMMENT '加購事實表'PARTITIONED BY (`dt` string)stored as parquetlocation '/warehouse/gmall/dwd/dwd_fact_cart_info/'tblproperties?("parquet.compression"="lzo");3.2、裝載數據 insert overwrite table dwd_fact_cart_info partition(dt='2021-05-03')select id, user_id, sku_id, cart_price, sku_num, sku_name, create_time, operate_time, is_ordered, order_time, source_type, source_idfrom ods_cart_infowhere?dt='2020-06-14';4、收藏事實表收藏事實表的操作和加購事實表一樣，從時間、商品、用戶三個維度來創建表。四、DWD層-累積型快照事實表1、累積型快照事實表的概念累積型快照事實表，用于周期性發生變化的業務，即需要周期性的跟蹤業務事實的變化。例如：數據倉庫中可能需要累積或者存儲訂單從下訂單開始，到訂單商品被打包、運輸、和簽收的各個業務階段的時間點數據來跟蹤訂單聲明周期的進展情況。當這個業務過程進行時，事實表的記錄也要不斷更新。業務表的同步策略是新增以及變化同步。2、解析思路我們以優惠券領用事實表為例。首先要了解優惠卷的生命周期：領取優惠卷——>用優惠卷下單——>優惠卷參與支付累積型快照事實表使用：統計優惠卷領取次數、優惠卷下單次數、優惠卷參與支付次數。

3、優惠券領用事實表（累積型快照事實表）

3.1、創建表結構 drop table if exists dwd_fact_coupon_use;create external table dwd_fact_coupon_use( `id` string COMMENT '編號', `coupon_id` string COMMENT '優惠券ID', `user_id` string COMMENT 'userid', `order_id` string COMMENT '訂單id', `coupon_status` string COMMENT '優惠券狀態', `get_time` string COMMENT '領取時間', `using_time` string COMMENT '使用時間(下單)', `used_time` string COMMENT '使用時間(支付)') COMMENT '優惠券領用事實表'PARTITIONED BY (`dt` string)stored as parquetlocation '/warehouse/gmall/dwd/dwd_fact_coupon_use/'tblproperties?("parquet.compression"="lzo");注意：這里dt是按照優惠卷領用時間get_time做為分區 `get_time`?string??COMMENT?'領取時間',`using_time`?string??COMMENT?'使用時間(下單)',`used_time`?string??COMMENT?'使用時間(支付)'3.2裝載數據首日裝載分析首日裝載SQL代碼,注意是動態分區。 insert overwrite table dwd_coupon_use partition(dt)select id, coupon_id, user_id, order_id, coupon_status, get_time, using_time, used_time, expire_time, coalesce(date_format(used_time,'yyyy-MM-dd'),date_format(expire_time,'yyyy-MM-dd'),'9999-99-99')from ods_coupon_usewhere?dt='2021-05-03';每日裝載思路分析SQL代碼 set hive.exec.dynamic.partition.mode=nonstrict;set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;insert overwrite table dwd_fact_coupon_use partition(dt)select if(new.id is null,old.id,new.id), if(new.coupon_id is null,old.coupon_id,new.coupon_id), if(new.user_id is null,old.user_id,new.user_id), if(new.order_id is null,old.order_id,new.order_id), if(new.coupon_status is null,old.coupon_status,new.coupon_status), if(new.get_time is null,old.get_time,new.get_time), if(new.using_time is null,old.using_time,new.using_time), if(new.used_time is null,old.used_time,new.used_time), date_format(if(new.get_time is null,old.get_time,new.get_time),'yyyy-MM-dd')from( select id, coupon_id, user_id, order_id, coupon_status, get_time, using_time, used_time from dwd_fact_coupon_use where dt in ( select date_format(get_time,'yyyy-MM-dd') from ods_coupon_use????????where?dt='2021-05-04' ))oldfull outer join( select id, coupon_id, user_id, order_id, coupon_status, get_time, using_time, used_time from ods_coupon_use where dt='2021-05-04')newon?old.id=new.id;其他類似的累積型事實表也是這個操作思路。這樣我們就完成了DWD層業務數據的建模和設計、搭建和使用包括簡要的SQL代碼的編寫。現在我們來總結一下：DWD層是對事實表的處理，代表的是業務的最小粒度層。任何數據的記錄都可以從這一層獲取，為后續的DWS和DWT層做準備。DWD層是站在選擇好事實表的基礎上，對維度建模的視角，這層維度建模主要做的4個步驟：選擇業務過程、聲明粒度、確認維度、確認事實。參考書籍： 數據倉庫第4版 數據倉庫工具箱 DAMA數據管理知識體系指南 華為數據之道