MOOC 网站访问日志分析

Wlikef_凡

于 2023-08-04 17:53:59 发布

阅读量166

点赞数

分类专栏：数据仓库文章标签：数据仓库 sql

本文链接：https://blog.csdn.net/spark5213/article/details/131649371

版权

数据仓库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

第一章日志资源

实验数据源于 MOOC 网站的日志以及 IP 地址：

access.log

access_index.log

ip.txt

第二章网站分析准备

2.1 网站分析概述

网站访问日志是用户在访问网站服务器时产生的日志，它包含了各种原始信息，一般以.log 结尾。通过它就可以清楚的知道用户的 IP，访问时间，请求链接，请求状态，请求字节数，来源链接，用户操作系统，浏览器内核，浏览器名称，浏览器版本等等信息。对网站日志的分析统计可以使我们了解网站当前的一些状况，为网站的各种优化升级甚至公司营销策略提供依据。

2.2 单条日志内容分析

222.68.172.190 - - [18/Sep/2013:06:49:57 +0000] "GET /images/my.jpg HTTP/1.1" 200 19939 "http://www.angularjs.cn/A00n" "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/29.0.1547.66 Safari/537.3

以上日志为例，我们可以从中获取 8 个指标：

Remote_Addr：记录客户端的 IP 地址，222.68.172.190
Remote_User：记录客户端用户名称，-
Time_Local：记录访问时间与时区：[18/Sep/2013:06:49:57 +0000]
Request：记录请求的 URL 和 HTTP 协议，"GET /images/my.jpg HTTP/1.1"
Status：记录请求状态，成功是 200，200
Body_Bytes_Sent：记录发送给客户端文件主体内容大小，19939
Http_Referer：用来记录从哪个页面链接访问过来的， http://www.angularjs.cn/A00n
Http_User_Agent：记录客户端浏览器的相关信息，"Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/29.0.1547.66 Safari/537.36

其中，服务器响应状态码通常状态码有以下几种：200,301,302,304,404,500 等。200 代表用户成功的获取到了所请求的文件，如果是搜索引擎，则证明蜘蛛在这次爬行中顺利的发现了一些新的内容。而 301 则代表用户所访问的某个页面 url 已经做了 301 重定向(永久性)处理，302 则是暂时性重定向。404 则代表所访问的页面已经不存在了，或者说访问的 url 根本就是个错误的。500 则是服务器的错误。

第三章网站分析步骤

3.1 对日志文件进行预处理

由于日志文件中没有唯一标识，所以使用 Java 处理日志文件，为每一行创建一个独立的 ID 标识。通过编写代码，对 D 盘根目录下的 access.log 文件进行处理，并将结果写入到根目录的 log.txt 中。具体代码如下：

import java.io.*;
public class LogParser {
    public void accessLogId(String inPath, String outPath) throws IOException{
        String line = "";
        long index = 0;
        BufferedReader bufferReader = null;
        FileWriter fileWriter = null;
        try {
            bufferReader = new BufferedReader(new FileReader(inPath));
            File outFile = new File(outPath);
            if (outFile.exists()){
                outFile.delete();
            }
            outFile.createNewFile();
            fileWriter = new FileWriter(outFile);
            while ((line = bufferReader.readLine()) != null) {
            index++;
            String newLine = String.valueOf(index) + " " + line + "\r\n";
            fileWriter.write(newLine);
            }
            } finally {
            if (bufferReader != null) {
            bufferReader.close();
            }
            if (fileWriter != null) {
            fileWriter.close();
        }
    }
}
    public static void main(String[] args) {
    try {
        LogParser logParser = new LogParser();
        //日志文件预处理 传入输入和输出地址
        logParser.accessLogId("D:\\access.log", "D:\\log.txt");
    } catch (IOException e) {
        e.printStackTrace()
        }
    }
}

3.2 下载并配置 MaxCompute，进行建表与导入数据

在阿里云里面下载 MaxCompute 工具（odpscmd），完成后解压并于 conf/odps_config.ini 文件中进行相应的个人空间配置:

配置完成后打开 odpscmd，运行脚本：

1. 创建原始表

CREATE TABLE t_web_access_log_content(content STRING);

2. 创建 ip 表

create table t_cz_ip_content(content string);

3.上传数据到表

tunnel upload -fd "NoDelimiter" D:\clouder\access_index.log t_web_access_log_content;

