广电用户数据清洗及数据导出¶

任务背景¶

大数据分析结果的有效性在很大程度上依赖于所处理数据的质量，使用合理的方法分析高质量的数据将得到准确的结果。数据的质量对任何依赖于该数据的应用所获得结果有重要影响。数据的不完整性、不一致性、重复性和无效性等是低质量数据的重要特征。例如，使用欧洲的用户画像衡量中国的用户画像，因为数据描述的对象并不对应，所以这些数据是无效的、不准确的。因此，一般在数据分析、数据挖掘之前，需要进行数据探索，即探索数据的完整性、一致性、重复性和合理性等，若发现无效数据，则应进行数据清洗，为后续的数据分析处理工作提供高质量的数据。

在前面章节中，使用的是广电用户的原始数据，通过数据查询发现其中存在许多缺失和异常的数据，如大量数据字段中包含NULL值。在统计字段中的类型数量时也会对NULL值进行计算，造成数据分析结果的不准确。因此，需要对广电用户数据中的不符合案例分析要求的数据，即无效数据，进行清洗并将清洗后的数据进行保存。

本章将对广电用户数据进行探索，寻找出各表中的无效数据，进行数据清洗并将清洗后的数据进行保存。本章将先探索无效的用户数据，如统计重复的用户数和探索特殊线路用户数据等，其次探索无效的收视行为数据，分析用户收视行为特征，筛选有效数据，接着探索无效账单和订单数据，最后将清洗好的数据进行保存。数据清洗的过程是比较烦琐的，但需细致入微、踔厉奋发、勇毅前行，为实现任务而努力、坚持。

清洗无效用户数据清洗无效收视行为数据清洗无效账单和订单数据导出处理结果至Linux本地和HDFS

清洗无效用户数据¶

在进行广电用户数据分析时，需要研究大众用户的行为特征。一般而言，政企用户、内部通信用户、测试用户和办理了销号的用户等都是无效用户，因此，需要对无效用户数据进行清洗。本任务探索广电用户数据中用户编号phone_no、用户等级编号owner_code、用户等级名称owner_name、品牌名称sm_name和状态名称run_name这些字段中的无效用户数据并进行清洗。

探索无效用户数据¶

在广电用户数据中，存在大量的无效用户数据，需要进行数据探索，查找无效用户数据，然后删除无效用户数据，实现数据清洗。探索过程如下。

1．统计重复的用户数¶

探索用户基本数据表中是否存在重复记录的用户先统计用户基本数据表中每个用户记录数，结合统计出的结果，观察是否存在重复记录的用户再分组统计每个用户编号phone_no的记录数，并按记录数降序排列，取前10条数据，发现用户基本数据表中不存在记录数大于1的用户，且phone_no分组统计记录数，将结果按降序排列，得到结果中所有的phone_no都唯一。

# 统计用户基本数据表中用户记录数大于1的个数
SELECT phone_no,COUNT(1) AS nums
FROM mediamatch_usermsg
GROUP BY phone_no
HAVING nums > 1;

# 分组统计每个phone_no的记录数，并按记录数降序排列，取前10条数据
SELECT phone_no,COUNT(1) AS nums
FROM mediamatch_usermsg
GROUP BY phone_no
ORDER BY nums DESC LIMIT 10;

2．探索特殊线路用户数据¶

根据业务人员提供的数据，用户等级编号owner_code字段含有多个取值，其中值为2、9或10的记录是特殊路线的用户的数据，特殊线路是用于用户测试、产品检验的。保存广电用户数据的5个表中都存在owner_code字段，对这5个表中是否存在特殊线路的用户及其数量进行分析，按照owner_code字段分组后，再统计该字段各值的记录数。

SELECT owner_code,COUNT(1) FROM mediamatch_usermsg GROUP BY owner_code;
SELECT owner_code,COUNT(1) FROM mediamatch_userevent GROUP BY owner_code;
SELECT owner_code,COUNT(1) FROM mmconsume_billevents GROUP BY owner_code;
SELECT owner_code,COUNT(1) FROM order_index GROUP BY owner_code;
SELECT owner_code,COUNT(1) FROM media_index GROUP BY owner_code;

