第 7 章广电用户数据清洗及数据导出

开篇说明

本章是 Hive 数据分析全流程的核心前置环节，解决的核心痛点是：原始数据中的脏数据、无效数据会直接导致后续的用户分析、营收统计、收视行为洞察结果完全失真。

前面章节我们用原始数据做了查询和统计，但原始数据中存在大量重复值、异常值、无效用户、无意义的收视记录、错误的账单数据，这些数据会让统计结果出现严重偏差。比如统计用户平均观看时长，包含了大量用户切台的 1 秒无效记录，会让平均值远低于真实水平；统计营收时包含了负数的应付金额，会让营收统计结果偏小。

学完本章，你将掌握完整的广电数据预处理全流程：

数据探索：快速定位原始数据中的无效数据、异常数据
数据清洗：针对用户、收视、账单、订单 4 大类数据，制定清洗规则，完成脏数据剔除
结果验证：验证清洗后的数据是否符合业务要求，确保数据质量
数据导出：将清洗后的高质量数据导出到 Linux 本地或 HDFS，供后续数据分析、数据挖掘使用

前置必备准备

已完成第 3 章广电 5 张核心业务表的创建与数据导入，分别是：
- mediamatch_usermsg：用户基本信息表
- mediamatch_userevent：用户状态变更数据表
- mmconsume_billevents：用户账单数据表
- order_index：用户订单数据表
- media_index：用户收视行为数据表
已掌握第 4-6 章的 Hive 查询、函数、分组、视图、优化等核心语法
进入 Hive 客户端后，先执行基础配置

hive

-- 切换到广电业务库
USE ZJSM;
-- 开启查询结果列名显示
SET hive.cli.print.header = true;
-- 列名仅显示字段名，不显示表名前缀
SET hive.resultset.use.unique.column.names=false;
-- 提示符显示当前所在数据库，避免走错库
SET hive.cli.print.current.db=true;

新手必守：数据清洗的正确流程

新手最容易犯的错误：上来就直接删数据，不做探索、不做备份。正确的流程是：

数据探索：先统计、分析脏数据的类型、数量、占比，和业务人员确认清洗规则
数据备份：创建原始表的备份表，避免清洗错误导致数据丢失
数据清洗：采用「创建新表存储清洗后的数据」的方式，不修改原始表
结果验证：对清洗后的表做数据校验，确认脏数据已被完全剔除
数据导出：将验证通过的清洗后数据导出到指定目录

模块一：无效用户数据清洗（任务 7.1 核心）

用户数据是所有分析的基础，无效用户数据会导致用户规模、用户分层、消费能力统计完全错误。本模块的核心是：剔除不符合家庭用户分析范围的无效用户，保留真实的家庭用户数据。

一、无效用户数据探索（先探索，后清洗）

清洗前必须先做数据探索，明确脏数据的类型、数量、占比，再和业务人员确认清洗规则，绝对不能凭感觉删数据。

1. 探索重复用户数据

用户编号phone_no是用户的唯一标识，重复的用户记录会导致用户数统计偏大，需要先确认是否存在重复数据。

hive

-- 代码7-1 统计用户基本表中重复记录的用户
-- 1. 查看是否存在记录数大于1的用户
SELECT phone_no,COUNT(1) AS nums
FROM mediamatch_usermsg
GROUP BY phone_no
HAVING nums > 1;

-- 2. 按记录数降序排列，取前10条，验证用户编号唯一性
SELECT phone_no,COUNT(1) AS nums
FROM mediamatch_usermsg
GROUP BY phone_no
ORDER BY nums DESC LIMIT 10;

探索结论：用户基本表中phone_no字段完全唯一，无重复用户记录，无需去重处理。

2. 探索特殊线路用户数据

根据业务规则，owner_code（用户等级编号）值为 2、9、10 的用户，是用于测试、产品检验的特殊线路用户，不属于真实家庭用户，需要剔除。

hive