tunnel upload -fd "NoDelimiter " D:\clouder\ip.txt t_cz_ip_content;

创建成功后，用 show与 select 查看表及表中数据：

截图为代码运行后的截图，因而表的数量较多；因为 cmd 的默认字符集编码为 936，而显示简体中文则需要为 UTF-8，虽有乱码问题，但不影响后续使用。

3.3 使用 DataWorks 处理数据

在 DataWorks 中创建工作空间后进入 DataStudio，新建业务流程：

新建完成后开始写业务流程，大致流程如下：

除开始和结束两个虚节点外，其余均为 ODPS_SQL 类型的组件。开始节点需设置根节点，双击开始节点，点击调度配置，选择使用工作空间根节点，加载完后，点击保存。

业务创建完成后给每个脚本写入相应的 SQL 语句，主要利用正则表达式来提取日志的内容（具体 SQl 语句见附录）。当除首尾两个虚节点之外的 9 个节点脚本都设置完毕后，保存。然后点击上面的提交，所有节点全部勾选，勾选忽略输入输出不一致的告警，填写备注，点击提交，提交完毕点击运行。

3.4 使用 Quick BI 展示分析数

进入 Quick BI 后，添加数据源，输入相应的 MaxCompute 工作空间名称与 AccessKeyID 与 KEY；连接成功后进行同步，将 MaxCompute 的数据同步到 Quick BI 上。

3.4.1创建数据集

选择刚刚创建的数据源下的表创建数据集，用 t_web_access_log、 t_web_access_log_url_top 、t_web_access_log_first、 t_web_access_log_ip_black 四个表创建四个数据集：

创建数据集的过程中需要对数据集进行编辑：

3.4.2编辑 t_web_access_log 数据集

点击编辑后进入批量配置页面，默认表的字符串类型属性归类到维度属性，数值类型字段归类到度量：

1.将维度 province 的维度类型切换为地理信息下的省／直辖市，然后编辑将维度显示名修改为“所在省”。

2.点击维度上的“+”，新建计算字段（维度），填写名称（运营商），表达式，数据类型默认。表达式如下：

case
        when instr([isp],'电信')>0 then '电信'
        when instr([isp],'移动')>0 then '移动'
        when instr([isp],'联通')>0 then '联通' else '其他'
end

3.编辑维度 access_hour，将其名称修改为访问时间。

4.编辑维度 ref_type，将其名称修改为搜索引擎

5.点击维度上的“+”，新建计算字段（维度），填写名称（访问来源），表达式，数据类型默认。表达式如下:

case

        when [搜索引擎] = 'self' then '直接访问'

        when [搜索引擎] = 'other' then '其他链接' else '搜索引擎'

end

6.编辑维度 client_type，将其名称修改为操作系统。

7.编辑维度 client_browser，将其名称修改为浏览器。

8.编辑度量 id，将其名称修改为访问人次，然后选择默认聚合方式为计数。

3.4.3 编辑 t_web_access_log_first 数据集

1.将维度 province 的维度类型切换为地理信息下的省／直辖市，然后编辑将维度显示名修改为“所在省”。

2.点击维度上的“+”，新建计算字段（维度），填写名称（运营商），表达式同上一个数据集，数据类型默认。

3.编辑维度 access_hour，将其名称修改为访问时间。

4.编辑维度 ref_type，将其名称修改为搜索引擎

5.点击维度上的“+”，新建计算字段（维度），填写名称（访问来源），表达式同上一个数据集，数据类型默认。

6.编辑维度 client_type，将其名称修改为操作系统。

7.编辑维度 client_browser，将其名称修改为浏览器。

8.编辑度量 id，将其默认聚合方式修改为计数，然后修改名称为访问人数。

9.编辑维度 ip，将其转换为度量。

10.编辑度量 ip，将其默认聚合方式修改为计数(去重)，然后修改名称为独立 IP。

3.4.4编辑 t_web_access_lo

1.编辑维度 ip，修改其名称为 IP 地址。

2.编辑度量 ip，将其默认聚合方式修改为求和，然后修改名称为攻击次数。

3.4.5 编辑 t_web_access_log_url_top 数据集

1.编辑维度 url，修改其名称为访问链接。

2.编辑度量 times，修改其名称为访问次数。

