第14周 · 教育大数据分析平台（二）

教育大数据分析平台（二）

1.1数据采集

创建/home/hadoop/shell/sql/目录，将相应的sql文件拷入，具体操作如下：

[hadoop@hadoop1 ~]$ cd shell

[hadoop@hadoop1 shell]$ mkdir sql

[hadoop@hadoop1 shell]$

如图1-1所示，使用FileZilla工具将相应的sql文件上传至sql目录。

📷 图1-1 上传相应sql文件

1.1.1创建数据库以及相应表格

1.使用hive用户登录MySQL具体操作如下所示。

[root@hadoop1 ~]# mysql -h hadoop1 -u hive -p

在MySQL数据库的命令行交互界面执行如下命令：

mysql>source /home/hadoop/shell/sql/nev.sql

如图1-2所示，数据库文件导入完成后执行“show databases;”命令查看数据库列表。

📷 图1-2 查看数据库列表

如图1-3所示，在MySQL的命令行交互界面执行“use nev;”命令，切换到数据库nev，然后执行“show tables;”命令查看数据库nev中的数据表。

📷 图1-3 查看数据库nev中的数据表

1.1.2 Sqoop采集MySQL数据库

1.启动集群相关服务

（1）启动Zookeeper集群。

①方法1：

[hadoop@hadoop1 zookeeper]$ bin/zkServer.sh start

[hadoop@hadoop2 zookeeper]$ bin/zkServer.sh start

[hadoop@hadoop3 zookeeper]$ bin/zkServer.sh start

②方法2：

[hadoop@hadoop1 ~]$ runRemoteCmd.sh '/home/hadoop/app/zookeeper/bin/zkServer.sh start' all

（2）启动HDFS集群。

[hadoop@hadoop1 ~]$ cd app/hadoop

[hadoop@hadoop1 hadoop]$ sbin/start-dfs.sh

（3）启动YARN集群。

[hadoop@hadoop1 hadoop]$ sbin/start-yarn.sh

（4）启动MySQL服务。

[hadoop@hadoop1 hadoop]$ service mysqld start

1.MySQL数据迁移至Hive

（1）进去到sqoop安装目录，命令如下：

[hadoop@hadoop1 ~]$ cd /home/hadoop/app/sqoop

（2）执行Sqoop命令将MySQL数据库的用户会话信息表web_chat_ems_2019_07中的数据迁移到Hive数据仓库源数据层的web_chat_ems_ods源数据表，具体命令如下所示。

[hadoop@hadoop1 sqoop]$bin/sqoop import \

--connect jdbc:mysql://191.168.2410.161:3306/nev \

--username hive \

--password hive \

--query "select

id,create_date_time,session_id,sid,create_time,seo_source,seo_keywords,ip,area,

country,province,city,origin_channel,user as

user_match,manual_time,begin_time,end_time,last_customer_msg_time_stamp,last_agent_msg_time_stamp, reply_msg_count,msg_count,browser_name,os_info from

web_chat_ems_2019_07 where 1=1 and \$CONDITIONS" \

--hcatalog-database hnsoft_ods \

--hcatalog-table web_chat_ems_ods \

-m 2 \

--split-by id

（3）在虚拟机hadoop1执行Sqoop命令将MySQL数据库的用户会话信息附属表

web_chat_text_ems_2019_07中的数据迁移到Hive数据仓库源数据层的web_chat_text_ems_ods源数据表，具体命令如下所示。

[hadoop@hadoop1 sqoop]$bin/sqoop import \

--connect jdbc:mysql://191.168.2410.161:3306/nev \

--username hive \

--password hive \

--query "select id,from_url,landing_page_url,url_title,platform_description,other_params,history from web_chat_text_ems_2019_07 where 1=1 and \$CONDITIONS" \

--hcatalog-database hnsoft_ods \

--hcatalog-table web_chat_text_ems_ods \

-m 2 \

--split-by id

（4）统计源数据表web_chat_ems_ods中包含的数据行数，具体命令如下。命令执行完成后,源数据表web_chat_ems_ods数据行数的统计结果。

select count(*) from hnsoft_ods.web_chat_ems_ods;

（5）统计源数据表 web_chat_text_ems_ods中包含的数据行数,具体命令如下。执行完成统计命令后，展示源数据表web_chat_text_ems_ods数据行数的统计结果。

