龙门飞甲快播：搜狗搜索日志分析

1. 概述

本文利用搜狗搜索日志的500w条数据，对搜索日志进行了一系列的分析。主要分为两个阶段，第一阶段是数据准备、数据预处理和数据加载阶段，第二阶段为分析阶段。

第一阶段首先下载数据，并在自己电脑上安装好实验所需的环境，然后通过数据扩展和数据过滤对原数据进行预处理，得到含有单独年、月、日、小时等字段且关键词和UID不为空的数据，最后将数据加载到HDFS上，并在Hive上创建数据库和数据表，将过滤后的数据灌入Hive中对应的表中，因而后续便可以通过在Hive中执行SQL语句对日志数据进行查询分析。

第二阶段为分析阶段，也分为两部分，一部分是单维度的数据描述性分析，另一部分时多维度的用户行为分析。在单维度的数据描述行分析中，分别针对总的条数、时间、关键词、UID、URL、RANK等单个的字段进行描述统计，例如对每个时间段的查询条数、关键词搜索排行榜、UID搜索排行榜、URL搜索排行榜等进行了查询统计，从而对数据有了一个大概的全局的把握；在多维度的用户行为分析中，以查询最多的用户、点击最多的网址、指定的关键词等为切入点进行深入的用户行为分析，例如在对查询最多的用户的用户行为分析中，通过其搜索的关键词及其频次，得到其目前的兴趣点等，通过其在每个时段的搜索次数，得到其大致的时间行为规律，这对于理解用户行为，描述用户画像，从而定向地针对性地进行广告推荐都是有一定的意义的。

2. 数据准备

2.1 实验环境

Cloudera QuickStart VM将包含所需的所有内容，例如CDH，Cloudera Manager，Cloudera Impala和Cloudera Search等，还包括教程，示例数据和入门脚本。Cloudera QuickStart虚拟机（单节点集群）可以轻松快速地进行CDH实验，以便进行测试，演示和自学，并包括Cloudera Manager以管理您的集群。

本文的使用的Cloudera QuickStart VM的版本是：

Cloudera-quickstart-vm-5.5.0-0-virtualbox

操作系统：Red Hat (64 bit)

内存大小：4096MB

处理器：2

所使用的virtualbox的版本是：VirtualBox-4.3.12-93733-Win

Cloudera QuickStart VM开启后的截图如下：

Hive查询操作页面截图如下：

2.2 数据描述

本文选取搜狗实验室提供的搜狗搜索日志数据，共计五百万条，每一行为一条记录，每条记录基本上含有ts、uid、rank、order、url等字段，字段的具体意义如表1所示。

表1 搜索日志中的内容

其中，UID是根据用户使用浏览器访问搜索引擎时的Cookie信息自动赋值，即同一次使用浏览器输入的不同查询对应同一个用户识别号。

3. 数据预处理

3.1 数据扩展

将原数据中包含时间的字段拆分并拼接，添加年、月、日、小时字段，以便后续在HDFS中进行分块存储。分割时间字段的shell脚本sogou-log-extend.sh的具体代码如下：

#!/bin/bash

infile=$1

outfile=$2

awk -F '\t' '{print $0"\t"substr($1,0,4)"\t"substr($1,4,2)"\t"

substr($1,6,2)"\t"substr($1,8,2)}' $infile > $outfile

在终端执行此shell脚本：

[cloudera@quickstart ~]$ bash /home/cloudera/sogou-log-extend.sh /home

/cloudera/500w/sogou.500w.utf8 /home/cloudera/500w/sogou.500w.utf8.ext

则sogou.500w.utf8.ext文件即为在原数据的基础上添加年、月、日、小时字段的文件。

3.2 数据过滤

完成数据扩展后，然后过滤UID和keyword字段为空的记录。数据过滤的shell脚本sogou-log-filter.sh的具体代码如下：

#!/bin/bash

infile=$1

outfile=$2

awk -F "\t" '{if($2 != "" && $3 != "" && $2 != " " && $3 != " ") print $0}' $infile > $outfile

在终端执行此shell脚本：

[cloudera@quickstart ~]$ bash /home/cloudera/sogou-log-filter.sh /home

/cloudera/500w/sogou.500w.utf8.ext /home/cloudera/500w/sogou.500w.utf8.

flt

则sogou.500w.utf8.flt即为过滤后的数据文件。

3.3 数据加载

将原数据文件sogou.500w.utf8和过滤后的数据文件sogou.500w.utf8.flt加载到HDFS上。在系统终端执行如下代码：

hadoop fs -mkdir -p /sogou/20111230

hadoop fs -put ./sogou.500w.utf8 /sogou/20111230

hadoop fs -mkdir -p /sogou_ext/20111230

hadoop fs -put ./sogou.500w.utf8.flt /sogou_ext/20111230

由此，已将数据加载到HDFS系统中，然后在Hive上创建日志数据的数据表。

首先在终端启动hive，在hive环境下创建数据库sogou：

hive> create database sogou;

hive> use sogou;

然后，创建扩展4个字段（year, month, day, hour）数据的外部表：

hive> CREATE EXTERNAL TABLE sogou.sogou_ext_20111230(

> ts STRING,

> uid STRING,

> keyword STRING,

> rank INT,

> order INT,

> url STRING,

> year INT,

> month INT,

> day INT,

> hour INT

> )

