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