hive排序特性研究
1. 排序定义
所谓排序就是使一串记录,按照其中的某个或某些关键字,递增或是递减的排列。
2. hive 中排序相关内容
2.1 order by
order by 会对输入做全局排序,故只有一个reducer,若数据的规模比较大时,需要较长的计算时间。hive中order by 也是对一个结果集进行排序,不同于关系型数据库是底层架构。hive的hive-site.xml配置文件中的参数hive.mapred.mode控制着hive的执行方式,若选择strict,则order by 则需要指定limit(若有分区还有指定哪个分区) ;若为nostrict,则与关系型数据库差不多。由于order by 执行时,只有一个reducer ,如果结果集过大,那执行时间相对会比较漫长。
注:若不想修改配置文件,可临时执行:set hive.mapred.mode=nonstrict 或set hive.mapred.mode=strict;也可以在当前会话中达到同样的效果。
测试:
--未开启strict模式,即nostrict模式
hive> select id,devid,devname from tb_in_base order by devid;
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=<number>
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
set mapred.reduce.tasks=<number>
Starting Job = job_201307151509_15431, Tracking URL = http://mwtec-50:50030/jobdetails.jsp?jobid=job_201307151509_15431
Kill Command = /home/hadoop/hadoop-0.20.2/bin/hadoop job -Dmapred.job.tracker=mwtec-50:9002 -kill job_201307151509_15431
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2013-08-05 16:33:21,817 Stage-1 map = 0%, reduce = 0%
2013-08-05 16:33:23,828 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.94 sec
2013-08-05 16:33:24,834 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.94 sec
2013-08-05 16:33:25,843 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.94 sec
2013-08-05 16:33:26,849 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.94 sec
2013-08-05 16:33:27,855 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.94 sec
2013-08-05 16:33:28,860 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.94 sec
2013-08-05 16:33:29,873 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.94 sec
2013-08-05 16:33:30,880 Stage-1 map = 100%, reduce = 33%, Cumulative CPU 0.94 sec
2013-08-05 16:33:31,888 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 2.51 sec
2013-08-05 16:33:32,893 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 2.51 sec
2013-08-05 16:33:33,899 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 2.51 sec
MapReduce Total cumulative CPU time: 2 seconds 510 msec
Ended Job = job_201307151509_15431
MapReduce Jobs Launched:
Job 0: Map: 1 Reduce: 1 Cumulative CPU: 2.51 sec HDFS Read: 559 HDFS Write: 138 SUCCESS
Total MapReduce CPU Time Spent: 2 seconds 510 msec
OK
1 121212 test1
2 131313 test2
3 141414 test3
4 151515 test5
5 161616 test6
6 171717 test7
8 191919 test9overwrite
8 191919 test9overwrite
Time taken: 16.872 seconds
结果说明:没有开启严格模式时,order by 与关系型数据库效果类似。
--开启scrict模式,且未在order by 后面加limit
hive> select id,devid from tb_in_base order by devid;
FAILED: Error in semantic 易做图ysis: 1:41 In strict mode, if ORDER BY is specified, LIMIT must also be specified. Error encountered near token 'devid'
注:没有指定limit 报错
--开启scrict模式,且未在order by 后面加limit ,且未指定分区
hive> select * from tb_in_base;
FAILED: Error in semantic 易做图ysis: No partition predicate found for Alias "tb_in_base" Table "tb_in_base"
结果说明:严格模式下,无法直接进行查询。
--开启scrict模式,且未在order by 后面加limit ,且指定分区
hive> select * from tb_in_base where job_time=030729 order by devid limit 2;
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=<number>
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
set mapred.reduce.tasks=<number>
Starting Job = job_201307151509_15432, Tracking URL = http://mwtec-50:50030/jobdetails.jsp?jobid=job_201307151509_15432
Kill Command = /home/hadoop/hadoop-0.20.2/bin/hadoop job -Dmapred.job.tracker=mwtec-50:9002 -kill job_201307151509_15432
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2013-08-05 16:47:32,900 Stage-1 map = 0%, reduce = 0%
2013-08-05 16:47:34,920 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.39 sec
2013-08-05 16:47:35,927 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.39 sec
2013-08-05 16:47:36,934 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.39 sec
2013-08-05 16:47:37,941 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.39 sec
2013-08-05 16:47:38,946 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.39 sec
2013-08-05 16:47:39,953 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.39 sec
2013-08-05 16:47:40,959 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.39 sec
2013-08-05 16:47:41,965 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.39 sec
2013-08-05 16:47:42,971 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 3.05 sec
2013-08-05 16:47:43,977 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 3.05 sec
2013-08-05 16:47:44,983 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 3.05 sec
MapReduce Total cumulative CPU time: 3 seconds 50 msec
Ended Job = job_201307151509_15432
MapReduce Jobs Launched:
Job 0: Map: 1 Reduce: 1 Cumulative CPU: 3.05 sec HDFS Read: 458 HDFS Write: 44 SUCCESS
Total MapReduce CPU Time Spent: 3 seconds 50 msec
OK
1 121212 test1 030729
2 131313 test2 030729
Time taken: 17.597 seconds
结果说明:严格模式下,使用order by 不仅需要指定limit 数量,若有表分区还需要指定表分区。
2.2 sort by
sort可以控制每个reduce产生的文件都是排序,再对多个排序的好的文件做二次归并排序。sort by 特点如下:
1) .