> COMMENT 'This is the sogou search data of extend data'

> ROW FORMAT DELIMITED

> FIELDS TERMINATED BY '\t'

> STORED AS TEXTFILE

> LOCATION '/sogou_ext/20111230';

创建带分区的表：

hive> CREATE EXTERNAL TABLE sogou.sogou_partition(

> ts STRING,

> uid STRING,

> keyword STRING,

> rank INT,

> order INT,

> url STRING

> )

> COMMENT 'This is the sogou search data by partition'

> partitioned by (

> year INT,

> month INT,

> day INT,

> hour INT

> )

> ROW FORMAT DELIMITED

> FIELDS TERMINATED BY '\t'

> STORED AS TEXTFILE;

最后，把HDFS中的数据灌入到刚刚创建的表中：

hive> set hive.exec.dynamic.partition.mode=nonstrict;

hive> INSERT OVERWRITE TABLE sogou.sogou_partition PARTITION(year,month,

day,hour) select * from sogou.sogou_ext_20111230;

到现在为止，已把经过预处理的数据加载到hive中，后续便可以在hive中执行SQL语句进行查询分析。

4. 单维度数据描述性分析

4.1  条数统计

查看前10条数据：

hive> select * from sogou.sogou_ext_20111230 limit 10;

前10条数据的具体信息如下：

查询数据的总条数：

hive> select count(*) from sogou.sogou_ext_20111230;

查询非空查询条数：

hive> select count(*) from sogou.sogou_ext_20111230 where keyword is not null and keyword !='';

查询无重复总条数（根据ts、uid、keyword、url）

hive> select count(*) from (select ts,uid,keyword,url,count(*) as cnt from sogou.sogou_ext_20111230 group by ts,uid,keyword,url) a where a.cnt==1;

查询UID唯一的条数：

hive> select count(distinct(uid)) from sogou.sogou_ext_20111230;

可知，(a) 本数据文件中共包含500万条数据；

(b) 非空查询条数为5000000条，即所有的数据的keyword都不为空；

(c) 以ts、uid、keyword、url字段作为判断记录是否重复的标准，得到的无重复总条数为4998321条；

(d) UID唯一的条数共有1352664条，即在此段时间内共有1352664个用户进行来搜狗搜索。

4.2   时间分析

查询每个时间段查询的条数：

hive> select year,month,day,hour,count(*) as cnt from

 sogou.sogou_ext_20111230 group by year,month,day,hour order by

 year,month,day,hour;

得到的结果如下：

根据上面的得到的数据，绘制2011年12月30日这一天各时间段的查询条数的条形图如下：

可知，(a) 2011年12月30日这一天中共有4999918条，而2011年12月31日仅有82条，可能本数据集中采集的12月31日的数据不全；

(b) 在2011年12月30日这一天中，0-4时的的查询次数逐渐减少，到4时达到最低，然后慢慢上升，特别在7-9时的增加特别显著，到9-10时逐渐趋于稳定，每个小时大概有300000次查询，到21时后查询次数开始减少。基本符合人们的作息时间；

(c) 在查询次数稳定的时间段内，即9-21时，中间有两次低谷，分别在11-12、17-18，这可能是因为此时间段是人们的中饭、晚饭时间，所以表现出查询次数的略微减少。

4.3  关键词分析

查询关键词的长度指的是用户提交的查询中包含几个词语或字（用空格隔开的）。

查询关键词的平均长度：

hive> select avg(a.cnt) from (select size(split(keyword,'\\s+')) as cnt from sogou. sogou_ext_20111230) a;

可知查询关键词的平均长度为1.0869984，这说明用户输入的查询通常比较短，这也意味着中文搜索引擎得到的用户需求信息更少，需要对用户需求有更多的分析和经验，才能更加准确地返回用户需求的信息。

查询关键词各长度的条数：

hive> select a.cnt,count(*) as total from( select uid,size(split(keyword,

'\\s+')) as cnt from sogou.sogou_ext_20111230) a group by a.cnt order by a.cnt;

得到的结果如下：

图表表示如下：

可知，(a) 查询关键词的长度最短为1个词，最长为45个词；

(b) 关键词长度为1的条数为4672047条，关键词长度为2的条数为260746，关键词长度为3的条数为48424，关键词长度为4的条数为11359条，关键词长度为5的条数为3288，关键词长度为6的条数为1522，而关键词长度大于6的条数低于1000条，关键词长度大于12的条数低于100条，关键词长度为36、45的条数仅为1条；

(c) 查询关键词长度为1的条数占总查询条数的93.44%，查询关键词长度为2的条数占5.21%，即查询关键词的长度小于等于2的条数为98.65%，几乎占据了所有的查询条数，这也再次说明了用户的查询关键词长度一般为1-2个词，与上面得到的查询关键词平均长度为1.087相符；

(d) 从图中也可以看出，关键词长度为1的条数远大于关键词长度为2的条数，可知，大多数查询还是只用一个关键词。

查询查询频度最高的前1000词：

hive> select keyword,count(*) as cnt from sogou.sogou_ext_20111230 group by keyword order by cnt desc limit 1000;

得到的部分结果如下（截取前50）：