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