-- 代码7-2 统计5张核心表的owner_code字段值分布
SELECT owner_code,COUNT(1) AS record_num
FROM mediamatch_usermsg 
GROUP BY owner_code;

SELECT owner_code,COUNT(1) AS record_num
FROM mediamatch_userevent 
GROUP BY owner_code;

SELECT owner_code,COUNT(1) AS record_num
FROM mmconsume_billevents 
GROUP BY owner_code;

SELECT owner_code,COUNT(1) AS record_num
FROM order_index 
GROUP BY owner_code;

SELECT owner_code,COUNT(1) AS record_num
FROM media_index 
GROUP BY owner_code;

探索结论：5 张表均存在owner_code为 2、9、10 的记录，需要全部剔除。

3. 探索政企用户数据

本次分析的核心是家庭用户，政企用户（owner_name为 EA 级、EB 级、EC 级、ED 级、EE 级）不纳入分析范围，需要先统计政企用户的数量和占比。

hive

-- 代码7-3 统计各表owner_name字段值分布
SELECT owner_name,COUNT(1) AS user_num
FROM mediamatch_usermsg 
GROUP BY owner_name;

探索结论：用户基本表中 HC 级（家庭用户）占比最高，政企用户占比低，所有表均需要剔除 EA/EB/EC/ED/EE 级的政企用户数据。

4. 探索无效业务品牌数据

广电当前核心业务为数字电视、互动电视、珠江宽频、甜果电视，模拟有线电视等其他业务类型不纳入本次分析，需要统计各品牌的用户占比。

hive

-- 代码7-4 统计用户基本表总记录数
SELECT COUNT(*) AS total_num FROM mediamatch_usermsg;

-- 代码7-5 统计各品牌的用户数及占比
SELECT 
  sm_name AS `品牌名称`,
  COUNT(1) AS `用户数`,
  ROUND(COUNT(1)/100000, 6) AS `用户占比`
FROM mediamatch_usermsg
GROUP BY sm_name;

探索结论：模拟有线电视占比约 49%，但不属于本次分析的核心业务，仅保留数字电视、互动电视、珠江宽频、甜果电视 4 类业务数据。

5. 探索无效用户状态数据

根据业务要求，仅保留状态为「正常、欠费暂停、主动暂停、主动销户」的用户，其余状态（如被动销户、销号、冲正等）的用户不纳入分析。

hive

-- 代码7-6 统计用户状态分布
SELECT run_name AS `用户状态`,COUNT(1) AS `用户数`
FROM mediamatch_usermsg
GROUP BY run_name;

探索结论：用户状态共 8 种，仅保留 4 种有效状态，其余全部剔除。

二、无效用户数据清洗实现

Hive 中不建议直接对原始表做 DELETE 删除操作，正确的做法是：创建新的清洗表，将筛选后的有效数据插入新表，既保留原始数据，又能得到清洗后的高质量数据。

1. 用户基本表数据清洗（核心）

hive

-- 代码7-7 清洗用户基本数据表，创建清洗后的新表mediamatch_usermsg_clean
CREATE TABLE IF NOT EXISTS mediamatch_usermsg_clean
COMMENT '用户基本信息清洗表'
AS 
SELECT * 
FROM mediamatch_usermsg 
WHERE 
  -- 1. 剔除特殊线路用户
  owner_code NOT IN ("2","9","10")
  -- 2. 剔除政企用户
  AND owner_name NOT IN ("EA级","EB级","EC级","ED级","EE级") 
  -- 3. 仅保留4类核心业务品牌
  AND sm_name IN ("数字电视","互动电视","珠江宽频","甜果电视")
  -- 4. 仅保留4类有效用户状态
  AND run_name IN ("正常","欠费暂停","主动暂停","主动销户");

2. 其他表的用户数据清洗

按照相同的清洗规则，对其他 4 张表做用户维度的清洗，确保所有表的用户维度统一。

hive

