老板讓我負責數倉 DIM 層建設，我說我不會
作者：甜獲師兄
本文將從理論到實踐，深入講解DIM層的建設原理、設計模式及具體實現方法，以我們項目中的實際案例為例進行說明。
不會是不可能的,hhh，今天我們來看看怎么建設dim層。
數據倉庫維度層(DIM層)是連接數據倉庫ODS原始數據與后續分析層的關鍵橋梁。本文將從理論到實踐，深入講解DIM層的建設原理、設計模式及具體實現方法，以我們項目中的實際案例為例進行說明。數倉代碼可訪問：
- github：https://github.com/Mrkuhuo/data-warehouse-learning
- gitee：https://gitee.com/wzylzjtn/data-warehouse-learning
一、DIM層的基本概念與作用
1. 什么是維度層(DIM層)？
維度層是數據倉庫架構中專門用于存儲和管理維度數據的層次，位于ODS層之上，為DWD、DWS和ADS層提供標準化的維度信息。維度數據描述業務實體的屬性特征，如商品、用戶、時間、地理位置等，它們與事實表(如訂單、支付)結合，構成完整的業務分析視圖。
2. DIM層的核心作用
提供統一維度視圖：
- 標準化企業維度定義，解決源系統維度不一致問題數據質量保障
- 對維度數據進行清洗、去重和一致性校驗提升查詢效率
- 預先整合維度信息，減少復雜分析時的表連接操作支持歷史追溯
- 記錄維度變化歷史，支持不同時間點的歷史分析降低冗余度
- 避免在各層重復維護維度屬性信息
3. 典型的DIM層表類型
DIM層表主要分為兩大類：常規維度表
(1) 如商品維度表、品牌維度表等，一般采用全量更新模式緩慢變化維度(SCD)
(2) 記錄隨時間變化的維度屬性，主要有三種類型：
- SCD Type 1：直接覆蓋更新，不保留歷史
- SCD Type 2：保留歷史版本，通過有效期標記區分
- SCD Type 3：保留有限歷史，通過額外字段存儲
二、DIM層表設計原則與策略
1. 表設計原則
用戶地址信息是電商系統的重要基礎數據，需要支持：
- 包含維度的所有關鍵屬性，滿足分析需求一致性
- 提供統一標準的維度定義，消除歧義穩定性
- 維度設計應具備穩定性，避免頻繁變更可用性
- 優化查詢效率，支持高并發訪問可追溯性
- 對于關鍵維度，保留歷史變更記錄
2. 更新策略選擇
根據維度變化特性選擇合適的更新策略：
- 適用于變化頻率低、數據量小的維度，如商品類目增量更新
- 適用于持續新增的維度，如新商品拉鏈表設計
- 適用于需要保留歷史版本的維度，如用戶屬性變化
三、案例分析：商品維度表實現
1. 表結構設計
以我們項目中的商品維度表(dim_sku_full)為例：
CREATE TABLE dim.dim_sku_full
(
`id` VARCHAR(255) COMMENT 'SKU ID，商品唯一標識',
`k1` DATE NOT NULL COMMENT '分區字段，數據日期',
`price` DECIMAL(16, 2) COMMENT '商品價格，單位元',
`sku_name` STRING COMMENT '商品名稱，展示用',
-- 其他商品屬性字段
`category3_id` STRING COMMENT '三級分類ID，最細粒度的商品分類',
`category3_name` STRING COMMENT '三級分類名稱，如"休閑男鞋"',
`category2_id` STRING COMMENT '二級分類ID，中間層級商品分類',
`category2_name` STRING COMMENT '二級分類名稱，如"男鞋"',
`category1_id` STRING COMMENT '一級分類ID，頂層商品分類',
`category1_name` STRING COMMENT '一級分類名稱，如"鞋靴"',
`tm_id` STRING COMMENT '品牌ID，品牌唯一標識',
`tm_name` STRING COMMENT '品牌名稱，如"Nike"、"Adidas"',
-- 擴展屬性字段
`attr_ids` ARRAY<int(11)> COMMENT '平臺屬性ID集合',
`sale_attr_ids` ARRAY<int(11)> COMMENT '銷售屬性ID集合',
`create_time` STRING COMMENT '創建時間，商品首次錄入時間'
)
ENGINE=OLAP
UNIQUE KEY(`id`,`k1`) -- 使用商品ID和日期作為聯合主鍵
PARTITION BY RANGE(`k1`) () -- 按日期范圍分區
DISTRIBUTED BY HASH(`id`) -- 按商品ID哈希分布2. 設計特點分析
- 寬表設計：整合了商品所有相關維度信息(SKU基本信息、分類信息、品牌信息)
- 多級分類：保留一、二、三級分類的ID和名稱，便于不同粒度的分析
- 分區策略：按日期分區，支持歷史版本管理和數據生命周期管理
- 復合主鍵：通過商品ID和日期組合，確保每天每個商品只有一個狀態
四、案例分析：用戶維度拉鏈表實現
1. 表結構設計
對于變化較為頻繁且需要歷史追溯的維度，如用戶信息，我們采用了拉鏈表設計。
CREATE TABLE dim.dim_user_zip
(
`id` VARCHAR(64) COMMENT '用戶ID，用戶唯一標識',
`k1` DATE NOT NULL COMMENT '分區字段，數據日期',
`login_name` STRING COMMENT '用戶登錄名，賬號名稱',
`nick_name` STRING COMMENT '用戶昵稱，用戶自定義展示名',
`name` STRING COMMENT '用戶真實姓名，已加密',
-- 其他用戶屬性字段
`start_date` STRING COMMENT '開始日期，當前版本生效開始日期',
`end_date` STRING COMMENT '結束日期，當前版本失效日期，9999-12-31表示當前有效版本'
)2. 拉鏈表處理邏輯
拉鏈表的核心在于ETL處理邏輯，需要處理兩種情況：初始化和增量更新。
(1) 初始化邏輯
-- 用戶維度拉鏈表初始化插入（設置歷史起點）
insert into dim.dim_user_zip(id, k1, login_name, ... , start_date, end_date)
select
id, -- 用戶ID
k1, -- 分區日期
login_name, -- 用戶登錄名
-- 其他字段
md5(name), -- 用戶真實姓名（MD5加密處理）
md5(phone_num), -- 手機號碼（MD5加密處理）
md5(email), -- 電子郵箱（MD5加密處理）
-- 其他字段
'2020-06-14' start_date, -- 拉鏈起始日期（業務起始日期）
'9999-12-31' end_date -- 拉鏈結束日期（當前有效版本標記）
from ods.ods_user_info_full;(2) 增量更新邏輯
拉鏈表的增量更新是其核心價值所在，我們來看實際項目中的邏輯代碼：
insert into dim.dim_user_zip(id, k1, login_name, ... , start_date, end_date)
with
tmp as
(
select
-- 當前拉鏈表最新有效數據
old.id old_id, -- 原用戶ID
-- 其他old字段
old.end_date old_end_date, -- 原版本結束日期
-- 新增數據(當日變化)
new.id new_id, -- 新用戶ID
-- 其他new字段
new.end_date new_end_date -- 新版本結束日期
from
-- 查詢當前拉鏈表中最新有效記錄
(
select * from dim.dim_user_zip
where end_date = '9999-12-31' -- 篩選當前有效版本
) old
-- 使用FULL OUTER JOIN合并新舊數據
full outer join
-- 當日增量數據處理
(
select
cast(t1.id as VARCHAR(64)) as id, -- 轉換用戶ID類型
-- 其他字段處理
md5(t1.name) as name, -- 敏感信息加密
-- 其他字段
'2024-06-15' as start_date, -- 新版本開始日期
'9999-12-31' as end_date -- 新版本結束日期
from
(-- 取每個用戶最新記錄
select id, k1, ... , row_number() over (partition by id order by create_time desc) rn
from ods.ods_user_info_full
) t1
where rn=1 -- 只取每個用戶的最新記錄
) new
on old.id=new.id -- 按用戶ID關聯
)
-- 查詢1：處理有變更的用戶數據，生成新版本記錄
select
if(new_id is not null, new_id, old_id), -- 用戶ID
-- 其他字段選擇邏輯
if(new_id is not null, new_start_date, old_start_date), -- 版本開始日期
if(new_id is not null, new_end_date, old_end_date) -- 版本結束日期
from tmp
where k2 is not NULL -- 只處理有新分區數據的記錄
union all
-- 查詢2：處理原記錄的歷史版本化，更新結束日期
select
old_id, -- 用戶ID
-- 其他原字段
old_start_date, -- 版本開始日期
cast(date_add(date('${pdate}'), -1) as string) old_end_date -- 更新版本結束日期為當前日期前一天
from tmp
where k1 is not NULL -- 有原分區數據
and old_id is not null -- 有原用戶ID
and new_id is not null; -- 同時有新用戶ID，說明是變更記錄3. 拉鏈表更新原理解析
拉鏈表的核心在于ETL拉鏈表更新的核心原理在于：保留當前記錄
- 將原有記錄的結束日期修改為變更前一天插入新版本
- 創建新記錄，開始日期為變更當天，結束日期為'9999-12-31'新增用戶處理
- 對于新增的用戶，直接插入記錄，無需處理歷史版本使用FULL JOIN
- 確保既能處理變更用戶，也能處理新增用戶
DIM層作為數據倉庫的關鍵組成部分，其設計質量直接影響整個數據倉庫的可用性和分析效率。通過正確選擇維度表類型、設計合理的更新策略，以及實施有效的優化措施，可以構建出高質量的維度層，為上層分析提供堅實基礎。
責任編輯：趙寧寧
來源：
大數據技能圈
