大数据案例--电信日志分析系统

序号	字段名	类型	长度	说明
0	reporttime	string	14	从文件名截取获取，动态填上
1	Length	unsigned int	2	用于指示整个XDR所占用字节数
2	City	byte	2	城市区号，如010代表北京
3	Interface	unsigned int	1	接口类型，16进制编码 1. Uu 2. X2 3. UE_MR 4. Cell_MR 5. S1-MME 6. S6a 7. S11 8. S10 9. SGs 10. S5/S8 11. S1-U 12. Gn 13~20 预留 21. IDC出口 22.省网出口 23.省网网间出口 24.骨干网网间出口
4	xDR ID	unsigned int	16	DPI设备内唯一的xDR编号，16进制编码。一个会话生成一个xDR ID S1-U接口中，一个会话在同一个小区中的传输过程生成一个xDR ID，如该会话切换到新的小区，则生成新的XDR ID及会话记录对于超长会话设立时间阈值，超过阈值则该条记录结束，重新生成另一条会话记录并生成新的XDR ID，阈值默认为5分钟
5	RAT	unsigned int	1	RAT类型，16进制编码 1. UTRAN 2. GERAN 3. WLAN 4. GAN 5. HSPA Evolution 6. EUTRAN
6	IMSI	byte	8	用户IMSI（TBCD编码），通过Gn/S11接口信令获取
7	IMEI	byte	8	终端IMEI（TBCD编码），通过Gn/S11接口信令获取
8	MSISDN	byte	16	用户号码（TBCD编码），通过Gn/S11接口信令获取
9	Machine IP Add type	Unsigned int	1	设备IP地址类型 0x01：IPv4 0x02：IPv6
10	SGW/GGSN IP Add	byte	4(16)	对于Gn口，填写GGSN的用户面IP地址；对于S1-U口，填写SGW的用户面IP地 Ipv4：4字节 Ipv6：16字节
11	eNB/SGSN IP Add	byte	4(16)	对于Gn口，填写SGSN的用户面IP地址；对于S1-U口，填写eNB用户面IP地址： Ipv4：4字节 Ipv6：16字节
12	SGW/GGSN Port	byte	2	对于Gn口，填写GGSN的端口；对于S1-U口，填写SGW端口
13	eNB/SGSN Port	byte	2	对于Gn口，填写SGSN的端口；对于S1-U口，填写eNB端口
14	eNB/SGSN GTP-TEID	byte	4	对于Gn口，填写该业务对应承载的SGSN侧GTP-TEID；对于S1-U口，填写该业务对应承载的eNB侧GTP-TEID
15	SGW/GGSN GTP-TEID	unsigned byte	4	对于Gn口，填写该业务对应承载的GGSN侧GTP-TEID；对于S1-U口，填写该业务对应承载的SGW侧GTP-TEID
16	TAC	byte	2	TAC
17	Cell ID	Byte	4	UE所在小区的ECI
18	APN	String	32	APN
19	App Type Code	byte	1	业务类型编码，参见附录D XDR类型编码定义
20	Procedure Start Time	dateTime	8	TCP/UDP流开始时间，UTC时间），从1970/1/1 00:00:00开始到当前的毫秒数。
21	Procedure End Time	dateTime	8	TCP/UDP流结束时间，UTC时间），从1970/1/1 00:00:00开始到当前的毫秒数。
22	Protocol Type	byte	2	协议类型，集团规定的协议识别分类，参加《XXXX数据流量DPI识别能力规范》中的“DPI设备协议识别能力要求”
23	App Type	byte	2	应用大类集团规定的18种应用大类，参见《XXXX数据流量DPI识别能力规范》
24	App Sub-type	byte	2	应用小类根据集团定义的识别规则识别出来的小类，参见《XXXX数据流量DPI识别能力规范》集团未定义的各厂家根据自己的DPI进行识别
25	App Content	byte	1	应用小类的内容细分，如微信文本、微信语音等 0.心跳，1.文本，2.图片，3.音频，4.视频，5.其他文件如无则填全F
26	App Status	byte	1	标识业务是否成功 0：业务成功 1：业务失败 2：业务未识别
27	USER_IPv4	byte	4	终端用户的IPv4地址，如无则填全F
28	USER_IPv6	byte	16	终端用户的IPv6地址，如无则填全F
29	User Port	byte	2	用户的四层端口号
30	L4 protocal	byte	1	L4协议类型： 0：TCP 1：UDP
31	App Server IP_IPv4	byte	4	访问服务器的IPv4地址，如无则填全F
32	App Server IP_IPv6	byte	16	访问服务器的IPv6地址，如无则填全F
33	App Server Port	byte	2	访问的服务器的端口
34	UL Data	byte	4	上行流量单位：字节对于场景一，定义为从内层IP包头开始计算的数据包大小总和；对于其他场景，定义为从链路层封装开始计算的数据包大小总和。
35	DL Data	byte	4	下行流量单位：字节对于场景一，定义为从内层IP包头开始计算的数据包大小总和；对于其他场景，定义为从链路层封装开始计算的数据包大小总和。
36	UL IP Packet	byte	4	上行IP包数，内层IP包的个数
37	DL IP Packet	byte	4	下行IP包数，内层IP包的个数
38	上行TCP乱序报文数	byte	4	上行TCP乱序报文数。L4协议非TCP传输时，此字段填0
39	下行TCP乱序报文数	byte	4	下行TCP乱序报文数。非TCP传输时，此字段填0
40	上行TCP重传报文数	byte	4	上行TCP重传报文数。非TCP传输时，此字段填0
41	下行TCP重传报文数	byte	4	下行TCP重传报文数，非TCP传输时，此字段填0
42	TCP建链响应时延（ms）	byte	4	TCP建链响应时延（ms），非TCP传输时，此字段填0
43	TCP建链确认时延（ms）	byte	4	TCP建链确认时延（ms），非TCP传输时，此字段填0
44	UL_IP_FRAG_PACKETS	byte	4	以内层IP包的分片为准
45	DL_IP_FRAG_PACKETS	byte	4	以内层IP包的分片为准
46	TCP建链成功到第一条事务请求的时延（ms）	byte	4	TCP建链成功到第一条事务请求的时延（ms）。没有事务的时候可以不填写，此时填0
47	第一条事务请求到其第一个响应包时延（ms）	byte	4	第一条事务请求到其第一个响应包时延（ms）。没有事务的时候可以不填写，此时填0
48	窗口大小	byte	4	窗口大小，TCP 建链协商后的窗口。非TCP传输时，此字段填0
49	MSS大小	byte	4	MSS尺寸，TCP层的最大分段大小。非TCP传输时，此字段填0
50	TCP建链尝试次数	byte	1	TCP SYN的次数，一次TCP流多次SYN的数值。非TCP传输时，此字段填0
51	TCP连接状态指示	byte	1	TCP连接状态指示 0：成功 1：失败
52	会话是否结束标志	byte	1	1：结束 2：未结束
53	HTTP版本	byte	1	0x01----HTTP0.9 0x02----HTTP1.0 0x03----HTTP1.1 0x04----HTTP2.0 0x05----WAP1.0 0x06----WAP1.1 0x07----WAP1.2
54	事务类型	byte	2	HTTP/WAP2.0的事务类型，遵照HTTP/WAP1.x协议上的编码
55	HTTP/WAP事务状态	byte	2	HTTP/WAP2.0层的响应码，参见附录A 状态编码
56	第一个HTTP响应包时延(MS)	byte	4	第一个HTTP响应包相对于第一个HTTP请求包（如get命令）的时延
57	最后一个HTTP内容包的时延(MS)	byte	4	最后一个HTTP内容包相对于第一个HTTP请求包（如get命令）的时延
58	最后一个ACK确认包的时延（ms）	byte	4	最后一个HTTP包的ACK相对于第一个HTTP请求包（如get命令）的时延，如果无ACK，则按照最后一个数据包计算
59	HOST	char	64	访问域名
60	URI	char	512	访问的URI，对于超过512字节的URI进行截短
61	X-Online-Host	char	128	针对wap代理上网的私有头部字段，对于cmnet上网该字段与HOST字段相同
62	User-Agent	char	256	终端向访问网站提供的终端信息，包括IMEI、浏览器类型等
63	HTTP_content_type	char	128	HTTP的内容是文字还是图片、视频、应用等，具体编码参考附录A
64	refer_URI	char	128	参考URI
65	Cookie	char	256	协议中Cookie字段
66	Content-Length	byte	4	协议中Content-Length字段
67	目标行为	byte	1	目标行为 0：会话是用户主动点击的页面 1：站点目标而产生的页面
68	Wtp中断类型	byte	1	WTP层的失败类型
69	wtp中断原因	byte	1	WTP层失败原因
70	title	char	256	网站名称，在上网内容里解析
71	key word	char	256	网站关键字，在上网内容里解析
72	业务行为标识	byte	1	0-业务登陆 1-页面访问 2-刷新 3-未识别判断规则详见《业务KPI定义(20130821)》
73	业务完成标识	byte	1	0-业务成功 1-业务失败 2-未识别成功的判断规则：状态码<400
74	业务时延(ms)	byte	4	登陆、访问响应或者刷新时延，具体指标描述参见《业务KPI定义(20130821)》
75	浏览工具	byte	1	详见附录F"浏览工具"，未识别的数据用0填充，该字段不能为空
76	门户应用集合	byte1		详见附录G"门户应用集合"，未识别的数据用0填充，该字段不能为空
77	未知列			例：1:894/1102？？这样字段

