查询大数据综合案例-搜狗搜索日志分析

1.数据处理

扩展脚本 年月日vim log-extend.sh

[root@master~]#bash log-extend.sh sogou.500w.utf8 sogou_log.txt 过滤脚本过滤搜索为空

Vim log-fi lter.sh

[root@master~]#bash log-fi lter.sh sogou_log.txt sogou_log.txt.flt

1.上传文件到h d fs

基于HIve构建日志数据的数据仓库

创建数据库hive>create database sogou;

使用数据库

Hive>use sogou;

创建扩展4个字段年、月、 日、小时数据的外部表hive>CREATE EXTERNALTABLE sogou_data(ts string,uid string,keyword string,rankint,sorder int,url string,year int,month int,dayint,hour int)

>ROWFORMATDELIMITED

>FIELDSTERMINATED BY'\t'

>STORED ASTEXTFILE;

OK

Timetaken:0.412seconds

Hive表加载数据load data inpath'/home/sogou_log.txt.flt' intotable sogou_data;

创建带分区的表hive>CREATE EXTERNALTABLE sogou_partitioned_data(ts string,uid string,keyword stringrankint,sorder int,url string)

>PARTITIONED BY(year int,monthint,dayint,hour int)>ROWFORMATDELIMITED

>FIELDSTERMINATED BY'\t'

>STORED ASTEXTFILE;

设置动态分区hive>set hive.exec.dynamic.partition.mode=nonstrict;hive> INSERT OVERWRITE TABLE sogou_partitioned_data partition(year,month,day,hour)SELECT* FROM sogou_data;

查询测试

Hive>select *from sogou_data l imit 10;

hive>select *from sogou_data l imit 10;

hive>select*from sogou_data where uid='6961d0c97fe93701fc9c0d861d096cd9';

(1)查询总条数hive>select count(*) from sogou_partitioned_data;

OK

5000000

(2)非空查询条数hive>select count(*) from sogou_partitioned_data where keyword is not nul l and keyword!=' ';

5000000

Time taken:28.606 seconds, Fetched: 1 row(s)

(3)无重复总条数hive> select count(*) from(select count(*) as no_repeat_count from sogou_partitioned_data group byts,uid,keyword,url having no_repeat_count=1)a;

OK

4999272

Time taken: 101.228 seconds, Fetched: 1 row(s)

(4)独立UID总数hive>select count(distinct(uid)) from sogou_partitioned_data;

OK

1352664

Time taken:44.639 seconds, Fetched: 1 row(s)

实现数据分析需求二关键字分析

(1)查询频度排名频度最高的前50词hive> select keyword,count(*)query_count from sogou_partitioned_data group by keyword orderby query_countdesc l imit50;

Total MapReduceCPUTimeSpent: 1 minutes4seconds510msec

OK

百度 38441baidu 18312

人体艺术 14475

4399小游戏 11438qq空间 10317

优酷 10158

新亮剑 9654

馆陶县县长闫宁的父亲 9127

公安卖萌 8192

百度一下你就知道7505

百度一下 7104

4399 7041

魏特琳 6665qq网名 6149

7k7k小游戏 5985

黑狐 5610

儿子与母亲不正当关系 5496

新浪微博 5369

李宇春体 5310

新疆暴徒被击毙图片 4997hao123 4834

123 4829

4399洛克王国 4112qq头像 4085nba 4027

龙门飞甲 3917qq个性签名 3880

张去死 3848cf官网3729

凰图腾 3632

快播 3423

金陵十三钗 3349

吞噬星空 3330dnf官网 3303

武动乾坤 3232

新亮剑全集 3210

电影 3155

优酷网 3115

两次才处决美女罪犯 3106

电影天堂 3028

土豆网 2969qq分组 2940

全国各省最低工资标准 2872

清代姚明 2784youku 2783

争产案 2755dnf 2686

12306 2682

身份证号码大全 2680

火影忍者 2604

Time taken: 119.195 seconds, Fetched:50 row(s)

实现数据分析需求三 UID分析

(1)查询次数大于2次的用户总数hive> select count(*) from(select count(*) as query_count from sogou_partitioned_data groupby uid having query_count>2)a;

OK

546353

Timetaken:69.837 seconds, Fetched: 1 row(s)

(2)查询次数大于2次的用户占比

A:hive> select count(*) from(select count(*) as query_count from sogou_partitioned_data groupby uid having query_count>2)a;

OK

546353

Timetaken:69.837 seconds, Fetched: 1 row(s)

Bhive>select count(distinct(uid)) from sogou_partitioned_data;

OK

1352664

A/B hive>select 546353/1352664;

OK

0.40390887907122536

Timetaken:0.255 seconds, Fetched: 1 row(s)

(3) rank次数在10以内的点击次数占比rank既是第四列的内容

A:

hive>select count(*) from sogou_partitioned_data where rank<11;

4999869

Time taken:29.653 seconds, Fetched: 1 row(s)

B:hive>select count(*) from sogou_partitioned_data;

5000000

A/B hive>select 4999869/5000000;

OK

0.9999738

(4)直接输入URL查询的比例

A:hive>select count(*) from sogou_partitioned_data where keyword l ike '%www%';

OK

73979

B:hive>select count(*) from sogou_partitioned_data;

OK

5000000

A/B hive>select 73979/5000000;

OK

0.0147958

实现数据分析需求四独立用户行为分析

(1)查询搜索过”仙剑奇侠传“的uid并且次数大于3hive> select uid,count(*) as cnt from sogou_partitioned_data where keyword='仙剑奇侠传'groupby uid havingcnt>3;