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

目录

一、项目概述

1、概述

二、字段解释分析

1、数据字段

2、应用大类

3、应用小类

三、项目架构

四、数据收集清洗

1、数据收集

2、数据清洗

五、Sqoop使用

1、简介

2、Sqoop安装步骤

3、Sqoop的基本命令

六、数据导出

七、数据可视化

1、概述

2、步骤

八、扩展

1、各个网站的表现能力

2、小区HTTP上网能力

3、小区上网喜好

4、查询语句


一、项目概述

1、概述

 ①、当用户通过网络设备(手机、平板电脑、笔记本电脑等)进行上网的时候,信号经过基站进行传输的时候,会在基站这儿留下日志

②、可以通过分析日志来获取用户的行为记录,例如用户浏览了哪个网站或者用户喜欢哪个APP等行为,从而可以做到相对精准的推荐

二、字段解释分析

1、数据字段

序号

字段名

类型

长度

说明

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';

评论 7
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

是小先生

知识是无价的,白嫖也可以的。

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值