一、完整可运行 Hive 执行脚本

文件名规范：【学号_姓名】Hive执行脚本.sql

-- ============================================================
-- 【期末考查B卷】广电用户账单营收与异常值处理 Hive 完整执行脚本
-- 对应考核模块：建库建表 → 脏数据清洗 → 财务指标统计 → 性能调优 → 本地导出
-- 数据源：Linux本地 /opt/data/mmconsume_billevents.csv
-- 字段分隔符：分号 ; （与真实CSV格式完全匹配）
-- 运行环境：Hive 3.1.2 + Hadoop 3.1.4 集群
-- ============================================================

-- ========== 会话级通用配置（仅当前终端生效，不影响全局） ==========
-- 开启查询结果列名显示，方便对应字段含义
SET hive.cli.print.header = true;
-- 关闭表名前缀，结果仅显示字段名，更简洁直观
SET hive.resultset.use.unique.column.names = false;
-- 命令行提示符显示当前数据库，避免误操作其他库
SET hive.cli.print.current.db = true;

-- ===================== 任务一：数据库与表结构构建（对应题目15分） =====================
-- 1.1 创建数据库 ZJSM_B，IF NOT EXISTS 避免重复创建报错
CREATE DATABASE IF NOT EXISTS ZJSM_B;

-- 切换到目标数据库，后续所有操作默认在该库下执行
USE ZJSM_B;


-- 1.2 创建原始账单内部表
-- 设计原则：字段顺序与CSV文件严格一一对应，彻底解决字段错位、数据全为NULL的问题
-- 内部表特性：表数据与元数据绑定，删除表时同步删除HDFS数据，适合中间计算表
CREATE TABLE IF NOT EXISTS bill_events_raw (
    terminal_no STRING COMMENT '终端设备唯一编号',
    phone_no STRING COMMENT '用户唯一编号',
    fee_code STRING COMMENT '费用项目编码',
    year_month STRING COMMENT '账单账期',
    owner_name STRING COMMENT '用户等级名称',
    owner_code STRING COMMENT '用户等级编码',
    sm_name STRING COMMENT '业务品牌名称',
    should_pay STRING COMMENT '应付金额（字符串存储，避免类型转换失败）',
    favour_fee STRING COMMENT '优惠金额'
)
-- 指定字段分隔符为分号，与CSV文件格式完全一致
ROW FORMAT DELIMITED FIELDS TERMINATED BY ';'
-- 指定存储格式为纯文本文件
STORED AS TEXTFILE
-- 跳过CSV第一行表头，避免表头被当成业务数据加载
TBLPROPERTIES ("skip.header.line.count"="1");

-- 1.3 加载Linux本地CSV数据到原始表
-- LOCAL 关键字：表示从Linux服务器本地文件系统读取，而非HDFS分布式文件系统
-- OVERWRITE 关键字：覆盖表中原有数据，避免重复加载导致数据冗余
LOAD DATA LOCAL INPATH '/opt/data/mmconsume_billevents.csv'
OVERWRITE INTO TABLE bill_events_raw;

-- ✅ 验证点1：查看前5条原始数据，确认字段映射正确
-- 预期结果：sm_name为品牌名、should_pay为数字、owner_code为等级编码
SELECT * FROM bill_events_raw LIMIT 5;


-- ===================== 任务二：脏数据清洗 ETL（对应题目25分） =====================
-- 清理上一次生成的清洗结果表
DROP TABLE IF EXISTS bill_events_clean;

-- 清洗逻辑（严格匹配题目要求）：
-- 规则1：剔除应付金额小于0的无效账单
-- 规则2：剔除测试线路用户（owner_code为2、9、10）
-- 规则3：仅保留广电核心品牌：数字电视、互动电视、珠江宽频
-- 兼容处理：真实数据中owner_code存在NULL值，不属于测试账号，予以保留
CREATE TABLE IF NOT EXISTS bill_events_clean AS
SELECT 
    phone_no,
    sm_name,
    owner_code,
    -- 将字符串格式的应付金额转为双精度数值，用于后续财务计算
    CAST(should_pay AS DOUBLE) AS should_pay