2、应用大类

序号	业务类型	业务说明
1	即时通信	互联网消息即时收发业务,如：QQ、飞信等
2	阅读	向用户提供在线或离线阅读服务的业务，如：移动手机阅读、熊猫阅读等
3	微博	微博业务，如：移动微博、新浪微博等
4	导航	提供浏览、查询、导航等功能的电子地图类业务，如：谷歌地图、高德导航等
5	视频	向用户提供音视频内容的直播、分享和下载服务的网站和应用（不包括传统意义上基于P2P技术的视频业务），如：优酷、手机电视等
6	音乐	提供音乐在线欣赏和下载服务的网站和应用，如：咪咕音乐、QQ音乐等
7	应用商店	提供应用程序、音乐、图书等内容浏览、下载及购买服务的业务，如：Mobile Market、AppStore等
8	游戏	基于客户端或者网页的游戏业务：QQ游戏、开心农场等
9	支付	电子商务类业务，如：手机支付、支付宝、网银等
10	动漫	提供动漫在线欣赏和下载服务的网站和应用，如：手机动漫、爱看动漫等
11	邮箱	电子邮箱业务，如：139邮箱、QQ邮箱等
12	P2P业务	基于P2P技术的资源共享业务，包括下载和视频两部分，前者如：迅雷、eMule等，后者如：迅雷看看、PPLive等
13	VoIP业务	互联网语音通信业务，如：Skype、Uucall等
14	彩信	彩信业务
15	浏览下载	基于HTTP、WAP、FTP等的普通浏览和下载业务
16	财经	金融资讯、股票证劵类业务，如：手机商界、大智慧等
17	安全杀毒	提供网络安全服务的应用，如：360安全卫士、麦咖啡等；以及网络恶意流量，如：病毒、攻击等
18	其他业务