-- 1. 用户状态变更表清洗
CREATE TABLE IF NOT EXISTS mediamatch_userevent_clean
COMMENT '用户状态变更清洗表'
AS 
SELECT * 
FROM mediamatch_userevent 
WHERE 
  owner_code NOT IN ("2","9","10")
  AND owner_name NOT IN ("EA级","EB级","EC级","ED级","EE级")
  AND run_name IN ("正常","欠费暂停","主动暂停","主动销户");

-- 2. 账单表清洗（用户维度）
CREATE TABLE IF NOT EXISTS mmconsume_billevents_user_clean
COMMENT '用户维度账单清洗表'
AS 
SELECT * 
FROM mmconsume_billevents 
WHERE 
  owner_code NOT IN ("2","9","10")
  AND owner_name NOT IN ("EA级","EB级","EC级","ED级","EE级")
  AND sm_name IN ("数字电视","互动电视","珠江宽频","甜果电视");

-- 3. 订单表清洗（用户维度）
CREATE TABLE IF NOT EXISTS order_index_user_clean
COMMENT '用户维度订单清洗表'
AS 
SELECT * 
FROM order_index 
WHERE 
  owner_code NOT IN ("2","9","10")
  AND owner_name NOT IN ("EA级","EB级","EC级","ED级","EE级")
  AND sm_name IN ("数字电视","互动电视","珠江宽频","甜果电视")
  AND run_name IN ("正常","欠费暂停","主动暂停","主动销户");

-- 4. 收视行为表清洗（用户维度）
CREATE TABLE IF NOT EXISTS media_index_user_clean
COMMENT '用户维度收视行为清洗表'
AS 
SELECT * 
FROM media_index 
WHERE 
  owner_code NOT IN ("2","9","10")
  AND owner_name NOT IN ("EA级","EB级","EC级","ED级","EE级");

三、清洗结果验证

清洗完成后，必须做数据验证，确认脏数据已被完全剔除。

hive

-- 代码7-8 验证用户基本清洗表的品牌数据是否符合要求
SELECT sm_name,COUNT(1) AS user_num 
FROM mediamatch_usermsg_clean 
GROUP BY sm_name;

-- 验证是否还存在政企用户
SELECT owner_name,COUNT(1) AS user_num
FROM mediamatch_usermsg_clean
GROUP BY owner_name;

-- 验证是否还存在无效用户状态
SELECT run_name,COUNT(1) AS user_num
FROM mediamatch_usermsg_clean
GROUP BY run_name;

-- 验证是否还存在特殊线路用户
SELECT owner_code,COUNT(1) AS record_num
FROM mediamatch_usermsg_clean
GROUP BY owner_code;

验证标准：查询结果中仅存在我们保留的有效数据，无任何无效数据。

模块二：无效收视行为数据清洗（任务 7.2 核心）

收视行为数据是用户偏好分析、频道价值评估的核心，无效的收视记录会导致用户观看时长、频道热度、节目偏好分析完全失真。本模块的核心是：剔除无意义的、非用户真实观看的收视记录，保留有效的用户观看行为数据。

一、无效收视行为数据探索

收视行为的无效数据主要分为 3 类：

观看时长过短：用户频繁切台产生的毫秒级 / 秒级记录，非真实观看
观看时长过长：用户关闭电视但未关机顶盒，产生的超长待机记录
机顶盒自动返回数据：直播场景下，开始 / 结束时间秒数为 00 的自动上报数据，非用户真实观看

1. 观看时长基础统计

先通过聚合函数，掌握用户观看时长的整体分布，确定有效时长的区间。

注意：duration字段的单位是毫秒，需要除以 1000 转换为秒，再做统计分析。

hive

-- 代码7-9 统计用户观看时长的均值、最值、标准差
SELECT
  ROUND(AVG(CAST(duration AS DOUBLE)/1000), 2) AS avg_duration_second, -- 平均观看时长（秒）
  ROUND(MIN(CAST(duration AS DOUBLE)/1000), 2) AS min_duration_second, -- 最小观看时长（秒）
  ROUND(MAX(CAST(duration AS DOUBLE)/1000), 2) AS max_duration_second, -- 最大观看时长（秒）
  ROUND(STDDEV(CAST(duration AS DOUBLE)/1000), 2) AS std_duration_second -- 观看时长标准差