select count(*) from hnsoft_ods.web_chat_text_ems_ods;

数据转换

（1） 两个源数据表进行合并过程中还需要根据业务需求对指定字段的数据格式进行转换，具体转换规则如下：

截取会话创建时间字段create_time的年和月，便于后续根据年和月的维度进行统计。

将用户发送消息数字段msg_count中的null值填充为数值0。

保持会话创建时间字段create_time的格式统一。

（2）使用datagrip远程连接虚拟机hadoop1的HiveServer2服务实现

数据转换，具体命令如下。

set hive.exec.dynamic.partition.mode=nonstrict

INSERT INTO TABLE hnsoft_dwd.visit_consult_dwd

PARTITION (yearinfo, monthinfo)

SELECT

wce.session_id,

wce.sid,

UNIX_TIMESTAMP(wce.create_time) create_time,

wce.ip,

wce.area,

IF(wce.msg_count IS NULL,0,CAST(wce.msg_count AS INT)) msg_count,

wce.origin_channel,

NVL(wcte.from_url,'') from_url,

SUBSTR(wce.create_time,1,4) yearinfo,

SUBSTR(wce.create_time,6,2) monthinfo

FROM hnsoft_ods.web_chat_ems_ods wce LEFT JOIN

hnsoft_ods.web_chat_text_ems_ods wcte

ON wce.id = wcte.id;

1.2 数据分析

实现地区访问用户量统计

1. 本需求是统计不同地区访问网站的用户量，公司决策者可以根据统计结果对不同地区的业务进行调整,例如访问用户量比较多的地区可以增加客服人数,减少用户等待时间；访问用户量比较少的地区可以加大推广，使更多用户了解并访问网站。接下来，实现地区访问用户量统计，统计结果将存储在访问用户量宽表visit_dws，具体命令如下。

INSERT INTO hnsoft_dws.visit_dws PARTITION (yearinfo,monthinfo)

SELECT

count(DISTINCT sid) sid_total,

count(DISTINCT session_id) sessionid_total,

count(DISTINCT ip) ip_total,

area,

'-1' origin_channel,

'-1' from_url,

'1' grouptype,

yearinfo,

monthinfo

FROM hnsoft_dwd.visit_consult_dwd

GROUP BY area,yearinfo,monthinfo;

地区访问用户量统计的命令执行完成后，查看地区访问用户量统计结果的前6条数据，具体命令如下。

SELECT

sid_total,sessionid_total,ip_total,area,yearinfo,monthinfo

FROM hnsoft_dws.visit_dws

WHERE grouptype='1'

LIMIT 6;

📷 图1-4 实现地区访问用户量统计

实现会话页面排行榜

1. 会话页面是指用户创建客服会话的页面,本需求是统计会话页面排行榜，公司决策者可以根据统计结果了解网站中哪些页面对用户比较有吸引力,促使用户向客服咨询。例如用户在浏览网站中的大数据学科相关课程介绍页面时，有大量用户向客服进行咨询，那么从侧面可以反映出用户对于大数据学科学习的需求量比较高。接下来，统计会话页面，统计结果存储在访问用户量宽表visit_dws，具体命令如下。

INSERT INTO hnsoft_dws.visit_dws PARTITION (yearinfo,monthinfo)

SELECT

count(DISTINCT sid) sid_total,

count(DISTINCT session_id) sessionid_total,

count(DISTINCT ip) ip_total,

'-1' area,

'-1' origin_channel,

from_url,

'3' grouptype,

yearinfo,

monthinfo

FROM hnsoft_dwd.visit_consult_dwd

GROUP BY from_url,yearinfo,monthinfo;

1. 统计会话页面的命令执行完成后,查看根据用户SessionID统计会话页面(sessionid_ total)的结果中排名第一的会话页面,具体命令如下。

SELECT

sessionid_total,from_url,yearinfo,monthinfo

FROM hnsoft_dws.visit_dws

WHERE grouptype='3' AND from_url!=""

ORDER BY sessionid_total DESC

LIMIT 1;

📷 图1-5 实现会话页面排行榜

实现访问用户量统计

1. 本需求是统计访问网站的总用户量，公司决策者可以根据统计结果了解网站的访问情况，并根据实际情况对网站内容以及业务进行有效调整，同时访问用户量统计结果也可以作为二次分析的底层数据，例如咨询率统计就需要访问用户量统计结果 。接下来，统计访问用户量,统计结果存储在访问用户量宽表visit_dws,具体命令如下。