3、应用小类

业务类型	子业务
	序号	子业务名称	优先级	备注
即时通信	1	飞聊	必选	自有业务
	2	飞信	必选
	3	Gtalk	必选	互联网业务
	4	MSN	必选
	5	QQ	必选
	6	TM	必选
	7	阿里旺旺	必选
	8	米聊	必选
	9	微信	必选
	10	人人桌面	必选
	11	AOL AIM	可选
	12	Gadu_Gadu	可选
	13	go聊	可选
	14	ICQ	可选
	15	IMVU	可选
	16	Lava-Lava	可选
	17	NetChat	可选
	18	Paltalk	可选
	19	PowWow	可选
	20	TeamSpeak	可选
	21	Trillian	可选
	22	VZOchat	可选
	23	Xfire	可选
	24	百度Hi	可选
	25	都秀	可选
	26	陌陌	可选
	27	天翼Live	可选
	28	翼聊	可选
	29	网易	泡泡	可选
	30	新浪UC	可选
	31	新浪UT	可选
	32	雅虎通	可选

三、项目架构

①、因为此处做的是离线的数据分析，所以没有使用到HBase

②、硬件环境：至少需要三台服务器，每一台服务器至少是2核4G(推荐是2核8G)，磁盘至少是20G(推荐是50G~60G)

③、软件环境：三台服务器上都需要安装Flume，第一台服务器上需要安装Hive。可以选择安装Hadoop的伪分布式或者也可以选择安装Hadoop的完全分布式，如果选择安装Hadoop的完全分布式，那么在这之前需要先安装好Zookeeper

软件	版本
Flume	1.9.0
Hive	3.1.2
Hadoop	3.1.3
Zookeeper	3.5.7
MySQL	5.7.33

