目录
一、项目概述
1、概述
二、字段解释分析
1、数据字段
2、应用大类
3、应用小类
三、项目架构
四、数据收集清洗
1、数据收集
2、数据清洗
五、Sqoop使用
1、简介
2、Sqoop安装步骤
3、Sqoop的基本命令
六、数据导出
七、数据可视化
1、概述
2、步骤
八、扩展
1、各个网站的表现能力
2、小区HTTP上网能力
3、小区上网喜好
4、查询语句
一、项目概述
1、概述
①、当用户通过网络设备(手机、平板电脑、笔记本电脑等)进行上网的时候，信号经过基站进行传输的时候，会在基站这儿留下日志
②、可以通过分析日志来获取用户的行为记录，例如用户浏览了哪个网站或者用户喜欢哪个APP等行为，从而可以做到相对精准的推荐
二、字段解释分析
1、数据字段
2、应用大类
3、应用小类
三、项目架构
①、因为此处做的是离线的数据分析，所以没有使用到HBase
②、硬件环境：至少需要三台服务器，每一台服务器至少是2核4G(推荐是2核8G)，磁盘至少是20G(推荐是50G~60G)
③、软件环境：三台服务器上都需要安装Flume，第一台服务器上需要安装Hive。可以选择安装Hadoop的伪分布式或者也可以选择安装Hadoop的完全分布式，如果选择安装Hadoop的完全分布式，那么在这之前需要先安装好Zookeeper
四、数据收集清洗
1、数据收集
①、在实际生产环境中，电信流量日志肯定不只是在一台服务器上产生，而是每一台服务器都会产生流量日志。所以此时，需要先搭建Flume的扇入流动模型，之后将收集到的数据传输到HDFS上进行存储
②、步骤
Ⅰ、在第二台和第三台服务器上来创建对应的目录用于存储日志(将第二台和第三台服务器当作是日志产生的服务器)
cd /home
mkdir telecomlog
Ⅱ、进入对应的目录，将日志上传或者下载到指定目录下(实际过程中，日志肯定是实时产生的)
cd telecomlog/
rz
这里的数据我们是提前准备好的模拟数据，想要的话可以私信我：
第二台服务器
第三台服务器
Ⅲ、收集第二台和第三台服务器上的日志，将收集到的日志传输到第一台服务器上，进行数据的扇入
hadoop02和hadoop03：
cd /home/software/apache-flume-1.9.0-bin/data
编辑文件 vim telecomlog.conf
在文件中添加如下内容：
a1.sources = s1a1.channels = c1a1.sinks = k1# 日志是放在指定目录下的# 所以此时监控指定目录下的变化# 如果目录下产生了新的文件# 需要收集这个新文件中的内容a1.sources.s1.type = spooldir# 指定要监听的目录a1.sources.s1.spoolDir = /home/telecomlog# 配置Channela1.channels.c1.type = memorya1.channels.c1.capacity = 10000a1.channels.c1.transactionCapacity = 1000# 需要将收集到的数据发送到第一台服务器上a1.sinks.k1.type = avroa1.sinks.k1.hostname = hadoop01a1.sinks.k1.port = 8090# 绑定a1.sources.s1.channels = c1a1.sinks.k1.channel = c1
Ⅳ、数据收集到第一台服务器上之后，需要将收集到的数据写到HDFS上
hadoop01：
cd /home/software/apache-flume-1.9.0-bin/data/
编辑文件vim telecomlog.conf
在文件中添加如下内容：
a1.sources = s1a1.channels = c1a1.sinks = k1# 需要接收第二台和第三台服务器传输来的数据a1.sources.s1.type = avroa1.sources.s1.bind = 0.0.0.0a1.sources.s1.port = 8090# 需要在数据中来添加一个时间戳-用于提取数据按天存储a1.sources.s1.interceptors = i1a1.sources.s1.interceptors.i1.type = timestamp# 配置Channela1.channels.c1.type = memorya1.channels.c1.capacity = 10000a1.channels.c1.transactionCapacity = 1000# 配置Sink# 需要将数据写到HDFS上，最好能够实现数据按天存储a1.sinks.k1.type = hdfs# 指定数据在HDFS上的存储路径a1.sinks.k1.hdfs.path = hdfs://hadoop01:9000/telecomlog/reporttime=%Y-%m-%d# 指定文件在HDFS上的存储类型a1.sinks.k1.hdfs.fileType = DataStream# 指定文件的滚动时间间隔a1.sinks.k1.hdfs.rollInterval = 3600a1.sinks.k1.hdfs.rollSize = 0a1.sinks.k1.hdfs.rollCount = 0# 绑定a1.sources.s1.channels = c1a1.sinks.k1.channel = c1
Ⅴ、启动HDFS
start-dfs.sh
Ⅵ、启动第一台服务器上的Flume
../bin/flume-ng agent -n a1 -c ../conf -f telecomlog.conf -Dflume.root.logger=INFO,console
Ⅶ、启动第二台服务器和第三台服务器上的Flume
../bin/flume-ng agent -n a1 -c ../conf -f telecomlog.conf -Dflume.root.logger=INFO,console
注意如果报错，可能是高可用的原因你的active不是在hadoop01上解决办法可以参考
大数据笔记--Hadoop（第六篇）的常见错误，解决完之后要注意文件被搜集之后会重新命名，所以我们要把名字改回来，然后再次启动
2、数据清洗
①、利用Flume将数据收集到了HDFS上，那么此时需要在Hive中建表来管理原始数据
启动YARN：
start-yarn.sh
进入HBase的安装目录的lib目录下：
cd /home/software/hbase-2.4.2/lib
进入子目录：
cd client-facing-thirdparty/
重命名：解决进入hive打印日志太多的问题
mv commons-logging-1.2.jar commons-logging-1.2.bak
mv log4j-1.2.17.jar log4j-1.2.17.bak
mv slf4j-log4j12-1.7.30.jar slf4j-log4j12-1.7.30.bak
启动Hive的服务进程：
hive --service metastore &
hive --service hiveserver2 &
进入Hive客户端：
hive
创建库：
create database telecom;
使用这个库：
use telecom;
建表（外部表）来管理原始数据：
修复分区：
msck repair table telecom;
数据抽样：
select * from telecom tablesample(5 rows);
②、原始数据中包含了77个字段，加上分区在内，一共有78个字段，但是这78个字段不代表都是需求中所需要的，所以此时需要从这77个字段中来抽取真正有用的字段来进行下一步的处理- 此时需要对数据进行第一次的简单的清洗
③、确定好要抽取的字段之后，需要建立一个表来存储这些被抽取的字段
建立抽取的字段表- 事实表：
从原始表中来抽取字段放入事实表中：
抽样数据：
select * from dataclear tablesample(5 rows);
④、抽取完字段之后，需要对字段整理，例如格式的转化，字段的整合，字段的填充等
建表来存储整理好格式之后的数据：
建表语句：
抽取字段：
insert overwrite table f_http_app_host select reporttime, appType, appSubtype, userIp, userPort, appServerIP, appServerPort, host, if(cellid == '', "000000000", cellid), if(appTypeCode == 103, 1, 0), if(appTypeCode == 103 and find_in_set(transStatus, "10,11,12,13,14,15,32,33,34,35,36,37,38,48,49,50,51,52,53,54,55,199,200,201,202,203,204,205,206,302,304,306")!=0 and interruptType == 0, 1, 0), if(apptypeCode == 103, trafficUL, 0), if(apptypeCode == 103, trafficDL, 0), if(apptypeCode == 103, retranUL, 0), if(apptypeCode == 103, retranDL, 0), if(appTypeCode == 103 and transStatus == 1 and interruptType == 0, 1, 0), if(appTypeCode == 103, procdureEndTime - procdureStartTime, 0) from dataclear;抽样数据：
select * from f_http_app_host tablesample(5 rows);
⑤、可以根据不同的子需求对数据进行处理。例如：各个APP的受欢迎程度，此时需要再从上边整理好的数据中再次抽取需要的字段来进行分析
确定好字段之后，需要建表来存储数据
建表语句：
从整理好的数据中来抽取整合，存储到子表中：
insert overwrite table D_H_HTTP_APPTYPE select reporttime, apptype, appsubtype, sum(attempts), sum(accepts), round(sum(accepts)/sum(attempts), 2), sum(trafficUL), sum(trafficDL), sum(trafficUL)+sum(trafficDL), sum(retranUL), sum(retranDL), sum(retranUL)+sum(retranDL), sum(failCount), sum(transDelay)from f_http_app_host group by reporttime, apptype, appsubtype;抽样数据：
select * from D_H_HTTP_APPTYPE TABLESAMPLE(5 ROWS);
查询最受欢迎的5个APP - 哪个APP的流量或者访问次数比较多：
select appSubtype, sum(totalTraffic) as total from D_H_HTTP_APPTYPE group by appSubtype sort by total desc limit 5;
五、Sqoop使用
1、简介
Sqoop是Apache提供的一套用于进行HDFS和关系型数据库之间数据导入和导出的机制，利用Sqoop可以轻松的将数据从HDFS中导出到数据库中，可以将数据从数据库中导入到HDFS中
2、Sqoop安装步骤
进入/home/software/目录下，下载Sqoop的安装包
cd /home/software/
rzsqoop-1.4.7.bin__hadoop-2.6.0.tar.gz
解压：
tar -xvf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz
重命名：
mv sqoop-1.4.7.bin__hadoop-2.6.0 sqoop-1.4.7
进入Sqoop的配置目录：
cd sqoop-1.4.7/conf/
复制文件：
cp sqoop-env-template.sh sqoop-env.sh
编辑文件：vim sqoop-env.sh
在文件尾部添加：
export HADOOP_COMMON_HOME=/home/software/hadoop-3.1.3export HADOOP_MAPRED_HOME=/home/software/hadoop-3.1.3export HIVE_HOME=/home/software/hive-3.1.2export ZOOKEEPER_HOME=/home/software/apache-zookeeper-3.5.7-binexport ZOOCFGDIR=/home/software/apache-zookeeper-3.5.7-bin/conf保存退出，重新生效：
source sqoop-env.sh
进入Sqoop安装目录的lib目录
cd ../lib
添加MySQL的连接驱动包
rz 之前上传过，版本为mysql-connector-java-5.1.27.jar
配置环境变量：
vim /etc/profile
在文件末尾添加：
export SQOOP_HOME=/home/software/sqoop-1.4.7export PATH=$PATH:$SQOOP_HOME/bin保存退出，重新生效：
source /etc/profile
检查是否配置正确：
sqoop version
3、Sqoop的基本命令
六、数据导出
1、在MySQL来创建的对应的库和表
create database telecom;
use telecom;
2、在MySQL建表
建立总表
create table F_HTTP_APP_HOST( reporttime datetime, appType bigint, appSubtype bigint, userIP varchar(20), userPort bigint, appServerIP varchar(20), appServerPort bigint, host varchar(255), cellid varchar(20), attempts bigint, accepts bigint, trafficDL bigint, trafficUL bigint, retranUL bigint, retranDL bigint, failCount bigint, transDelay bigint) ;建立各个APP的受欢迎程度表：
create table D_H_HTTP_APPTYPE(hourid datetime, appType int, appSubtype int, attempts bigint, accepts bigint, succRatio double, trafficUL bigint, trafficDL bigint, totalTraffic bigint, retranUL bigint,retranDL bigint, retranTraffic bigint, failCount bigint, transDelay bigint) ;
3、将HDFS上的数据导出到MySQL中：
sqoop export --connect jdbc:mysql://hadoop01:3306/telecom --username root --password root --export-dir '/user/hive/warehouse/telecom.db/f_http_app_host/000000_0' --table F_HTTP_APP_HOST -m 1 --fields-terminated-by '|'
sqoop export --connect jdbc:mysql://hadoop01:3306/telecom --username root --password root --export-dir '/user/hive/warehouse/telecom.db/d_h_http_apptype/000000_0' --table D_H_HTTP_APPTYPE -m 1 --fields-terminated-by '|'
七、数据可视化
1、概述
①、数据导出到MySQL之后，需要进行可视化，可视化的目的是为了让数据进行更直观的展现，例如可以以折线图或者曲线图的形式来展现数据的增长趋势，可以用圆形图或者饼形图的形式来展现各部分数据所占的比例
②、市面上可视化的工具非常多，例如说Echarts、HighCharts等工具都可以进行可视化，同时市面上大部分的BI工具也都提供了可视化功能，例如TableAU，Power BI等机制
2、步骤
①、需要在Windows系统中来安装MySQL的连接驱动
②、需要在Windows系统中来安装Power BI工具
一直下一步就行
③、打开Power BI，配置连接
配置凭据：
建立层次结构，然后进行比较
八、扩展
1、各个网站的表现能力
字段：
建表语句：
create table D_H_HTTP_HOST(hourid datetime, host varchar(255), appserverip varchar(20), attempts bigint, accepts bigint, succratio bigint, trafficul bigint, trafficdl bigint, totaltraffic bigint, retranul bigint, retrandl bigint, retrantraffic bigint, failcount bigint, transdelay bigint);
插入语句：
insert into D_H_HTTP_HOST select reporttime as hourid, host, appserverip, sum(attempts) as attempts, sum(accepts) as accepts, sum(accepts)/sum(attempts) as succRatio, sum(trafficul) as trafficul, sum(trafficdl) as trafficdl, sum(trafficul)+sum(trafficdl) as totaltraffic, sum(retranul) as retranul, sum(retrandl) as retrandl, sum(retranul)+sum(retrandl) as retrantraffic, sum(failcount) as failcount, sum(transdelay) as transdelay from F_HTTP_APP_HOST group by reporttime, host, appserverip;
2、小区HTTP上网能力
字段：
建表语句：
create table D_H_HTTP_CELLID(hourid datetime, cellid varchar(20), attempts bigint, accepts bigint, succratio bigint, trafficul bigint, trafficdl bigint, totaltraffic bigint, retranul bigint, retrandl bigint, retrantraffic bigint, failcount bigint, transdelay bigint);
插入语句：
insert into D_H_HTTP_CELLID select reporttime as hourid, cellid, sum(attempts) as attempts, sum(accepts) as accepts, sum(accepts)/sum(attempts) as succRatio, sum(trafficul) as trafficul, sum(trafficdl) as trafficdl, sum(trafficul)+sum(trafficdl) as totaltraffic, sum(retranul) as retranul, sum(retrandl) as retrandl, sum(retranul)+sum(retrandl) as retrantraffic, sum(failcount) as failcount, sum(transdelay) as transdelay from F_HTTP_APP_HOST group by reporttime, cellid;
3、小区上网喜好
字段：
建表语句：
create table D_H_HTTP_CELLID_HOST(hourid datetime, cellid varchar(20), host varchar(255), attempts bigint, accepts bigint, succratio bigint, trafficul bigint, trafficdl bigint, totaltraffic bigint, retranul bigint, retrandl bigint, retrantraffic bigint, failcount bigint, transdelay bigint);
插入语句：
insert into D_H_HTTP_CELLID_HOST select reporttime as hourid, cellid, host, sum(attempts) as attempts, sum(accepts) as accepts, sum(accepts)/sum(attempts) as succRatio, sum(trafficul) as trafficul, sum(trafficdl) as trafficdl, sum(trafficul)+sum(trafficdl) as totaltraffic, sum(retranul) as retranul, sum(retrandl) as retrandl, sum(retranul)+sum(retrandl) as retrantraffic, sum(failcount) as failcount, sum(transdelay) as transdelay from F_HTTP_APP_HOST group by reporttime, cellid, host;
4、查询语句
①、查询欢迎度前十的应用
select apptype, DATE_FORMAT(hourid, '%Y%m%d') dateid, sum(totalTraffic) from D_H_HTTP_APPTYPE group by apptype, dateid having dateid ='0615' order by sum(totaltraffic) desc limit 0, 10;
②、子应用欢迎度前10
select apptype, appsubtype, DATE_FORMAT(hourid, '%Y%m%d') dateid, sum(totalTraffic) from D_H_HTTP_APPTYPE group by apptype, appsubtype, dateid having dateid='0615' and apptype=15 order by sum(totaltraffic) desc limit 0, 10;
③、网站表现前10
select host, DATE_FORMAT(hourid, '%Y%m%d') dateid, sum(attempts) from D_H_HTTP_HOST group by host, dateid having dateid='0615' order by sum(attempts) desc limit 0, 10;
④、单网站一天表现
select host, hourid, sum(attempts) from D_H_HTTP_HOST group by host, hourid having host='' order by attempts desc;
⑤、总小区上网能力
select DATE_FORMAT(hourid, '%Y%m%d') dateid, cellid, sum(totaltraffic) from D_H_HTTP_CELLID group by dateid, cellid having dateid='0615' limit 0, 10;
⑥、指定小区上网能力
select hourid, cellid, sum(totaltraffic) from D_H_HTTP_CELLID group by cellid, hourid having DATE_FORMAT(hourid, '%Y%m%d')='0615' and cellid='131432478';
