一、数据仓库概述
数据仓库是一个面向主题的、集成的、非易失的、随时间变化的数据集合，用于支持管理决策。数据仓库与操作型数据库的主要区别在于其面向分析而非事务处理。
二、数据仓库架构
2.1 经典架构
graph TD
A[数据源] --> B[ODS层]
B --> C[DWD层]
C --> D[DWS层]
D --> E[ADS层]
E --> F[数据应用]
A --> A1[业务数据库]
A --> A2[日志数据]
A --> A3[外部数据]
B --> B1[原始数据]
C --> C1[明细数据]
D --> D1[汇总数据]
E --> E1[应用数据]
2.2 分层说明
| 层级 | 名称 | 作用 | 数据特征 |
|---|---|---|---|
| ODS | 操作数据存储 | 原始数据采集 | 原始、无加工 |
| DWD | 明细数据层 | 数据清洗转换 | 清洗、关联 |
| DWS | 汇总数据层 | 数据聚合 | 轻度汇总 |
| ADS | 应用数据层 | 面向应用 | 高度汇总 |
三、维度建模
3.1 星型模型
星型模型是最常用的维度建模方式，由一个事实表和多个维度表组成：
erDiagram
FACT_SALES {
int sale_id PK
int product_id FK
int customer_id FK
int store_id FK
date sale_date
decimal amount
int quantity
}
DIM_PRODUCT {
int product_id PK
string product_name
string category
decimal price
}
DIM_CUSTOMER {
int customer_id PK
string customer_name
string city
string gender
}
DIM_STORE {
int store_id PK
string store_name
string region
}
FACT_SALES ||--o{ DIM_PRODUCT : "product_id"
FACT_SALES ||--o{ DIM_CUSTOMER : "customer_id"
FACT_SALES ||--o{ DIM_STORE : "store_id"
3.2 雪花模型
雪花模型是星型模型的扩展，维度表可以进一步规范化：
erDiagram
FACT_SALES {
int sale_id PK
int product_id FK
int customer_id FK
int store_id FK
date sale_date
decimal amount
}
DIM_PRODUCT {
int product_id PK
string product_name
int category_id FK
decimal price
}
DIM_CATEGORY {
int category_id PK
string category_name
int parent_category_id FK
}
DIM_CUSTOMER {
int customer_id PK
string customer_name
int city_id FK
}
DIM_CITY {
int city_id PK
string city_name
int region_id FK
}
FACT_SALES ||--o{ DIM_PRODUCT : "product_id"
FACT_SALES ||--o{ DIM_CUSTOMER : "customer_id"
DIM_PRODUCT ||--o{ DIM_CATEGORY : "category_id"
DIM_CUSTOMER ||--o{ DIM_CITY : "city_id"
3.3 星型模型vs雪花模型
| 特性 | 星型模型 | 雪花模型 |
|---|---|---|
| 查询性能 | 高 | 低 |
| 数据冗余 | 高 | 低 |
| 维护复杂度 | 低 | 高 |
| 适用场景 | 数据仓库 | 数据集市 |
四、ETL流程设计
4.1 ETL概述
ETL（Extract-Transform-Load）是数据仓库的核心流程：
flowchart TD
A[Extract抽取] --> B[Transform转换]
B --> C[Load加载]
A --> A1[从数据源读取]
A --> A2[数据格式识别]
B --> B1[数据清洗]
B --> B2[数据转换]
B --> B3[数据验证]
C --> C1[写入目标]
C --> C2[数据校验]
4.2 Extract抽取阶段
从各种数据源抽取数据：
// 全量抽取
SELECT * FROM source_table;
// 增量抽取
SELECT * FROM source_table WHERE update_time > 'last_sync_time';
// CDC抽取（基于日志）
// 使用Debezium、Canal等工具4.3 Transform转换阶段
对数据进行清洗、转换和验证：
// 数据清洗
SELECT
id,
TRIM(name) AS name,
CASE WHEN email IS NULL THEN '' ELSE email END AS email,
DATE_FORMAT(create_time, 'yyyy-MM-dd') AS create_date
FROM raw_data;
// 数据转换
SELECT
user_id,
CASE
WHEN age < 18 THEN '未成年'
WHEN age BETWEEN 18 AND 65 THEN '成年'
ELSE '老年'
END AS age_group
FROM user_data;
// 数据验证
SELECT * FROM transformed_data
WHERE amount IS NULL OR amount < 0;4.4 Load加载阶段
将转换后的数据加载到目标系统：
// 全量加载（覆盖）
TRUNCATE TABLE target_table;
INSERT INTO target_table SELECT * FROM transformed_data;
// 增量加载（追加）
INSERT INTO target_table SELECT * FROM transformed_data
WHERE id NOT IN (SELECT id FROM target_table);
// MERGE加载（更新或插入）
MERGE INTO target_table t
USING transformed_data s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);五、ETL工具对比
| 工具 | 优点 | 缺点 | 适用场景 |
|---|---|---|---|
| Apache NiFi | 可视化、可扩展 | 学习曲线陡 | 实时数据流 |
| Apache Airflow | 灵活、可调度 | 需要编码 | 批处理流程 |
| Talend | 可视化、丰富组件 | 商业版收费 | 企业级ETL |
| DataX | 开源、高性能 | 功能有限 | 数据迁移 |
| Spark | 高性能、灵活 | 需要编码 | 大数据ETL |
六、ETL最佳实践
6.1 数据质量保障
在ETL流程中加入数据质量检查：
// 数据质量规则
1. 非空检查：必填字段不能为空
2. 格式检查：日期格式、邮箱格式等
3. 范围检查：数值在合理范围内
4. 唯一检查：唯一键不重复
5. 引用检查：外键引用存在6.2 增量同步策略
选择合适的增量同步策略：
| 策略 | 原理 | 适用场景 |
|---|---|---|
| 时间戳 | 基于更新时间 | 常规增量 |
| 日志CDC | 基于数据库日志 | 实时同步 |
| 快照对比 | 对比前后快照 | 无时间戳字段 |
6.3 ETL调度与监控
使用调度工具管理ETL任务：
graph TD
A[调度器] --> B[任务1]
A --> C[任务2]
A --> D[任务3]
B --> E[成功]
B --> F[失败]
C --> G[成功]
C --> H[失败]
E --> I[监控告警]
F --> J[重试/告警]
G --> I
H --> J
6.4 ETL性能优化
- 并行处理：使用多线程并行处理数据
- 批量操作：使用批量插入和更新
- 分区处理：按分区并行处理
- 压缩传输：压缩数据减少网络传输
- 索引管理：加载时禁用索引，加载后重建
七、数据仓库选型
| 数据仓库 | 优点 | 缺点 | 适用场景 |
|---|---|---|---|
| Hive | 开源、兼容Hadoop | 延迟高 | 离线分析 |
| Impala | 实时查询 | 资源消耗大 | 实时分析 |
| ClickHouse | 高性能、列式存储 | 写入性能一般 | 实时分析 |
| Snowflake | 云原生、弹性扩展 | 价格高 | 企业级 |
八、总结
数据仓库和ETL是大数据分析的基础架构，合理的架构设计和ETL流程能够保障数据质量和分析效率。通过选择合适的维度建模方式和ETL工具，能够构建高效的数据仓库系统，支持企业的决策分析需求。