四、数据收集清洗

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 = s1
a1.channels = c1
a1.sinks = k1

# 日志是放在指定目录下的
# 所以此时监控指定目录下的变化
# 如果目录下产生了新的文件
# 需要收集这个新文件中的内容
a1.sources.s1.type = spooldir
# 指定要监听的目录
a1.sources.s1.spoolDir = /home/telecomlog

# 配置Channel
a1.channels.c1.type = memory
a1.channels.c1.capacity = 10000
a1.channels.c1.transactionCapacity = 1000

# 需要将收集到的数据发送到第一台服务器上
a1.sinks.k1.type = avro
a1.sinks.k1.hostname = hadoop01
a1.sinks.k1.port = 8090

# 绑定
a1.sources.s1.channels = c1
a1.sinks.k1.channel = c1

Ⅳ、数据收集到第一台服务器上之后，需要将收集到的数据写到HDFS上

hadoop01：

cd /home/software/apache-flume-1.9.0-bin/data/

编辑文件 vim telecomlog.conf

在文件中添加如下内容：

a1.sources = s1
a1.channels = c1
a1.sinks = k1

# 需要接收第二台和第三台服务器传输来的数据
a1.sources.s1.type = avro
a1.sources.s1.bind = 0.0.0.0
a1.sources.s1.port = 8090
# 需要在数据中来添加一个时间戳-用于提取数据按天存储
a1.sources.s1.interceptors = i1
a1.sources.s1.interceptors.i1.type = timestamp

# 配置Channel
a1.channels.c1.type = memory
a1.channels.c1.capacity = 10000
a1.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 = 3600
a1.sinks.k1.hdfs.rollSize = 0
a1.sinks.k1.hdfs.rollCount = 0

# 绑定
a1.sources.s1.channels = c1
a1.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;

建表（外部表）来管理原始数据：

create EXTERNAL table telecom (a1 string, a2 string, a3 string, a4 string, a5 string, a6 string, a7 string, a8 string, a9 string, a10 string, a11 string, a12 string, a13 string, a14 string, a15 string, a16 string, a17 string, a18 string, a19 string, a20 string, a21 string, a22 string, a23 string, a24 string, a25 string, a26 string, a27 string, a28 string, a29 string, a30 string, a31 string, a32 string, a33 string, a34 string, a35 string, a36 string, a37 string, a38 string, a39 string, a40 string, a41 string, a42 string, a43 string, a44 string, a45 string, a46 string, a47 string, a48 string, a49 string, a50 string, a51 string, a52 string, a53 string, a54 string, a55 string, a56 string, a57 string, a58 string, a59 string, a60 string, a61 string, a62 string, a63 string, a64 string, a65 string, a66 string, a67 string, a68 string, a69 string, a70 string, a71 string, a72 string, a73 string, a74 string, a75 string, a76 string, a77 string) partitioned by (reporttime string) row format delimited fields terminated by '|' stored as textfile location '/telecomlog';

修复分区：

msck repair table telecom;

数据抽样：

select * from telecom tablesample(5 rows);

②、原始数据中包含了77个字段，加上分区在内，一共有78个字段，但是这78个字段不代表都是需求中所需要的，所以此时需要从这77个字段中来抽取真正有用的字段来进行下一步的处理 - 此时需要对数据进行第一次的简单的清洗

