目录
一、项目概述
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 | | 必选 | ||
| 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';
扫一扫
8211
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
