Hive基于搜狗搜索的用户日志行为分析

本帖最后由 howtodown 于 2015-4-13 00:19 编辑
问题导读
1.本文是如何学习大数据的？
2.用户查询中包含的中文、英文字数的平均个数,本文提出哪两个思路？
3.用户访问应用的时间特点如何使用hive实现统计的？

前言
”大数据时代“，“大数据/云计算”，“大数据平台”，每天听到太多的大数据相关的词语，好像现在说一句话不跟大数据沾边都不好意思说自己是做IT的。可能这与整个IT圈子的炒作也有关联，某一个方面来看其实就是一营销术语。很多朋友就想问，我想做大数据，但是没有这个条件，没有这个数据量，没有那么多业务场景，没有那多集群可以吗？其实，我觉得是可以的，大数据只是一个华丽的词语，实际的背后也是一些开源框架的支撑，也是通过技术来实现的，所以只要掌握这一套理论体系，开源框架，技术手段，底层实现，就ok。
所以我想写一系列的博客，来让这个看起揭开这个高大上技术的面纱，展露它的本质，让更多的人领略大数据的魅力。
至于怎么搭建hadoop集群，安装生态圈中的hbase、hive、pig、mahout、spark、flume等等，就不在我想讨论的范围内，有太多的的文章、博客都详实的记录了。
这篇我主要想分享，基于搜索引擎的用户日志行为的一些分析，时间比较仓促，如有遗漏或错误欢迎留言，互动，大家进步。

数据来源
打造最权威的中文信息处理数据提供和评测平台。数据来源，搜狗实验室

理论知识
      做技术分析之前必须需要相关的理论知识作为研究支撑，所以建议先掌握相应的理论知识。主要分两部分，一个是统计分析相关的，一些关于得出数据总量分量的关系，百分比，进而绘制出趋势走向，历史图标，各种报表等，提供BI的功能。另外一部分是数据挖掘/文本挖掘，挖掘用户查询词的语义，查到相邻词语，进而进行相关搜索推荐等，挖掘出用户兴趣，人群画像等。
统计分析相关
         搜索引擎用户行为分析
  中文搜索引擎用户日志分析
            数据挖掘/文本挖掘相关
  大规模中文搜索引擎的用户日志分析
基于用户日志的查询推荐系统
准备工作
1、下载搜狗搜索的用户日志，有完整版(2GB)和迷你版(87KB)，可以先下载迷你版查看数据格式，最终使用完整版做数据分析
      2、建hive表： create table querylog (time string,userid string,keyword string,pagerank int,clickorder int,url string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

分析过程

      1、用户搜索排行榜 >100       select * from ( select userid,count(*) as c from querylog group by userid having c>1 ) a order by c desc limit 100 ;

      2、url搜索访问排行榜 > 100
   select * from ( select url,count(*) as c from querylog group by url having c>1 ) a order by c desc limit 100 ;

   总共的url的访问次数是 43545444

   可以得到以下结果
http://www.baidu.com/ 660791 搜索引擎 1.517%

http://www.4399.com/ 231722 小游戏网站 0.532%
http://www.youku.com/ 133650 视频网站 0.306%
http://www.hao123.com/ 129220 导航网站
http://qzone.qq.com/ 114878 qq空间 sns网站
http://www.7k7k.com/ 98103 小游戏网站
http://cf.qq.com/ 85555 穿越火线网站

   发现用户通过搜狗搜索获取百度的地址的数量比较多，其次是游戏类，视频。可以发现有很高的比例的人，其实不是真正的使用搜狗搜索直接搜索数据，而是通过搜狗获得百度搜索，搜索数据。

   思考扩展：可以通过对每个网站的分类，比如可以划为视频、搜索、导航、SNS、游戏等网站类型，获得用户最关心的网站类型，什么类型网站最热门，在同一类型网站中热度排行榜，哪个网站最热门，排行榜是怎样的。

   3、用户搜索时，点击url在页面排行pagerank的统计
   select pagerank,count(pagerank) as c from querylog group by pagerank order by pagerank asc;
   获得用户初次搜索的准确率，比如pagerank比较靠前，说明用户的搜索习惯比较好，或者搜索引擎的查询命中率较高。

      1 18274343  41.966% 第一次搜索的命中率比较高  18274343 / 43545444

2 7926133 18.201%
3 4798577 11.019%
43250306  7.464%
52439101
61860029
71530145
81285208
91082268
101097680  2.520%
11231
12205
13193

可以看出来，以每页10条数据的显示，用户通常情况下只查看第一页的数据，占到了绝大多数，最后很少一部分会查看第二页的数据。
查询之后返回结果的第一页的点击率是41.966%，接近一半，说明用户会得到想要的查询的结果，或者更改查询语句

   4、用户访问应用的时间特点
      算出一年时间中，某一个时间段内的搜索数量
      比如，查询一年中，所有23点搜索的数量：
      hive -e "INSERT OVERWRITE LOCAL DIRECTORY './time/23' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select count(*) from querylog where time like '________23%';"

00点
817218

01点
543054

02点
375981

03点
283579

04点
235414

05点
221724

06点
256387

07点
405673

08点
1208389

09点
2032463

10点
2435041

11点
2408354

12点
2405614

13点
2612695

14点
2769876

15点
2870105

16点
2847254

17点
2609760

18点
2614522

19点
3050032
7.004%

20点
3167798
7.274%

21点 2995447

22点
2517636

23点
1861428

      分析：
      用一年的平均值给出的结论可以看出，用户在10点-17点为使用高峰期，应该可以看出来是上班时间使用较多。17点到18点为低峰期，可能是用户下班在回家的路上。
      18点到22点为高峰期，特别是20点，达到一天使用的最高峰，应该是用户在家里使用。
      用户基本以上班族为主。

         思考扩展：
         可以考虑出用户的使用习惯是在回家的时候达到最高峰，用户达到最活跃，凌晨时间段用户最少，可以从运营的角度考虑服务器的一些升级部署可以安排到凌晨时段，高峰期在晚上应该可以提高访问速度等。
         继续搜索以周、月、季度为单位，搜索活跃度同比、环比排行榜

   5、用户查询中包含的中文、英文字数的平均个数
   思路1：userid和keyword作为唯一搜索条件。这样有遗漏，因为有的人隔一段时间可能会搜索同一个语句。
   select count(distinct(keyword)) from querylog ;
   没有重复的查询次数共有： 25531020
   select sum(length(keyword)) from ( select keyword from querylog group by userid,keyword ) a ;
   用户搜索出来的查询词总共长度是：189521004
   用户查询语句中包含的中文、英文的平均个数是： 189521004/25531020=7.423个中文、英文或数字

   思路2：根据点击顺序为1来判断，说明他第一次点击。如果点击顺序 > 1 说明用户是第二次以上点击了，keyword不是第一次输入。这样也有遗漏，有可能用户只输入了查询词，没有点击网页。
   select count(keyword) from querylog where clickorder=1;
   第一次点击的查询次数共有：30366974
   用户搜索出来的查询词总共长度是：  221004260
   用户查询语句中包含的中文、英文的平均个数是： 221004260/30366974=7.277个中文、英文或数字

   6、用户查看结果页面停留的时间有多长
   select * from querylog where unix_timestamp(time,"yyyyMMddhhMMss") < unix_timestamp("20111230003415","yyyyMMddhhMMss") limit 100;

扩展

  还可以做更多的数据分析，在此就自己去思考，我点一下思路,比如：