27-学习笔记尚硅谷数仓搭建-数据仓库DWD层介绍及其事务表（行为）相关概念

目录

一、DWD层介绍

专业解释：

通俗解释：

举例：电商订单处理

二、DWD层事务表详解

1. 维度与度量值

2. 存储方式

3. 压缩方式

4. 数据域划分

三、事务表分类详解

1. 事务事实表（Transaction Fact Table）

2. 周期快照事实表（Periodic Snapshot Fact Table）

3. 累积快照事实表（Accumulating Snapshot Fact Table）

四、三类事实表对比总结

五、实际业务案例：电商订单全流程

六、最佳实践建议

1. 设计原则

2. 性能优化

3. 数据质量保障

一、DWD层介绍

专业解释：

DWD层（Data Warehouse Detail，数据仓库明细层）是数据仓库的核心层次，位于ODS层之后。它主要承担以下职责：

通俗解释：

DWD层就像厨房的备菜区，把从菜市场（ODS层）买来的原材料进行：

• 清洗（去掉泥沙坏叶）

• 切配（统一形状大小）

• 分装（按菜品需要备料）

这样厨师（DWS层）可以直接取用，不用每次从原始材料开始处理。

举例：电商订单处理

ODS原始数据： 订单表：order_id=1001, user_id="U001", amount="100.00元", create_time="2023-01-01 12:30:00" 用户表：user_id=1, name="张三", gender="M"

DWD处理后： order_id=1001, user_key=10001, user_name="张三", gender="男", amount=100.00, create_date_key=20230101, create_time="12:30:00"

二、DWD层事务表详解

1. 维度与度量值

专业解释：

• 维度：描述业务过程的上下文环境，如时间、地点、人物、商品等

• 度量值：可量化的业务数值，可进行聚合计算，如金额、数量、次数

通俗解释：

• 维度：回答"谁？什么时候？在哪里？"等描述性问题

• 度量值：回答"多少？多大？多频繁？"等数值问题

电商订单示例：

CREATE TABLE dwd.fact_order_detail (
— 维度（描述性字段）
order_id BIGINT, — 订单ID
user_key INT, — 用户维度代理键
product_key INT, — 商品维度代理键
date_key INT, — 日期维度键（如20230101）
store_key INT, — 店铺维度键

— 退化维度（常用维度字段直接存储）
user_level VARCHAR(10), — 用户等级
product_category VARCHAR(50), — 商品分类

— 度量值（可计算的数值）
order_amount DECIMAL(10,2), — 订单金额（可求和）
product_count INT, — 商品数量（可求和）
discount_amount DECIMAL(10,2) — 优惠金额（可求和）
);

2. 存储方式

专业选择：

• 列式存储：Parquet、ORC格式，适合分析查询

• 分区策略：按时间（天/月）分区，提高查询效率

• 分桶策略：对常用查询字段分桶，优化JOIN性能

通俗解释： 就像图书馆的书架管理：

• 列式存储：把所有书的作者信息放一起，把所有书名放一起，查作者时只需看一个区域

• 分区：按年份分书架，查2023年数据只需看一个书架

• 分桶：按作者姓氏字母分格，找"张"姓作者很快

存储示例：

— Parquet列式存储 + 日期分区 + 用户分桶
CREATE TABLE dwd.fact_order_detail (
— 字段定义…
)
PARTITIONED BY (dt STRING) — 按天分区
CLUSTERED BY (user_key) INTO 10 BUCKETS — 按用户分10个桶
STORED AS PARQUET — Parquet格式
TBLPROPERTIES (
'parquet.compression'='SNAPPY', — 压缩方式
'parquet.block.size'='256MB' — 块大小
);

3. 压缩方式

专业方案对比：

通俗解释： 就像打包行李：

• Snappy：快速打包，虽然箱子有点大，但取东西快

• Gzip：仔细打包，箱子最小，但打包和拆包都慢

• ZSTD：新型智能打包，箱子小且速度不慢

压缩设置示例：

— Hive中设置压缩
SET hive.exec.compress.output=true;
SET mapred.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;

— 建表时指定压缩
CREATE TABLE dwd.fact_order_detail
STORED AS PARQUET
TBLPROPERTIES ('parquet.compression'='SNAPPY');

4. 数据域划分

专业的数据域设计：