序号	字段名	类型	长度	说明
16	TAC	byte	2	TAC
17	Cell ID	Byte	4	UE所在小区的ECI
19	App Type Code	byte	1	业务类型编码，参见附录D XDR类型编码定义
20	Procedure Start Time	dateTime	8	TCP/UDP流开始时间，UTC时间），从1970/1/1 00:00:00开始到当前的毫秒数。
21	Procedure End Time	dateTime	8	TCP/UDP流结束时间，UTC时间），从1970/1/1 00:00:00开始到当前的毫秒数
23	App Type	byte	2	应用大类，集团规定的18种应用大类，参见《XXXX数据流量DPI识别能力规范》
24	App Sub-type	byte	2	应用小类，根据集团定义的识别规则识别出来的小类, 参见《XXXX数据流量DPI识别能力规范》。集团未定义的各厂家根据自己的DPI进行识别
27	USER_IPv4	byte	4	终端用户的IPv4地址，如无则填全F
29	User Port	byte	2	用户的四层端口号
31	App Server IP_IPv4	byte	4	访问服务器的IPv4地址，如无则填全F
33	App Server Port	byte	2	访问的服务器的端口
34	UL Data	byte	4	上行流量单位：字节对于场景一，定义为从内层IP包头开始计算的数据包大小总和；对于其他场景，定义为从链路层封装开始计算的数据包大小总和
35	DL Data	byte	4	下行流量单位：字节对于场景一，定义为从内层IP包头开始计算的数据包大小总和；对于其他场景，定义为从链路层封装开始计算的数据包大小总和
40	上行TCP重传报文数	byte	4	上行TCP重传报文数，非TCP传输时，此字段填0
41	下行TCP重传报文数	byte	4	下行TCP重传报文数，非TCP传输时，此字段填0
55	HTTP/WAP事务状态	byte	2	HTTP/WAP2.0层的响应码，参见附录A 状态编码
59	HOST	char	64	访问域名
62	User-Agent	char	256	终端向访问网站提供的终端信息，包括IMEI、浏览器类型等
63	HTTP_content_type	char	128	HTTP的内容是文字还是图片、视频、应用等，具体编码参考附录A
68	Wtp中断类型	byte	1	WTP层的失败类型
72	业务行为标识	byte	1	0-业务登陆 1-页面访问 2-刷新 3-未识别判断规则详见《业务KPI定义(20130821)》
73	业务完成标识	byte	1	0-业务成功 1-业务失败 2-未识别成功的判断规则：状态码<400

③、确定好要抽取的字段之后，需要建立一个表来存储这些被抽取的字段

建立抽取的字段表 - 事实表：

create table dataclear(reporttime string, appType bigint, appSubtype bigint, userIp string, userPort bigint, appServerIP string, appServerPort bigint, host string, cellid string, appTypeCode bigint, interruptType String, transStatus bigint, trafficUL bigint, trafficDL bigint, retranUL bigint, retranDL bigint, procdureStartTime bigint, procdureEndTime bigint)row format delimited fields terminated by '|';

从原始表中来抽取字段放入事实表中：

insert overwrite table dataclear select concat(reporttime, ' ', '00:00:00'), a23, a24, a27, a29, a31, a33, a59, a17, a19, a68, a55, a34, a35, a40, a41, a20, a21 from telecom;

抽样数据：

select * from dataclear tablesample(5 rows);

④、抽取完字段之后，需要对字段整理，例如格式的转化，字段的整合，字段的填充等

序号	字段	字段类型	描述
0	reportTime	datetime	小时时间片 default 'YYYY-MM-DD HH24:MI:SS'
1	appType	int	应用大类
2	appSubtype	int	应用小类
3	userIP	varchar(20)	用户IP
4	userPort	int	用户端口
5	appServerIP	varchar(20)	服务器IP
6	appServerPort	int	服务器端口
7	host	varchar(50)	域名
8	cellid	varchar(20)	小区ID
9	attempts	int(20)	尝试次数
10	accepts	int(20)	接受次数
11	trafficUL	int(20)	上行流量
12	trafficDL	int(20)	下行流量
13	retranUL	int(20)	重传上行报文数
14	retranDL	int(20)	重传下行报文数
15	failCount	int(20)	延时失败次数
16	transDelay	int(20)	传输时延*

建表来存储整理好格式之后的数据：

建表语句：

create table f_http_app_host( reporttime string, appType bigint, appSubtype bigint, userIP string, userPort bigint, appServerIP string, appServerPort bigint, host string, cellid string, attempts bigint, accepts bigint, trafficDL bigint, trafficUL bigint, retranUL bigint, retranDL bigint, failCount bigint, transDelay bigint) row format delimited fields terminated by '|' stored as textfile;

抽取字段：
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的受欢迎程度，此时需要再从上边整理好的数据中再次抽取需要的字段来进行分析

序号	字段	字段类型	描述
0	hourid	datetime	小时时间片
1	appType	int	应用大类
2	appSubtype	int	应用小类
3	attempts	int(20)	尝试次数
4	accepts	int(20)	接受次数
5	succRatio	double	尝试成功率
6	trafficUL	int(20)	上行流量
7	trafficDL	int(20)	下行流量
8	totalTraffic	int(20)	总流量
9	retranUL	int(20)	重传上行报文数
10	retranDL	int(20)	重传下行报文数
11	retranTraffic	int(20)	重传报文数据
12	failCount	int(20)	延时失败次数
13	transDelay	int(20)	传输时延

确定好字段之后，需要建表来存储数据

