Hive的各种排序详解与代码演示

一：基本的Select操作

- 语法结构

SELECT [ALL | DISTINCT] select_expr, select_expr, ...

FROM table_reference

[WHERE where_condition]

[GROUP BY col_list [HAVING condition]]

[CLUSTER BY col_list

  | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]

]

[LIMIT number]

1、order by 会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。

2、sort by不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。即每个文件是有序的。如果reduce=1,sort by结果和order by 一样，一般sort by 不单独使用

3、distribute by(字段)根据指定的字段将数据分到不同的reducer，且分发算法是hash散列常和sort by排序一起使用。

     1.distribute by负责把文件哈希散列到不同文件，sort by负责给不同文件进行排序。

     2.当然必须要设置set mareduce.job.reduces 数量，设置的reduces数量个数，就是实际hash散列的文件个数，因为hash 散列的是通过hash值与reduce个数取模决定存储在哪个文件 里的。所以如果不设置 reduces个数，即使distribute by+sort by结果还是和 orderby结果一样的。

4、Cluster by(字段) 除了具有Distribute by的功能外，还会对该字段进行排序。

     因此，如果分桶和sort字段是同一个时，此时，cluster by = distribute by + sort by

     分桶表的作用：最大的作用是用来提高join操作的效率；

   （思考这个问题：

           select a.id,a.name,b.addr from a join b on a.id = b.id;

          如果a表和b表已经是分桶表，而且分桶的字段是id字段

           做这个join操作时，还需要全表做笛卡尔积吗？）

举例说明：（也可用insert将查询的结果导出文件）

       1. select * from em

           where empno >7800

           order byempno

           limit 3;(显示工号大于7800员工的最后三名。)

           只是针对所有数据全局排序

         2.sort by排序,为每个reducer产生一个排序文件

           ①设置mapred.reduce.tasks （旧版设置方法）即reduce任务个数 ，如果为1跟order by 解果一样。

                set mapred.reduce.tasks; 回车以后显示mapred.reduce.task的值

                set mapred.reduce.tasks =3;临时设置的值，一旦hive关闭重启就没用了。

               set mapreduce.job.reduces=3;(hadoop2.x以后的设置方式)

           ②sort by是在reduce之间排序，

                insert overwrite local directory '/home/robot/mydata/sort'

                row format delimited fields terminated by '\t'

                select empno,ename ,deptno from emp

                sort by empno

                结果是3个文件夹，随机分布的数据，然后给每个文件夹里的数据按empno进行了排序。

                insert overwrite directory '/user/hive/'

                row format delimited fields terminated by '\t'

                select empno,ename ,deptno from emp

                sort by empno      //sort by (empno asc/dsc)

        3.distributed 类似于mapreduce里的patition分区(根据指定值hash散列到不同文件)一般要结合sort by使用。

               set mapreduce.job.reduces=3;

                insert overwrite local directory '/home/robot/mydata/sort'

                row format delimited fields terminated by '\t'

                select empno,ename ,deptno from emp

                distribute by deptno

                sort by empno

                这里的输出结果是sort下面三个文件夹，分别是三个部门员工的文件夹，且每个部门按工号排序

        4  cluster by :除了具有Distribute by的分区的功能外，还会对该字段进行排序

               当distribute和sort字段相同时，就是cluster by，即分区的字段和排序的字段相同

               insert overwrite local directory '/home/robot/mydata/sort'

               row format delimited fields terminated by '\t'

               select empno,ename ,deptno from emp

               distribute by deptno

               sort by deptno

              等价与（cluter可以用sort+distribute替换，但d+s，cluster不一定可以替换）

               insert overwrite local directory '/home/robot/mydata/sort'

               row format delimited fields terminated by '\t'

               select empno,ename ,deptno from emp

               cluster by depno