以统计用户基本数据表的owner_code字段值的结果为例，如图所示，发现owner_code存在字段值为2的数据，且所占的比例较小。此外owner_code字段还存在空值（NULL），经过与业务人员沟通确认owner_code字段存在空值是正常的。

文本描述已自动生成

对其余4个表统计owner_code字段值，发现存在2、9或10，因此各表都需要清洗owner_code字段值为2、9或10的记录，各表的owner_code字段值如表所示。

数据表	owner_code字段值
用户基本数据表	0、15、2、5、6、7、8、NULL
用户状态变更数据表	0、15、2、5、6、8、NULL
账单数据表	0、1、15、2、30、31、4、5、6、7、8、9、NULL
订单数据表	0、1、10、15、2、30、31、4、5、6、7、8、9、NULL
用户收视行为数据表	0、8、15、1、5、2、6、31、7、10、NULL

3．探索政企用户数据¶

由于广电公司的用户主要是家庭用户，所以政企用户不纳入分析范围。根据业务人员提供的数据，政企用户的标识是用户等级名称owner_name字段值为EA级、EB级、EC级、ED级或EE级。

SELECT owner_name,COUNT(1) FROM mediamatch_usermsg GROUP BY owner_name;
SELECT owner_name,COUNT(1) FROM mediamatch_userevent GROUP BY owner_name;
SELECT owner_name,COUNT(1) FROM mmconsume_billevents GROUP BY owner_name;
SELECT owner_name,COUNT(1) FROM order_index GROUP BY owner_name;
SELECT owner_name,COUNT(1) FROM media_index GROUP BY owner_name;

根据第3章的数据说明所提供的数据，保存广电用户数据的5个表中都存在owner_name字段，需要探索这些表中是否存在政企用户以及存在的数量。按照owner_name字段进行分组，再统计该字段各值的记录数。

执行代码中的代码可得出每个表的owner_name字段值的记录数，每个表存在的owner_name字段值都不一致。以用户基本数据表的owner_name字段值为例，存在EA级、EB级和EE级。

文本描述已自动生成

且owner_name字段值为HC级的记录数最多，而政企用户的数量较少，这也印证了广电公司的用户主要是家庭用户。用户基本信息表owner_name字段数据统计情况。 | 数据表 | owner_name字段值 | | --- | --- | | 用户基本数据表 | EA级、EB级、EE级、HA级、HB级、HC级、HE级 | | 用户状态变更数据表 | EA级、EB级、EE级、HA级、HB级、HC级 | | 账单数据表 | EA级、EB级、EE级、HA级、HB级、HC级、HE级 | | 订单数据表 | EA级、EB级、EE级、HA级、HB级、HC级、HE级、NULL | | 用户收视行为数据表 | EA级、EE级、HA级、HB级、HC级、HE级 |

从3.1.4小节可知，本书使用的数据是一段时间内的广电业务数据，而在实际的业务数据库中，各信息表中可能会出现owner_name字段值为EC级或ED级的政企用户记录。因此在进行数据预处理时，需要清洗owner_name字段值为EA级、EB级、EC级、ED级和EE级的政企用户。

4．统计sm_name字段值¶

广电公司目前的业务类型主要是数字电视、互动电视、珠江宽频、模拟有线电视和甜果电视这5种，品牌名称可以通过sm_name字段进行标识。除了用户状态变更数据表，其余4个表都含有sm_name字段。下面以统计用户基本数据表中的所有业务类型、每种类型的用户数以及每种类型的用户数占比为例，实现sm_name字段数据探索，操作步骤如下。

# 统计总记录数
SELECT COUNT(*) FROM mediamatch_usermsg;

首先统计用户基本数据表的总记录数，将其作为后续统计sm_name字段值数量占比的分母。接着按sm_name字段分组统计该字段各值的数量及其占比，统计用户基本数据表的sm_name字段各值的数量及其占比，统计用户基本数据表中sm_name字段各值的数量及其占比，结果显示，sm_name字段的值一共有5种，且模拟有线电视的用户最多，约占总数的49%，其次数字电视的用户约占30%。现在的主要业务是互动电视、数字电视、甜果电视、珠江宽频，这四者约占总数的50%，需要保留这4种业务类型的用户，删除其他业务类型的用户。

