Hadoop学习笔记—20.网站日志分析项目案例(三)统计分析

网站日志分析项目案例(一)项目介绍:http://www.cnblogs.com/edisonchou/p/4449082.html 网站日志分析项目案例(二)数据清洗:http://www.cnblogs.com/edisonchou/p/4458219.html 网站日志分析项目案例(三)统计分析:当前页面

一、借助Hive进行统计

1.1 准备工作:建立分区表

  为了能够借助Hive进行统计分析,首先我们需要将清洗后的数据存入Hive中,那么我们需要先建立一张表。这里我们选择分区表,以日期作为分区的指标,建表语句如下:(这里关键之处就在于确定映射的HDFS位置,我这里是/project/techbbs/cleaned即清洗后的数据存放的位置)

hive>CREATE EXTERNAL TABLE techbbs(ip string, atime string, url string) PARTITIONED BY (logdate string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LOCATION '/project/techbbs/cleaned';

  建立了分区表之后,就需要增加一个分区,增加分区的语句如下:(这里主要针对20150425这一天的日志进行分区)

hive>ALTER TABLE techbbs ADD PARTITION(logdate='2015_04_25') LOCATION '/project/techbbs/cleaned/2015_04_25';  

  有关分区表的详细介绍此处不再赘述,如有不明白之处可以参考本笔记系列之17-Hive框架学习一文。

1.2 使用HQL统计关键指标

(1)关键指标之一:PV量

  页面浏览量即为PV(Page View),是指所有用户浏览页面的总和,一个独立用户每打开一个页面就被记录1 次。这里,我们只需要统计日志中的记录个数即可,HQL代码如下:

hive>CREATE TABLE techbbs_pv_2015_04_25 AS SELECT COUNT(1) AS PV FROM techbbs WHERE logdate='2015_04_25';

(2)关键指标之二:注册用户数

  该论坛的用户注册页面为member.php,而当用户点击注册时请求的又是member.php?mod=register的url。因此,这里我们只需要统计出日志中访问的URL是member.php?mod=register的即可,HQL代码如下:

hive>CREATE TABLE techbbs_reguser_2015_04_25 AS SELECT COUNT(1) AS REGUSER FROM techbbs WHERE logdate='2015_04_25' AND INSTR(url,'member.php?mod=register')>0;  

(3)关键指标之三:独立IP数

一天之内,访问网站的不同独立 IP 个数加和。其中同一IP无论访问了几个页面,独立IP 数均为1。因此,这里我们只需要统计日志中处理的独立IP数即可,在SQL中我们可以通过DISTINCT关键字,在HQL中也是通过这个关键字:

hive>CREATE TABLE techbbs_ip_2015_04_25 AS SELECT COUNT(DISTINCT ip) AS IP FROM techbbs WHERE logdate='2015_04_25';

(4)关键指标之四:跳出用户数

只浏览了一个页面便离开了网站的访问次数,即只浏览了一个页面便不再访问的访问次数。这里,我们可以通过用户的IP进行分组,如果分组后的记录数只有一条,那么即为跳出用户。将这些用户的数量相加,就得出了跳出用户数,HQL代码如下:

hive>CREATE TABLE techbbs_jumper_2015_04_25 AS SELECT COUNT(1) AS jumper FROM (SELECT COUNT(ip) AS times FROM techbbs WHERE logdate='2015_04_25' GROUP BY ip HAVING times=1) e;

PS:跳出率是指只浏览了一个页面便离开了网站的访问次数占总的访问次数的百分比,即只浏览了一个页面的访问次数 / 全部的访问次数汇总。这里,我们可以将这里得出的跳出用户数/PV数即可得到跳出率。

(5)将所有关键指标放入一张汇总表中以便于通过Sqoop导出到MySQL

  为了方便通过Sqoop统一导出到MySQL,这里我们借助一张汇总表将刚刚统计到的结果整合起来,通过表连接结合,HQL代码如下:

hive>CREATE TABLE techbbs_2015_04_25 AS SELECT '2015_04_25', a.pv, b.reguser, c.ip, d.jumper FROM techbbs_pv_2015_04_25 a JOIN techbbs_reguser_2015_04_25 b ON 1=1 JOIN techbbs_ip_2015_04_25 c ON 1=1 JOIN techbbs_jumper_2015_04_25 d ON 1=1;

二、使用Sqoop导入到MySQL

2.1 准备工作:在MySQL中创建结果汇总表

  (1)Step1:创建一个新数据库:techbbs

mysql> create database techbbs; Query OK, 1 row affected (0.00 sec)

  (2)Step2:创建一张新数据表:techbbs_logs_stat

mysql> create table techbbs_logs_stat( -> logdate varchar(10) primary key, -> pv int, -> reguser int, -> ip int, -> jumper int); Query OK, 0 rows affected (0.01 sec)

2.2 导入操作:通过export命令

  (1)Step1:编写导出命令

sqoop export --connect jdbc:mysql://hadoop-master:3306/techbbs --username root --password admin --table techbbs_logs_stat --fields-terminated-by '\001' --export-dir '/hive/techbbs_2015_04_25'

  这里的--export-dir是指定的hive目录下的汇总表所在位置,我这里是/hive/techbbs_2015_04_25。

  (2)Step2:查看导出结果

三、改写Linux定时任务

  刚刚我们已经借助Hive进行了关键指标的统计分析,并且借助Sqoop导出到了MySQL,后续可以借助JSP或者ASP.NET开发指标浏览界面供决策者进行浏览分析。但是刚刚这些操作都是我们自己手工操作的,我们需要实现自动化的统计分析并导出,于是我们改写前一篇中提到的定时任务脚本文件。

3.1 加入分区、统计与导出操作

  重写techbbs_core.sh文件,内容如下,step4~step8为新增内容:

#!/bin/sh

......

#step4.alter hive table and then add partition
hive -e "ALTER TABLE techbbs ADD PARTITION(logdate='${yesterday}') LOCATION '/project/techbbs/cleaned/${yesterday}';"
#step5.create hive table everyday
hive -e "CREATE TABLE hmbbs_pv_${yesterday} AS SELECT COUNT(1) AS PV FROM hmbbs WHERE logdate='${yesterday}';"
hive -e "CREATE TABLE hmbbs_reguser_${yesterday} AS SELECT COUNT(1) AS REGUSER FROM hmbbs WHERE logdate='${yesterday}' AND INSTR(url,'member.php?mod=register')>0;"
hive -e "CREATE TABLE hmbbs_ip_${yesterday} AS SELECT COUNT(DISTINCT ip) AS IP FROM hmbbs WHERE logdate='${yesterday}';"
hive -e "CREATE TABLE hmbbs_jumper_${yesterday} AS SELECT COUNT(1) AS jumper FROM (SELECT COUNT(ip) AS times FROM hmbbs WHERE logdate='${yesterday}' GROUP BY ip HAVING times=1) e;"
hive -e "CREATE TABLE hmbbs_${yesterday} AS SELECT '${yesterday}', a.pv, b.reguser, c.ip, d.jumper FROM hmbbs_pv_${yesterday} a JOIN hmbbs_reguser_${yesterday} b ON 1=1 JOIN hmbbs_ip_${yesterday} c ON 1=1 JOIN hmbbs_jumper_${yesterday} d ON 1=1;"
#step6.delete hive tables
hive -e "drop table hmbbs_pv_${yesterday};"
hive -e "drop table hmbbs_reguser_${yesterday};"
hive -e "drop table hmbbs_ip_${yesterday};"
hive -e "drop table hmbbs_jumper_${yesterday};"
#step7.export to mysql
sqoop export --connect jdbc:mysql://hadoop-master:3306/techbbs --username root --password admin --table techbbs_logs_stat --fields-terminated-by '\001' --export-dir '/hive/hmbbs_${yesterday}'
#step8.delete hive table
hive -e "drop table techbbs_${yesterday};"

3.2 分离日期获取操作

  (1)改写techbbs_core.sh脚本文件:

#!/bin/sh

#step1.get yesterday format string
#yesterday=`date --date='1 days ago' +%Y_%m_%d`
yesterday=$1

  这里将日期字符串作为参数传入,将该步骤转移到了其他脚本文件中;

  (2)新增techbbs_daily.sh脚本文件:

#!/bin/sh

yesterday=`date --date='1 days ago' +%Y_%m_%d`
hmbbs_core.sh $yesterday

  这里获取日期并作为参数传递给techbbs_core.sh文件;

  (3)改写crontab定时任务配置:crontab -e

* 1 * * * /usr/local/files/apache_logs/techbbs_daily.sh

  这里每天凌晨1点自动执行的就变为techbbs_daily.sh脚本文件了;从此,我们只需定期查看mysql数据库中的汇总结果表进行浏览即可;

3.3 初始化任务操作

  当一个网站已经生成了很多天的日志,而我们的日志分析系统却一直没上线,一直等到了某天才上线。这时,我们需要写一个初始化脚本任务,来对之前的每天的日志进行统计分析与导出结果。这里,我们新增一个techbbs_init.sh脚本文件,内容如下:

#!/bin/sh

#step1.create external table in hive
hive -e "CREATE EXTERNAL TABLE techbbs(ip string, atime string, url string) PARTITIONED BY (logdate string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LOCATION '/project/techbbs/cleaned';"

#step2.compute the days between start date and end date
s1=`date --date="$1"  +%s`
s2=`date +%s`
s3=$((($s2-$s1)/3600/24))

#step3.excute techbbs_core.sh $3 times
for ((i=$s3; i>0; i--))
do
  logdate=`date --date="$i days ago" +%Y_%m_%d`
  techbbs_core.sh $logdate
done

四、小结

  通过三部分的介绍,该网站的日志分析工作基本完成,当然还有很多没有完成的东西,但是大体上的思路已经明了,后续的工作只需要在此基础上稍加分析即可完成。当然,我们还可以通过JSP或ASP.NET读取MySQL或HBase中的分析结果表来开发关键指标查询系统,供网站运营决策者进行查看和分析。

作者:周旭龙

出处:http://www.cnblogs.com/edisonchou/

本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文链接。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

循序渐进:Oracle 12c新特性Sharding技术解读

引言 数据库构架设计中主要有 Shared Everthting、Shared Nothing 和 Shared Disk: Shared Everthting...

4607
来自专栏跟着阿笨一起玩NET

SQL语句使用总结(一)

1>. FROM 2>. WHERE 3>. GROUP BY 4>. HAVING 5>. SELECT 6>. ORDER BY

561
来自专栏WindCoder

网易MySQL微专业学习笔记(二)-Mysql数据对象

这个系列属于个人学习网易云课堂MySQL数据库工程师微专业的相关课程过程中的笔记,本篇为其“MySQL数据库对象与应用”中的MySQL数据类型相关笔记。

721
来自专栏MYSQL轻松学

MySQL 面试选择题15道(单选)

1、MySQL数据库四种特性,不包括() A.原子性 B.事务性 C.一致性 D.隔离性 2、MySQL报错error 1062 的意思是() A.连接数据库失...

6326
来自专栏后端技术探索

mysql 水平分表的几种方法

当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,我想有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。

1302
来自专栏数据和云

远程数据库的表超过20个索引的影响

小编寄语 我们都知道,对于通过dblink关联本地表和远程表,远程表的索引个数一般不超过20个,对其本身不会有什么影响。但是当索引个数超过20个的时候,又会发生...

3047
来自专栏匠心独运的博客

数据库技术之记一次排查DB死锁的分析与思考

虽然很多童鞋在学数据库课程时都了解数据库隔离级别、死锁和事务等概念,但在测试/线上环境遇到死锁却不一定能够及时分析并解决这类问题。本文主要以作者在测试环境中遇到...

2191
来自专栏后端技术探索

mysql 水平分表的几种方法

当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,我想有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。

1.5K2
来自专栏杨建荣的学习笔记

MySQL中使用pt-osc的一些小结

Percona的pt-osc工具算是DBA的一个福利工具。想想一个数据量有些大的表,在上面做DDL操作真是一种煎熬,我们也基本理解了这是一种空间换时间的...

1361
来自专栏漫漫全栈路

Oracle数据库项目——MC服务器管理系统

数据库设计内容,分为:系统功能说明,数据库分析与设计,数据库实现。本数据库系统提供的功能用于项目MC-Servers-Web,详情内容参见MC-Service...

7244

扫码关注云+社区

领取腾讯云代金券