大数据教程FG102-Hive数据仓库ETL数据清洗实战

本教程主要介绍Hive数据仓库中ETL数据清洗的实战方法和技巧，包括数据抽取、转换和加载的完整流程。风哥教程参考bigdata官方文档Hive SQL、ETL最佳实践等相关内容。

通过本教程的学习，您将掌握Hive数据仓库中ETL数据清洗的方法，提高数据质量，为数据分析和业务决策提供可靠的数据基础。

目录大纲

Part01-基础概念与理论知识
Part02-生产环境规划与建议
Part03-生产环境项目实施方案
Part04-生产案例与实战讲解
Part05-风哥经验总结与分享

Part01-基础概念与理论知识

1.1 ETL概念与流程

ETL（Extract-Transform-Load）是数据仓库中的核心流程，包括：

数据抽取（Extract）：从源系统获取数据
数据转换（Transform）：清洗、转换和标准化数据
数据加载（Load）：将处理后的数据加载到目标数据仓库

ETL流程的质量直接影响数据仓库的可用性和可靠性，学习交流加群风哥微信: itpux-com

1.2 Hive数据仓库架构

Hive数据仓库架构包括：

存储层：基于HDFS存储数据
元数据层：Hive Metastore管理元数据
查询引擎层：将SQL转换为MapReduce/Spark任务
接口层：HiveServer2提供JDBC/ODBC接口

1.3 数据质量问题分析

常见的数据质量问题包括：

数据缺失：某些字段值为空
数据重复：存在重复记录
数据格式错误：日期、数值等格式不正确
数据一致性问题：不同系统间数据不一致
数据异常：存在异常值或离群值

Part02-生产环境规划与建议

2.1 数据仓库设计

风哥提示：数据仓库设计应遵循星型模型或雪花模型，合理规划维度表和事实表，确保数据结构清晰。

数据仓库设计建议：

采用分层设计：ODS（操作数据存储）、DWD（数据仓库明细层）、DWS（数据仓库汇总层）、ADS（应用数据服务层）
合理设计分区策略：按时间、地区等维度分区
选择合适的存储格式：ORC、Parquet等列式存储格式
建立数据字典和元数据管理体系

2.2 ETL工具选择

常用的ETL工具：

Hive SQL：适合大规模数据处理
Sqoop：用于关系型数据库与Hadoop之间的数据传输
Flume：用于日志数据采集
Kafka：用于实时数据传输
Spark：用于快速数据处理

2.3 数据清洗策略

数据清洗策略：

缺失值处理：填充默认值、删除或插值
重复值处理：去重
格式转换：统一日期、时间、数值等格式
异常值处理：识别和处理异常值
数据标准化：统一编码和命名规范

Part03-生产环境项目实施方案

3.1 数据抽取方案

数据抽取方案：

# 使用Sqoop从MySQL抽取数据到Hive
#!/bin/bash
# extract_data.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

sqoop import \
–connect jdbc:mysql://192.168.1.100:3306/fgedudb \
–username fgedu \
–password fgedu123 \
–table sales \
–hive-import \
–hive-table fgedu.sales_ods \
–hive-overwrite \
–partition-key date \
–split-by id \
–num-mappers 4

3.2 数据转换实现

数据转换实现：

— 创建清洗后的表
CREATE TABLE fgedu.sales_dwd (
id STRING,
product_id STRING,
customer_id STRING,
amount DOUBLE,
sale_date STRING,
region STRING
)
PARTITIONED BY (dt STRING)
STORED AS ORC;

— 数据清洗转换
INSERT OVERWRITE TABLE fgedu.sales_dwd PARTITION (dt=’2026-04-08′)
SELECT
id,
product_id,
customer_id,
CASE
WHEN amount IS NULL THEN 0
ELSE amount
END AS amount,
FROM_UNIXTIME(UNIX_TIMESTAMP(sale_date, ‘yyyy-MM-dd’), ‘yyyy-MM-dd’) AS sale_date,
CASE
WHEN region IS NULL THEN ‘Unknown’
ELSE region
END AS region
FROM fgedu.sales_ods
WHERE dt=’2026-04-08′;

3.3 数据加载策略

数据加载策略：

全量加载：适用于小数据集，直接覆盖目标表
增量加载：适用于大数据集，只加载新增或变更数据
分区加载：按时间或其他维度分区，提高查询性能
合并加载：将增量数据与历史数据合并

Part04-生产案例与实战讲解

4.1 用户行为日志清洗实战

案例：清洗用户行为日志数据

# 查看原始日志数据

