实验手册——搜狗搜索日志分析系统

实验手册——搜狗搜索日志分析系统

前奏：请提前搭建好你的集群和必要的软件:hadoop + jdk + hive + Sqoop + HBase；

数据（链接是网友的，感谢，感谢，感谢。里面有测试数据）：链接：http://pan.baidu.com/s/1dFD7mdr 密码：xwu8

一：数据预处理（Linux 环境）:

1 搜狗数据的数据格式：
2 访问时间\t 用户 ID\t[查询词]\t 该 URL 在返回结果中的排名\t 用户点击的顺序号\t 用户点击的 URL
3 其中，用户 ID 是根据用户使用浏览器访问搜索引擎时的 Cookie 信息自动赋值，即同一次使用浏览器输入的不同查询对应同一个用户 ID。

1：查看数据，构建系统前,需要了解如何进行数据相关的预处理：
　　进入实验数据文件夹，然后进行less 查看：

解决中文显示乱码问题：
本步骤已经完成从 gbk 转化为 utf-8 格式，不需要再操作。见下面目录，该目录是乱码清洗的 Java 代码：

查看总行数，如下所示（小等片刻）：

1 [root@master data_hadoop]# wc -l /home/hadoop/data_hadoop/sogou.500w.utf8 
2 5000000 /home/hadoop/data_hadoop/sogou.500w.utf8
3 [root@master data_hadoop]# 

截取部分数据数据（此操作没卵用）：

1 [root@master data_hadoop]# head -100 sogou.500w.utf8 >  sogou.500w.utf8.demo
2 [root@master data_hadoop]# ls
3 b.txt  sogou.500w.utf8  sogou.500w.utf8.demo  tb_buck
4 [root@master data_hadoop]# wc -l sogou.500w.utf8.demo 
5 100 sogou.500w.utf8.demo

2：数据扩展：
　　将时间字段拆分并拼接，添加年、月、日、小时字段；

利用bash命令执行sogou-log-extend.sh文件，该文件的内容如下：

1 #!/bin/bash
2 #infile=/data/sogou-data/sogou.500w.utf8
3 infile=$1
4 #outfile=/data/sogou-data/sogou.500w.utf8.final
5 outfile=$2
6 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

3：数据过滤
　　过滤第 2 个字段（UID）或者第 3 个字段（搜索关键词）为空的行（需要用第 2 步数据扩展的结果）：

利用bash命令执行sogou-log-filter.sh文件，文件内容如下:

1 #!/bin/bash
2 #infile=/data/sogou-data/sogou.500w.utf8.ext
3 infile=$1
4 #outfile=/data/sogou-data/sogou.500w.utf8.flt
5 outfile=$2
6 awk -F "\t" '{if($2 != "" && $3 != "" && $2 != " " && $3 != " ") print $0}' $infile > $outfile

 最后，将数据加载到 HDFS 上，操作如下所示（上传到hdfs略慢，记得先启动你的集群哈）：

创建hadoop fs -mkdir -p /sougou/20111230的时候多了一个u，my god，导致后来创建数据表的时候未导入数据。可以选择重新删除表，建表，或者直接使用命令导入；

二、基于 Hive 构建日志数据的数据仓库（要求：Hadoop 集群正常启动）：

1：打开 Hive 客户端：
基本操作：利用bin文件夹下hive打开Hive客户端。

2：Hive的基本操作：

 1 # 查看数据库
 2 show databases;
 3 # 创建数据库
 4 create databases sogou;
 5 # 使用数据库
 6 use sogou;
 7 # 查看所有表名
 8 show tables;
 9 # 创建外部表,使用相对路径绝对URI， # 创建数据库要小心关键字冲突，不能使用date，order,user等关键字。
10 create external table sogou.sogou_20111230(ts string,uid string,keyword string,rank int,sorder int,url string)Row FORMAT DELIMITED FIELDS TERMINATED BY '\t' stored as TEXTFILE location 'hdfs://master:9000/sogou/20111230';
11 # 查看新创建的表结构
12 show create table sogou.sogou_20111230;
13 describe sogou.sogou_20111230;
14 # 删除表
15 drop table sogou.sogou_20111230;

2：创建分区表（按照年、月、天、小时分区）：
　　创建扩展 4 个字段（年、月、日、小时）数据的外部表：

create external table sogou.sogou_ext_20111230(ts string,uid string,keyword string,rank int,sorder int,url string,year int,month int,day int,hour int)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;
OK

灌入数据（sogou_ext_20111230这个表里面的数据是创建的时候加载进去的，location 'hdfs://master:9000/sogou_ext/20111230';）：

1 # 设置动态分区。nonstrict全分区字段是动态的
2 set hive.exec.dynamic.partition.mode=nonstrict;
3 insert overwrite table sogou.sogou_partition partition(year,month,day,hour) select * from sogou.sogou_ext_20111230;

查询结果，如下所示：

1 hive> select * from sogou_ext_20111230 limit 10;2 hive> select url from sogou_ext_20111230 limit 10;
3 hive> select * from sogou_ext_20111230 where uid='96994a0480e7e1edcaef67b20d8816b7';

四、实现数据分析需求一：条数统计：

1：数据总条数：

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

 2：非空查询条数：

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

3：无重复总条数（根据 ts、uid、keyword、url）：

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

4:独立 UID 总数：

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

 五、实现数据分析需求二：关键词分析：

1：查询关键词长度统计：

split是hive字符串分割函数:
split(str,regex),对于特殊字符，需要使用两个\.eg:\\s表示空白字符。

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

2：查询频度排名（频度最高的前 50 词）：

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

可以看看广大网友最爱搜的词汇，哈哈哈哈，查询结果如下所示：

 1 百度    38441
 2 baidu    18312
 3 人体艺术    14475
 4 4399小游戏    11438
 5 qq空间    10317
 6 优酷    10158
 7 新亮剑    9654
 8 馆陶县县长闫宁的父亲    9127
 9 公安卖萌    8192
10 百度一下 你就知道    7505
11 百度一下    7104
12 4399    7041
13 魏特琳    6665
14 qq网名    6149
15 7k7k小游戏    5985
16 黑狐    5610
17 儿子与母亲不正当关系    5496
18 新浪微博    5369
19 李宇春体    5310
20 新疆暴徒被击毙图片    4997
21 hao123    4834
22 123    4829
23 4399洛克王国    4112
24 qq头像    4085
25 nba    4027
26 龙门飞甲    3917
27 qq个性签名    3880
28 张去死    3848
29 cf官网    3729
30 凰图腾    3632
31 快播    3423
32 金陵十三钗    3349
33 吞噬星空    3330
34 dnf官网    3303
35 武动乾坤    3232
36 新亮剑全集    3210
37 电影    3155
38 优酷网    3115
39 两次才处决美女罪犯    3106
40 电影天堂    3028
41 土豆网    2969
42 qq分组    2940
43 全国各省最低工资标准    2872
44 清代姚明