INSERT INTO hnsoft_dws.visit_dws PARTITION (yearinfo,monthinfo)

SELECT

count(DISTINCT sid) sid_total,

count(DISTINCT session_id) sessionid_total,

count(DISTINCT ip) ip_total,

'-1' area,

'-1' origin_channel,

'-1' from_url,

'4' grouptype,

yearinfo,

monthinfo

FROM hnsoft_dwd.visit_consult_dwd

GROUP BY yearinfo,monthinfo;

1.实现访问用户量统计的命令执行完成后，查看访问用户量统计结果，具体命令如下。

SELECT

sid_total,sessionid_total,ip_total,yearinfo,monthinfo

FROM hnsoft_dws.visit_dws

WHERE grouptype='4';

📷 图1-6 实现访问用户量统计

实现来源渠道访问用户量统计

1. 本需求是统计通过不同来源渠道访问网站的用户量，公司决策者可以根据统计结果了解不同渠道投放广告的效果，并根据实际情况进行调整，例如投放效果好的渠道可以加大投资，投放效果不好的渠道可以舍弃等。接下来，统计来源渠道访问用户量，统计结果将存储在访问用户量宽表visit_dws。

INSERT INTO hnsoft_dws.visit_dws PARTITION (yearinfo,monthinfo)

SELECT

count(DISTINCT sid) sid_total,

count(DISTINCT session_id) sessionid_total,

count(DISTINCT ip) ip_total,

'-1' area,

origin_channel,

'-1' from_url,

'2' grouptype,

yearinfo,

monthinfo

FROM hnsoft_dwd.visit_consult_dwd

GROUP BY origin_channel,yearinfo,monthinfo;

1. 实现来源渠道访问用户量统计的命令执行完成后，查看来源渠道访问用户量统计结果，具体命令如下。

SELECT

sid_total,sessionid_total,ip_total,origin_channel,yearinfo,monthinfo

FROM hnsoft_dws.visit_dws

WHERE grouptype='2';

📷 图1-7 实现来源渠道访问用户量统计

实现咨询率统计

1. 本需求从访问网站的用户中统计发起有效咨询的用户比例,公司决策者可以根据统计结果优化网站结构及客服话术，使用户能更深一步了解课程内容，并发起有效咨询。统计咨询用户量，将统计结果存储在咨询用户量宽表visit_dws，命令如下。

INSERT INTO hnsoft_dws.consult_dws PARTITION (yearinfo,monthinfo)

SELECT

count(DISTINCT sid) sid_total,

count(DISTINCT session_id) sessionid_total,

count(DISTINCT ip) ip_total,

yearinfo,

monthinfo

FROM hnsoft_dwd.visit_consult_dwd

WHERE msg_count >= 1

GROUP BY yearinfo, monthinfo;

1. 实现咨询用户量统计的命令执行完成后，通过访问用户量和咨询用户量统计结果实现咨询率统计。

具体命令如下。

设置参数hive.strict.checks.cartesian.product为false，设置参数hive.mapred.mode为非strict，即nonstrict非严格模式，设置这两个参数临时生效的命令如下。

set hive.mapred.mode=nostrict;

set hive.strict.checks.cartesian.product=false;

设置两个参数临时生效的命令执行完成后，再次执行实现咨询率统计命令。

WITH msgNumber AS (

SELECT

sid_total,

sessionid_total,

ip_total,

yearinfo,

monthinfo

FROM hnsoft_dws.consult_dws

),

totalNumber AS (

SELECT

sid_total,

sessionid_total,

ip_total,

yearinfo,

monthinfo

FROM hnsoft_dws.visit_dws

WHERE grouptype='4'

)

SELECT

concat(round(msgNumber.sid_total/totalNumber.sid_total,4)*100,'%')

sid_rage,

concat(round(msgNumber.sessionid_total/totalNumber.sessionid_total,4)*100,'%

') session_rage,

concat(round(msgNumber.ip_total/totalNumber.ip_total,4)*100,'%')

ip_rage,

msgNumber.yearinfo,

msgNumber.monthinfo

FROM msgNumber,totalNumber;

📷 图1-8 实现来源渠道访问用户量统计

II