做電商離線數(shù)倉DWD層永遠是最費心思的一層。ODS層的原始數(shù)據(jù)又雜又亂DWS層的指標又高度聚合中間這層事務(wù)事實表要是沒設(shè)計好后面所有統(tǒng)計口徑都會跟著歪。我之前刷到尚硅谷數(shù)倉搭建項目里第31篇筆記專門講DWD層一組事務(wù)事實表的建表語句和設(shè)計思路今天就把這個話題展開聊聊工具域優(yōu)惠券使用支付、互動域收藏商品、流量域頁面瀏覽、用戶域用戶注冊、用戶域用戶登錄這五張表在實際數(shù)倉項目里到底該怎么建、怎么用以及建表之后那些容易踩的坑。這篇內(nèi)容主要適合三類人正在系統(tǒng)學(xué)數(shù)倉建模、準備做離線數(shù)倉項目、或者已經(jīng)入行但想核對DWD層建表思路的數(shù)據(jù)開發(fā)同學(xué)。我會把每張表的事務(wù)粒度、字段設(shè)計、ETL加工要點都拆開講寫得不深但保證實用。1. 先想清楚這五張表為什么都叫“事務(wù)事實表”1.1 事務(wù)事實表和其他事實表的區(qū)別很多新手會把“事實表”和“明細表”混著叫但實際上事實表還能細分出三種類型。今天這五張表都屬于同一個類型事務(wù)事實表也就是記錄“每一件發(fā)生過的事”的表。一行數(shù)據(jù)代表一次業(yè)務(wù)事件比如一次優(yōu)惠券核銷、一次商品收藏、一次頁面瀏覽、一次用戶注冊、一次用戶登錄。與它對應(yīng)的是周期快照事實表典型場景是庫存快照、余額快照每天記錄一次“當前還剩多少”。還有累積快照事實表常用在訂單履約流程里一行訂單不斷更新下單、支付、發(fā)貨、完成這些環(huán)節(jié)的時間字段。用生活化的方式理解就是銀行卡流水是事務(wù)事實表每一筆進出賬都記一行余額是周期快照事實表每天記一次當前余額信用卡賬單還款進度則像累積快照表關(guān)注的是同一筆業(yè)務(wù)從開始到結(jié)束的完整鏈路。1.2 為什么不做成狀態(tài)表或用戶維表實際開發(fā)中經(jīng)常有人問用戶收藏商品不是有收藏狀態(tài)嗎直接同步一張收藏全量表不就行了注冊和登錄的信息在用戶維表里也有為什么還要單獨建表問題在于狀態(tài)表回答的是“當前是什么”回答不了“某一天發(fā)生了什么”。分析優(yōu)惠券核銷率需要知道每天在支付環(huán)節(jié)用了多少張券、每張券抵扣了多少錢分析用戶活躍需要知道一天有多少次登錄、分布在哪些時段分析商品收藏需要知道哪天收藏量突然漲了才能去追活動效果。這些都是過程指標只能靠事務(wù)事實表還原。DWD層的價值就是把這些業(yè)務(wù)過程展開成可分析的明細DWS和ADS才能在上面放心聚合。2. 建表語句逐張拆解建表之前先把五張表的核心設(shè)計信息列個總覽后面展開時不容易亂建議表名業(yè)務(wù)過程事務(wù)粒度主鍵/唯一鍵分區(qū)字段dwd_tool_coupon_pay優(yōu)惠券支付抵扣每次支付使用一張券coupon_use_iddtdwd_interaction_favor_add用戶收藏商品每次收藏事件favor_id 或 user_id sku_id create_timedtdwd_traffic_page_view用戶瀏覽頁面每次頁面訪問埋點日志唯一IDdtdwd_user_register用戶注冊每個用戶注冊一次user_iddtdwd_user_login用戶登錄每次登錄事件login_iddt這五張表里注冊表有點特殊它雖然是事務(wù)表但一個用戶一輩子通常只有一條數(shù)據(jù)所以更準確地說它是“一次性事件事務(wù)表”。登錄表和頁面瀏覽表則是典型的高頻事務(wù)表一天可能產(chǎn)生大量行。2.1 工具域優(yōu)惠券使用支付事務(wù)事實表先看建表語句CREATE TABLE IF NOT EXISTS dwd_tool_coupon_pay ( coupon_use_id STRING COMMENT 優(yōu)惠券使用記錄ID, order_id STRING COMMENT 關(guān)聯(lián)訂單ID, user_id STRING COMMENT 用戶ID, coupon_id STRING COMMENT 優(yōu)惠券ID, coupon_amount DECIMAL(16,2) COMMENT 優(yōu)惠券抵扣金額, payment_time STRING COMMENT 支付時間yyyy-MM-dd HH:mm:ss, province_id STRING COMMENT 收貨省份ID, coupon_type STRING COMMENT 優(yōu)惠券類型滿減/立減/折扣, source_type STRING COMMENT 優(yōu)惠券來源領(lǐng)取/系統(tǒng)發(fā)放/活動, etl_time STRING COMMENT ETL處理時間 ) COMMENT 工具域優(yōu)惠券使用支付事務(wù)事實表 PARTITIONED BY (dt STRING COMMENT 日期分區(qū)) STORED AS PARQUET TBLPROPERTIES (parquet.compressionSNAPPY);這張表記錄的核心事件是“優(yōu)惠券在支付環(huán)節(jié)被使用”。最關(guān)鍵的字段是coupon_use_id、order_id、coupon_id和coupon_amount。我建議把coupon_use_id當成唯一業(yè)務(wù)鍵因為一張訂單可能用多張券如果用order_id做主鍵數(shù)據(jù)會直接丟失。字段里冗余了province_id和coupon_type這屬于數(shù)倉建模里的“維度退化”操作。原本省份信息和券類型都能通過關(guān)聯(lián)維度表拿到但在DWD層直接冗余下來DWS層聚合時就能少好幾次join。數(shù)倉里性能問題往往就出在join太多所以像這種常用的分析維度能退化就退化。金額字段用DECIMAL(16,2)而不是DOUBLE是為了避免浮點精度問題。做優(yōu)惠券核銷金額匯總時哪怕差一分錢都可能被財務(wù)側(cè)找上門。時間字段用STRING存格式化字符串離線分析場景下完全夠用而且比TIMESTAMP更直觀。2.2 互動域收藏商品事務(wù)事實表收藏商品的建表語句相對簡潔CREATE TABLE IF NOT EXISTS dwd_interaction_favor_add ( favor_id STRING COMMENT 收藏記錄ID, user_id STRING COMMENT 用戶ID, sku_id STRING COMMENT 商品SKU ID, spu_id STRING COMMENT 商品SPU ID, create_time STRING COMMENT 收藏時間yyyy-MM-dd HH:mm:ss, is_favor STRING COMMENT 收藏狀態(tài)1收藏中0已取消, etl_time STRING COMMENT ETL處理時間 ) COMMENT 互動域收藏商品事務(wù)事實表 PARTITIONED BY (dt STRING COMMENT 日期分區(qū)) STORED AS PARQUET TBLPROPERTIES (parquet.compressionSNAPPY);收藏表很容易被誤解成“當前收藏狀態(tài)表”但這張表的定位是“收藏行為發(fā)生記錄”。我把收藏和取消收藏都看成收藏域的兩種業(yè)務(wù)事件如果只統(tǒng)計新增收藏就在ETL時過濾出收藏動作如果后續(xù)需要分析取消收藏也可以單獨做分析。之所以保留is_favor字段是為了讓這張表能兼容多種分析場景。但要注意這張表的行不代表當前收藏狀態(tài)比如用戶今天收藏、明天取消如果只查這張事務(wù)表會看到兩條記錄而不是“當前沒有收藏”。要實現(xiàn)“當前收藏夾”功能得另外做拉鏈表或直接同步業(yè)務(wù)庫全量表。冗余spu_id的原因和優(yōu)惠券表冗余省份一樣常見的收藏分析都是按SPU維度看的比如“被收藏最多的商品”直接從表里取spu_id能省一次SKU維表關(guān)聯(lián)。收藏表整體數(shù)據(jù)量不大多冗余一兩個字段對存儲影響很小。2.3 流量域頁面瀏覽事務(wù)事實表頁面瀏覽表是五張表里數(shù)據(jù)量最大、字段設(shè)計最需要克制的一張CREATE TABLE IF NOT EXISTS dwd_traffic_page_view ( page_id STRING COMMENT 頁面ID, page_name STRING COMMENT 頁面名稱, visit_time STRING COMMENT 頁面瀏覽時間yyyy-MM-dd HH:mm:ss, user_id STRING COMMENT 登錄用戶ID未登錄為空, session_id STRING COMMENT 會話ID, is_entry STRING COMMENT 是否入口頁1是 0否, is_exit STRING COMMENT 是否退出頁1是 0否, source_type STRING COMMENT 流量來源類型直接/搜索/廣告/分享, refer_url STRING COMMENT 來源URL, target_url STRING COMMENT 當前頁面URL, province_id STRING COMMENT 地域ID, device_type STRING COMMENT 設(shè)備類型PC/APP/H5, os_type STRING COMMENT 操作系統(tǒng), etl_time STRING COMMENT ETL處理時間 ) COMMENT 流量域頁面瀏覽事務(wù)事實表 PARTITIONED BY (dt STRING COMMENT 日期分區(qū)) STORED AS PARQUET TBLPROPERTIES (parquet.compressionSNAPPY);頁面瀏覽表的事務(wù)粒度是“一次頁面訪問”。埋點日志里字段非常多設(shè)備品牌、網(wǎng)絡(luò)類型、App版本、分辨率等都能拿到但建表時我只挑最常用的十幾個字段。原因很直接頁面瀏覽日志的體量是幾億行起步把所有字段都存進DWD層存儲成本會高得離譜而且大部分字段上層分析根本用不到。真要查設(shè)備品牌、網(wǎng)絡(luò)類型可以回ODS原始日志里去撈DWD層只保留高頻分析字段。session_id是這張表的靈魂它決定了一次會話內(nèi)頁面瀏覽怎么串聯(lián)。如果上游埋點沒有給session_idETL階段就要按用戶ID和時間間隔去劃分會話通常超過30分鐘沒有新動作就算新會話。這塊邏輯一般放到流量域?qū)ｉT處理。這里要注意未登錄用戶的user_id是空的但這類瀏覽日志不能丟。流量分析里匿名用戶的行為同樣重要后續(xù)可以用session_id作為分析主體等用戶登錄后再做用戶關(guān)聯(lián)。所以user_id字段不要設(shè)成非空約束DWD層沒有強約束但ETL里要保留空值。2.4 用戶域用戶注冊事務(wù)事實表注冊表的建表語句不是很復(fù)雜CREATE TABLE IF NOT EXISTS dwd_user_register ( user_id STRING COMMENT 用戶ID, username STRING COMMENT 用戶名, mobile STRING COMMENT 手機號已脫敏, email STRING COMMENT 郵箱, register_time STRING COMMENT 注冊時間yyyy-MM-dd HH:mm:ss, register_channel STRING COMMENT 注冊渠道APP/小程序/H5/PC, register_ip STRING COMMENT 注冊IP, province_id STRING COMMENT 注冊地省份ID, etl_time STRING COMMENT ETL處理時間 ) COMMENT 用戶域用戶注冊事務(wù)事實表 PARTITIONED BY (dt STRING COMMENT 日期分區(qū)) STORED AS PARQUET TBLPROPERTIES (parquet.compressionSNAPPY);注冊表的事務(wù)事件是“一個新用戶完成注冊”。正常業(yè)務(wù)里注冊是低頻事件一個用戶只有一次不過它在用戶增長分析中地位很高每天新增用戶數(shù)、各渠道拉新效果都要靠這張表。我看到有些團隊會把注冊信息直接并進用戶維表那樣做用戶維表確實更完整但要按天統(tǒng)計新增用戶時就得翻維表的變更記錄非常痛苦。不如單獨建一張注冊事務(wù)表要用戶最新信息時再關(guān)聯(lián)用戶維表各司其職。手機號和郵箱是敏感字段DWD層建表時就要考慮脫敏。我一般把手機號處理成前3后4的格式郵箱只保留用戶名首字符和域名既能支持渠道分析和用戶分群又避免把明文隱私落到分析環(huán)境。這個操作在ODS到DWD的ETL里完成而不是在下游再脫敏。2.5 用戶域用戶登錄事務(wù)事實表登錄表字段更簡潔CREATE TABLE IF NOT EXISTS dwd_user_login ( login_id STRING COMMENT 登錄記錄ID, user_id STRING COMMENT 用戶ID, login_time STRING COMMENT 登錄時間yyyy-MM-dd HH:mm:ss, login_channel STRING COMMENT 登錄渠道APP/小程序/H5/PC, login_ip STRING COMMENT 登錄IP, device_type STRING COMMENT 設(shè)備類型, app_version STRING COMMENT App版本號, etl_time STRING COMMENT ETL處理時間 ) COMMENT 用戶域用戶登錄事務(wù)事實表 PARTITIONED BY (dt STRING COMMENT 日期分區(qū)) STORED AS PARQUET TBLPROPERTIES (parquet.compressionSNAPPY);登錄表和頁面瀏覽表一樣是高頻事務(wù)表一個用戶一天可能登錄很多次。它最核心的用途是估算DAU、分析活躍時段、統(tǒng)計登錄渠道分布。用戶維表里的“最近登錄時間”只能看單個用戶最后一次登錄完全無法支撐活躍趨勢分析所以登錄流水必須單獨建表。login_channel和device_type看著有重疊但一個是業(yè)務(wù)入口一個是硬件環(huán)境。比如用戶可能在APP里通過微信授權(quán)登錄渠道是“微信登錄”設(shè)備類型是“手機”。這兩個維度都能單獨做分析所以都保留。登錄表的數(shù)據(jù)源一般是業(yè)務(wù)后端打的登錄日志。如果上游只有用戶表的last_login_time那說明日志鏈路沒打通需要反向推動業(yè)務(wù)側(cè)記錄登錄流水。數(shù)倉開發(fā)經(jīng)常要處理這種“上游沒數(shù)據(jù)”的尷尬情況提前知道這個坑能少走很多彎路。3. 從ODS到DWD的ETL加工要點建表語句只是第一步五張表能不能真正落地可用取決于ODS到DWD這段ETL寫得到不到位。3.1 公共清洗邏輯離線數(shù)倉的ODS層通常按天同步源系統(tǒng)數(shù)據(jù)或日志數(shù)據(jù)。寫入DWD前我至少會做三件事過濾無效數(shù)據(jù)、處理時間字段、去重。無效數(shù)據(jù)包括主鍵為空的記錄、核心業(yè)務(wù)字段為空的記錄、內(nèi)部測試數(shù)據(jù)。比如頁面瀏覽表如果page_id為空這日志基本沒法分析直接過濾。收藏表如果user_id或sku_id為空也直接丟棄。時間字段處理需要注意時區(qū)尤其是埋點日志里的13位時間戳。日志服務(wù)端可能記錄的是ts毫秒值需要轉(zhuǎn)成北京時間再落分區(qū)。我的寫法比較固定FROM_UNIXTIME(CAST(ts / 1000 AS BIGINT), yyyy-MM-dd HH:mm:ss) AS visit_time離線任務(wù)跑批通常用${bizdate}作為業(yè)務(wù)日期參數(shù)dt分區(qū)直接取這個值。ETL里查詢ODS時也用dt ${bizdate}保證批次之間不會互相干擾。去重是DWD層最容易出問題的地方。ODS數(shù)據(jù)因為上游重試、消息重復(fù)消費、同步任務(wù)重復(fù)執(zhí)行經(jīng)常有重復(fù)記錄。常見方案是用ROW_NUMBER()窗口函數(shù)按業(yè)務(wù)主鍵排序取rn1。比如優(yōu)惠券使用表INSERT OVERWRITE TABLE dwd_tool_coupon_pay PARTITION (dt ${bizdate}) SELECT coupon_use_id, order_id, user_id, coupon_id, coupon_amount, payment_time, province_id, coupon_type, source_type, etl_time FROM ( SELECT coupon_use_id, order_id, user_id, coupon_id, coupon_amount, payment_time, province_id, coupon_type, source_type, etl_time, ROW_NUMBER() OVER (PARTITION BY coupon_use_id ORDER BY etl_time DESC) AS rn FROM ods_tool_coupon_use WHERE dt ${bizdate} ) t WHERE t.rn 1;如果業(yè)務(wù)上沒有真正的業(yè)務(wù)主鍵比如頁面瀏覽日志就用日志埋點ID或者session_id 時間戳 頁面ID組合作為去重鍵。去重鍵越窄越容易誤刪數(shù)據(jù)越寬越容易留重復(fù)數(shù)據(jù)這個尺度要結(jié)合上游發(fā)送機制來定。3.2 各表加工的差異點五張表雖然都走清洗、轉(zhuǎn)換、去重的套路但細節(jié)差異很大。優(yōu)惠券支付表加工時重點校驗payment_time是否為空、coupon_amount是否大于0。如果一張訂單用了多張券上游通常會拆成多條記錄coupon_use_id必須有獨立值不能用order_id當主鍵否則會漏數(shù)據(jù)。還需要額外確認這個券到底有沒有被支付核銷有些券可能只是領(lǐng)取了但沒有在支付環(huán)節(jié)使用這類數(shù)據(jù)不能進入這張表。收藏表加工時要區(qū)分“新增收藏”和“取消收藏”。如果ODS層是收藏全量表在ETL里要對比前一天DWD表中的收藏記錄只有新增的收藏才寫入這張事務(wù)表。實際操作中可以用LEFT JOIN加空值判斷也可以用LAG()取上一次狀態(tài)。我建議用user_id sku_id作為判斷唯一鍵而不是依賴favor_id因為有些業(yè)務(wù)系統(tǒng)在取消再收藏時可能生成新的favor_id這時候用favor_id判斷會漏掉“重新收藏”的事件。頁面瀏覽表加工時日志解析邏輯最重。埋點日志通常是一整條JSON需要用get_json_object把公共字段和頁面字段拆出來。拆完字段后再過濾爬蟲流量和內(nèi)部測試流量。比如UserAgent里包含spider、bot關(guān)鍵字的日志要剔除。這里要格外注意過濾條件不能誤傷真實用戶。登錄表加工時重點是保留完整流水不要做“一個用戶只保留一條登錄記錄”的錯誤處理。登錄流水量大SELECT字段要克制不需要帶用戶名、郵箱只保留能定位用戶和登錄環(huán)境的字段即可。注冊表加工時要特別注意跨天回補。凌晨0點到8點的批任務(wù)經(jīng)常處理的是昨天的數(shù)據(jù)register_time是昨天但同步任務(wù)的業(yè)務(wù)日期可能已經(jīng)切到明天分區(qū)不能用current_date必須用調(diào)度參數(shù)${bizdate}統(tǒng)一控制。3.3 分區(qū)與存儲優(yōu)化這五張事務(wù)事實表我都建議用dt單分區(qū)每次跑批只覆蓋當天分區(qū)重跑歷史數(shù)據(jù)也不會污染其他天。存儲格式選PARQUET SNAPPY在查詢性能和存儲成本之間最平衡。如果數(shù)據(jù)量特別大比如頁面瀏覽表還可以考慮分區(qū)內(nèi)的數(shù)據(jù)分布優(yōu)化。INSERT時用DISTRIBUTE BY (user_id)讓相同用戶的數(shù)據(jù)落到同一個文件既有利于會話分析也能避免某個Reduce處理過多數(shù)據(jù)導(dǎo)致傾斜。不建議在這幾張表上用ORC之外的格式也不建議為了極致壓縮去用高壓縮比算法。離線分析最重要是列裁剪和謂詞下推PARQUET SNAPPY已經(jīng)能很好支持。字段類型上所有ID字段我建議統(tǒng)一用STRING。雖然BIGINT更省空間但來源系統(tǒng)的ID經(jīng)常出現(xiàn)前導(dǎo)零、超長整型、拼接字符串等情況用字符串最穩(wěn)妥。數(shù)倉是給人查數(shù)的不是給數(shù)據(jù)庫省空間的。4. 常見問題與排查實錄建表和ETL看著不難真正跑數(shù)倉任務(wù)的時候問題經(jīng)常藏在細節(jié)里。我挑幾個自己實際踩過的坑。4.1 優(yōu)惠券使用流水重復(fù)導(dǎo)致補貼金額翻倍有一次跑完DWS層的優(yōu)惠券核銷匯總發(fā)現(xiàn)補貼金額比業(yè)務(wù)后臺報表高了接近5%。排查下來發(fā)現(xiàn)不是計算邏輯錯而是上游在訂單支付回調(diào)時對優(yōu)惠券核銷接口重試了兩次ODS層同一張訂單帶了兩個相同的coupon_use_id。業(yè)務(wù)庫沒做唯一索引問題一直潛伏到數(shù)倉層才爆發(fā)。解決辦法就是前面提到的ROW_NUMBER()去重但去重鍵必須定成coupon_use_id不能圖省事用order_id否則一單多券的數(shù)據(jù)會被誤刪。另外建議在去重時按etl_time倒序取最新一條不要隨便取第一條因為上游可能出現(xiàn)先快照后更新的場景。4.2 收藏表一天重復(fù)寫入多條收藏事件收藏業(yè)務(wù)有“收藏”和“取消”兩個操作。如果ODS同步的是業(yè)務(wù)庫收藏流水問題不大如果同步的是收藏全量表直接把全量數(shù)據(jù)覆蓋進DWD事務(wù)表就會把歷史已存在且沒有變化的收藏記錄再次當成新事件寫進來。我的處理方式是在ETL中先按user_id sku_id取全量表最新狀態(tài)再用當前數(shù)據(jù)去和已經(jīng)落地的DWD表做匹配匹配不上的才作為新收藏事件寫入。這樣能保證事務(wù)表只記錄“新發(fā)生的收藏”而不是把全量快照當流水用。4.3 頁面瀏覽表出現(xiàn)嚴重數(shù)據(jù)傾斜頁面瀏覽日志體量大ETL里一旦涉及到按session_id聚合或join很容易因為少數(shù)大流量會話導(dǎo)致某個Reduce卡死。我遇到過某種異常腳本用同一個session_id刷了幾十萬條日志直接把當天任務(wù)拖垮。后續(xù)加了兩個措施第一在ETL里過濾明顯異常的session比如短時間內(nèi)訪問次數(shù)超過正常閾值的第二加“超大session拆分”邏輯當一個session訪問次數(shù)超過閾值時把session_id加后綴拆成多個不同session。閾值要看業(yè)務(wù)正常用戶行為分布來定一般50到100次比較合理不能設(shè)太低否則會把真實大促期間的用戶session拆碎。4.4 注冊表和登錄表的日期分區(qū)對不齊注冊表按register_time分區(qū)登錄表按login_time分區(qū)看起來沒問題。但曾經(jīng)有一次上游日志服務(wù)時區(qū)配置錯誤導(dǎo)致凌晨時段出現(xiàn)“注冊時間在昨天”但調(diào)度系統(tǒng)業(yè)務(wù)日期是“今天”的情況兩層數(shù)據(jù)對不上。后來在ETL里統(tǒng)一加了一層時間規(guī)范所有時間字段先轉(zhuǎn)成標準北京時間字符串再根據(jù)轉(zhuǎn)換后的時間計算對應(yīng)的dt分區(qū)。這樣每條記錄都屬于它真實發(fā)生的日期而不是同步執(zhí)行的日期。數(shù)倉里最怕的就是各表時間口徑不一致尤其是分區(qū)字段和業(yè)務(wù)時間字段混用。4.5 小文件過多拖慢查詢性能DWD層如果每天跑批而不控制文件數(shù)量一年下來小文件數(shù)量會非常驚人。特別是頁面瀏覽表一天產(chǎn)生的文件數(shù)可能上千查詢時頻繁掃描文件列表性能下降明顯。我現(xiàn)在的流程是每次INSERT OVERWRITE前先DISTRIBUTE BY一個能均勻打散的字段比如user_id讓每個Reduce寫出的文件大小相對均勻。再配合定期的小文件合并任務(wù)把小于一定閾值的小文件重寫到更大的文件里。對于頁面瀏覽表這種大表小文件控制尤其重要否則后患無窮。建表這件事看似簡單但事務(wù)事實表的設(shè)計質(zhì)量直接決定了DWS層好不好寫、ADS層準不準。我個人的習慣是每張DWD表建完后一定在表注釋里把事務(wù)粒度寫清楚這張表記錄什么事件一行代表什么唯一鍵是什么。這三個問題想明白了建表語句就是水到渠成的事。尤其是優(yōu)惠券支付、收藏、頁面瀏覽、注冊、登錄這五張表業(yè)務(wù)上看著不復(fù)雜但粒度定義差一點后面統(tǒng)計的口徑就會差很多。多寫幾行注釋比寫十頁設(shè)計文檔都管用。