FROM media_index;

探索结论：

平均观看时长约 1104 秒（18 分钟）
观看时长范围 0 秒～17992 秒（约 5 小时）
标准差约 1439 秒（24 分钟），数据离散程度较小

2. 观看时长分布统计

按小时、分钟、秒三个维度，统计观看时长的分布，确定无效数据的阈值。

hive

-- 代码7-10 按小时统计观看时长分布
SELECT 
  hour AS `观看时长(小时)`,
  COUNT(hour) AS `记录数`,
  ROUND(COUNT(hour)/4754442, 6) AS `占比`
FROM (
  -- 转换为小时并向下取整
  SELECT FLOOR(CAST(duration AS DOUBLE)/(1000*60*60)) AS hour
  FROM media_index
) h 
GROUP BY h.hour
ORDER BY h.hour;

-- 代码7-11 按分钟统计1小时内的观看时长分布
SELECT 
  minutes AS `观看时长(分钟)`,
  COUNT(minutes) AS `记录数`,
  ROUND(COUNT(minutes)/4754442, 6) AS `占比`
FROM (
  SELECT FLOOR(CAST(duration AS DOUBLE)/(1000*60)) AS minutes
  FROM media_index 
) m 
GROUP BY m.minutes 
ORDER BY m.minutes 
LIMIT 10;

-- 代码7-12 按秒统计1分钟内的观看时长分布
SELECT 
  seconds AS `观看时长(秒)`,
  COUNT(seconds) AS `记录数`,
  ROUND(COUNT(seconds)/4754442, 6) AS `占比`
FROM (
  SELECT FLOOR(CAST(duration AS DOUBLE)/1000) AS seconds
  FROM media_index 
) s 
GROUP BY s.seconds 
ORDER BY s.seconds 
LIMIT 10;

探索结论：

94% 的观看记录时长小于 1 小时，5.9% 的记录时长在 1-2 小时，超过 2 小时的记录占比不足 0.2%
观看时长小于 1 分钟的记录占比 18%，其中小于 20 秒的记录占比极高，属于用户切台的无效记录
结合业务实际，确定有效观看时长区间为 20 秒～5 小时，超出该区间的均为无效数据

3. 机顶盒自动返回数据统计

直播场景下（res_type=0），origin_time和end_time秒数为 00 的记录，是机顶盒自动上报的心跳数据，非用户真实观看，需要统计数量。

hive

-- 代码7-13 统计机顶盒自动返回的无效数据量
SELECT COUNT(*) AS invalid_record_num
FROM media_index 
WHERE res_type='0' 
  AND origin_time LIKE '%00'
  AND end_time LIKE '%00';

探索结论：该类无效记录约 88 万条，占总记录数的 18.5%，必须全部剔除。

二、无效收视行为数据清洗实现

结合探索结果，制定完整的清洗规则，创建清洗后的收视行为表。

hive

-- 代码7-14 清洗无效收视行为数据，创建清洗表media_index_clean
CREATE TABLE IF NOT EXISTS media_index_clean
COMMENT '用户收视行为清洗表'
AS 
SELECT * 
FROM media_index 
WHERE 
  -- 1. 有效观看时长：20秒 ≤ 时长 < 5小时
  (CAST(duration AS DOUBLE)/1000 >= 20 
  AND CAST(duration AS DOUBLE)/(1000*60*60) < 5)
  -- 2. 分场景剔除无效数据
  AND (
    -- 直播场景：剔除机顶盒自动返回的数据
    (res_type='0' AND origin_time NOT LIKE '%00' AND end_time NOT LIKE '%00')
    -- 点播/回看场景：无自动上报数据，仅需满足时长要求
    OR res_type='1'
  )
  -- 3. 复用用户维度的清洗规则，剔除无效用户
  AND owner_code NOT IN ("2","9","10")
  AND owner_name NOT IN ("EA级","EB级","EC级","ED级","EE级");