FROM bill_events_raw
WHERE
    -- 过滤条件1：剔除负金额和空值异常账单
    CAST(should_pay AS DOUBLE) >= 0
    -- 过滤条件2：剔除内部测试账号，兼容带前导零的格式；NULL值正常用户予以保留
    AND (owner_code NOT IN ('2', '9', '10', '02', '09', '10') OR owner_code IS NULL)
    -- 过滤条件3：仅保留广电三大核心业务品牌
    AND sm_name IN ('数字电视', '互动电视', '珠江宽频');

-- ✅ 验证点2：查看清洗后的数据量和样本数据
-- 预期结果：数据量小于原始表，无负金额、无测试账号、无违规品牌
SELECT COUNT(*) AS clean_count FROM bill_events_clean;
SELECT * FROM bill_events_clean LIMIT 10;


-- ===================== 任务三：核心财务指标统计（对应题目25分） =====================
-- ========== SQL执行性能调优参数 ==========
-- 优化1：开启Map端预聚合，Map阶段提前完成分组求和，大幅减少Shuffle数据量
SET hive.map.aggr = true;
-- 优化2：开启MapReduce中间数据压缩，降低网络IO开销，提升Shuffle效率
SET hive.exec.compress.intermediate = true;
SET mapreduce.map.output.compress = true;
-- 优化3：开启数据倾斜优化，自动拆分热点品牌的Reduce任务，解决长尾问题
SET hive.groupby.skewindata = true;
-- 优化4：开启CBO代价优化器，自动选择最优执行计划
SET hive.cbo.enable = true;

-- 核心统计SQL：统计各大品牌的总营收与独立缴费用户规模
-- 输出字段严格对应题目要求：sm_name、total_revenue、user_count
SELECT
    sm_name,
    -- 总营收求和，保留两位小数
    ROUND(SUM(should_pay), 2) AS total_revenue,
    -- 独立缴费用户数：对用户编号去重后计数
    COUNT(DISTINCT phone_no) AS user_count
FROM bill_events_clean
-- 按品牌名称分组聚合
GROUP BY sm_name
-- 按总应收金额降序排列
ORDER BY total_revenue DESC;


-- ===================== 任务四：性能调优实战（对应题目15分） =====================
-- 4.1 开启Fetch抓取优化
-- 作用：简单的SELECT + LIMIT查询直接读取存储文件，不触发MapReduce任务，秒级返回结果
SET hive.fetch.task.conversion = more;

-- 4.2 开启Hive并行执行
-- 作用：SQL中多个无依赖的阶段可并行运行，缩短整体执行时间
SET hive.exec.parallel = true;
-- 设置并行执行的最大线程数，匹配集群CPU核心数
SET hive.exec.parallel.thread.number = 4;

-- ✅ 验证点3：执行简单查询，验证不再触发MapReduce任务
-- SELECT * FROM bill_events_clean LIMIT 10;


-- ===================== 任务五：数据导出与下发（对应题目20分） =====================
-- 需求：将品牌营收统计结果导出至Linux本地服务器指定目录，字段以逗号分隔
-- LOCAL 关键字：导出到Linux本地文件系统，而非HDFS
-- INSERT OVERWRITE：覆盖目标目录原有内容，保证结果唯一性
INSERT OVERWRITE LOCAL DIRECTORY '/opt/export/brand_revenue'
-- 指定导出文件的字段分隔符为逗号 ,，严格匹配题目要求
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
SELECT
    sm_name,
    ROUND(SUM(should_pay), 2) AS total_revenue,
    COUNT(DISTINCT phone_no) AS user_count
FROM bill_events_clean
GROUP BY sm_name
ORDER BY total_revenue DESC;