— 电商平台典型数据域划分
dwd.trade_order_detail — 交易域：订单明细
dwd.trade_payment_detail — 交易域：支付明细
dwd.trade_refund_detail — 交易域：退款明细
dwd.user_behavior_log — 用户域：行为日志
dwd.product_sku_daily — 商品域：SKU日粒度
dwd.marketing_coupon_use — 营销域：优惠券使用
dwd.logistics_delivery_trace — 物流域：配送轨迹

通俗解释： 就像公司的部门划分：

• 交易域：销售部门，负责订单、支付

• 用户域：客服部门，管理用户信息行为

• 商品域：采购部门，管理商品库存

• 营销域：市场部门，管理活动优惠

• 物流域：物流部门，管理配送

三、事务表分类详解

1. 事务事实表（Transaction Fact Table）

专业解释： 记录业务过程发生的事件，每行对应一个业务事件。特点是：

• 数据一旦产生不再变化

• 粒度最细，记录原始业务事件

• 主要用于分析事件发生情况

通俗解释： 就像银行的流水账，记录每一笔存取款：

• 存100元 → 记一条

• 取50元 → 记一条

• 永不修改，只追加新记录

创建步骤：

— 步骤1：创建表结构
CREATE TABLE dwd.fact_order_transaction (
— 代理键和维度键
order_detail_key BIGINT COMMENT '订单明细代理键',
order_id STRING COMMENT '订单业务ID',
user_key INT COMMENT '用户维度代理键',
product_key INT COMMENT '商品维度代理键',
date_key INT COMMENT '日期维度代理键',
time_key INT COMMENT '时间维度代理键(小时分钟)',

— 退化维度
user_level STRING COMMENT '用户等级',
product_category STRING COMMENT '商品分类',

— 度量值
order_amount DECIMAL(10,2) COMMENT '订单金额',
product_quantity INT COMMENT '商品数量',
discount_amount DECIMAL(10,2) COMMENT '优惠金额',

— 分区字段
dt STRING COMMENT '分区日期'
)
PARTITIONED BY (dt)
STORED AS PARQUET;

— 步骤2：ETL处理逻辑（伪代码）
1. 从ODS层抽取订单原始数据
2. 清洗：去重、去空值、格式化金额
3. 关联维度：用户维度获取user_key，商品维度获取product_key
4. 生成代理键：order_detail_key = 时间戳 + 序列号
5. 数据转换：金额单位统一、时间格式标准化
6. 写入分区：按dt=YYYY-MM-DD分区存储

2. 周期快照事实表（Periodic Snapshot Fact Table）

专业解释： 按固定时间间隔记录业务状态，如每日、每周、每月快照。特点是：

• 定期记录状态，无论是否有变化

• 用于分析趋势和状态变化

• 常见于余额、库存等场景

通俗解释： 就像每天下班前拍一张仓库库存照片：

• 周一：库存100件 → 记录

• 周二：库存95件 → 记录

• 周三：库存98件 → 记录

• 无论是否变化，每天都要记录

创建步骤：

— 步骤1：创建商品库存日快照表
CREATE TABLE dwd.fact_inventory_daily_snapshot (
inventory_snapshot_key BIGINT COMMENT '库存快照代理键',
product_key INT COMMENT '商品维度键',
warehouse_key INT COMMENT '仓库维度键',
date_key INT COMMENT '日期键',

— 度量值（当天的状态）
opening_quantity INT COMMENT '期初库存',
closing_quantity INT COMMENT '期末库存',
incoming_quantity INT COMMENT '入库数量',
outgoing_quantity INT COMMENT '出库数量',

— 分区
dt STRING COMMENT '快照日期'
)
PARTITIONED BY (dt)
STORED AS PARQUET;

— 步骤2：ETL处理流程
1. 确定快照时间点：每天23:59:59
2. 获取期初库存：昨天的期末库存
3. 计算当日变动：汇总当天的出入库记录
4. 计算期末库存：期初 + 入库 – 出库
5. 生成快照记录：每个商品在每个仓库生成一条记录
6. 异常处理：处理负库存、数据延迟等情况

3. 累积快照事实表（Accumulating Snapshot Fact Table）

专业解释： 记录有生命周期的业务过程，从开始到结束不断更新。特点是：

• 一行记录代表一个完整业务过程

• 随着流程推进不断更新

• 记录各关键节点的时间戳

通俗解释： 就像快递追踪单：

• 下单：记录下单时间

• 发货：更新发货时间

• 运输：更新当前位置