# 统计用户基本数据中sm_name字段各值的数量及其占比
SELECT sm_name,COUNT(1) AS sm_nums,(COUNT(1)/100000) AS sm_percent
FROM mediamatch_usermsg
GROUP BY sm_name;

文本描述已自动生成

5．筛选正常、欠费暂停、主动暂停和主动销户的用户数据¶

根据业务要求，除了需要筛选指定品牌名称的用户外，还需要对状态名称进行过滤，只保留状态名称为正常、欠费暂停、主动暂停和主动销户的用户，其余的状态名称不需要进行分析处理。状态名称的字段标识为run_name，只有用户状态变更数据表、订单数据表与用户基本数据表含有run_name字段。以对用户基本数据中的用户状态进行探索为例，实现run_name字段数据探索，按照run_name字段分组后，再统计该字段各值的记录数。

# 统计用户基本数据表中run_name字段的各值的记录数
SELECT run_name,COUNT(1)
FROM mediamatch_usermsg
GROUP BY run_name;

代码得到的统计结果显示run_name字段的值一共有8种。其中只保留正常、欠费暂停、主动暂停和主动销户的用户，其余的值不需要进行分析处理。

文本, 表格描述已自动生成

在以用户状态变更数据表和订单数据表统计run_name字段值的统计结果中，同样存在多种类型，也只需保留正常、欠费暂停、主动暂停和主动销户的用户数据。用户状态变更数据表、订单数据表与用户基本数据表run_name字段值，如下所示。 | 数据表 | run_name字段值 | | --- | --- | | 用户基本数据表 | 主动暂停、主动销户、冲正、创建、欠费暂停、正常、被动销户、销号 | | 用户状态变更数据表 | 主动暂停、主动销户、冲正、创建、欠费暂停、正常、被动销户 | | 订单数据表 | 主动暂停、主动销户、冲正、创建、欠费暂停、正常、被动销户、未激活、BG、BY、DB、DG、DI、GI、GY、NULL、Y、YB、YD、YG、YI、YN、YY、dd |

删除无效用户数据¶

通过对广电用户数据进行无效用户数据的探索，查询出许多无效的用户数据。本小节的任务是清洗无效用户数据，任务实现步骤如下。用户去重。经统计，并无重复的用户记录，无须处理。清洗特殊线路用户数据，即清洗各表owner_code字段值为2、9或10的记录。清洗政企用户数据，即清洗各表中owner_name字段值为EA级、EB级、EC级、ED级或EE级的政企用户数据。只保留用户基本数据表、账单数据表、订单数据表和用户收视行为数据表中sm_name字段值为数字电视、互动电视、珠江宽频和甜果电视的数据。只保留用户基本数据表、用户状态变更数据表、订单数据表中run_name字段值为正常、欠费暂停、主动暂停和主动销户的用户数据。

本任务以清洗用户基本数据表中的数据为例，实现以上数据清洗要求。因为所创建的5张广电用户数据Hive表均为普通的内部表，所以将使用筛选有效数据导入另一个表的方式实现数据清洗。创建一个mediamatch_usermsg_clean表，将无效的数据剔除，将有效数据导入表中，实现数据清洗。

# 清洗用户基本数据表中的数据
CREATE TABLE IF NOT EXISTS mediamatch_usermsg_clean
AS 
SELECT * FROM mediamatch_usermsg 
WHERE 
owner_code NOT IN ("02","09","10")
AND 
owner_name NOT IN ("EA级","EB级","EC级","ED级","EE级") 
AND
sm_name IN ("数字电视","互动电视","珠江宽频","甜果电视")
AND 
run_name IN ("正常","欠费暂停","主动暂停","主动销户");
```如果需要验证mediamatch_usermsg_clean表中的数据是否有效，可以通过分组查询owner_code、owner_name、sm_name和run_name字段值验证是否还存在无效数据。
以验证mediamatch_usermsg_clean表中sm_name字段值是否全为数字电视、互动电视、珠江宽频或甜果电视为例。

```hive
# 验证mediamatch_usermsg_clean表中数据是否有效
SELECT sm_name,COUNT(1) FROM mediamatch_usermsg_clean GROUP BY sm_name;

文本描述已自动生成

清洗无效收视行为数据¶