-- ✅ 验证点4：Linux终端执行命令，查看本地导出结果
-- ls -l /opt/export/brand_revenue
-- cat /opt/export/brand_revenue/*


-- ========== 拓展：导出至HDFS目录（供下游大数据任务使用） ==========
/*
INSERT OVERWRITE DIRECTORY '/user/hive/export/brand_revenue_hdfs'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
SELECT
    sm_name,
    ROUND(SUM(should_pay), 2) AS total_revenue,
    COUNT(DISTINCT phone_no) AS user_count
FROM bill_events_clean
GROUP BY sm_name
ORDER BY total_revenue DESC;
*/
-- ✅ 验证HDFS导出结果
-- hdfs dfs -ls /user/hive/export/brand_revenue_hdfs
-- hdfs dfs -cat /user/hive/export/brand_revenue_hdfs/*

---

二、上机实操报告模板

文件名规范：【学号_姓名】上机实操报告.docx

一、实验环境说明

• 集群环境：Hadoop 3.1.4 + Hive 3.1.2
• 元数据库：MySQL
• 操作方式：Hive CLI 命令行终端
• 数据源：HDFS 路径 /origin_data/mmconsume_billevents.csv

二、关键任务运行截图（按任务顺序粘贴）

任务 1：数据库与表结构构建

截图要求：

• 完整 Hive 终端窗口，显示 hive (ZJSM_B) 提示符
• 包含建库、建表、LOAD 数据执行成功的输出
• 附带 SELECT * FROM bill_events_raw LIMIT 5; 的查询结果，验证字段与分隔符正确

结果说明：

成功创建数据库 ZJSM_B 与内部表 bill_events_raw，完成 HDFS 数据加载，字段按分号正确解析，数据完整导入。

任务 2：脏数据清洗

截图要求：

• 显示 hive (ZJSM_B) 提示符
• 包含 CTAS 建表执行成功的输出
• 附带 SELECT COUNT(*) FROM bill_events_clean; 的统计结果

结果说明：

按照财务规则完成三轮数据过滤：剔除负金额账单、删除测试账号、保留核心品牌，清洗后数据符合业务对账标准，有效数据量为 XXX 条。

任务 3：核心财务指标统计

截图要求：

• 显示 hive (ZJSM_B) 提示符
• 完整展示查询结果，包含 sm_name、total_revenue、user_count 三列
• 确认金额保留两位小数，结果按总营收降序排列

结果说明：

统计得出三大核心品牌的总营收与缴费用户规模，其中 XXX 品牌营收最高，符合业务实际分布。

任务 4：性能调优实战

截图要求：

• 显示两条 SET 参数执行后的终端界面
• 附带调优后执行 SELECT * FROM bill_events_clean LIMIT 10; 的结果，验证无 MapReduce 任务启动

结果说明：

开启 Fetch 抓取优化后，简单查询不再触发 MapReduce，响应速度大幅提升；开启并行执行可提升多阶段 SQL 的整体执行效率。

任务 5：数据导出与下发

截图要求：

• 第一张：Hive 终端中 INSERT 导出语句执行成功的输出
• 第二张：Linux 命令行执行 cat /opt/export/brand_revenue/* 的结果，验证字段以逗号分隔

结果说明：

品牌营收统计结果已成功导出至 Linux 本地指定目录，文件字段以逗号分隔，可直接提供给财务部门对账使用。

三、实验总结

1. 本次实验完整完成了广电账单数据从入库、清洗、财务统计到本地导出的全流程操作。
2. 掌握了 Hive 内部表创建、HDFS 数据加载、CTAS 清洗、聚合统计、会话级参数调优、本地文件导出等核心操作。
3. 关键注意事项：
   • 区分 HDFS 路径加载 和 本地路径加载：HDFS 数据源不加LOCAL，导出到本地需加LOCAL。
   • 财务统计中用户数需去重，使用 COUNT(DISTINCT)，避免同一用户多笔账单重复计数。
   • 性能调优参数为会话级，仅对当前 Hive 连接生效，退出终端后失效。
   • INSERT OVERWRITE 会覆盖目标目录原有数据，执行前需确认目录内容。

---

三、答题避坑说明（得分关键点）

1. 表类型区分：题目要求内部表，不要加 EXTERNAL 关键字，加错会扣分。
2. LOAD 路径区分：数据源在 HDFS 上，LOAD DATA INPATH 不加LOCAL；如果是 Linux 本地文件才加LOCAL。
3. 导出路径区分：导出到 Linux 本地服务器，必须加LOCAL，即 INSERT OVERWRITE LOCAL DIRECTORY，否则会导出到 HDFS。
4. 字段类型匹配：should_pay 要求双精度浮点型，建表写 DOUBLE，不要写成 STRING。
5. 分隔符对应：建表分隔符是分号;，导出分隔符是逗号,，两者不要写反。
6. 调优参数：两个参数均为会话级 SET 命令，不需要写进建表语句，单独执行即可。
7. 用户数去重：独立缴费用户数必须用 COUNT(DISTINCT phone_no)，直接COUNT(*)会统计账单数而非用户数，属于逻辑错误。