建表语句：

create table D_H_HTTP_APPTYPE(hourid string, 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) row format delimited fields terminated by '|' stored as textfile;

从整理好的数据中来抽取整合，存储到子表中：
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/

rz sqoop-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.3
export HADOOP_MAPRED_HOME=/home/software/hadoop-3.1.3
export HIVE_HOME=/home/software/hive-3.1.2
export ZOOKEEPER_HOME=/home/software/apache-zookeeper-3.5.7-bin
export 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.7
export PATH=$PATH:$SQOOP_HOME/bin
保存退出，重新生效：

source /etc/profile

检查是否配置正确：

sqoop version

3、Sqoop的基本命令

命令	解释
sqoop version	查看Sqoop的版本
sqoop list-databases --connect jdbc:mysql://hadoop01:3306/ -username root -password root	查看MySQL中的所有的database
sqoop list-tables --connect jdbc:mysql://hadoop01:3306/hive --username root --password root	查看MySQL中hive库下的所有的表
在MySQL中建表 create table person(id int primary key, name varchar(20), age int, gender varchar(20)); 在表中插入数据 insert into person values(1,'amy',15,'male') 导入到HDFS上 sqoop import --connect jdbc:mysql://hadoop01:3306/test --username root --password root --table person --target-dir '/sqoop/person' --fields-terminated by '\t' -m 1;	将MySQL中的数据导入到HDFS上
在MySQL中建表 create table orders(orderid int primary key, orderdate varchar(10), productid int, num int); 导出到MySQL中 sqoop export --connect jdbc:mysql://hadoop01:3306/test --username root --password root --export-dir '/orders/order.txt' --table orders -m 1 --fields-terminated-by ' ';	将HDFS上的数据导出到MySQL中

六、数据导出

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、各个网站的表现能力

字段：

序号	字段	字段类型	描述
0	hourid	datetime	小时时间片
1	host	varchar(50)	域名
2	appServerIP	varchar(20)	服务器IP
3	attempts	int(20)	尝试次数
4	accepts	int(20)	接受次数
5	succRatio	int(20)	尝试成功率
6	trafficUL	int(20)	上行流量
7	trafficDL	int(20)	下行流量
8	totalTraffic	int(20)	总流量
9	retranUL	int(20)	重传上行报文数
10	retranDL	int(20)	重传下行报文数
11	retranTraffic	int(20)	重传报文数据
12	failCount	int(20)	延时失败次数
13	transDelay	int(20)	传输时延

建表语句：

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上网能力

字段：

序号	字段	字段类型	描述
0	hourid	datetime	小时时间片
1	cellid	varchar	小区ID
2	attempts	int(20)	尝试次数
3	accepts	int(20)	接受次数
4	succRatio	int(20)	尝试成功率
5	trafficUL	int(20)	上行流量
6	trafficDL	int(20)	下行流量
7	totalTraffic	int(20)	总流量
8	retranUL	int(20)	重传上行报文数
9	retranDL	int(20)	重传下行报文数
10	retranTraffic	int(20)	重传报文数据
11	failCount	int(20)	延时失败次数
12	transDelay	int(20)	传输时延

建表语句：

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、小区上网喜好

字段：

序号	字段	字段类型	描述
0	hourid	datetime	小时时间片
1	cellid	varchar	小区ID
2	host	varchar(50)	域名
3	attempts	int(20)	尝试次数
4	accepts	int(20)	接受次数
5	succRatio	int(20)	尝试成功率
6	trafficUL	int(20)	上行流量
7	trafficDL	int(20)	下行流量
8	totalTraffic	int(20)	总流量
9	retranUL	int(20)	重传上行报文数
10	retranDL	int(20)	重传下行报文数
11	retranTraffic	int(20)	重传报文数据
12	failCount	int(20)	延时失败次数
13	transDelay	int(20)	传输时延

建表语句：

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 ='20150615' 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='20150615' 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='20150615' order by sum(attempts) desc limit 0, 10;

④、单网站一天表现

select host, hourid, sum(attempts) from D_H_HTTP_HOST group by host, hourid having host='apilocate.amap.com' 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='20150615' limit 0, 10;

⑥、指定小区上网能力

select hourid, cellid, sum(totaltraffic) from D_H_HTTP_CELLID group by cellid, hourid having DATE_FORMAT(hourid, '%Y%m%d')='20150615' and cellid='131432478';

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)