信息科技发展迅速，无论是硬件还是软件每天都在更新，在计算机、手机上使用各种视频软件或App即可实现节目观看，达到取代电视机观看的结果。因此，新时代的人们使用电视机观看节目的时间逐渐减少，甚至某些家庭已经不安装电视机了。对此，广电公司急需研究用户收视行为数据，分析用户的兴趣，提高电视机的使用率。在分析用户行为数据之前，应先进行无效收视行为数据探索，以免造成分析结果出现严重错误。本任务探索用户收视行为数据表中观看时长duration、节目类型res_type、用户观看开始时间origin_time和用户观看结束时间end_time字段的无效收视行为数据并将其进行删除。

探索无效收视行为数据¶

用户在观看电视节目时，常常为了找到喜欢的节目而频繁地切换，这时将产生大量的观看时长较短的数据，分析这样的数据对研究用户观看兴趣有较大的影响，因此需要探索并清除。此外，许多用户只是关闭了显示设备，转而进行另外的个人活动，而连接终端还处于观看状态，因此也将产生大量的观看时长过长的数据，同样地，这样的数据也属于无效数据。

为获得更有分析价值的用户收视行为数据，需要探索无效收视行为数据并将其清洗，探索过程如下。

1．统计用户收视行为记录观看时长的均值、最值和标准差¶

为了掌握用户收视行为记录中的观看时长的取值范围，以便后续业务需求探索中为用户收视行为无效数据的分析探索提供帮助，而且由于用户收视行为数据表中的记录数较多，所以有必要对用户观看时长进行基本的探索分析。使用AVG、MIN、MAX与STDDEV函数分别统计用户观看时长的均值、最小值、最大值和标准差，由于duration字段记录的是用户观看时间（以秒位单位）乘以1000的值，所以duration字段的值需要除以1000以得到以秒为单位的用户观看时长。其中duration字段值需要使用CAST函数转换成DOUBLE类型的值。

# 统计用户观看时长的均值、最值和标准差
SELECT
AVG(CAST(duration AS double)/1000) AS avg_duraion,
MIN(CAST(duration AS double)/1000) AS min_duraion,
MAX(CAST(duration AS double)/1000) AS max_duraion,
STDDEV(CAST(duration AS double)/1000) AS std_duraion 
FROM media_index;

从右图所示的统计结果可以发现，用户收视行为数据表中平均每条记录的观看时长约为1104秒（约为18分钟）。记录中观看时长最小值约为0秒，观看时长的最大值为17992秒（约为5小时），标准差约为1439秒（约为24分钟）。统计结果说明了用户观看时长的范围（0秒～5小时）是比较大的，观看时长的离散程度较小（观看时长的标准差约为24分钟）。

文本中度可信度描述已自动生成

2．统计用户收视时长分布¶

用户收视行为无效数据是指用户观看时长过短或过长的数据，这种数据出现的原因可能是用户频繁切换频道或只关闭电视机而忘记关闭机顶盒。在用户收视行为数据表中，duration字段记录了用户的每次观看时长。由于各记录的观看时长差异较大，所以需要将观看时长以每小时为区间进行划分，统计各区间的记录数。首先使用COUNT函数统计用户收视行为数据表的总记录数，可参考代码实现，统计结果为4754442条。

# 增加一个字段hour：将观看时长转为小时并向下取整；FLOOR函数用于向下取整
SELECT hour,COUNT(hour),(COUNT(hour)/4754442) AS hour_precent
FROM (
SELECT FLOOR(duration/(1000*60*60)) AS hour
FROM media_index
) h GROUP BY h.hour
ORDER BY h.hour;

接着统计观看时长以每小时为区间的记录数，因为duration字段记录的是用户观看时间（以秒为单位）乘以1000的值，所以将duration字段的值除以1000×60×60即可得到观看时长以小时为单位的值，其中子查询语句中的FLOOR函数的作用是向下取整，结果如图所示。

据图所示的统计结果可知，绝大部分的观看时长都小于1小时，约占总记录数的94%，观看时长大于等于1小时小于2小时的记录数约占总数的5.9%。由于观看时长小于1小时的记录数占了绝大部分，所以将这部分记录再按1分钟为时间间隔进行划分，分析落在每个区间的记录数分布情况，结果如图所示。

