HiveQL应用体验
Hive提供的类SQL的操作体验,从语法上来看,近似于MySQL,从功能上来看,近似于SQL92,不过大家注意了,都只是近似而已。
HiveQL详细语法参见:https://cwiki.apache.org/confluence/display/Hive/LanguageManual
我们先尝试使用Hive来实现之前使用PIG分析access_log的任务,每个ip的点击次数。
创建表对象如下:
hive> create table access_log(
> ip string,
> other string
> )
> row format delimited fields terminated by ¨ ¨
> stored as textfile;
OK
Time taken: 0.106 seconds加载数据也是用LOAD DATA 命令,这里要处理的文件:
hive> load data local inpath ¨/data/software/access_log.txt¨ overwrite into table access_log;
Copying data from file:/data/software/access_log.txt
Copying file: file:/data/software/access_log.txt
Loading data to table default.access_log
Moved to trash: hdfs://hdnode1:9000/user/hive/warehouse/access_log
OK
Time taken: 0.753 seconds查询加载的数据,查询出前20条,我了个去,连limit子句都提供了,方便呀:
hive> select * from access_log limit 20;
OK
220.181.108.151 -
208.115.113.82 -
220.181.94.221 -
112.97.24.243 -
112.97.24.243 -
112.97.24.243 -
112.97.24.243 -
112.97.24.243 -
112.97.24.243 -
112.97.24.243 -
112.97.24.243 -
112.97.24.243 -
112.97.24.243 -
112.97.24.243 -
112.97.24.243 -
112.97.24.243 -
112.97.24.243 -
112.97.24.243 -
112.97.24.243 -
112.97.24.243 -
Time taken: 0.156 seconds借助SQL统计就很方便了,GROUP BY即可。不过考虑到这次要操作的数据量较大,执行GROUP BY将结果集输出到屏幕恐有不妥,因此这里我们将结果保存到另外的表中,执行SQL语句如下:
hive> create table access_result as select ip,count(1) ct from access_log group by ip;
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 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 mapred.reduce.tasks=
Starting Job = job_201304220923_0007, Tracking URL = http://hdnode1:50030/jobdetails.jsp?jobid=job_201304220923_0007
Kill Command = /usr/local/hadoop-0.20.2/bin/../bin/hadoop job -Dmapred.job.tracker=hdnode1:9001 -kill job_201304220923_0007
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2013-05-02 17:02:12,037 Stage-1 map = 0%, reduce = 0%
2013-05-02 17:02:18,082 Stage-1 map = 100%, reduce = 0%
2013-05-02 17:02:27,128 Stage-1 map = 100%, reduce = 100%
Ended Job = job_201304220923_0007
Moving data to: hdfs://hdnode1:9000/user/hive/warehouse/access_result
[Warning] could not update stats.
476 Rows loaded to hdfs://hdnode1:9000/tmp/hive-grid/hive_2013-05-02_17-02-05_557_8882025499109535399/-ext-10000
MapReduce Jobs Launched:
Job 0: Map: 1 Reduce: 1 HDFS Read: 7118627 HDFS Write: 8051 SUCESS
Total MapReduce CPU Time Spent: 0 msec
OK
Time taken: 25.529 seconds查询数据(由于需要排序,因此又是个M-R任务):
hive> select * from access_result order by ct desc limit 10;
Total MapReduce 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 mapred.reduce.tasks=
Starting Job = job_201304220923_0009, Tracking URL = http://hdnode1:50030/jobdetails.jsp?jobid=job_201304220923_0009
Kill Command = /usr/local/hadoop-0.20.2/bin/../bin/hadoop job -Dmapred.job.tracker=hdnode1:9001 -kill job_201304220923_0009
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2013-05-02 17:04:45,208 Stage-1 map = 0%, reduce = 0%
2013-05-02 17:04:48,220 Stage-1 map = 100%, reduce = 0%
2013-05-02 17:04:57,260 Stage-1 map = 100%, reduce = 100%
Ended Job = job_201304220923_0009
MapReduce Jobs Launched:
Job 0: Map: 1 Reduce: 1 HDFS Read: 8051 HDFS Write: 191 SUCESS
Total MapReduce CPU Time Spent: 0 msec
OK
218.20.24.203 4597
221.194.180.166 4576
119.146.220.12 1850
117.136.31.144 1647
121.28.95.48 1597
113.109.183.126 1596
182.48.112.2 870
120.84.24.200 773
61.144.125.162 750
27.115.124.75 470
Time taken: 20.608 seconds看吧,不怕不识货,就怕货比货,怪不得PIG要没落呢,相比Hive,PIG那一套可以直接忽视了。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7607759/viewspace-761358/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7607759/viewspace-761358/