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