# 增加一个字段minutes：将观看时长转为以1min为间隔的数并向下取整
SELECT minutes,COUNT(minutes),(COUNT(minutes)/4754442) AS minutes_precent
FROM (
SELECT FLOOR(duration/(1000*60)) AS minutes
FROM media_index 
) m GROUP BY m.minutes 
ORDER BY m.minutes LIMIT 10;

文本描述已自动生成

由图可得用户观看时长记录数随着时间间隔而大约呈现出指数递减的趋势，其中观看时长小于1分钟的数据最多，约占总记录数的18%。为了进一步了解观看时长小于1分钟的秒级数据分布情况，再将这部分数据按秒进行划分，结果如图所示。

# 新增字段seconds：将观看时长转为以每秒为间隔的数并向下取整
SELECT seconds,COUNT(seconds),(COUNT(seconds)/4754442) AS seconds_precent
FROM (
SELECT FLOOR(duration/1000) AS seconds
FROM media_index 
) s GROUP BY s.seconds 
ORDER BY s.seconds LIMIT 10;

文本描述已自动生成

左图只展现了1～10s的记录数，为了更直观地展现1分钟内每秒的记录数和分布情况，将统计结果保存至Linux本地系统并使用Python、Excel或MATLAB等工具制作折线图，如右图所示。由右图可得，观看时长在1～19秒的每个区间内的观看记录数相差不大，从20秒开始每个区间的记录数远高于1～19秒每个区间的记录数。综合以上的分析统计结果以及结合业务的实际情况，将观看时长小于20秒和大于5小时的数据视为无效数据，需要将这些数据删除以便能够更好地分析用户的收视行为。

7-12

文本描述已自动生成

3．查询机顶盒自动返回的数据¶

在用户收视行为数据表中，还有一部分数据的节目类型为直播，即res_type字段值为0时，观看行为开始时间origin_time和观看行为结束时间end_time的秒时间单位为00结尾的记录，这些记录是机顶盒自动返回的数据，并不是用户真实的观看记录。因此这一部分数据也是需要删除的。探索分析由机顶盒自动返回的数据以及其数据量的大小，使用“LIKE '%00'”语句即可查询某字段以00结尾的数据，结果如图所示。

# 查询rest_type字段值为0时，origin_time和end_time结尾为00的记录
SELECT COUNT(*) 
FROM media_index 
WHERE res_type='0' 
AND origin_time LIKE '%00'
AND end_time LIKE '%00';

从图所示的统计分析结果来看，用户收视行为数据表中res_type字段值为0时，origin_time和end_time的秒时间单位为00的记录的确存在并且记录数约为88万，因此在进行数据预处理时需要清洗这部分无效的数据。

文本中度可信度描述已自动生成

删除无效收视行为数据¶

对广电用户收视行为数据表的探索，主要是探索用户的观看时长，将无效的收视数据进行清洗。本小节的任务是获得更有分析价值的用户收视行为数据，探索无效收视行为数据并将其进行清洗，任务实现步骤如下。统计用户观看时长的均值、最值和标准差，探索用户观看时长范围。由图所示的统计结果可得，用户的观看时长范围为0～5小时。根据统计用户观看时长分布结果分析，删除观看时长小于20秒和观看时长大于5小时的数据。删除节目类型为直播，即res_type字段值为0时，观看行为开始时间origin_time和观看行为结束时间end_time为00的无效收视行为数据。

#删除无效收视行为数据
CREATE TABLE IF NOT EXISTS media_index_clean
AS 
SELECT * FROM media_index 
WHERE 
(CAST(duration AS double)/1000 >= 20 
AND 
CAST(duration AS double)/(1000*60*60) < 5
AND
res_type='0'
AND
origin_time NOT LIKE '%00'
AND
end_time NOT LIKE '%00')
OR
(CAST(duration AS double)/1000 >= 20
AND
CAST(duration AS double)/(1000*60*60) < 5
AND
res_type='1');

如果需要验证media_index_clean表中的数据是否有效，可以通过查询观看时长duration字段的最大值、最小值，以及节目类型res_type字段是否还存在无效数据实现。

