阿里雲 DataWorks 學習——數倉架構設計
(一)技術架構選型
============
在數據模型設計之前,您需要首先完成技術架構的選型。本教程中使用阿里雲大數據產品 MaxCompute 配合 DataWorks,完成整體的數據建模和研發流程。
完整的技術架構圖如下圖所示。其中,DataWorks 的數據集成負責完成數據的採集和基本的 ETL。MaxCompute 作爲整個大數據開發過程中的離線計算引擎。DataWorks 則包括數據開發、數據質量、數據安全、數據管理等在內的一系列功能。
(二)數倉分層
在阿里巴巴的數據體系中,我們建議將數據倉庫分爲三層,自下而上爲:數據引入層(ODS,Operation Data Store)、數據公共層(CDM,Common Data Model)和數據應用層(ADS,Application Data Service)。
數據倉庫的分層和各層級用途如下圖所示。
-
數據引入層 ODS(Operation Data Store):存放未經過處理的原始數據至數據倉庫系統,結構上與源系統保持一致,是數據倉庫的數據準備區。主要完成基礎數據引入到 MaxCompute 的職責,同時記錄基礎數據的歷史變化。
-
數據公共層 CDM(Common Data Model,又稱通用數據模型層),包括 DIM 維度表、DWD 和 DWS,由 ODS 層數據加工而成。主要完成數據加工與整合,建立一致性的維度,構建可複用的面向分析和統計的明細事實表,以及彙總公共粒度的指標。
-
公共維度層(DIM):基於維度建模理念思想,建立整個企業的一致性維度。降低數據計算口徑和算法不統一風險。
公共維度層的表通常也被稱爲邏輯維度表,維度和維度邏輯表通常一一對應。
-
公共彙總粒度事實層(DWS):以分析的主題對象作爲建模驅動,基於上層的應用和產品的指標需求,構建公共粒度的彙總指標事實表,以寬表化手段物理化模型。構建命名規範、口徑一致的統計指標,爲上層提供公共指標,建立彙總寬表、明細事實表。
公共彙總粒度事實層的表通常也被稱爲彙總邏輯表,用於存放派生指標數據。
-
明細粒度事實層(DWD):以業務過程作爲建模驅動,基於每個具體的業務過程特點,構建最細粒度的明細層事實表。可以結合企業的數據使用特點,將明細事實表的某些重要維度屬性字段做適當冗餘,即寬表化處理。
明細粒度事實層的表通常也被稱爲邏輯事實表。
-
數據應用層 ADS(Application Data Service):存放數據產品個性化的統計指標數據。根據 CDM 與 ODS 層加工生成。
該數據分類架構在 ODS 層分爲三部分:數據準備區、離線數據和準實時數據區。整體數據分類架構如下圖所示。
在本教程中,從交易數據系統的數據經過 DataWorks 數據集成,同步到數據倉庫的 ODS 層。經過數據開發形成事實寬表後,再以商品、地域等爲維度進行公共彙總。
整體的數據流向如下圖所示。其中,ODS 層到 DIM 層的 ETL(萃取(Extract)、轉置(Transform)及加載(Load))處理是在 MaxCompute 中進行的,處理完成後會同步到所有存儲系統。ODS 層和 DWD 層會放在數據中間件中,供下游訂閱使用。而 DWS 層和 ADS 層的數據通常會落地到在線存儲系統中,下游通過接口調用的形式使用。
(三)數據模型
數據引入層(ODS)
ODS 層存放您從業務系統獲取的最原始的數據,是其他上層數據的源數據。業務數據系統中的數據通常爲非常細節的數據,經過長時間累積,且訪問頻率很高,是面向應用的數據。
說明 在構建 MaxCompute 數據倉庫的表之前,您需要首先了解 MaxCompute 支持的數據類型版本說明。
數據引入層表設計
本教程中,在 ODS 層主要包括的數據有:交易系統訂單詳情、用戶信息詳情、商品詳情等。這些數據未經處理,是最原始的數據。邏輯上,這些數據都是以二維表的形式存儲。雖然嚴格的說 ODS 層不屬於數倉建模的範疇,但是合理的規劃 ODS 層並做好數據同步也非常重要。本教程中,使用了 6 張 ODS 表:
-
記錄用於拍賣的商品信息:s_auction。
-
記錄用於正常售賣的商品信息:s_sale。
-
記錄用戶詳細信息:s_users_extra。
-
記錄新增的商品成交訂單信息:s_biz_order_delta。
-
記錄新增的物流訂單信息:s_logistics_order_delta。
-
記錄新增的支付訂單信息:s_pay_order_delta。
說明
-
表或字段命名儘量和業務系統保持一致,但是需要通過額外的標識來區分增量和全量表。例如,我們通過_delta 來標識該表爲增量表。
-
命名時需要特別注意衝突處理,例如不同業務系統的表可能是同一個名稱。爲區分兩個不同的表,您可以將這兩個同名表的來源數據庫名稱作爲後綴或前綴。例如,表中某些字段的名稱剛好和關鍵字重名了,可以通過添加_col1 後綴解決。
ODS 層設計規範
ODS 層表命名、數據同步任務命名、數據產出及生命週期管理及數據質量規範請參見 ODS 層設計規範。
建表示例
爲方便您使用,集中提供建表語句如下。更多建表信息,請參見表操作。
CREATE TABLE IF NOT EXISTS s_auction
(
id STRING COMMENT '商品ID',
title STRING COMMENT '商品名',
gmt_modified STRING COMMENT '商品最後修改日期',
price DOUBLE COMMENT '商品成交價格,單位元',
starts STRING COMMENT '商品上架時間',
minimum_bid DOUBLE COMMENT '拍賣商品起拍價,單位元',
duration STRING COMMENT '有效期,銷售週期,單位天',
incrementnum DOUBLE COMMENT '拍賣價格的增價幅度',
city STRING COMMENT '商品所在城市',
prov STRING COMMENT '商品所在省份',
ends STRING COMMENT '銷售結束時間',
quantity BIGINT COMMENT '數量',
stuff_status BIGINT COMMENT '商品新舊程度 0 全新 1 閒置 2 二手',
auction_status BIGINT COMMENT '商品狀態 0 正常 1 用戶刪除 2 下架 3 從未上架',
cate_id BIGINT COMMENT '商品類目ID',
cate_name STRING COMMENT '商品類目名稱',
commodity_id BIGINT COMMENT '品類ID',
commodity_name STRING COMMENT '品類名稱',
umid STRING COMMENT '買家umid'
)
COMMENT '商品拍賣ODS'
PARTITIONED BY (ds STRING COMMENT '格式:YYYYMMDD')
LIFECYCLE 400;
CREATE TABLE IF NOT EXISTS s_sale
(
id STRING COMMENT '商品ID',
title STRING COMMENT '商品名',
gmt_modified STRING COMMENT '商品最後修改日期',
starts STRING COMMENT '商品上架時間',
price DOUBLE COMMENT '商品價格,單位元',
city STRING COMMENT '商品所在城市',
prov STRING COMMENT '商品所在省份',
quantity BIGINT COMMENT '數量',
stuff_status BIGINT COMMENT '商品新舊程度 0 全新 1 閒置 2 二手',
auction_status BIGINT COMMENT '商品狀態 0 正常 1 用戶刪除 2 下架 3 從未上架',
cate_id BIGINT COMMENT '商品類目ID',
cate_name STRING COMMENT '商品類目名稱',
commodity_id BIGINT COMMENT '品類ID',
commodity_name STRING COMMENT '品類名稱',
umid STRING COMMENT '買家umid'
)
COMMENT '商品正常購買ODS'
PARTITIONED BY (ds STRING COMMENT '格式:YYYYMMDD')
LIFECYCLE 400;
CREATE TABLE IF NOT EXISTS s_users_extra
(
id STRING COMMENT '用戶ID',
logincount BIGINT COMMENT '登錄次數',
buyer_goodnum BIGINT COMMENT '作爲買家的好評數',
seller_goodnum BIGINT COMMENT '作爲賣家的好評數',
level_type BIGINT COMMENT '1 一級店鋪 2 二級店鋪 3 三級店鋪',
promoted_num BIGINT COMMENT '1 A級服務 2 B級服務 3 C級服務',
gmt_create STRING COMMENT '創建時間',
order_id BIGINT COMMENT '訂單ID',
buyer_id BIGINT COMMENT '買家ID',
buyer_nick STRING COMMENT '買家暱稱',
buyer_star_id BIGINT COMMENT '買家星級 ID',
seller_id BIGINT COMMENT '賣家ID',
seller_nick STRING COMMENT '賣家暱稱',
seller_star_id BIGINT COMMENT '賣家星級ID',
shop_id BIGINT COMMENT '店鋪ID',
shop_name STRING COMMENT '店鋪名稱'
)
COMMENT '用戶擴展表'
PARTITIONED BY (ds STRING COMMENT 'yyyymmdd')
LIFECYCLE 400;
CREATE TABLE IF NOT EXISTS s_biz_order_delta
(
biz_order_id STRING COMMENT '訂單ID',
pay_order_id STRING COMMENT '支付訂單ID',
logistics_order_id STRING COMMENT '物流訂單ID',
buyer_nick STRING COMMENT '買家暱稱',
buyer_id STRING COMMENT '買家ID',
seller_nick STRING COMMENT '賣家暱稱',
seller_id STRING COMMENT '賣家ID',
auction_id STRING COMMENT '商品ID',
auction_title STRING COMMENT '商品標題 ',
auction_price DOUBLE COMMENT '商品價格',
buy_amount BIGINT COMMENT '購買數量',
buy_fee BIGINT COMMENT '購買金額',
pay_status BIGINT COMMENT '支付狀態 1 未付款 2 已付款 3 已退款',
logistics_id BIGINT COMMENT '物流訂單ID',
mord_cod_status BIGINT COMMENT '物流狀態 0 初始狀態 1 接單成功 2 接單超時3 攬收成功 4攬收失敗 5 簽收成功 6 簽收失敗 7 用戶取消物流訂單',
status BIGINT COMMENT '狀態 0 訂單正常 1 訂單不可見',
sub_biz_type BIGINT COMMENT '業務類型 1 拍賣 2 購買',
end_time STRING COMMENT '交易結束時間',
shop_id BIGINT COMMENT '店鋪ID'
)
COMMENT '交易成功訂單日增量表'
PARTITIONED BY (ds STRING COMMENT 'yyyymmdd')
LIFECYCLE 7200;
CREATE TABLE IF NOT EXISTS s_logistics_order_delta
(
logistics_order_id STRING COMMENT '物流訂單ID ',
post_fee DOUBLE COMMENT '物流費用',
address STRING COMMENT '收貨地址',
full_name STRING COMMENT '收貨人全名',
mobile_phone STRING COMMENT '移動電話',
prov STRING COMMENT '省份',
prov_code STRING COMMENT '省份ID',
city STRING COMMENT '市',
city_code STRING COMMENT '城市ID',
logistics_status BIGINT COMMENT '物流狀態
1 - 未發貨
2 - 已發貨
3 - 已收貨
4 - 已退貨
5 - 配貨中',
consign_time STRING COMMENT '發貨時間',
gmt_create STRING COMMENT '訂單創建時間',
shipping BIGINT COMMENT '發貨方式
1,平郵
2,快遞
3,EMS',
seller_id STRING COMMENT '賣家ID',
buyer_id STRING COMMENT '買家ID'
)
COMMENT '交易物流訂單日增量表'
PARTITIONED BY (ds STRING COMMENT '日期')
LIFECYCLE 7200;
CREATE TABLE IF NOT EXISTS s_pay_order_delta
(
pay_order_id STRING COMMENT '支付訂單ID',
total_fee DOUBLE COMMENT '應支付總金額 (數量*單價)',
seller_id STRING COMMENT '賣家ID',
buyer_id STRING COMMENT '買家ID',
pay_status BIGINT COMMENT '支付狀態
1等待買家付款,
2等待賣家發貨,
3交易成功',
pay_time STRING COMMENT '付款時間',
gmt_create STRING COMMENT '訂單創建時間',
refund_fee DOUBLE COMMENT '退款金額(包含運費)',
confirm_paid_fee DOUBLE COMMENT '已經確認收貨的金額'
)
COMMENT '交易支付訂單增量表'
PARTITIONED BY (ds STRING COMMENT '日期')
LIFECYCLE 7200;
數據引入層存儲
爲了滿足歷史數據分析需求,您可以在 ODS 層表中添加時間維度作爲分區字段。實際應用中,您可以選擇採用增量、全量存儲或拉鍊存儲的方式。
-
增量存儲
以天爲單位的增量存儲,以業務日期作爲分區,每個分區存放日增量的業務數據。舉例如下:
說明 交易、日誌等事務性較強的 ODS 表適合增量存儲方式。這類表數據量較大,採用全量存儲的方式存儲成本壓力大。此外,這類表的下游應用對於歷史全量數據訪問的需求較小(此類需求可通過數據倉庫後續彙總後得到)。例如,日誌類 ODS 表沒有數據更新的業務過程,因此所有增量分區 UNION 在一起就是一份全量數據。
-
1 月 1 日,用戶 A 訪問了 A 公司電商店鋪 B,A 公司電商日誌產生一條記錄 t1。1 月 2 日,用戶 A 又訪問了 A 公司電商店鋪 C,A 公司電商日誌產生一條記錄 t2。採用增量存儲方式,t1 將存儲在 1 月 1 日這個分區中,t2 將存儲在 1 月 2 日這個分區中。
-
1 月 1 日,用戶 A 在 A 公司電商網購買了 B 商品,交易日誌將生成一條記錄 t1。1 月 2 日,用戶 A 又將 B 商品退貨了,交易日誌將更新 t1 記錄。採用增量存儲方式,初始購買的 t1 記錄將存儲在 1 月 1 日這個分區中,更新後的 t1 將存儲在 1 月 2 日這個分區中。
-
全量存儲
以天爲單位的全量存儲,以業務日期作爲分區,每個分區存放截止到業務日期爲止的全量業務數據。例如, 1 月 1 日,賣家 A 在 A 公司電商網發佈了 B、C 兩個商品,前端商品表將生成兩條記錄 t1、t2。1 月 2 日,賣家 A 將 B 商品下架了,同時又發佈了商品 D,前端商品表將更新記錄 t1,同時新生成記錄 t3。採用全量存儲方式, 在 1 月 1 日這個分區中存儲 t1 和 t2 兩條記錄,在 1 月 2 日這個分區中存儲更新後的 t1 以及 t2、t3 記錄。
說明 對於小數據量的緩慢變化維度數據,例如商品類目,可直接使用全量存儲。
-
拉鍊存儲
拉鍊存儲通過新增兩個時間戳字段(start_dt 和 end_dt),將所有以天爲粒度的變更數據都記錄下來,通常分區字段也是這兩個時間戳字段。
拉鍊存儲舉例如下。
| 商品 | start_dt | end_dt | 賣家 | 狀態 | | --- | --- | --- | --- | --- | | B | 20160101 | 20160102 | A | 上架 | | C | 20160101 | 30001231 | A | 上架 | | B | 20160102 | 30001231 | A | 下架 |
這樣,下游應用可以通過限制時間戳字段來獲取歷史數據。例如,用戶訪問 1 月 1 日數據,只需限制
start_dt<=20160101
並且end_dt>20160101
。
緩慢變化維度
MaxCompute 不推薦使用代理鍵,推薦使用自然鍵作爲維度主鍵,主要原因有兩點:
-
MaxCompute 是分佈式計算引擎,生成全局唯一的代理鍵工作量非常大。當遇到大數據量情況下,這項工作就會更加複雜,且沒有必要。
-
使用代理鍵會增加 ETL 的複雜性,從而增加 ETL 任務的開發和維護成本。
在不使用代理鍵的情況下,緩慢變化維度可以通過快照方式處理。
快照方式下數據的計算週期通常爲每天一次。基於該週期,處理維度變化的方式爲每天一份全量快照。
例如商品維度,每天保留一份全量商品快照數據。任意一天的事實表均可以取到當天的商品信息,也可以取到最新的商品信息,通過限定日期,採用自然鍵進行關聯即可。該方式的優勢主要有以下兩點:
-
處理緩慢變化維度的方式簡單有效,開發和維護成本低。
-
使用方便,易於理解。數據使用方只需要限定日期即可取到當天的快照數據。任意一天的事實快照與任意一天的維度快照通過維度的自然鍵進行關聯即可。
該方法的弊端主要是存儲空間的極大浪費。例如某維度每天的變化量佔總體數據量比例很低,極端情況下,每天無變化,這種情況下存儲浪費嚴重。該方法主要實現了通過犧牲存儲獲取 ETL 效率的優化和邏輯上的簡化。請避免過度使用該方法,且必須要有對應的數據生命週期制度,清除無用的歷史數據。
數據同步加載與處理
ODS 的數據需要由各數據源系統同步到 MaxCompute,才能用於進一步的數據開發。本教程建議您使用 DataWorks 數據集成功能完成數據同步,詳情請參見概述。在使用數據集成的過程中,建議您遵循以下規範:
-
一個系統的源表只允許同步到 MaxCompute 一次,保持表結構的一致性。
-
數據集成僅用於離線全量數據同步,實時增量數據同步需要您使用數據傳輸服務 DTS 實現,詳情請參見數據傳輸服務 DTS。
-
數據集成全量同步的數據直接進入全量表的當日分區。
-
ODS 層的表建議以統計日期及時間分區表的方式存儲,便於管理數據的存儲成本和策略控制。
-
數據集成可以自適應處理源系統字段的變更:
-
如果源系統字段的目標表在 MaxCompute 上不存在,可以由數據集成自動添加不存在的表字段。
-
如果目標表的字段在源系統不存在,數據集成填充 NULL。
公共維度彙總層(DIM)
公共維度彙總層(DIM)基於維度建模理念,建立整個企業的一致性維度。
公共維度彙總層(DIM)主要由維度表(維表)構成。維度是邏輯概念,是衡量和觀察業務的角度。維表是根據維度及其屬性將數據平臺上構建的表物理化的表,採用寬表設計的原則。因此,構建公共維度彙總層(DIM)首先需要定義維度。
定義維度
在劃分數據域、構建總線矩陣時,需要結合對業務過程的分析定義維度。以本教程中 A 電商公司的營銷業務板塊爲例,在交易數據域中,我們重點考察確認收貨(交易成功)的業務過程。
在確認收貨的業務過程中,主要有商品和收貨地點(本教程中,假設收貨和購買是同一個地點)兩個維度所依賴的業務角度。從商品角度可以定義出以下維度:
-
商品 ID
-
商品名稱
-
商品價格
-
商品新舊程度:0 - 全新、1 - 閒置、 2 - 二手
-
商品類目 ID
-
商品類目名稱
-
品類 ID
-
品類名稱
-
買家 ID
-
商品狀態:0 - 正常、1 - 用戶刪除、2 - 下架、3 - 從未上架
-
商品所在城市
-
商品所在省份
從地域角度,可以定義出以下維度:
-
買家 ID
-
城市 code
-
城市名稱
-
省份 code
-
省份名稱
作爲維度建模的核心,在企業級數據倉庫中必須保證維度的唯一性。以 A 公司的商品維度爲例,有且只允許有一種維度定義。例如,省份 code 這個維度,對於任何業務過程所傳達的信息都是一致的。
設計維表
完成維度定義後,您就可以對維度進行補充,進而生成維表了。維表的設計需要注意:
-
建議維表單表信息不超過 1000 萬條。
-
維表與其他表進行 Join 時,建議您使用 Map Join。
-
避免過於頻繁的更新維表的數據。
在設計維表時,您需要從下列方面進行考慮:
-
維表中數據的穩定性。例如 A 公司電商會員通常不會出現消亡,但會員數據可能在任何時候更新,此時要考慮創建單個分區存儲全量數據。如果存在不會更新的記錄,您可能需要分別創建歷史表與日常表。日常表用於存放當前有效的記錄,保持表的數據量不會膨脹;歷史表根據消亡時間插入對應分區,使用單個分區存放分區對應時間的消亡記錄。
-
是否需要垂直拆分。如果一個維表存在大量屬性不被使用,或由於承載過多屬性字段導致查詢變慢,則需考慮對字段進行拆分,創建多個維表。
-
是否需要水平拆分。如果記錄之間有明顯的界限,可以考慮拆成多個表或設計成多級分區。
-
核心的維表產出時間通常有嚴格的要求。
設計維表的主要步驟如下:
-
完成維度的初步定義,並保證維度的一致性。
-
確定主維表(中心事實表,本教程中採用星型模型)。此處的主維表通常是數據引入層(ODS)表,直接與業務系統同步。例如,s_auction 是與前臺商品中心繫統同步的商品表,此表即是主維表。
-
確定相關維表。數據倉庫是業務源系統的數據整合,不同業務系統或者同一業務系統中的表之間存在關聯性。根據對業務的梳理,確定哪些表和主維表存在關聯關係,並選擇其中的某些表用於生成維度屬性。以商品維度爲例,根據對業務邏輯的梳理,可以得到商品與類目、賣家、店鋪等維度存在關聯關係。
-
確定維度屬性,主要包括兩個階段。第一個階段是從主維表中選擇維度屬性或生成新的維度屬性;第二個階段是從相關維表中選擇維度屬性或生成新的維度屬性。以商品維度爲例,從主維表(s_auction)和類目 、賣家、店鋪等相關維表中選擇維度屬性或生成新的維度屬性。
-
儘可能生成豐富的維度屬性。
-
儘可能多地給出富有意義的文字性描述。
-
區分數值型屬性和事實。
-
儘量沉澱出通用的維度屬性。
公共維度彙總層(DIM)維表規範
公共維度彙總層(DIM)維表命名規範:dim_{業務板塊名稱 / pub}_{維度定義}[_{自定義命名標籤}],所謂 pub 是與具體業務板塊無關或各個業務板塊都可公用的維度,如時間維度。舉例如下:
-
公共區域維表 dim_pub_area
-
A 公司電商板塊的商品全量表 dim_asale_itm
建表示例
本例中,最終的維表建表語句如下所示。
CREATE TABLE IF NOT EXISTS dim_asale_itm
(
item_id BIGINT COMMENT '商品ID',
item_title STRING COMMENT '商品名稱',
item_price DOUBLE COMMENT '商品成交價格_元',
item_stuff_status BIGINT COMMENT '商品新舊程度_0全新1閒置2二手',
cate_id BIGINT COMMENT '商品類目ID',
cate_name STRING COMMENT '商品類目名稱',
commodity_id BIGINT COMMENT '品類ID',
commodity_name STRING COMMENT '品類名稱',
umid STRING COMMENT '買家ID',
item_status BIGINT COMMENT '商品狀態_0正常1用戶刪除2下架3未上架',
city STRING COMMENT '商品所在城市',
prov STRING COMMENT '商品所在省份'
)
COMMENT '商品全量表'
PARTITIONED BY (ds STRING COMMENT '日期,yyyymmdd');
CREATE TABLE IF NOT EXISTS dim_pub_area
(
buyer_id STRING COMMENT '買家ID',
city_code STRING COMMENT '城市code',
city_name STRING COMMENT '城市名稱',
prov_code STRING COMMENT '省份code',
prov_name STRING COMMENT '省份名稱'
)
COMMENT '公共區域維表'
PARTITIONED BY (ds STRING COMMENT '日期分區,格式yyyymmdd')
LIFECYCLE 3600;
明細粒度事實層(DWD)
明細粒度事實層以業務過程驅動建模,基於每個具體的業務過程特點,構建最細粒度的明細層事實表。您可以結合企業的數據使用特點,將明細事實表的某些重要維度屬性字段做適當冗餘,即寬表化處理。
公共彙總粒度事實層(DWS)和明細粒度事實層(DWD)的事實表作爲數據倉庫維度建模的核心,需緊繞業務過程來設計。通過獲取描述業務過程的度量來描述業務過程,包括引用的維度和與業務過程有關的度量。度量通常爲數值型數據,作爲事實邏輯表的依據。事實邏輯表的描述信息是事實屬性,事實屬性中的外鍵字段通過對應維度進行關聯。
事實表中一條記錄所表達的業務細節程度被稱爲粒度。通常粒度可以通過兩種方式來表述:一種是維度屬性組合所表示的細節程度,一種是所表示的具體業務含義。
作爲度量業務過程的事實,通常爲整型或浮點型的十進制數值,有可加性、半可加性和不可加性三種類型:
-
可加性事實是指可以按照與事實表關聯的任意維度進行彙總。
-
半可加性事實只能按照特定維度彙總,不能對所有維度彙總。例如庫存可以按照地點和商品進行彙總,而按時間維度把一年中每個月的庫存累加則毫無意義。
-
完全不可加性,例如比率型事實。對於不可加性的事實,可分解爲可加的組件來實現聚集。
事實表相對維表通常更加細長,行增加速度也更快。維度屬性可以存儲到事實表中,這種存儲到事實表中的維度列稱爲維度退化,可加快查詢速度。與其他存儲在維表中的維度一樣,維度退化可以用來進行事實表的過濾查詢、實現聚合操作等。
明細粒度事實層(DWD)通常分爲三種:事務事實表、週期快照事實表和累積快照事實表,詳情請參見數倉建設指南。
-
事務事實表用來描述業務過程,跟蹤空間或時間上某點的度量事件,保存的是最原子的數據,也稱爲原子事實表。
-
週期快照事實表以具有規律性的、可預見的時間間隔記錄事實。
-
累積快照事實表用來表述過程開始和結束之間的關鍵步驟事件,覆蓋過程的整個生命週期,通常具有多個日期字段來記錄關鍵時間點。當累積快照事實表隨着生命週期不斷變化時,記錄也會隨着過程的變化而被修改。
明細粒度事實表設計原則
明細粒度事實表設計原則如下所示:
-
通常,一個明細粒度事實表僅和一個維度關聯。
-
儘可能包含所有與業務過程相關的事實 。
-
只選擇與業務過程相關的事實。
-
分解不可加性事實爲可加的組件。
-
在選擇維度和事實之前必須先聲明粒度。
-
在同一個事實表中不能有多種不同粒度的事實。
-
事實的單位要保持一致。
-
謹慎處理 Null 值。
-
使用退化維度提高事實表的易用性。
明細粒度事實表整體設計流程如下圖所示。
在一致性度量中已定義好了交易業務過程及其度量。明細事實表注意針對業務過程進行模型設計。明細事實表的設計可以分爲四個步驟:選擇業務過程、確定粒度、選擇維度、確定事實(度量)。粒度主要是在維度未展開的情況下記錄業務活動的語義描述。在您建設明細事實表時,需要選擇基於現有的表進行明細層數據的開發,清楚所建表記錄存儲的是什麼粒度的數據。
明細粒度事實層(DWD)規範
通常您需要遵照的命名規範爲:dwd_{業務板塊 / pub}_{數據域縮寫}_{業務過程縮寫}[_{自定義表命名標籤縮寫}] _{單分區增量全量標識},pub 表示數據包括多個業務板塊的數據。單分區增量全量標識通常爲:i 表示增量,f 表示全量。例如:dwd_asale_trd_ordcrt_trip_di(A 電商公司航旅機票訂單下單事實表,日刷新增量)及 dwd_asale_itm_item_df(A 電商商品快照事實表,日刷新全量)。
本教程中,DWD 層主要由三個表構成:
-
交易商品信息事實表:dwd_asale_trd_itm_di。
-
交易會員信息事實表:ods_asale_trd_mbr_di。
-
交易訂單信息事實表:dwd_asale_trd_ord_di。
DWD 層數據存儲及生命週期管理規範請參見 CDM 明細層設計規範。
建表示例
本教程中充分使用了維度退化以提升查詢效率,建表語句如下所示。
CREATE TABLE IF NOT EXISTS dwd_asale_trd_itm_di
(
item_id BIGINT COMMENT '商品ID',
item_title STRING COMMENT '商品名稱',
item_price DOUBLE COMMENT '商品價格',
item_stuff_status BIGINT COMMENT '商品新舊程度_0全新1閒置2二手',
item_prov STRING COMMENT '商品省份',
item_city STRING COMMENT '商品城市',
cate_id BIGINT COMMENT '商品類目ID',
cate_name STRING COMMENT '商品類目名稱',
commodity_id BIGINT COMMENT '品類ID',
commodity_name STRING COMMENT '品類名稱',
buyer_id BIGINT COMMENT '買家ID',
)
COMMENT '交易商品信息事實表'
PARTITIONED BY (ds STRING COMMENT '日期')
LIFECYCLE 400;
CREATE TABLE IF NOT EXISTS ods_asale_trd_mbr_di
(
order_id BIGINT COMMENT '訂單ID',
bc_type STRING COMMENT '業務分類',
buyer_id BIGINT COMMENT '買家ID',
buyer_nick STRING COMMENT '買家暱稱',
buyer_star_id BIGINT COMMENT '買家星級ID',
seller_id BIGINT COMMENT '賣家ID',
seller_nick STRING COMMENT '賣家暱稱',
seller_star_id BIGINT COMMENT '賣家星級ID',
shop_id BIGINT COMMENT '店鋪ID',
shop_name STRING COMMENT '店鋪名稱'
)
COMMENT '交易會員信息事實表'
PARTITIONED BY (ds STRING COMMENT '日期')
LIFECYCLE 400;
CREATE TABLE IF NOT EXISTS dwd_asale_trd_ord_di
(
order_id BIGINT COMMENT '訂單ID',
pay_order_id BIGINT COMMENT '支付訂單ID',
pay_status BIGINT COMMENT '支付狀態_1未付款2已付款3已退款',
succ_time STRING COMMENT '訂單交易結束時間',
item_id BIGINT COMMENT '商品ID',
item_quantity BIGINT COMMENT '購買數量',
confirm_paid_amt DOUBLE COMMENT '訂單已經確認收貨的金額',
logistics_id BIGINT COMMENT '物流訂單ID',
mord_prov STRING COMMENT '收貨人省份',
mord_city STRING COMMENT '收貨人城市',
mord_lgt_shipping BIGINT COMMENT '發貨方式_1平郵2快遞3EMS',
mord_address STRING COMMENT '收貨人地址',
mord_mobile_phone STRING COMMENT '收貨人手機號',
mord_fullname STRING COMMENT '收貨人姓名',
buyer_nick STRING COMMENT '買家暱稱',
buyer_id BIGINT COMMENT '買家ID'
)
COMMENT '交易訂單信息事實表'
PARTITIONED BY (ds STRING COMMENT '日期')
LIFECYCLE 400;
公共彙總粒度事實層(DWS)
公共彙總粒度事實層以分析的主題對象作爲建模驅動,基於上層的應用和產品的指標需求構建公共粒度的彙總指標事實表。公共彙總層的一個表通常會對應一個派生指標。
公共彙總事實表設計原則
聚集是指針對原始明細粒度的數據進行彙總。DWS 公共彙總層是面向分析對象的主題聚集建模。在本教程中,最終的分析目標爲:最近一天某個類目(例如:廚具)商品在各省的銷售總額、該類目 Top10 銷售額商品名稱、各省用戶購買力分佈。因此,我們可以以最終交易成功的商品、類目、買家等角度對最近一天的數據進行彙總。
注意
-
聚集是不跨越事實的。聚集是針對原始星形模型進行的彙總。爲獲取和查詢與原始模型一致的結果,聚集的維度和度量必須與原始模型保持一致,因此聚集是不跨越事實的。
-
聚集會帶來查詢性能的提升,但聚集也會增加 ETL 維護的難度。當子類目對應的一級類目發生變更時,先前存在的、已經被彙總到聚集表中的數據需要被重新調整。
此外,進行 DWS 層設計時還需遵循以下原則:
-
數據公用性:需考慮彙總的聚集是否可以提供給第三方使用。您可以判斷,基於某個維度的聚集是否經常用於數據分析中。如果答案是肯定的,就有必要把明細數據經過彙總沉澱到聚集表中。
-
不跨數據域。數據域是在較高層次上對數據進行分類聚集的抽象。數據域通常以業務過程進行分類,例如交易統一劃到交易域下, 商品的新增、修改放到商品域下。
-
區分統計週期。在表的命名上要能說明數據的統計週期,例如_1d 表示最近 1 天,td 表示截至當天,nd 表示最近 N 天。
公共彙總事實表規範
公共彙總事實表命名規範:dws_{業務板塊縮寫 / pub}_{數據域縮寫}_{數據粒度縮寫}[_{自定義表命名標籤縮寫}]_{統計時間週期範圍縮寫}。
-
關於統計實際週期範圍縮寫,缺省情況下,離線計算應該包括最近一天(_1d),最近 N 天(_nd)和歷史截至當天(_td)三個表。如果出現_nd 的表字段過多需要拆分時,只允許以一個統計週期單元作爲原子拆分。即一個統計週期拆分一個表,例如最近 7 天(_1w)拆分一個表。不允許拆分出來的一個表存儲多個統計週期。
-
對於小時表(無論是天刷新還是小時刷新),都用_hh 來表示。
-
對於分鐘表(無論是天刷新還是小時刷新),都用_mm 來表示。
舉例如下:
-
dws_asale_trd_byr_subpay_1d(A 電商公司買家粒度交易分階段付款一日彙總事實表)
-
dws_asale_trd_byr_subpay_td(A 電商公司買家粒度分階段付款截至當日彙總表)
-
dws_asale_trd_byr_cod_nd(A 電商公司買家粒度貨到付款交易彙總事實表)
-
dws_asale_itm_slr_td(A 電商公司賣家粒度商品截至當日存量彙總表)
-
dws_asale_itm_slr_hh(A 電商公司賣家粒度商品小時彙總表)--- 維度爲小時
-
dws_asale_itm_slr_mm(A 電商公司賣家粒度商品分鐘彙總表)--- 維度爲分鐘
DWS 層數據存儲及生命週期管理規範請參見 CDM 彙總層設計規範。
建表示例
滿足業務需求的 DWS 層建表語句如下。
CREATE TABLE IF NOT EXISTS dws_asale_trd_byr_ord_1d
(
buyer_id BIGINT COMMENT '買家ID',
buyer_nick STRING COMMENT '買家暱稱',
mord_prov STRING COMMENT '收貨人省份',
cate_id BIGINT COMMENT '商品類目ID',
cate_name STRING COMMENT '商品類目名稱',
confirm_paid_amt_sum_1d DOUBLE COMMENT '最近一天訂單已經確認收貨的金額總和'
)
COMMENT '買家粒度所有交易最近一天彙總事實表'
PARTITIONED BY (ds STRING COMMENT '分區字段YYYYMMDD')
LIFECYCLE 36000;
CREATE TABLE IF NOT EXISTS dws_asale_trd_itm_ord_1d
(
item_id BIGINT COMMENT '商品ID',
item_title STRING COMMENT '商品名稱',
cate_id BIGINT COMMENT '商品類目ID',
cate_name STRING COMMENT '商品類目名稱',
mord_prov STRING COMMENT '收貨人省份',
confirm_paid_amt_sum_1d DOUBLE COMMENT '最近一天訂單已經確認收貨的金額總和'
)
COMMENT '商品粒度交易最近一天彙總事實表'
PARTITIONED BY (ds STRING COMMENT '分區字段YYYYMMDD')
LIFECYCLE 36000;
(五)層次調用規範
在完成數據倉庫的分層後,您需要對各層次的數據之間的調用關係作出約定。
層次調用規範
ADS 應用層優先調用數據倉庫公共層數據。如果已經存在 CDM 層數據,不允許 ADS 應用層跨過 CDM 中間層從 ODS 層重複加工數據。CDM 中間層應該積極瞭解應用層數據的建設需求,將公用的數據沉澱到公共層,爲其他數據層次提供數據服務。同時,ADS 應用層也需積極配合 CDM 中間層進行持續的數據公共建設的改造。避免出現過度的 ODS 層引用、不合理的數據複製和子集合冗餘。總體遵循的層次調用原則如下:
-
ODS 層數據不能直接被應用層任務引用。如果中間層沒有沉澱的 ODS 層數據,則通過 CDM 層的視圖訪問。CDM 層視圖必須使用調度程序進行封裝,保持視圖的可維護性與可管理性。
-
CDM 層任務的深度不宜過大(建議不超過 10 層)。
-
一個計算刷新任務只允許一個輸出表,特殊情況除外。
-
如果多個任務刷新輸出一個表(不同任務插入不同的分區),DataWorks 上需要建立一個虛擬任務,依賴多個任務的刷新和輸出。通常,下游應該依賴此虛擬任務。
-
CDM 彙總層優先調用 CDM 明細層,可累加指標計算。CDM 彙總層儘量優先調用已經產出的粗粒度彙總層,避免大量彙總層數據直接從海量的明細數據層中計算得出。
-
CDM 明細層累計快照事實表優先調用 CDM 事務型事實表,保持數據的一致性產出。
-
有針對性地建設 CDM 公共彙總層,避免應用層過度引用和依賴 CDM 層明細數據。
本文由 Readfog 進行 AMP 轉碼,版權歸原作者所有。
來源:https://mp.weixin.qq.com/s/pt_w4AMQb05HwoRWmUFsig