ETL 字段拆分映射练习
场景说明
本练习模拟一个典型的 ETL 场景：从旧系统导出的未范式化数据，需要拆分成多个范式化的目标表。
源表采用扁平化设计，复合字段用分隔符（|、-）拼接；目标表遵循 3NF 范式，字段独立存储。
数据流向图
flowchart LR
subgraph 源端
S[source_orders<br/>源表]
end
subgraph 转换规则
R1["SPLIT 分隔符拆分"]
R2["类型转换"]
R3["一对多拆分"]
end
subgraph 目标端
C["dim_customer<br/>客户维度表"]
A["dim_address<br/>地址维度表"]
O["fact_order_items<br/>订单商品事实表"]
end
S -->|"customer_info<br/>张三|Mr.|28"| R1
S -->|"address_full<br/>广东省|深圳市..."| R1
S -->|"product_info<br/>P001-笔记本...|P002-鼠标..."| R3
S -->|"order_date<br/>String→Date"| R2
R1 --> C
R1 --> A
R3 --> O
R2 --> O
ER 图（目标表关系）
erDiagram
dim_customer ||--o{ dim_address : "1:N"
dim_customer ||--o{ fact_order_items : "1:N"
dim_address ||--o{ fact_order_items : "1:N"
dim_customer {
int customer_id PK
varchar customer_name
varchar title
int age
int source_id UK
date insert_date
}
dim_address {
int address_id PK
varchar province
varchar city
varchar district
varchar street
int source_id UK
date insert_date
}
fact_order_items {
int item_id PK
int source_id
varchar order_no
varchar product_code
varchar product_name
int quantity
decimal unit_price
decimal total_price
date order_date
date insert_date
}
表结构详解
源表：source_orders
| 字段 | 类型 | 说明 | 示例 |
|---|---|---|---|
| id | INT | 主键 | 1 |
| raw_data | VARCHAR(500) | 原始数据行 | order_001 |
| customer_info | VARCHAR(100) | 客户信息合并 | 张三|Mr.|28 |
| address_full | VARCHAR(200) | 地址信息合并 | 广东省|深圳市|南山区|科技路123号 |
| product_info | VARCHAR(300) | 商品信息合并（多商品用|分隔） | P001-笔记本电脑-2-5999.00|P002-无线鼠标-3-199.00 |
| order_date | VARCHAR(20) | 订单日期（字符串） | 2026-04-01 |
| created_at | TIMESTAMP | 记录创建时间 | 2026-04-23 10:00:00 |
分隔符说明：
|— 同一字段内多值分隔-— 商品信息内部字段分隔
目标表1：dim_customer（客户维度表）
| 字段 | 类型 | 说明 | 来源 |
|---|---|---|---|
| customer_id | INT | 主键（自增） | 自增 |
| customer_name | VARCHAR(50) | 客户姓名 | customer_info[0] |
| title | VARCHAR(10) | 称谓 | customer_info[1] |
| age | INT | 年龄 | customer_info[2] |
| source_id | INT | 源表外键（唯一约束） | id |
| insert_date | DATE | ETL处理日期 | CURRENT_DATE |
目标表2：dim_address（地址维度表）
| 字段 | 类型 | 说明 | 来源 |
|---|---|---|---|
| address_id | INT | 主键（自增） | 自增 |
| province | VARCHAR(50) | 省 | address_full[0] |
| city | VARCHAR(50) | 市 | address_full[1] |
| district | VARCHAR(50) | 区/县 | address_full[2] |
| street | VARCHAR(200) | 街道详细地址 | address_full[3] |
| source_id | INT | 源表外键（唯一约束） | id |
| insert_date | DATE | ETL处理日期 | CURRENT_DATE |
目标表3：fact_order_items（订单商品事实表）
| 字段 | 类型 | 说明 | 来源 |
|---|---|---|---|
| item_id | INT | 主键（自增） | 自增 |
| source_id | INT | 源表外键 | id |
| order_no | VARCHAR(50) | 订单号 | raw_data（如 order_001 → 取 _ 后部分） |
| product_code | VARCHAR(50) | 商品编码 | product_info[*][0] |
| product_name | VARCHAR(100) | 商品名称 | product_info[*][1] |
| quantity | INT | 数量 | product_info[*][2] |
| unit_price | DECIMAL(10,2) | 单价 | product_info[*][3] |
| total_price | DECIMAL(10,2) | 小计（计算字段） | quantity × unit_price |
| order_date | DATE | 订单日期 | order_date（类型转换） |
| insert_date | DATE | ETL处理日期 | CURRENT_DATE |
字段映射规则
一、SPLIT 拆分映射（源1:1目标）
graph LR
subgraph customer_info
A["张三|Mr.|28"]
end
subgraph SPLIT
B0["[0] 张三"]
B1["[1] Mr."]
B2["[2] 28"]
end
subgraph dim_customer
C1["customer_name"]
C2["title"]
C3["age"]
end
A --> B0 --> C1
A --> B1 --> C2
A --> B2 --> C3
转换公式：
customer_name = SPLIT(customer_info, '|')[0]
title = SPLIT(customer_info, '|')[1]
age = CAST(SPLIT(customer_info, '|')[2] AS INT)
二、一对多拆分映射（源1:N目标）
这是 ETL 中最复杂的场景：一条源记录拆分成多条目标记录。
graph LR
%% 修复1：节点内容换行建议使用 <br> 标签，避免原始换行符导致解析错误
subgraph "拆分前"
P["product_info<br>P001-笔记本电脑-2-5999.00|<br>P002-无线鼠标-3-199.00"]
end
%% 修复2：标题包含特殊符号 | 必须加引号
subgraph "Step1 按|拆分"
L1["P001-笔记本电脑-2-5999.00"]
L2["P002-无线鼠标-3-199.00"]
end
%% 修复3：标题包含特殊符号 - 建议加引号
%% 修复4：节点内容中的英文逗号 , 必须替换为中文逗号 ， 或空格
subgraph "Step2 按-拆分"
R1["P001，笔记本电脑，2，5999.00"]
R2["P002，无线鼠标，3，199.00"]
end
%% 修复5：标题包含下划线 _ 建议加引号
subgraph "fact_order_items"
%% 修复6：节点 ID 不能以数字开头 (01 -> O1)
O1["item_id=1, code=P001..."]
O2["item_id=2, code=P002..."]
end
%% 修复7：连接线不能串联 (A->B->C 是错误的)，必须拆分为两行
P -->|"拆分为2行"| L1
P -->|"拆分为2行"| L2
L1 -->|"按-拆分"| R1
R1 --> O1
L2 -->|"按-拆分"| R2
R2 --> O2
转换公式：
1. 先按 '|' 拆分为多行
2. 每行按 '-' 拆分：
- product_code = SPLIT_ITEM(行, '-')[0]
- product_name = SPLIT_ITEM(行, '-')[1]
- quantity = CAST(SPLIT_ITEM(行, '-')[2] AS INT)
- unit_price = CAST(SPLIT_ITEM(行, '-')[3] AS DECIMAL)
3. total_price = quantity × unit_price
数据预览
源表数据（etl_source_orders.csv）
id,raw_data,customer_info,address_full,product_info,order_date
1,order_001,张三|Mr.|28,广东省|深圳市|南山区|科技路123号,P001-笔记本电脑-2-5999.00,2026-04-01
2,order_002,李四|Ms.|35,北京市|北京市|朝阳区|建国路456号,P002-无线鼠标-3-199.00|P003-键盘膜-3-49.00,2026-04-02
3,order_003,王五|Mr.|42,浙江省|杭州市|西湖区|文一路789号,P004-显示器-1-1999.00|P005-USBHub-2-89.00,2026-04-03
4,order_004,赵六|Ms.|31,江苏省|南京市|鼓楼区,中山路321号,P001-笔记本电脑-1-5999.00|P006-电脑包-1-299.00,2026-04-04
5,order_005,孙七|Mr.|26,上海市|上海市|浦东新区|世纪大道111号,P007-耳机-5-299.00|P008-鼠标垫-5-39.00,2026-04-05
目标表期望结果
dim_customer（5行）
customer_id,customer_name,title,age,source_id,insert_date
1,张三,Mr.,28,1,2026-04-23
2,李四,Ms.,35,2,2026-04-23
3,王五,Mr.,42,3,2026-04-23
4,赵六,Ms.,31,4,2026-04-23
5,孙七,Mr.,26,5,2026-04-23
dim_address（5行）
address_id,province,city,district,street,source_id,insert_date
1,广东省,深圳市,南山区,科技路123号,1,2026-04-23
2,北京市,北京市,朝阳区,建国路456号,2,2026-04-23
3,浙江省,杭州市,西湖区,文一路789号,3,2026-04-23
4,江苏省,南京市,鼓楼区,中山路321号,4,2026-04-23
5,上海市,上海市,浦东新区,世纪大道111号,5,2026-04-23
fact_order_items（9行 — 注意一对多）
item_id,source_id,order_no,product_code,product_name,quantity,unit_price,total_price,order_date,insert_date
1,1,order_001,P001,笔记本电脑,2,5999.00,11998.00,2026-04-01,2026-04-23
2,2,order_002,P002,无线鼠标,3,199.00,597.00,2026-04-02,2026-04-23
3,2,order_002,P003,键盘膜,3,49.00,147.00,2026-04-02,2026-04-23
4,3,order_003,P004,显示器,1,1999.00,1999.00,2026-04-03,2026-04-23
5,3,order_003,P005,USBHub,2,89.00,178.00,2026-04-03,2026-04-23
6,4,order_004,P001,笔记本电脑,1,5999.00,5999.00,2026-04-04,2026-04-23
7,4,order_004,P006,电脑包,1,299.00,299.00,2026-04-04,2026-04-23
8,5,order_005,P007,耳机,5,299.00,1495.00,2026-04-05,2026-04-23
9,5,order_005,P008,鼠标垫,5,39.00,195.00,2026-04-05,2026-04-23
SQL 建表语句
源表
CREATE TABLE source_orders (
id INT PRIMARY KEY,
raw_data VARCHAR(500) COMMENT '原始未处理的数据行',
customer_info VARCHAR(100) COMMENT '客户信息(姓名|称谓|年龄合并)',
address_full VARCHAR(200) COMMENT '完整地址(省|市|区|街道合并)',
product_info VARCHAR(300) COMMENT '商品信息(编码-名称-数量-单价合并)',
order_date VARCHAR(20) COMMENT '订单日期',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
目标表
-- 客户维度表
CREATE TABLE dim_customer (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
customer_name VARCHAR(50) NOT NULL COMMENT '客户姓名',
title VARCHAR(10) COMMENT '称谓(Mr./Ms.)',
age INT COMMENT '年龄',
source_id INT COMMENT '源表ID',
insert_date DATE COMMENT 'ETL处理日期',
UNIQUE KEY uk_source (source_id)
);
-- 地址维度表
CREATE TABLE dim_address (
address_id INT PRIMARY KEY AUTO_INCREMENT,
province VARCHAR(50) COMMENT '省',
city VARCHAR(50) COMMENT '市',
district VARCHAR(50) COMMENT '区/县',
street VARCHAR(200) COMMENT '街道详细地址',
source_id INT COMMENT '源表ID',
insert_date DATE COMMENT 'ETL处理日期',
UNIQUE KEY uk_source (source_id)
);
-- 订单商品事实表
CREATE TABLE fact_order_items (
item_id INT PRIMARY KEY AUTO_INCREMENT,
source_id INT COMMENT '源表ID',
order_no VARCHAR(50) COMMENT '订单号',
product_code VARCHAR(50) COMMENT '商品编码',
product_name VARCHAR(100) COMMENT '商品名称',
quantity INT COMMENT '数量',
unit_price DECIMAL(10,2) COMMENT '单价',
total_price DECIMAL(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED COMMENT '小计',
order_date DATE COMMENT '订单日期',
insert_date DATE COMMENT 'ETL处理日期',
INDEX idx_order (order_date),
INDEX idx_product (product_code)
);
导入 MySQL 步骤
# 1. 创建数据库
CREATE DATABASE etl_demo;
USE etl_demo;
# 2. 执行建表语句
source etl_demo_source.sql;
source etl_demo_target.sql;
# 3. 导入CSV数据
LOAD DATA INFILE 'etl_source_orders.csv'
INTO TABLE source_orders
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
映射速查表
三、字符串截取映射
有些字段无法用分隔符拆分，需要根据固定位置截取。
graph LR
subgraph raw_data
A["order_001"]
end
subgraph SUBSTRING解析
B1["位置1-5: order"]
B2["位置6: _"]
B3["位置7-10: 001"]
end
subgraph order_no
C["001"]
end
A -->|"raw_data[7] 从第7位开始取"| B3 --> C
转换公式：
order_no = SUBSTRING(raw_data, 7)
= 从第7位开始截取到末尾
= "order_001".substring(6) (Java 0-based索引)
Java/ETL 中的索引说明：
| 语言/环境 | 索引方式 | 示例 | 说明 |
|---|---|---|---|
| SQL SUBSTRING(str, pos) | 1-based | SUBSTRING('order_001', 7) | 从第7位开始，1-based |
| Java String.substring(index) | 0-based | "order_001".substring(6) | 从索引6开始，0-based |
实际数据对比：
| raw_data | SUBSTRING(raw_data, 7) 结果 |
|---|---|
| order_001 | 001 |
| order_002 | 002 |
| order_003 | 003 |
| order_999 | 999 |
映射速查表
| 目标表 | 目标字段 | 来源字段 | 转换规则 |
|---|---|---|---|
| dim_customer | customer_name | customer_info | SPLIT('|')[0] |
| dim_customer | title | customer_info | SPLIT('|')[1] |
| dim_customer | age | customer_info | CAST(SPLIT('|')[2] AS INT) |
| dim_address | province | address_full | SPLIT('|')[0] |
| dim_address | city | address_full | SPLIT('|')[1] |
| dim_address | district | address_full | SPLIT('|')[2] |
| dim_address | street | address_full | SPLIT('|')[3] |
| fact_order_items | order_no | raw_data | SUBSTRING(raw_data, 7) |
| fact_order_items | product_code | product_info | SPLIT('-')[0] (先按|拆行) |
| fact_order_items | product_name | product_info | SPLIT('-')[1] (先按|拆行) |
| fact_order_items | quantity | product_info | CAST(SPLIT('-')[2] AS INT) |
| fact_order_items | unit_price | product_info | CAST(SPLIT('-')[3] AS DECIMAL) |
| fact_order_items | total_price | - | quantity × unit_price |
文件清单
| 文件名 | 说明 |
|---|---|
etl_source_orders.csv |
源表CSV数据 |
etl_dim_customer_expected.csv |
目标表1期望结果 |
etl_dim_address_expected.csv |
目标表2期望结果 |
etl_fact_order_items_expected.csv |
目标表3期望结果 |
etl_demo_source.sql |
源表建表+数据SQL |
etl_demo_target.sql |
目标表建表SQL |
浙公网安备 33010602011771号