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

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

-- ============================================================
-- 【期末考查A卷】广电用户收视行为与流量分析 Hive 完整执行脚本
-- 对应考核模块：建库建表 → 脏数据清洗 → Top5指标统计 → 视图封装 → HDFS导出
-- 数据源：Linux本地 /opt/data/media_index.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;
-- 临时关闭HDFS回收站，删除表时直接清除文件，提升执行速度（实训环境专用）
SET fs.trash.interval = 0;

-- ===================== 任务一：数据库与表结构构建（对应题目15分） =====================
-- 1.1 创建数据库 ZJSM_A，IF NOT EXISTS 避免重复创建报错
CREATE DATABASE IF NOT EXISTS ZJSM_A;
-- 切换到目标数据库，后续所有操作默认在该库下执行
USE ZJSM_A;

-- 1.2 创建原始收视行为外部表
-- 设计原则：字段顺序与CSV文件严格一一对应，彻底解决字段错位、数据全为NULL的问题
-- 外部表特性：删除表时仅删除元数据，不删除HDFS上的真实文件，适合原始数据归档
CREATE EXTERNAL TABLE IF NOT EXISTS media_index_raw (
    terminal_no STRING COMMENT '终端设备唯一编号',
    phone_no STRING COMMENT '用户唯一编号',
    duration STRING COMMENT '观看时长，单位：毫秒（字符串存储，避免类型转换失败）',
    station_name STRING COMMENT '频道/节目名称',
    origin_time STRING COMMENT '收视开始时间',
    end_time STRING COMMENT '收视结束时间',
    owner_code STRING COMMENT '用户等级编码',
    owner_name STRING COMMENT '用户等级名称',
    vod_cat_tags STRING COMMENT '点播分类标签集合',
    resolution STRING COMMENT '视频分辨率',
    audio_lang STRING COMMENT '音频语言',
    region STRING COMMENT '用户所属区域',
    res_name STRING COMMENT '资源名称',
    res_type STRING COMMENT '节目类型：0=直播，1=点播',
    vod_title STRING COMMENT '点播视频标题',
    category_name STRING COMMENT '内容分类名称',
    program_title STRING COMMENT '节目名称',
    sm_name 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/media_index.csv'
OVERWRITE INTO TABLE media_index_raw;

-- ✅ 验证点1：查看前5条原始数据，确认字段映射正确
-- 预期结果：res_type为0/1、duration为纯数字、station_name为频道名称
SELECT * FROM media_index_raw LIMIT 5;


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

-- 清洗逻辑（严格匹配题目要求）：
-- 规则1：仅提取直播数据（res_type = '0'）
-- 规则2：剔除无效时长，仅保留 20秒 ≤ 观看时长 < 5小时 的有效记录
-- 实现说明：原始duration为字符串格式，需通过CAST转为数值型再做单位换算
CREATE TABLE IF NOT EXISTS media_index_clean AS
SELECT 
    phone_no,
    duration,
    station_name,
    res_type
FROM media_index_raw
WHERE 
    -- 过滤条件1：仅保留直播节目
    res_type = '0'
    -- 过滤条件2：观看时长 ≥ 20秒（毫秒转秒：除以1000）
    AND CAST(duration AS DOUBLE) / 1000 >= 20
    -- 过滤条件3：观看时长 < 5小时（毫秒转小时：除以 1000*60*60）
    AND CAST(duration AS DOUBLE) / (1000 * 60 * 60) < 5;

-- ✅ 验证点2：查看清洗后的数据量和样本数据
-- 预期结果：数据量小于原始表，无异常时长记录
SELECT COUNT(*) AS clean_count FROM media_index_clean;
SELECT * FROM media_index_clean LIMIT 10;


-- ===================== 任务三：直播频道Top5时长统计（对应题目25分） =====================
-- ========== SQL执行性能调优参数 ==========
-- 优化1：开启Map端预聚合，Map阶段提前完成分组求和，大幅减少Shuffle阶段数据传输量
SET hive.map.aggr = true;
-- 优化2：开启数据倾斜处理，自动拆分热门频道的热点Key，解决Reduce任务长尾问题
SET hive.groupby.skewindata = true;
-- 优化3：开启CBO代价优化器，自动选择最优执行计划和运算顺序
SET hive.cbo.enable = true;

-- 核心统计SQL：统计直播观看总时长最长的前5个频道
-- 输出字段严格对应题目要求：station_name、total_hours（保留两位小数）
SELECT 
    station_name,
    -- 总时长计算：毫秒求和后转换为小时，通过ROUND函数保留2位小数
    ROUND(SUM(CAST(duration AS DOUBLE)) / (1000 * 60 * 60), 2) AS total_hours
FROM media_index_clean
-- 按频道名称分组聚合
GROUP BY station_name
-- 按总观看时长降序排列
ORDER BY total_hours DESC
-- 取排名前5的频道
LIMIT 5;


-- ===================== 任务四：视图封装与应用（对应题目15分） =====================
-- 业务背景：业务部门需频繁查询频道独立用户数，直接查明细表速度慢，且会暴露用户敏感信息
-- 视图优势：1. 数据安全：仅暴露允许访问的字段，隐藏用户明细等敏感数据
--          2. 使用便捷：封装复杂查询逻辑，业务人员直接查视图即可
--          3. 性能稳定：视图仅存储查询逻辑，不存储真实数据，自动跟随源表更新
CREATE VIEW IF NOT EXISTS view_station_uv AS
SELECT 
    station_name,
    -- 统计每个频道的独立观看用户数，通过DISTINCT对用户编号去重
    COUNT(DISTINCT phone_no) AS uv
FROM media_index_clean
GROUP BY station_name;

-- ✅ 验证点3：查询视图前10条，确认字段和数据正确
-- SELECT * FROM view_station_uv LIMIT 10;


-- ===================== 任务五：数据导出与下发（对应题目20分） =====================
-- 需求：将Top5频道统计结果导出至HDFS指定目录，字段以制表符分隔
-- 说明：INSERT OVERWRITE 会覆盖目标目录原有内容，保证结果唯一性
INSERT OVERWRITE DIRECTORY '/user/hive/export/top5_stations'
-- 指定导出文件的字段分隔符为制表符 \t，严格匹配题目要求
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
SELECT 
    station_name,
    ROUND(SUM(CAST(duration AS DOUBLE)) / (1000 * 60 * 60), 2) AS total_hours
FROM media_index_clean
GROUP BY station_name
ORDER BY total_hours DESC
LIMIT 5;

-- ✅ 验证点4：Linux终端执行HDFS命令，查看导出结果
-- hdfs dfs -ls /user/hive/export/top5_stations
-- hdfs dfs -cat /user/hive/export/top5_stations/*

-- ========== 拓展：导出Top5结果至Linux本地目录 ==========
-- 用途：导出为CSV格式，方便本地查看、下载、用Excel打开，直接下发给业务部门
-- 提前在Linux终端创建目录：mkdir -p /opt/export/top5_stations_local
INSERT OVERWRITE LOCAL DIRECTORY '/opt/export/top5_stations_local'
-- 本地导出使用逗号分隔，标准CSV格式，兼容Excel直接打开
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
SELECT 
    station_name,
    ROUND(SUM(CAST(duration AS DOUBLE)) / (1000 * 60 * 60), 2) AS total_hours
FROM media_index_clean
GROUP BY station_name
ORDER BY total_hours DESC
LIMIT 5;

-- ✅ 验证点5：Linux终端查看本地导出结果
-- ls -l /opt/export/top5_stations_local
-- cat /opt/export/top5_stations_local/*

---

二、上机实操报告模板

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

一、实验环境说明

• 集群环境：Hadoop 3.1.4 + Hive 3.1.2
• 元数据库：MySQL
• 操作方式：Hive CLI 命令行终端
• 数据源：/opt/data/media_index.csv

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

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

截图要求：

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

结果说明：

成功创建数据库ZJSM_A与外部表media_index_raw，完成 CSV 数据加载，字段按逗号正确分隔，数据行数与原始文件一致。

任务 2：脏数据清洗

截图要求：

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

结果说明：

按照规则完成直播数据筛选与时长范围过滤，剔除了 1 秒切台等无效数据，清洗后数据量为 XXX 条，符合业务预期。

任务 3：Top 5 频道时长统计

截图要求：

• 显示hive (ZJSM_A)提示符
• 完整展示查询结果的 5 行数据，包含station_name和total_hours两列
• 确认结果按总时长降序排列，小数保留两位

结果说明：

统计得出直播观看总时长最高的 5 个频道，时长单位统一转换为小时并保留两位小数，排序正确。

任务 4：视图封装与应用

截图要求：

• 显示hive (ZJSM_A)提示符
• 包含创建视图的执行结果
• 附带SELECT * FROM view_station_uv LIMIT 10;的查询结果

结果说明：

成功创建视图view_station_uv，仅暴露频道名称和独立用户数，隐藏用户明细等敏感字段，满足业务部门频繁查询的需求。

任务 5：数据导出与下发

截图要求：

• 第一张：Hive 终端中 INSERT 导出语句执行成功的输出
• 第二张：Linux 命令行执行hdfs dfs -cat /user/hive/export/top5_stations/*的结果，验证字段以制表符分隔

结果说明：

Top5 频道统计结果已成功导出至 HDFS 指定目录，文件字段以制表符分隔，可直接下发给下游业务系统。

三、实验总结

1. 本次实验完整完成了广电收视数据从入库、清洗、分析到导出的全流程操作。
2. 掌握了 Hive 外部表创建、CTAS 清洗、聚合统计、视图封装、HDFS 导出等核心操作。
3. 注意事项：
   • 时长计算需注意单位转换，原始字段为字符串毫秒值，必须 CAST 为数值类型再运算。
   • 导出目录使用 INSERT OVERWRITE 会覆盖原有数据，执行前需确认。
   • 视图仅存储查询逻辑，不存储实际数据，可有效保护明细数据安全。

---

三、答题关键注意事项（避坑说明）

1. 外部表关键字：题目要求创建外部表，必须加EXTERNAL关键字，遗漏会扣分。
2. 分隔符匹配：原始表分隔符是逗号,，导出要求是制表符\t，两者不要混淆。
3. 时长边界值：20 秒是含，5 小时是不含，条件写>=20和<5，边界错误会导致清洗结果偏差。
4. 小数精度：总时长要求保留两位小数，必须使用ROUND(数值, 2)函数。
5. 去重用户数：视图中用户数需要去重，使用COUNT(DISTINCT phone_no)，不能直接用COUNT(*)。
6. 导出路径：HDFS 路径严格按照题目写/user/hive/export/top5_stations，不要写错路径。