修改完成后，根据网站分析要求，在不同数据集上创建仪表盘，导入相应的维度、度量数据，勾选自动更新，点击更新查看最终结果。

3.5 结果展示

以 access_index.log 数据为例展示结果：对数据进行入库、清洗，最后给出：

1.用户访问链接 Top10 （饼图或者柱图）

2.恶意攻击 IP（柱状图）

3.访客地域分布以及频次（柱状图）

4. 访客操作系统（饼图）

5. 访客网络运营商（饼图）

第四章附录（完整SQL）

	--初始化IP表
	DROP TABLE IF EXISTS t_cz_ip;
	CREATE TABLE t_cz_ip (
 	  ip_start BIGINT,
 	  ip_start_1 STRING,
 	  ip_end BIGINT,
 	  city STRING,
 	  isp STRING
 	);
 	 
 	--使用正则表达式提取并向IP表插入数据
 	INSERT OVERWRITE TABLE t_cz_ip
 	SELECT CAST(regexp_substr(content, '\\d+', 1, 1) AS BIGINT) * 255 * 255 * 255 + CAST(regexp_substr(content, '\\d+', 1, 2) AS BIGINT) * 255 * 255 + CAST(regexp_substr(content, '\\d+', 1, 3) AS BIGINT) *255 + CAST(regexp_substr(content, '\\d+', 1, 4) AS BIGINT) AS ip_start
 	  ,regexp_substr(content, '\\d+', 1, 1) AS ip_start_1
 	  ,CAST(regexp_substr(content, '\\d+', 1, 5) AS BIGINT) * 255 * 255 * 255 +CAST(regexp_substr(content, '\\d+', 1, 6) AS BIGINT) * 255 * 255 +CAST(regexp_substr(content, '\\d+', 1, 7) AS BIGINT) * 255 +CAST(regexp_substr(content, '\\d+', 1, 8) AS BIGINT) AS ip_end
 	  ,regexp_replace(regexp_substr(content, ' +[^ ]+', 1, 2), '^ +', '') AS city
 	  ,regexp_replace(regexp_substr(content, ' +[^ ]+', 1, 3), '^ +', '') AS isp
 	FROM t_cz_ip_content;
	

	--初始化临时表
	DROP TABLE IF EXISTS t_web_access_log_tmp1;
 	CREATE TABLE t_web_access_log_tmp1 (
 	  id BIGINT,
 	  ip STRING,
 	  ip_num BIGINT,
 	  ip_1 STRING ,
 	  access_time STRING,
 	  url STRING,
 	  status STRING,
 	  traffic STRING,
 	  referer STRING,
 	  c_info STRING
 	);
 	 
 	--使用正则表达式提取并向临时表插入数据
 	INSERT OVERWRITE TABLE t_web_access_log_tmp1
 	SELECT CAST(regexp_replace(regexp_substr(content,'[[].*?[]] |".*?" |.*? ', 1, 1), ' $', '') AS BIGINT) AS id
 	  ,regexp_replace(regexp_substr(content, '[[].*?[]] |".*?" |.*? ', 1,2), ' $', '') AS ip
 	  ,CAST(regexp_substr(content, '\\d+', 1, 2) AS BIGINT) * 255 * 255 * 255 +CAST(regexp_substr(content, '\\d+', 1, 3) AS BIGINT) * 255 * 255 + CAST(regexp_substr(content,'\\d+', 1, 4) AS BIGINT) * 255 + CAST(regexp_substr(content, '\\d+', 1, 5) AS BIGINT) AS ip_num
 	  ,regexp_substr(content, '\\d+', 1, 2) AS ip_1
 	  ,regexp_replace(regexp_substr(content, '[[].*?[]] |".*?" |.*? ', 1,5), '^[[]|[]] $', '') AS access_time
 	  ,regexp_replace(regexp_substr(content, '[[].*?[]] |".*?" |.*? ', 1,6), '^"|" $', '') AS url
 	  ,regexp_replace(regexp_substr(content, '[[].*?[]] |".*?" |.*? ', 1,7), ' $', '') AS status
 	  ,regexp_replace(regexp_substr(content, '[[].*?[]] |".*?" |.*? ', 1,8), ' $', '') AS traffic
 	  ,regexp_replace(regexp_substr(content, '[[].*?[]] |".*?" |.*? ', 1,9), '^"|" $', '') AS referer
 	  ,regexp_replace(regexp_substr(content, '".*?"', 1, 3),'^"|"$', '') AS c_info
 	FROM t_web_access_log_content;

	--提取运营商及地区	
	DROP TABLE IF EXISTS t_web_access_log_tmp2;
	CREATE TABLE t_web_access_log_tmp2 (
	  id BIGINT,
	  ip STRING,
	  city STRING,
	  isp STRING,
	  access_time STRING,
	  url STRING,
	  status STRING,
	  traffic STRING,
	  referer STRING,
	  c_info STRING
	);
	 
	--从ip表中查询城市和运营商信息
	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;

	--用户所在省份与访问链接
	--初始化临时表
 	DROP TABLE IF EXISTS t_web_access_log_tmp3;
 	 
 	CREATE TABLE t_web_access_log_tmp3 (
 	  id BIGINT,
 	  ip STRING,
 	  province STRING,
 	  city STRING,
 	  isp STRING,
 	  access_time STRING,
 	  url STRING,
 	  status STRING,
 	  traffic STRING,
 	  referer STRING,
 	  c_info STRING
 	);
 	 
 	--提取省信息使用正则表达式提取访问链接的实际地址并向临时表插入数据
 	INSERT OVERWRITE TABLE t_web_access_log_tmp3
 	SELECT id, ip
 	  , CASE
 	   WHEN INSTR(city, '省') >0 THEN SUBSTR(city, 1, INSTR(city, '省') - 1)
 	   WHEN INSTR(city, '内蒙古')> 0 THEN '内蒙古'
 	   WHEN INSTR(city, '西藏') >0 THEN '西藏'
 	   WHEN INSTR(city, '广西') >0 THEN '广西'
 	   WHEN INSTR(city, '宁夏') >0 THEN '宁夏'
 	   WHEN INSTR(city, '新疆') >0 THEN '新疆'
 	   WHEN INSTR(city, '北京') >0 THEN '北京'
 	   WHEN INSTR(city, '上海') >0 THEN '上海'
 	   WHEN INSTR(city, '天津') >0 THEN '天津'
 	   WHEN INSTR(city, '重庆') >0 THEN '重庆'
 	   WHEN INSTR(city, '香港') >0 THEN '香港'
 	   WHEN INSTR(city, '澳门') >0 THEN '澳门'
 	   ELSE city
 	  END AS province,city, isp, access_time
 	  ,regexp_replace(regexp_substr(url, ' .*?(\\.mooc| )', 1, 1), '^ | $', '') AS url
 	  , status, traffic,referer, c_info
 	FROM t_web_access_log_tmp2;
	

	--访问时间
	--初始化临时表
 	DROP TABLE IF EXISTS t_web_access_log_tmp4;
 	 
 	CREATE TABLE t_web_access_log_tmp4 (
 	  id BIGINT,
 	  ip STRING,
 	  province STRING,
 	  city STRING,
 	  isp STRING,
 	  access_time STRING,
 	  access_hour STRING,
 	  url STRING,
 	  status STRING,
 	  traffic STRING,
 	  referer STRING,
 	  ref_type STRING,
 	  c_info STRING
 	);
 	 
 	--使用正则表达式提取访问时间和来源分类并向临时表插入数据
 	INSERT OVERWRITE TABLE t_web_access_log_tmp4
 	SELECT id, ip, province, city, isp
 	  ,regexp_replace(regexp_substr(access_time, ':\\d.*? ', 1, 1), ':|$', '') ASaccess_time
 	  ,regexp_replace(regexp_substr(access_time, ':\\d\\d:', 1, 1), '^:|:$', '') ASaccess_hour
 	  , url, status,traffic, referer
 	  , CASE
 	   WHEN INSTR(referer, 'www.chinamoocs.com') > 0
 	   OR LENGTH(referer)< 5 THEN 'self'
 	   WHEN INSTR(referer, 'www.google.com') > 0 THEN 'google'
 	   WHEN INSTR(referer, 'www.baidu.com') > 0 THEN 'baidu'
 	   WHEN INSTR(referer, 'www.bing.com') > 0 THEN 'bing'
 	   WHEN INSTR(referer, 'www.so.com') > 0 THEN '360'
 	   ELSE 'other'
 	  END AS ref_type,c_info
 	FROM t_web_access_log_tmp3;


	--用户信息处理
	--初始化访问日志表
 	DROP TABLE IF EXISTS t_web_access_log;
 	 
 	CREATE TABLE t_web_access_log (
 	  id BIGINT,
 	  ip STRING,
 	  province STRING,
 	  city STRING,
 	  isp STRING,
 	  access_time STRING,
 	  access_hour STRING,
 	  url STRING,
 	  status STRING,
 	  traffic STRING,
 	  referer STRING,
 	  ref_type STRING,
 	  c_info STRING,
 	  client_type STRING,
 	  client_browser STRING
 	);
 	 
 	--使用正则表达式提取客户端信息中的操作系统和浏览器信息并向表插入数据
 	INSERT OVERWRITE TABLE t_web_access_log
 	SELECT id, ip, province, city, isp
 	  , access_time,access_hour, url, status, traffic
 	  , referer,ref_type, c_info
 	  , CASE
 	   WHEN INSTR(c_info,'iPhone;') > 0 THEN 'IOS'
 	   WHEN INSTR(c_info,'iPad;') > 0 THEN 'IOS'
 	   WHEN INSTR(c_info,'Mac OS X ') > 0 THEN 'OS X'
 	   WHEN INSTR(c_info,'X11;') > 0 THEN 'Linux'
 	   WHEN INSTR(c_info,'Android ') > 0 THEN 'Android'
 	   WHEN INSTR(c_info,'Windows NT ') > 0 THEN 'Windows'
 	   ELSE 'other'
 	  END AS client_type
 	  , CASE
 	   WHEN INSTR(c_info,' QQBrowser') > 0 THEN 'QQBrowser'
 	   WHEN INSTR(c_info,' UCBrowser') > 0 THEN 'UCBrowser'
 	   WHEN INSTR(c_info,' Edge') > 0 THEN 'Edge'
 	   WHEN INSTR(c_info,' LBBROWSER') > 0 THEN 'LBBROWSER'
 	   WHEN INSTR(c_info,' Maxthon') > 0 THEN 'Maxthon'
 	   WHEN INSTR(c_info,' Firefox') > 0 THEN 'Firefox'
 	   WHEN INSTR(c_info,' Chrome') > 0 THEN 'Chrome'
 	   WHEN INSTR(c_info,' Mac OS X') > 0
 	   AND INSTR(c_info,' Safari') > 0 THEN 'Safari'
 	   WHEN INSTR(c_info,' MSIE') > 0 THEN 'IE'
 	   ELSE 'other'
 	  END ASclient_browser
 	FROM t_web_access_log_tmp4;


	--初始化访问链接TopN表
 	DROP TABLE IF EXISTS t_web_access_log_url_top;
 	 
 	CREATE TABLE t_web_access_log_url_top (
 	  url STRING,
 	  times BIGINT
 	);
 	 
 	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;



	--初始化每个访客的第一个访问日志
 	DROP TABLE IF EXISTS t_web_access_log_first;
 	 
 	CREATE TABLE t_web_access_log_first (
 	  id BIGINT,
 	  ip STRING,
 	  province STRING,
 	  city STRING,
 	  isp STRING,
 	  access_time STRING,
 	  access_hour STRING,
 	  url STRING,
 	  status STRING,
 	  traffic STRING,
 	  referer STRING,
 	  ref_type STRING,
 	  c_info STRING,
 	  client_type STRING,
 	  client_browser STRING
 	);
 	 
 	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
 	  FROM t_web_access_log c
 	  GROUP BY c.ip,
 	   c.c_info
 	) b
 	ON a.ip = b.ip
 	  AND a.id = b.id;


	--初始化访问IP黑名单表
 	DROP TABLE IF EXISTS t_web_access_log_ip_black;
 	 
 	CREATE TABLE t_web_access_log_ip_black (
 	  ip STRING,
 	  times BIGINT
 	);
 	 
 	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;

Wlikef_凡

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
1
评论
MOOC 网站访问日志分析

网站访问日志是用户在访问网站服务器时产生的日志，它包含了各种原始信息，一般以.log 结尾。通过它就可以清楚的知道用户的 IP，访问时间，请求链接，请求状态，请求字节数，来源链接，用户操作系统，浏览器内核，浏览器名称，浏览器版本等等信息。对网站日志的分析统计可以使我们了解网站当前的一些状况，为网站的各种优化升级甚至公司营销策略提供依据。
复制链接

扫一扫