开发者学堂课程【场景实践-基于阿里云 Quick BI 对 MOOC 网站日志分析：数据处理】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/524/detail/7077
数据处理
内容介绍：
一、使用正则提取 IP 属性
二、从 IP 表中查询城市和运营商的信息
三、提取省信息和有效访问链接
四、访问时间处理和来源分类
五、访客客户端信息处理
六、访问链接 TopN
七、获取每个访客的第一条日志
八、IP 黑名单
一、使用正则提取 IP 属性
使用正则表达式提取传真的 IP 数据库中的属性，传真的 IP 数据库已经上传到 Mascomputer 中，其格式为：IP 开始+IP 结束+地址信息+运营商信息。例如：121.237.105.0 121.237.105.255江苏省镇江市 电信
对此，提取 IP 的每一个段，然后计算 IP 的数字表达的方式，如下图：（获取 IP 第一个段的提取）
代码 regexp_substr(content,‘\\d+'，1,1）AS ip_start_1 的作用是用于后面表关联的优化。
下图是 IP 的截至段：
同样5、6、7、8表示 IP 的段，分别乘相应位置的2的6次方，进行累加，最后得到 IP 的数值。最后使用正则表达式，其规则为：（regexp_substr(content, ' +[^ ]+',1，2),‘^ +‘,''）
[^ ] 前的加号前面是空格，表示一个或多个空格，[^ ]表示内容不能由空格组成，“+”号表示一次或多次。由此可见，满足第一个格式的实际上是第二个 IP，因为其之前也有空格，并且在其中间也不是空格，直到匹配到空格后其匹配结束。所以第一个匹配到的是截至的 IP，第二个匹配到的是城市信息，最后匹配到的是网络运营商的信息。在获取后，因为其前面保留了一个空格，所以要所以正则表达式替换的方式将开头所有的空格去除，最终得到城市和运营商信息，将该信息插入到新表中。
二、从 IP 表中查询城市和运营商的信息
再对 IP 数据和日志数据的属性进行提取后，可以从 IP 表中查询城市和运营商的信息。
由于 IP 库是区间类型的原因，所以需要在 join 中加入非等值。而MaxCompute 不支持非等值 join，所以使用 MapJoin。（不仅仅是MaxCompute ，其他大数据框架虽然允许使用 SQL 进行操作，但是实际上并不允许在 join 中加入非等值的，也就是大于小于这类的操作，这和其底层原理有相关性所以不能单纯使用 join 进行非等值的操作。）
MaxCompute 提供了 MapJoin，其实大多数的 join 都是 Reduce join，大数据平台的底层实际上是转换成 MapReduce 的作业，其默认的关联方式是在 Reduce 端进行关联。同样可以在 Map 端进行关联操作，但是 Map 端有一个特点，其查询的数据量不能太大。因为实际上是将一批数据存储在缓存中，在 Map 的过程中会持续不断的从公共的内存中读取数据，所以要求 join 的表不能太大。MaxCompute 中的性质是500兆，也就是 join 的表大小不能超过500兆。
MaxCompute 编写的 select 语句：
INSERT OVERWRITE TABLE t_web_access__log_tmp2
SELECT/*+ mapjoin(b) */ a.id, a.ip, b.city，b.isp，a.access_time, a.url,a.status, a.traffic, a.referer,a.c_info
FROM t_web_access_log_tmp1 a
JOIN t_cz_ip b
ON a.ip_1 = b.ip_start_1
AND a.ip_num >= b.ip_start
AND a.ip_num <= b.ip_end;
在上面的代码中添加了一个注释：mapjoin(b)，其表示将以 Map 的方式和 b 表进行 join 的操作。然后去查询 a 表，也就是第一张临时表的一些字段（从是一个表中提取的信息），并且将 IP 表中的城市和运营商信息页提取出来。在进行一个 join 后，要使用前面获取到的条件，第一个是日志数据中访问者的 IP 第一段，后面是 IP 表中开始的第一段。使用这样的等价条件是因为增加了该过滤条件后可以极大的对select 语句进行执行的优化。倒数第二行语句是区间的不等值处理，要求访问者的 IP 必须处于 IP 库的开始和截至中间，所以用 IP 的数字表达的方式进行大于和小于来获取到 IP 的信息，就是当前的城市和运营商信息。关于使用 IP 的第一段进行等于的情况，在文本文件实际上有一个根据，该根据是因为 IP 的第一个段到面前为止没有任何运营商去跨第一个 IP 的段。也就是说所有的运营商都是在第二个段进行跨段的 IP 的段的拥有，不可能第一个 IP 段和第二个 IP 段都属于同一个运营商。所以使用的第一个段等于用户访问日志的第一个 IP 的段去作为一个过滤条件。
这个简单的操作会使 select 语句的执行从原来的几十分钟降低为几分钟，这就是 select 优化的结果。
三、提取省信息和有效访问链接
要进行全国性的区域展示，那么要使省的信息符合规则，就一条普通的 IP 信息而言，其为辽宁省沈阳市。如果要在省的范围内进行范围统计，那么必须将后面的字符串进行去除，所以用如下的脚本来进行一个简单的去除，脚本如下图：
对照之前提取的城市信息，首先使用 INSTR 函数，第一个参数是文本内容，然后是匹配内容，也就是在 city 中找到“省”这个字，如果包含则为大于0，就使用字符串截取，将 city 从第一个字符串开始截取到“省”的字符串之前的一个字符串，也就是将“辽宁省”中的“辽宁”提取出来。但是中国所有的省并不是都以“省“来命名的，所以还要提取其不同之处。如果发现字符串中有”内蒙古“则直接将其提取为内蒙古，对于西藏、广西、宁夏等自治区也以同样的方式。最后还有4个直辖市，北京、上海、天津和重庆，也要以这样的方式，还有香港的澳门两个特别行政区。提取信息后，还需要提取有效的访问链接，访问链接的字符串如下：
"POST /study/updateDurationVideo.mooc?si=21&ii=469 HTTP/1.1"
POST 为请求类型，study 为实际请求的地址，updateDurationVideo.mooc?si=21&ii=469 HTTP是协议的类型，1.1 是 HTTP 协议的版本。这是一个完整的情况，对于不完整的情况，字符串如下：
"HEAD / HTTP/1.1"
其头和尾是以上面这种方式展示的，中间正名只有一个‘/‘，所以要提取中间的方式依然采取正则表达式。
表达式为：
(url, ’ *?(\\.mooc | ) ‘, 1,1), "^ l $’ .’’)
这里对 url （也就是之前的访问链接）进行正则表达式，其内容为：空格开头（以空格开头说明只能是中间或者最后的段，这里是任意数量的字符串），直到匹配到“.mooc“或者空格，也就是如果中间的字符串包含 mooc 则会从空格一直提取到 mooc。对于中间的字符串如果不包含 mooc，则会提取到第二个空格，也就是从第一个空格到第二个空格就会提取出中间的段。”1,1”表示从第一个字符串开始获取到第一个匹配的结果，最后将其首部空格和尾部空格进行删除，这样就得到了真实有效的访问路径。然后将其存储到第三张临时表中。
四、访问时间处理和来源分类
要做趋势分析统计，就需要将日期进行处理。日期的格式如下：
[22/Aug/2017:00:01:06 +0800]
代码如下：
regexp_replace( regexp_substr(access_time,':\\d.*? ‘,1,1), ’:|$', ‘’)AS access_time
regexp_replace(regexp_substrlaccess_time,':\\d\\d:'，1,1)，"^:|:$'，’’)AS access_hour
其中，定义一个每个小时的趋势统计，就要将其小时数进行提取，这里依然采用正则表达式。这里的“\d”表示单个数字，和另一个“\d”组合就表示两个数字，因为其两侧都是冒号，所以将提取符合两边都是冒号并且中间是两个数字的字符串。这样可以发现第一个匹配的就是小时数，所以这里指定第一个，然后将两侧的冒号去除，最终得到当前访问的小时数。在此同样进行了简单的处理，第一个冒号之后的内容实际上是其时分秒，将其赋给原来的字段，也就是当前访问时间。实际情况中，使用的是一天的访问日志，所以其年月日都是一样的，这里将其进行一个简单的转换，实际在后面的分析中可能并不需要用到。
处理完时间后，要对于访问来源分析，则需要对用户访问来源进行分类归纳。常用的用户访问来源格式为：
“http;//www.chinamoocs.com/home/login.mooc?historyUrl=3D"
正常的用户访问来源大概可以分为三类，首先是网站的自身，然后网站的访问来源为空，因为用户可能直接在浏览器地址栏中手动输入，这时进入网站时没有源。还有一种情况是搜索引擎，比如谷歌、百度等等。还有可能在其他网站中用户通过首页链接进入网站，则网站的来源可能是放在网站首页的一些网站，所以将其归类为 other 。对于搜索引擎，同样将其进行一个分类的判断，如果其包含 www.google.com，则将其分类为 Google 的类型；如果是百度，则为 baidu 的类型，以此类推。然后将自身以及用户通过输入网址进行点击的情况归类为self。最后将这些字段提取到第四个临时表中。
代码如下：
五、访客客户端信息处理
可以从客户端中提取出很多的信息，但是要对这些信息进行统计，同样要按规则进行提取，这里主要提取其操作系统和浏览器。在其整个字符串中，首先判断字符串中有无“iPhone；”的字样，如果有则认为这是一个 IOS 的操作系统；如果是 “iPad；”，也会是 IOS 的操作系统；如果包含“Mac OS X”，则为 OS X 的操作系统；如果包含“X11”，则将其认为是 Linux 的操作系统；之后如果还含有安卓则为 Android 的操作系统；如果为 Windows NT ，则为 Windows的操作系统（Windows NT 10.0 代表 Windows10的操作系统，但是在这里不做更细致的统计）；如果不符合以上条件则将其归类为 other。
最后将其指定为 client_type，也就是客户端操作系统类型的的一个字段。
再后面，还要判断其浏览器，这里总结了一些常用浏览器。例如QQ浏览器、UC浏览器、win10的浏览器Edge、猎豹浏览器、火狐浏览器、谷歌浏览器等等。判断如下图所示：
注意，如果其操作系统为 Mac OS ，并且其浏览器中包含 Safari 的字样，则认为其为 Safari 浏览器。如果其字样中含有 MSIE，则认为其为微软的 IE 浏览器。如果没有匹配到则将其赋值为 other。
需要说明的是，实际上用户请求的 Mozilla 是用浏览器去指定的，其信息在某种程度上来说并不是完全准确的。比如360浏览器，这里并没有指出，因为其浏览器和谷歌浏览器实际上并没有区别。因为360修改了自己的客户端，对于360这种直接修改自己客户端的行为，没办法去统计（还有其他浏览器）。最终得到web_access_log ，也就是网站日志的主表。
代码如下：
六、访问链接 TopN
通过用户访问链接 TopN 的统计，可以知道用户点击量最高的网站是哪几个，由此可以对链接进行一些针对性的手机优化和网站布局等等。这里使用 group by 来查询用户访问最多的10个链接。
首先对 url 已经进行了提取，所以很多 url 实际上已经具备一定的共性，所以可以直接对 url 进行分组。这里还要简单的判断，url 必须含有“.mooc”的字样，因为其有可能是加载的一些 js 等等的一些文件，所以要对其进行提取。只需要用户去真正访问网站的链接，在分组后对其的一个 top 进行 ORDER BY 倒叙，然后进行一个 LIMIT 10，所以最终的结果是获取到用户点击量最多的前10个链接。top.url 是用户点击的实际链接，top.times 是用户点击的次数。
代码如下：
INSERT OVERWRITE TABLE t_web_access_log_url_top
SELECT top.url, top.times
FROM（
SELECT url,COUNT(1) AS times
FROM t_web_access_log
WHERE INSTR(url, ".mooc') >0
GROUP BY url
) top
ORDER BY top.times DESC
LIMIT 10;
七、获取每个访客的第一条日志
获取每个访客的第一条日志，因为第一条日志实际上记录了很多有效的信息，对于每个访使用对 IP 和客户端信息一起 group by 来保证独立访客（独立客户端）。也就是说即使是同一个 IP，但是用户的客户端信息不一致，使用 IP 和客户端信息两个条件来将其认为是不同的访客。
这里同样使用了一个 join 操作，在 join 的表中，去对 IP 和客户端信息进行 group by，然后取到其 IP 以及当前最小的 id，所以要在之前对编程语言的日志进行 id 自增长的处理。因为在MaxComputer 中，实际上并不提供这种自增长的处理，其本身是没有顺序的，在日志数据或日志处理过程中需要先将 id 收集。还有一个问题，就是当前的访问时间的精度不够，其只精确到秒，对于同一个秒中，访问其他链接并不能判定哪个先访问，所以并不采用 access_time，如果 access_time 精确到毫秒级，那么就不需要去生成自增长的 id，所以当分组完后，这是一个临时表 b，将网站日志表和 b 表进行一个 join 操作，将其IP 属性等于 b 表的 IP，将 IP 属性和 b 表的 IP 进行关联，这样就得到了网站的访问日志的每一个独立访客的第一次的访问记录。
代码如下：
INSERT OVERWRITE TABLE t_web_access__log_first
SELECT a.id, a.ip, a.province, a.city. a.isp
, a.access_time,a.access_hour, a.url,a.status, a.traffic
, a.referer,a.ref_type，a.c_info，a.client_type,a.client_browser
FROM t_web_access_log a
JOIN（
SELECT c.ip，MIN(c.id) AS id
FROMt_web_access_log c
GROUP BY c.ip,
c.c_info
) b
ON a.ip = b.ip
AND a.id = b.id;
八、IP 黑名单
要通过 IP 黑名单的方式，来禁止那些对网站进行攻击的 IP 去访问网站，这样可以提升网站的稳定性，从而提高用户对网站的体验，而防止网站用户的人员流失。
对于 IP 的黑名单的处理，要对 IP 进行group by ，而黑名单的条件就是查询404状态下其访问次数 count 大于10次的。
这里只是做一个单纯的实验，可能并不包含真正的攻击，但是这里认为访问发生404错误的次数超过10的就是疑似为恶意攻击的 IP 。实际上，可能会更大，比如100次，要结合具体情况进行判断。
代码如下：
INSERT OVERWRITE TABLE t_web_access_log_ip_black
SELECT ip,COUNT(1) AS times
FROM t_web_access_log
WHERE status = '404'
GROUP BY ip
HAVING COUNT(1) >10;