• 签收：更新签收时间

• 同一张单子，不同时间更新不同字段

创建步骤：

— 步骤1：创建订单累积快照表
CREATE TABLE dwd.fact_order_accumulating (
order_key BIGINT COMMENT '订单代理键',
order_id STRING COMMENT '订单业务ID',
user_key INT COMMENT '用户键',

— 关键时间点
order_date_key INT COMMENT '下单日期',
order_time STRING COMMENT '下单时间',

pay_date_key INT COMMENT '支付日期',
pay_time STRING COMMENT '支付时间',
pay_amount DECIMAL(10,2) COMMENT '支付金额',

ship_date_key INT COMMENT '发货日期',
ship_time STRING COMMENT '发货时间',
logistics_company STRING COMMENT '物流公司',

receive_date_key INT COMMENT '签收日期',
receive_time STRING COMMENT '签收时间',
receiver_name STRING COMMENT '收货人',

— 状态跟踪
current_status STRING COMMENT '当前状态',
status_update_time TIMESTAMP COMMENT '状态更新时间',

— 度量值
total_amount DECIMAL(10,2) COMMENT '订单总额',
discount_amount DECIMAL(10,2) COMMENT '优惠金额',
actual_amount DECIMAL(10,2) COMMENT '实付金额',

— 分区（按下单日期分区）
dt STRING COMMENT '下单日期分区'
)
PARTITIONED BY (dt)
STORED AS PARQUET;

— 步骤2：ETL处理流程（增量更新）
1. 初始插入：新订单创建时插入记录，填充下单信息
2. 支付更新：支付发生时，更新pay_相关字段
3. 发货更新：发货时更新ship_相关字段
4. 签收更新：签收时更新receive_相关字段
5. 状态更新：每次更新同步更新current_status和更新时间
6. 延迟数据：处理乱序到达的数据，保证最终一致性

四、三类事实表对比总结

五、实际业务案例：电商订单全流程

— 1. 事务事实表：记录每次支付事件
CREATE TABLE dwd.fact_payment_transaction (
payment_id BIGINT,
order_id BIGINT,
user_key INT,
payment_date_key INT,
payment_time STRING,
payment_method STRING, — 支付方式
payment_amount DECIMAL(10,2),
payment_status STRING,
dt STRING
);

— 2. 周期快照表：每日用户余额快照
CREATE TABLE dwd.fact_user_balance_daily (
user_key INT,
date_key INT,
opening_balance DECIMAL(15,2), — 期初余额
closing_balance DECIMAL(15,2), — 期末余额
total_income DECIMAL(15,2), — 当日收入
total_expense DECIMAL(15,2), — 当日支出
dt STRING
);

— 3. 累积快照表：订单全生命周期
CREATE TABLE dwd.fact_order_lifecycle (
order_key BIGINT,
user_key INT,
— 时间节点
create_time TIMESTAMP,
pay_time TIMESTAMP,
ship_time TIMESTAMP,
confirm_time TIMESTAMP,
refund_time TIMESTAMP,
— 时长计算
pay_duration_seconds INT, — 下单到支付时长
ship_duration_seconds INT, — 支付到发货时长
delivery_duration_seconds INT, — 发货到确认时长
— 当前状态
current_status STRING,
last_update_time TIMESTAMP,
dt STRING
);

六、最佳实践建议

1. 设计原则

• 一致性：同一业务过程在不同表中定义一致

• 完整性：确保关键业务过程都有对应事实表

• 可追溯：保留数据血缘，便于问题排查

2. 性能优化

— 分区策略优化
PARTITIONED BY (dt STRING, hour STRING) — 双级分区

— 索引优化
CREATE INDEX idx_user_date ON dwd.fact_order (user_key, date_key);

— 数据生命周期管理
ALTER TABLE dwd.fact_order_detail
DROP PARTITION (dt < '2023-01-01'); — 定期清理历史数据

3. 数据质量保障

— 增加数据质量检查字段
ALTER TABLE dwd.fact_order_detail ADD COLUMNS (
data_source STRING COMMENT '数据来源',
etl_batch_id STRING COMMENT 'ETL批次ID',
checksum_value STRING COMMENT '数据校验值',
quality_score DECIMAL(3,2) COMMENT '数据质量评分'
);

通过这样的分层设计，DWD层为上层应用提供了干净、一致、易用的数据基础，是数据仓库建设的核心环节。