三、清洗结果验证

hive

-- 代码7-15 验证是否还存在机顶盒自动返回的无效数据
SELECT COUNT(*) AS invalid_num
FROM media_index_clean 
WHERE res_type='0' 
  AND origin_time LIKE '%00'
  AND end_time LIKE '%00';
-- 验证标准：结果为0，说明无效数据已完全剔除

-- 验证观看时长的范围是否符合要求
SELECT 
  ROUND(MIN(CAST(duration AS DOUBLE)/1000), 2) AS min_duration,
  ROUND(MAX(CAST(duration AS DOUBLE)/1000), 2) AS max_duration
FROM media_index_clean;
-- 验证标准：最小值≥20，最大值<18000（5小时）

-- 统计清洗前后的记录数，确认数据量变化
SELECT '原始表' AS table_type, COUNT(*) AS record_num FROM media_index
UNION ALL
SELECT '清洗表' AS table_type, COUNT(*) AS record_num FROM media_index_clean;

模块三：无效账单和订单数据清洗（任务 7.3 核心）

账单和订单数据是广电营收分析、用户价值评估的核心，无效的账单 / 订单数据会导致营收统计、ARPU 值计算、用户消费能力分层完全错误。本模块的核心是：剔除金额异常的无效账单 / 订单数据，保证营收统计的准确性。

一、无效数据探索

1. 无效账单数据探索

无效账单数据指的是should_pay（用户应付金额）小于 0 的记录，这类数据属于冲正、退款类的异常账单，不能纳入正常营收统计。

hive

-- 代码7-16 统计无效账单数据的数量
SELECT COUNT(1) AS invalid_bill_num
FROM mmconsume_billevents
WHERE should_pay < 0;

探索结论：账单表中存在 377 条应付金额小于 0 的无效记录，需要全部剔除。

2. 无效订单数据探索

无效订单数据指的是cost（订购产品价格）为空或小于 0 的记录，这类数据属于无效订单，不能纳入用户消费能力统计。

hive

-- 代码7-17 统计无效订单数据的数量
SELECT COUNT(*) AS invalid_order_num
FROM order_index
WHERE cost IS NULL OR cost < 0;

探索结论：订单表中无无效订单数据，无需做金额维度的清洗。

二、数据清洗实现

结合用户维度的清洗规则，完成账单和订单表的全量清洗。

hive

-- 代码7-18 账单表全量清洗，创建清洗表mmconsume_billevents_clean
CREATE TABLE IF NOT EXISTS mmconsume_billevents_clean
COMMENT '用户账单清洗表'
AS
SELECT * 
FROM mmconsume_billevents
WHERE 
  -- 1. 剔除应付金额小于0的无效账单
  should_pay >= 0
  -- 2. 复用用户维度清洗规则
  AND owner_code NOT IN ("2","9","10")
  AND owner_name NOT IN ("EA级","EB级","EC级","ED级","EE级")
  AND sm_name IN ("数字电视","互动电视","珠江宽频","甜果电视");

-- 订单表全量清洗，创建清洗表order_index_clean
CREATE TABLE IF NOT EXISTS order_index_clean
COMMENT '用户订单清洗表'
AS
SELECT * 
FROM order_index
WHERE 
  -- 1. 剔除金额异常的订单
  cost IS NOT NULL AND cost >= 0
  -- 2. 复用用户维度清洗规则
  AND owner_code NOT IN ("2","9","10")
  AND owner_name NOT IN ("EA级","EB级","EC级","ED级","EE级")
  AND sm_name IN ("数字电视","互动电视","珠江宽频","甜果电视")
  AND run_name IN ("正常","欠费暂停","主动暂停","主动销户");

三、清洗结果验证

hive

-- 验证账单表是否还存在无效数据
SELECT COUNT(*) AS invalid_num
FROM mmconsume_billevents_clean
WHERE should_pay < 0;
-- 验证标准：结果为0