$ hdfs dfs -cat /user/fgedu/input/user_behavior.log | head -5
2026-04-08 10:00:00,1001,item001,click,192.168.1.1
2026-04-08 10:00:01,1002,,view,192.168.1.2
2026-04-08 10:00:02,1003,item003,purchase,192.168.1.3
2026-04-08 10:00:03,,item004,click,192.168.1.4
2026-04-08 10:00:04,1005,item005,view,192.168.1.5

# 创建外部表

CREATE EXTERNAL TABLE fgedu.user_behavior_raw (
log_time STRING,
user_id STRING,
item_id STRING,
behavior_type STRING,
ip STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,’
LOCATION ‘/user/fgedu/input’;

# 清洗数据并加载到目标表

CREATE TABLE fgedu.user_behavior_clean (
log_time STRING,
user_id STRING,
item_id STRING,
behavior_type STRING,
ip STRING
)
PARTITIONED BY (dt STRING)
STORED AS ORC;

INSERT OVERWRITE TABLE fgedu.user_behavior_clean PARTITION (dt=’2026-04-08′)
SELECT
log_time,
CASE WHEN user_id = ” THEN ‘unknown’ ELSE user_id END AS user_id,
CASE WHEN item_id = ” THEN ‘unknown’ ELSE item_id END AS item_id,
behavior_type,
ip
FROM fgedu.user_behavior_raw
WHERE log_time LIKE ‘2026-04-08%’;

# 查看清洗结果

$ beeline -u jdbc:hive2://fgedu.net.cn:10000 -n fgedu -p fgedu123 -e “SELECT * FROM fgedu.user_behavior_clean WHERE dt=’2026-04-08′ LIMIT 5;”
Connecting to jdbc:hive2://fgedu.net.cn:10000
Connected to: Apache Hive (version 3.1.3)
Driver: Hive JDBC (version 3.1.3)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 3.1.3 by Apache Hive
+———————+———+———+—————+————-+
| log_time | user_id | item_id | behavior_type | ip |
+———————+———+———+—————+————-+
| 2026-04-08 10:00:00 | 1001 | item001 | click | 192.168.1.1 |
| 2026-04-08 10:00:01 | 1002 | unknown | view | 192.168.1.2 |
| 2026-04-08 10:00:02 | 1003 | item003 | purchase | 192.168.1.3 |
| 2026-04-08 10:00:03 | unknown | item004 | click | 192.168.1.4 |
| 2026-04-08 10:00:04 | 1005 | item005 | view | 192.168.1.5 |
+———————+———+———+—————+————-+
5 rows selected (0.123 seconds)

4.2 销售数据清洗实战

案例：清洗销售数据

# 查看销售数据

$ beeline -u jdbc:hive2://fgedu.net.cn:10000 -n fgedu -p fgedu123 -e “SELECT * FROM fgedu.sales_ods WHERE dt=’2026-04-08′ LIMIT 5;”
Connecting to jdbc:hive2://fgedu.net.cn:10000
Connected to: Apache Hive (version 3.1.3)
Driver: Hive JDBC (version 3.1.3)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 3.1.3 by Apache Hive
+—–+————+————-+——–+————+——–+
| id | product_id | customer_id | amount | sale_date | region |
+—–+————+————-+——–+————+——–+
| 1 | P001 | C001 | 100.5 | 2026-04-08 | North |
| 2 | P002 | C002 | NULL | 2026-04-08 | South |
| 3 | P003 | C003 | 200.0 | 2026-04-08 | East |
| 4 | P004 | NULL | 150.0 | 2026-04-08 | West |
| 5 | P005 | C005 | 300.0 | 2026-04-08 | NULL |
+—–+————+————-+——–+————+——–+
5 rows selected (0.123 seconds)

# 清洗销售数据

INSERT OVERWRITE TABLE fgedu.sales_dwd PARTITION (dt=’2026-04-08′)
SELECT
id,
product_id,
CASE WHEN customer_id IS NULL THEN ‘Unknown’ ELSE customer_id END AS customer_id,
CASE WHEN amount IS NULL THEN 0 ELSE amount END AS amount,
sale_date,
CASE WHEN region IS NULL THEN ‘Unknown’ ELSE region END AS region
FROM fgedu.sales_ods
WHERE dt=’2026-04-08′;

# 查看清洗结果

$ beeline -u jdbc:hive2://fgedu.net.cn:10000 -n fgedu -p fgedu123 -e “SELECT * FROM fgedu.sales_dwd WHERE dt=’2026-04-08′ LIMIT 5;”
Connecting to jdbc:hive2://fgedu.net.cn:10000
Connected to: Apache Hive (version 3.1.3)
Driver: Hive JDBC (version 3.1.3)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 3.1.3 by Apache Hive
+—–+————+————-+——–+————+——–+
| id | product_id | customer_id | amount | sale_date | region |
+—–+————+————-+——–+————+——–+
| 1 | P001 | C001 | 100.5 | 2026-04-08 | North |
| 2 | P002 | C002 | 0.0 | 2026-04-08 | South |
| 3 | P003 | C003 | 200.0 | 2026-04-08 | East |
| 4 | P004 | Unknown | 150.0 | 2026-04-08 | West |
| 5 | P005 | C005 | 300.0 | 2026-04-08 | Unknown|
+—–+————+————-+——–+————+——–+
5 rows selected (0.123 seconds)

4.3 数据质量监控实现

案例：实现数据质量监控

# 创建数据质量监控表

CREATE TABLE fgedu.data_quality_monitor (
table_name STRING,
partition_date STRING,
total_records BIGINT,
null_count BIGINT,
duplicate_count BIGINT,
data_quality_score DOUBLE,
check_time STRING
)
STORED AS ORC;

# 执行数据质量检查

INSERT INTO fgedu.data_quality_monitor
SELECT
‘fgedu.sales_dwd’ AS table_name,
‘2026-04-08’ AS partition_date,
COUNT(*) AS total_records,
SUM(CASE WHEN customer_id = ‘Unknown’ OR region = ‘Unknown’ OR amount = 0 THEN 1 ELSE 0 END) AS null_count,
COUNT(*) – COUNT(DISTINCT id) AS duplicate_count,
(1 – (SUM(CASE WHEN customer_id = ‘Unknown’ OR region = ‘Unknown’ OR amount = 0 THEN 1 ELSE 0 END) + (COUNT(*) – COUNT(DISTINCT id))) / COUNT(*)) * 100 AS data_quality_score,
FROM_UNIXTIME(UNIX_TIMESTAMP()) AS check_time
FROM fgedu.sales_dwd
WHERE dt=’2026-04-08′;

# 查看数据质量监控结果

$ beeline -u jdbc:hive2://fgedu.net.cn:10000 -n fgedu -p fgedu123 -e “SELECT * FROM fgedu.data_quality_monitor WHERE partition_date=’2026-04-08′;”
Connecting to jdbc:hive2://fgedu.net.cn:10000
Connected to: Apache Hive (version 3.1.3)
Driver: Hive JDBC (version 3.1.3)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 3.1.3 by Apache Hive
+—————-+—————-+————–+———–+—————-+———————+———————+
| table_name | partition_date | total_records | null_count| duplicate_count| data_quality_score | check_time |
+—————-+—————-+————–+———–+—————-+———————+———————+
| fgedu.sales_dwd| 2026-04-08 | 10000 | 500 | 0 | 95.0 | 2026-04-08 12:00:00 |
+—————-+—————-+————–+———–+—————-+———————+———————+
1 row selected (0.123 seconds)

Part05-风哥经验总结与分享

5.1 ETL最佳实践

ETL最佳实践：

增量抽取：只抽取新增或变更的数据，减少数据处理量
并行处理：利用Hadoop的并行计算能力，提高处理速度
数据压缩：使用压缩格式存储数据，减少存储空间和网络传输
错误处理：建立完善的错误处理机制，确保ETL流程的可靠性
日志记录：详细记录ETL过程，便于问题排查

5.2 性能优化建议

ETL性能优化建议：

使用分区表：按时间或其他维度分区，提高查询性能
使用列式存储：ORC、Parquet等列式存储格式，提高数据压缩率和查询速度
启用谓词下推：减少数据扫描量
合理设置MapReduce参数：根据数据量和集群资源调整参数
使用Spark处理：对于复杂的ETL任务，使用Spark提高处理速度

5.3 常见问题解决方案

风哥提示：在ETL过程中，应建立完善的监控和告警机制，及时发现和解决问题。

常见问题解决方案：

数据倾斜：使用随机前缀、局部聚合等方法解决
内存溢出：调整JVM参数，增加内存分配
任务失败：检查错误日志，分析失败原因，调整参数或修复数据问题
性能瓶颈：识别瓶颈环节，进行针对性优化
更多视频教程www.fgedu.net.cn

通过本教程的学习，您已经掌握了Hive数据仓库中ETL数据清洗的方法和实战技巧。在实际生产环境中，应根据具体业务场景和数据特点，选择合适的ETL策略和工具，以提高数据质量和处理效率。学习交流加群风哥QQ113257174

更多学习教程公众号风哥教程itpux_com

from bigdata视频:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html