Hive SQL的排序

Order By

在HSQL中的Order By类似于在SQL中的Order By。在严格模式下(hive.mapred.mode=strict),order by 必须和limit一起使用，如果hive.mapred.mode=nonstrict，order by就可以单独使用。
为什么在严格模式下，order by和limit必须一起使用呢？原因是为了进行整体输出排序，必须使用一个reducer进行输出排序，如果输出的行数太多，单个reducer会花很长的时间才能完成。

#测试Hive的hive.mapred.mode
hive> set hive.mapred.mode;
hive.mapred.mode=nonstrict
select id, name from test_transform order by id;
1   snow
2   feng
3   wind
hive> set hive.mapred.mode=strict;
hive>  select id, name from test_transform order by id;
FAILED: SemanticException 1:45 Order by-s without limit are disabled for safety reasons. If you know what you are doing, please make sure that hive.strict.checks.large.query is set to false and that hive.mapred.mode is not set to 'strict' to enable them.. Error encountered near token 'id'

一般通过列的名字来配置列，不能通过位置，但是在Hive.0.11.0(包含)以上，通过以下的配置可以用位置来配置列。
对于HIve 0.11.0到2.1.X，设置hive.groupby.orderby.position.alias为true(默认是false)
对于Hive 2.2.0以上,设置hive.orderby.position.alias为true(默认是true)

#通过位置来配置列的测试
hive > set hive.groupby.orderby.position.alias;
hive.groupby.orderby.position.alias=false
hive> select id, name from test_transform order by 1 asc;
3   wind
2   feng
1   snow
#这时hive.groupby.orderby.position.alias为false，所以排序不起作用
hive> set hive.groupby.orderby.position.alias=true;
hive> set hive.groupby.orderby.position.alias;
hive.groupby.orderby.position.alias=true
select id, name from test_transform order by 1 asc;
1   snow
2   feng
3   wind
select id, name from test_transform order by 1 desc;
3   wind
2   feng
1   snow

默认的order by的排序是升序
在Hive 2.1.0以上，在order by中可以配置NULL排序。对于升序，默认NULL排序是NULLS FIRST。对于降序，默认NULL排序是NULLS LAST。

#测试数据
hive> select id, name from test_transform;
OK
1   snow
2   feng
3   wind
NULL    order
#默认的升序，默认是的NULLS FIRST
hive> select id, name from test_transform order by id;
NULL    order
1   snow
2   feng
3   wind
#默认的升序，NULL排序是NULLS LAST
hive> select id, name from test_transform order by id NULLS LAST;
1   snow
2   feng
3   wind
NULL    order
#降序排列，NULL排序是默认的NULLS LAST
hive> select id, name from test_transform order by id desc;
3   wind
2   feng
1   snow
NULL    order
#降序排序，NULL排序是NULLS FIRST
hive> select id, name from test_transform order by id desc NULLS FIRST;
NULL    order
3   wind
2   feng
1   snow

在Hive 3.0.0以上版本，在subqueries和views中没有limit的Order by将会被optimizer(优化器)移除，为了禁用它，设置hive.remove.orderby.in.subquery为false。

Sort By

在发送行到一个reducer之前，Hive使用Sort By中的列进行排序，排序依赖于列的类型，如果列是数值类型，那么排序以数值顺序进行排序。如果列是字符串类型，那么排序以字典顺序进行排序。
在Hive 3.0.0以上，在subqueries和views中没有limit的Order by将会被optimizer(优化器)移除，为了禁用它，设置hive.remove.orderby.in.subquery为false。

Sort By和Order By的区别

Order By是进行输出的整体排序，而Sort By是在每个reducer中的排序，如果有多个reducer，Sort By可能会是部分排序输出。
可能在单列的Sort By和Cluster By的不同上会有些迷惑，不同是：Cluster By根据在Cluster By的列在reducers中分配数据，而Sort By在多个reducers的情况下，是随机分配数据的
Sort By会在每个reducer中根据列进行排序，例如：

select key, value from src sort by key asc, value desc;
#这个查询有两个reducers，那么每个的输出是：
#1
0   5
0   3
3   6
9   1
#2
0   4
0   3
1   1
2   5

Sort By的设置类型

当一个transform之后，变量类型是字符串类型，这意味着数值类型的数据会根据字段顺序进行排序，为了克服这个问题，第二个select在使用sort by之前用cast进行类型转换

FROM (FROM (FROM src
            SELECT TRANSFORM(value)
            USING 'mapper'
            AS value, count) mapped
      SELECT cast(value as double) AS value, cast(count as int) AS count
      SORT BY value, count) sorted
SELECT TRANSFORM(value, count)
USING 'reducer'
AS whatever

Cluster By和Distribute By的语义

Cluster By和Distribute By主要和Transform/Map-Reduce脚本进行使用，但是如果在select中需要一个查询的分区和排序，那么这两个语义是非常有用的。
Cluster By是Distribute By和Sort By的简写。
Hive使用在Distribute By中的列在reducers进行分配数据，拥有相同列值的数据会被分配到同一个reducer中，但是Distribute By并不能保证在Distribute By的列上的排序。
例如：我们根据列x进行Distribute By，下面是5行数据分配到2个reducers

x1
x2
x4
x3
x1

第一个reducer得到：

x1
x2
x1

第二个reducer得到：

x4
x3

拥有x1的行分配到同一个reducer(在第一个reducer)，但是并不能保证在临近的位置
但是如果我们使用Cluster By x，那么两个reducers会在x列上进行排序数据。
第一个reducer得到：

x1
x1
x2