-- 验证订单表是否还存在无效数据
SELECT COUNT(*) AS invalid_num
FROM order_index_clean
WHERE cost IS NULL OR cost < 0;
-- 验证标准：结果为0

模块四：清洗后的数据导出（任务 7.4 核心）

数据清洗完成并验证通过后，需要将高质量的清洗数据导出到文件系统，供后续的 Python 数据分析、数据可视化、机器学习建模使用。Hive 支持将数据导出到Linux 本地文件系统和HDFS 分布式文件系统。

一、核心导出语法

hive

INSERT OVERWRITE [LOCAL] DIRECTORY '导出目录路径'
[ROW FORMAT DELIMITED FIELDS TERMINATED BY '字段分隔符']
[STORED AS 文件存储格式]
SELECT * FROM 清洗后的表名;

核心参数说明：

参数	核心作用	新手必知
`LOCAL`	可选，加了该参数表示导出到Linux 本地文件系统，不加则导出到HDFS 文件系统	本地导出目录需要提前在 Linux 中创建，否则会报错
`DIRECTORY`	指定数据导出的目录路径	目录必须是空目录，否则会被 OVERWRITE 覆盖，原有数据全部丢失
`ROW FORMAT DELIMITED FIELDS TERMINATED BY`	指定导出文件的字段分隔符，常用逗号`,`、制表符`\t`	不指定的话，默认用 ^A（\001）分隔，Excel、Python 无法正常读取
`STORED AS`	指定文件存储格式，常用`TEXTFILE`文本格式	不指定默认是 TEXTFILE，新手直接用文本格式即可，兼容性最强

二、导出前准备

在 Linux 中创建导出目录，确保目录权限正确

bash

# 在Linux终端执行，创建根目录
mkdir -p /opt/zjsm_clean/
# 为每个清洗表创建单独的导出目录
mkdir -p /opt/zjsm_clean/mediamatch_usermsg_clean
mkdir -p /opt/zjsm_clean/media_index_clean
mkdir -p /opt/zjsm_clean/mmconsume_billevents_clean
mkdir -p /opt/zjsm_clean/order_index_clean
mkdir -p /opt/zjsm_clean/mediamatch_userevent_clean

bash

# 在Linux终端执行，创建HDFS导出目录
hdfs dfs -mkdir -p /opt/zjsm_clean/
hdfs dfs -mkdir -p /opt/zjsm_clean/mediamatch_usermsg_clean

三、完整导出实战

1. 导出到 Linux 本地文件系统

hive

-- 代码7-19 导出账单清洗表到Linux本地
INSERT OVERWRITE LOCAL DIRECTORY '/opt/zjsm_clean/mmconsume_billevents_clean' 
ROW FORMAT DELIMITED FIELDS TERMINATED by ','
STORED AS TEXTFILE
SELECT * FROM mmconsume_billevents_clean;

-- 代码7-21 导出用户基本清洗表到Linux本地
INSERT OVERWRITE LOCAL DIRECTORY '/opt/zjsm_clean/mediamatch_usermsg_clean'
ROW FORMAT DELIMITED FIELDS TERMINATED by ','
STORED AS TEXTFILE
SELECT * FROM mediamatch_usermsg_clean;

-- 导出收视行为清洗表到Linux本地
INSERT OVERWRITE LOCAL DIRECTORY '/opt/zjsm_clean/media_index_clean'
ROW FORMAT DELIMITED FIELDS TERMINATED by ','
STORED AS TEXTFILE
SELECT * FROM media_index_clean;

2. 导出到 HDFS 分布式文件系统

hive

-- 导出用户基本清洗表到HDFS，仅需去掉LOCAL关键字即可
INSERT OVERWRITE DIRECTORY '/opt/zjsm_clean/mediamatch_usermsg_clean'
ROW FORMAT DELIMITED FIELDS TERMINATED by ','
STORED AS TEXTFILE
SELECT * FROM mediamatch_usermsg_clean;