# 验证media_index_clean表中数据是否有效
SELECT COUNT(*) 
FROM media_index_clean 
WHERE res_type='0' 
AND origin_time LIKE '%00'
AND end_time LIKE '%00';

以验证media_index_clean表中数据是否有效为例，结果如图所示，可知media_index_clean不存在直播的节目类型。

清洗无效账单和订单数据¶

账单是指与消费者发生交易的商户或公司向消费者提供的账目发生明细单，也是商户或公司记录和统计营收的数据依据。订单是订购货物的合同或单据。用户在挑选商品后，在实体店前台或在网上下单，这时需要打印订单表。订单表记录了用户的订购产品详细信息，一般包括用户名称、订购货物名称、订购金额和订购数量等。

广电公司的用户账单数据表和订单数据表分别记录了各用户的消费详情和订购产品信息。这两个表中存在无效数据，在进行营收统计分析时，若采用了无效的订单和账单数据，则将导致统计错误，因此需要对用户账单数据表和订单数据表进行数据清洗。本任务探索用户账单数据表中用户应付金额should_pay字段和用户订单数据表中订购产品价格cost字段的无效数据并进行清洗。

探索无效账单数据¶

无效账单数据是指账单数据表mmconsume_billevents的用户应付金额should_pay字段值小于0的数据。若在统计营收金额时将应收金额小于0的数据算入，则将造成营收统计错误。查询无效账单的数量，结果如图所示。

# 统计应收金额小于0的数据
SELECT COUNT(1)
FROM mmconsume_billevents
WHERE should_pay < 0;

由图所示的统计结果可得，账单数据表中存在377条无效账单数据，需要将这些无效账单数据清洗。

文本低可信度描述已自动生成

探索无效订单数据¶

无效订单数据是指订单数据表order_index的订购产品价格cost字段值为空或小于0的数据。若在统计用户订单金额时将无效订购产品价格算入，将造成错误的收入预算，会对公司资产调用和计划支出造成重大影响。查询无效订单数据的数量。

# 查询无效订单数据的数量
SELECT COUNT(*)
FROM order_index
WHERE cost IS NULL
OR
cost < 0;

执行代码中的代码得出无效订单数据的数量为0，即不存在订购产品价格cost字段值为空或小于0的数据，因此无须清洗，如图所示。

文本低可信度描述已自动生成

删除无效账单和无效订单数据¶

本小节任务是删除无效账单和无效订单数据，经探索分析得用户订单数据表中不存在无效订单数据，而用户账单数据表中存在无效账单数据，因此需删除无效账单数据即可。先创建mmconsume_billevents_clean表，再删除账单数据表中的无效账单数据，最后将清洗好的数据导入mmconsume_billevents_clean表中，如图。

CREATE TABLE IF NOT EXISTS mmconsume_billevents_clean
AS
SELECT * FROM mmconsume_billevents
WHERE should_pay >= 0 ;

可参考代码统计mmconsume_billevents_clean中应收金额小于0的数据量，验证是否删除了无效账单数据，若统计值为0，则完成了删除无效账单数据，如图。

文本描述已自动生成

导出处理结果至Linux本地和HDFS¶

随着移动互联网和物联网的出现和快速发展，数据量也在爆发式增长，对应的数据存储量也在增大，因此诞生了分布式存储框架，用于解决大数据量的存储问题。广电公司在清洗完数百万条数据后，需要将数据进行保存。本任务将清洗后的数据保存在Linux本地目录和HDFS目录下。

使用INSERT OVERWRITE语句将数据导出至文件系统¶

在Hive CLI中实现Hive表数据的导出，可以使用INSERT OVERWRITE语句编写选择导出的文件系统目录和表内容。使用INSERT OVERWRITE语句将数据导出至文件系统目录的语法如下。

参数	说明
DIRECTORY	在参数后指定数据导出的目录
LOCAL	若使用LOCAL参数，则Hive会将数据写入Linux本地目录下。若不使用LOCAL参数，则将数据写入HDFS目录下
ROW FORMAT	Hive 0.11.0开始允许指定字段间的分隔符，默认按^A字符(\001)分隔
STORED AS	用于指定文件存储格式，如TXT文档或ORC优化行柱状文件等
SELECT ... FROM ...	用于选择存储内容

