hadoop的Hive实战操作sogou的日志

huhahapz

　　动手实战操作搜狗日志文件
　　本节中所用到的内容是来自搜狗实验室，网址为：
　　http://www.sogou.com/labs/dl/q.html
　　用户可以根据自己的Spark机器实际的内存配置等情况选择下载不同的数据版本，为了让所有的学习者都可以成功操作日志，我们使用的是迷你版本的tar.gz格式的文件，其大小为384K，下载地址：http://www.sogou.com/labs/resource/q.php
　　日志格式如下：
　　单挑记录：
　　00:00:002982199073774412[360安全卫士]8 3download.it.com.cn/softweb/software/firewall/antivirus/20067/17938.html
　　该文件的格式如下所示：
　　访问时间\t用户ID\t查询词\t该URL在返回结果中的排名\t用户点击的顺序号\t用户点击的URL
　　在hive中创建表格：
　　create table sogou(sTime string,userId string,Keyword string,Paiming int,Dianji int,URL string) row format delimited
　　fields terminated by '\t' lines terminated by '\n' stored as textfile
　　结果如下：
　　hive> create table sogou(sTime string,userId string,Keyword string,Paiming int,Dianji int,URL string) row format delimited
　　> fields terminated by '\t' lines terminated by '\n' stored as textfile;
　　OK
　　Time taken: 4.422 seconds
　　导入数据：
　　load data local inpath  '/home/dyq/Documents/SogouQ.sample' overwrite into table sogou;
　　结果显示：
　　hive> load data local inpath  '/home/dyq/Documents/SogouQ.sample' overwrite into table sogou;
　　Loading data to table default.sogou
　　OK
　　Time taken: 3.567 seconds
　　查看表的建立情况：
　　hive> desc sogou;
　　OK
　　stime               string
　　userid              string
　　keyword             string
　　paiming             int
　　dianji              int
　　url                 string
　　Time taken: 2.515 seconds, Fetched: 6 row(s)
　　查看数据导入情况：
　　查看记录条数：
　　select count(*) from sogou;
　　可以看到查询过程很漫长，效率不高的说
　　hive> select count(*) from sogou;

　　WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. tez, spark) or using Hive 1.X>
　　Query>　　Total jobs = 1
　　Launching Job 1 out of 1
　　Number of reduce tasks determined at compile time: 1
　　In order to change the average load for a reducer (in bytes):
　　set hive.exec.reducers.bytes.per.reducer=
　　In order to limit the maximum number of reducers:
　　set hive.exec.reducers.max=
　　In order to set a constant number of reducers:
　　set mapreduce.job.reduces=
　　Starting Job = job_1472347049416_0004, Tracking URL = http://ubuntu:8088/proxy/application_1472347049416_0004/
　　Kill Command = /opt/hadoop-2.6.2/bin/hadoop job  -kill job_1472347049416_0004
　　Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
　　2016-08-28 11:47:28,067 Stage-1 map = 0%,  reduce = 0%
　　2016-08-28 11:48:09,628 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 8.1 sec
　　2016-08-28 11:48:35,383 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 10.68 sec
　　MapReduce Total cumulative CPU time: 10 seconds 680 msec
　　Ended Job = job_1472347049416_0004
　　MapReduce Jobs Launched:
　　Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 10.68 sec   HDFS Read: 906303 HDFS Write: 105 SUCCESS
　　Total MapReduce CPU Time Spent: 10 seconds 680 msec
　　OK
　　10000
　　Time taken: 109.492 seconds, Fetched: 1 row(s)
　　导出数据到外部文件
　　insert overwrite local directory '/home/dyq/Documents/SogouQdept' select a.Keyword,a.URL from sogou a;
　　只导出2个字段，看看情况
　　hive> insert overwrite local directory '/home/dyq/Documents/SogouQdept' select a.Keyword,a.URL from sogou a;

　　WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. tez, spark) or using Hive 1.X>
　　Query>　　Total jobs = 1
　　Launching Job 1 out of 1
　　Number of reduce tasks is set to 0 since there's no reduce operator
　　Starting Job = job_1472347049416_0005, Tracking URL = http://ubuntu:8088/proxy/application_1472347049416_0005/
　　Kill Command = /opt/hadoop-2.6.2/bin/hadoop job  -kill job_1472347049416_0005
　　Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
　　2016-08-28 11:51:54,594 Stage-1 map = 0%,  reduce = 0%
　　2016-08-28 11:52:06,398 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 3.14 sec
　　MapReduce Total cumulative CPU time: 3 seconds 140 msec
　　Ended Job = job_1472347049416_0005
　　Moving data to local directory /home/dyq/Documents/SogouQdept
　　MapReduce Jobs Launched:
　　Stage-Stage-1: Map: 1   Cumulative CPU: 3.14 sec   HDFS Read: 901894 HDFS Write: 178503 SUCCESS
　　Total MapReduce CPU Time Spent: 3 seconds 140 msec
　　OK
　　Time taken: 28.859 seconds
　　查看sogouQdept文件的内容：