四、导出结果验证

bash

# 代码7-20 代码7-22 在Linux终端验证导出结果
# 1. 查看Linux本地导出的账单数据前10行
cd /opt/zjsm_clean/mmconsume_billevents_clean
cat 000000_0 | head -n 10

# 2. 查看HDFS导出的用户数据前10行
hdfs dfs -cat /opt/zjsm_clean/mediamatch_usermsg_clean/000000_0 | head -n 10

# 3. 统计导出的文件行数，和Hive表中的记录数对比，确认数据无丢失
wc -l /opt/zjsm_clean/mediamatch_usermsg_clean/000000_0

验证标准：导出的文件行数和 Hive 清洗表中的记录数完全一致，字段分隔符正确，无乱码、无缺失。

模块五：全流程数据清洗避坑指南（新手必看）

1. 数据清洗核心避坑

❌ 坑 1：直接对原始表执行 DELETE/DROP 操作，导致原始数据丢失
✅ 解决：永远采用「创建新表存储清洗后的数据」的方式，保留原始表不动，清洗错误可以随时回滚
❌ 坑 2：不做数据探索，直接凭感觉制定清洗规则，导致有效数据被误删
✅ 解决：严格遵循「先探索 - 再确认规则 - 后清洗 - 最后验证」的流程，所有清洗规则必须有业务依据
❌ 坑 3：清洗规则不统一，5 张表的用户清洗规则不一致，导致后续多表关联时数据匹配不上
✅ 解决：所有表的用户维度清洗规则必须完全统一，确保用户编号在所有清洗表中是一致的
❌ 坑 4：忽略空值处理，WHERE 条件中 NOT IN 会过滤掉 NULL 值，导致数据丢失
✅ 解决：清洗前先统计字段的 NULL 值占比，和业务人员确认 NULL 值是否为有效数据，再决定是否保留

2. 数据导出核心避坑

❌ 坑 1：导出目录不为空，执行 INSERT OVERWRITE 后原有数据被全部覆盖
✅ 解决：导出前必须确认目录为空，或者使用全新的目录路径，绝对不要用根目录、家目录等已有数据的目录
❌ 坑 2：Linux 本地导出目录未提前创建，导致导出报错
✅ 解决：导出前必须在 Linux 终端用 mkdir -p 创建好对应的目录，Hive 不会自动创建本地目录
❌ 坑 3：不指定字段分隔符，导出的文件用默认的 ^A 分隔，无法正常读取
✅ 解决：导出时必须指定

FIELDS TERMINATED by ','


，用逗号或制表符分隔，保证兼容性

- ❌ 坑 4：导出大数据量时，生成多个小文件，后续处理麻烦

✅ 解决：导出前通过

SET mapreduce.job.reduces=1;


设置 Reduce 数为 1，让导出结果生成一个文件，方便后续处理

------

## 模块六：入门自测题（学完检验成果）

### 题目 1

请写出 SQL，对用户收视行为表做清洗，要求：仅保留家庭用户、有效观看时长 30 秒～4 小时、剔除机顶盒自动上报数据，创建清洗表`media_index_test_clean`。