使用INSERT OVERWRITE语句和LOCAL DIRECTORY参数将用代码所创建的账单数据清洗表mmconsume_billevents_clean中的数据导出至Linux本地目录/opt/zjsm_clean/mmconsume_billevents_clean（目录需先手动创建）下，如图。

INSERT OVERWRITE LOCAL DIRECTORY '/opt/zjsm_clean/mmconsume_billevents_clean' 
ROW FORMAT DELIMITED FIELDS TERMINATED by ','
SELECT * FROM mmconsume_billevents_clean;

执行代码，在Linux本地目录/opt/zjsm_clean/mmconsume_billevents_clean下生成一个名为000000_0的文件，文件记录了mmconsume_billevents_clean表中的数据。在Linux终端查看000000_0文件的前10条数据，结果如图。

# 进入/opt/zjsm_clean/mmconsume_billevents_clean目录
cd /opt/zjsm_clean/mmconsume_billevents_clean
#查看000000_0文件的前10条数据
cat 000000_0 | head -n 10

保存处理结果至Linux本地和HDFS¶

本章对广电用户数据的5个表进行了无效数据探索并清洗，其中数据清洗规则如图。 | 数据表 | 数据清洗规则 | | --- | --- | | 账单数据表订单数据表用户基本数据表用户收视行为数据表 | 删除owner_name字段值为EA级、EB级、EC级、ED级、EE级的数据。删除owner_code字段值为2、9、10的数据。保留sm_name字段值为珠江宽频、数字电视、互动电视、甜果电视的数据。 | | 用户状态变更数据表订单数据表用户基本数据表 | 保留run_name字段值为正常、主动暂停、欠费暂停、主动销户的数据。 | | 用户基本数据表 | 用户编号phone_no数据去重 | | 账单数据表 | 删除应收金额should_pay字段值小于0的记录 | | 用户收视行为数据表 | 收视时长duration字段值大于等于20秒且duration字段值小于等于5小时的数据。删除用户收视行为数据表中res_type字段值为0时，origin_time和end_time中为00的数据（以秒为单位） | | 用户状态变更数据表 | 删除owner_name字段值为EA级、EB级、EC级、ED级、EE级。删除owner_code字段值为2、9、10的数据。 |

Linux本地文件系统的安全性较好，且传输数据文件较为方便，如将文件传至Windows系统时，无须启动Hadoop。HDFS能够解决大数据量的存储问题，且存储效率较高。因此本小节将实现保存Hive清洗后的数据表至Linux本地目录下和HDFS目录下。以保存清洗后的用户基本数据表mediamatch_usermsg_clean为例，使用INSERT OVERWRITE语句实现保存至Linux的/opt/zjsm_clean/mediamatch_usermsg_clean目录下和HDFS的/opt/zjsm_clean/mediamatch_usermsg_clean目录下。

# 进入/opt/zjsm_clean/mediamatch_usermsg_clean目录
cd /opt/zjsm_clean/mediamatch_usermsg_clean
# 查看保存至Linux目录下的结果
cat 000000_0 | head -n 10
# 查看保存至HDFS目录下的结果
hdfs dfs -cat /opt/zjsm_clean/mediamatch_usermsg_clean/000000_0 | head -n 10

# 将数据保存至Linux目录下
INSERT OVERWRITE LOCAL DIRECTORY '/opt/zjsm_clean/mediamatch_usermsg_clean'
ROW FORMAT DELIMITED FIELDS TERMINATED by ','
SELECT * FROM mediamatch_usermsg_clean;
# 将数据保存至HDFS目录下
INSERT OVERWRITE DIRECTORY '/opt/zjsm_clean/mediamatch_usermsg_clean'
ROW FORMAT DELIMITED FIELDS TERMINATED by ','
SELECT * FROM mediamatch_usermsg_clean;

小结¶

本章的目标是先探索广电用户无效数据，再进行数据清洗与保存。首先探索了无效用户数据，如探索重复的用户数、特殊线路用户数据和政企用户数据等。其次探索了无效收视行为数据，主要探索用户观看时长。接着探索了无效账单和订单数据，探索用户应付金额should_pay字段和订购产品价格cost字段是否存在小于0的数据，再按照探索结果进行数据清洗。最终将清洗结果保存至Linux本地目录和HDFS目录。