```hive
CREATE TABLE IF NOT EXISTS media_index_test_clean
COMMENT '收视行为测试清洗表'
AS 
SELECT * 
FROM media_index 
WHERE 
-- 有效观看时长
(CAST(duration AS DOUBLE)/1000 >= 30 
AND CAST(duration AS DOUBLE)/(1000*60*60) < 4)
-- 剔除机顶盒自动数据
AND (
  (res_type='0' AND origin_time NOT LIKE '%00' AND end_time NOT LIKE '%00')
  OR res_type='1'
)
-- 剔除无效用户
AND owner_code NOT IN ("2","9","10")
AND owner_name NOT IN ("EA级","EB级","EC级","ED级","EE级");

题目 2

请写出 SQL，统计清洗后的用户基本表中，各用户状态的用户数及占比。

hive

-- 先统计总用户数
WITH total AS (
  SELECT COUNT(*) AS total_num FROM mediamatch_usermsg_clean
)
SELECT 
  run_name AS `用户状态`,
  COUNT(*) AS `用户数`,
  ROUND(COUNT(*)/total.total_num, 4) AS `用户占比`
FROM mediamatch_usermsg_clean, total
GROUP BY run_name, total.total_num
ORDER BY `用户数` DESC;

题目 3

请写出完整的导出语句，将清洗后的订单表导出到 Linux 本地的/opt/zjsm_clean/order_test目录，要求字段用制表符\t分隔，文本格式存储。

hive

-- 先在Linux终端创建目录：mkdir -p /opt/zjsm_clean/order_test
INSERT OVERWRITE LOCAL DIRECTORY '/opt/zjsm_clean/order_test'
ROW FORMAT DELIMITED FIELDS TERMINATED by '\t'
STORED AS TEXTFILE
SELECT * FROM order_index_clean;

第 7 章 广电用户数据清洗及数据导出 ​

开篇说明 ​

模块一：无效用户数据清洗（任务 7.1 核心） ​

一、无效用户数据探索（先探索，后清洗） ​

1. 探索重复用户数据 ​

2. 探索特殊线路用户数据 ​

3. 探索政企用户数据 ​

4. 探索无效业务品牌数据 ​

5. 探索无效用户状态数据 ​

二、无效用户数据清洗实现 ​

1. 用户基本表数据清洗（核心） ​

2. 其他表的用户数据清洗 ​

三、清洗结果验证 ​

模块二：无效收视行为数据清洗（任务 7.2 核心） ​

一、无效收视行为数据探索 ​

1. 观看时长基础统计 ​

2. 观看时长分布统计 ​

3. 机顶盒自动返回数据统计 ​

二、无效收视行为数据清洗实现 ​

三、清洗结果验证 ​

模块三：无效账单和订单数据清洗（任务 7.3 核心） ​

一、无效数据探索 ​

1. 无效账单数据探索 ​

2. 无效订单数据探索 ​

二、数据清洗实现 ​

三、清洗结果验证 ​

模块四：清洗后的数据导出（任务 7.4 核心） ​

一、核心导出语法 ​

二、导出前准备 ​

三、完整导出实战 ​

1. 导出到 Linux 本地文件系统 ​

2. 导出到 HDFS 分布式文件系统 ​

四、导出结果验证 ​

模块五：全流程数据清洗避坑指南（新手必看） ​

1. 数据清洗核心避坑 ​

2. 数据导出核心避坑 ​

题目 2 ​

题目 3 ​

第 7 章广电用户数据清洗及数据导出

开篇说明

模块一：无效用户数据清洗（任务 7.1 核心）

一、无效用户数据探索（先探索，后清洗）

1. 探索重复用户数据

2. 探索特殊线路用户数据

3. 探索政企用户数据

4. 探索无效业务品牌数据

5. 探索无效用户状态数据

二、无效用户数据清洗实现

1. 用户基本表数据清洗（核心）

2. 其他表的用户数据清洗

三、清洗结果验证

模块二：无效收视行为数据清洗（任务 7.2 核心）

一、无效收视行为数据探索

1. 观看时长基础统计

2. 观看时长分布统计

3. 机顶盒自动返回数据统计

二、无效收视行为数据清洗实现

三、清洗结果验证

模块三：无效账单和订单数据清洗（任务 7.3 核心）

一、无效数据探索

1. 无效账单数据探索

2. 无效订单数据探索

二、数据清洗实现

三、清洗结果验证

模块四：清洗后的数据导出（任务 7.4 核心）

一、核心导出语法

二、导出前准备

三、完整导出实战

1. 导出到 Linux 本地文件系统

2. 导出到 HDFS 分布式文件系统

四、导出结果验证

模块五：全流程数据清洗避坑指南（新手必看）

1. 数据清洗核心避坑

2. 数据导出核心避坑

题目 2

题目 3