hive order by sort by distribute by cluster by

order by 语法

Hive QL中的order by 类似于SQL语言中的ORDER BY语法。

排序方式：ASC|DESC

空值排序方式：NULLS FIRST|NULLS LAST

语法：order by 字段名称 ASC|DESC [NULLS FIRST|NULLS LAST]

“ order by”子句中有一些限制。在严格模式下（即hive.mapred.mode = strict），必须在order by子句后跟一个“ limit”子句。如果将hive.mapred.mode设置为nonstrict，则limit子句不是必须的。Order by 会对查询结果集执行一个全局排序。这也就是说会有一个所有的数据都通过一个reducer进行处理的过程。对于大数据集，这个过程可能会消耗太过漫长的时间来执行。

所以，为了避免这种情况发生，一个优化方案为：hive.mapred.mode=strict。（colin总结）

请注意，列是通过名称而不是位置编号指定的。但是，在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。

默认排序顺序为升序（ASC）。

在Hive 2.1.0和更高版本中，支持为“ order by”子句中的每个列指定空排序顺序。ASC顺序的默认空排序顺序为NULLS FIRST，而DESC顺序的默认空排序顺序为NULLS LAST。

In Hive 3.0.0 and later, order by without limit in subqueries and views will be removed by the optimizer. To disable it, set hive.remove.orderby.in.subquery to false.

在hive3.0.0及更高版本中，子查询和视图中没有limit子句的order by将会被优化器移除。可以通过设置 hive.remove.orderby.in.subquery为false来使此失效。

Sort by 语法

Sort by语法类似于语法SQL语言中的ORDER BY。

排序方式: ( ASC | DESC )

语法: SORT BY 列名称 [ASC|DESC]

Sort by只会在每个reducer中对数据进行排序，也就是一个局部排序过程。这可以保证每个reducer的输出数据都是有序的（但并非全局有序列）。

排序方式将取决于列的类型。如果列是一个数值列，则排序也是数值排序。如果列是一个字符列，那么排序也是字符排序。

在hive3.0.0及后续版本中，子查询和视图中没有limit的sort by 子句将会被优化器给移除。使此失效，设置hive.remove.orderby.in.subquery为false.

Sort by 和order by 的区别

Hive支持SORT BY，可对每个reducer的数据进行排序。“ order by”和“ sort by”之间的区别在于，前者保证输出中的总顺序，而后者仅保证reducer中行的排序。如果存在多个reducer，则“排序依据”可能会给出最终结果的部分排序。

注意：关于单列的单独SORT BY与CLUSTER BY之间的区别可能会造成混淆。区别在于，如果有多个reducer分区，则CLUSTER BY按字段划分，而SORT BY则是随机划分，以便在reducer上均匀地分布数据（和负载）。

基本上，每个reducer中的数据将根据用户指定的顺序进行排序。以下示例显示

SELECT key, value FROM src SORT BY key ASC, value DESC

该查询具有2个reducer，每个的输出为：

0 5

0 3

3 6

9 1

0 4

0 3

1 1

2 5

Cluster By和Distribute By语法

Cluster By和Distribute By主要与Transform / Map-Reduce脚本一起使用。但是，如果需要对查询的输出进行分区和排序以用于后续查询，有时在SELECT语句中很有用。

Cluster By相当于Distribute By和Sort By。

hive使用 Distribute By中的列在reducer之间分配列。具有相同Distribute By列的所有行将转到相同的reducer。但是，“Distribute By不保证分布键上的聚类或排序属性。

例如，我们将以下5行上的x分配给2个reducer：

x1

x2

x4

x3

x1

Reducer 1得到以下：

x1

x2

x1

Reducer 2得到以下：

x4

x3

请注意，具有相同键x1的所有行都可以保证分配给同一reducer（在这种情况下为reducer 1），但不能保证它们聚集在相邻位置。

相反，如果我们使用Cluster By x，则两个化简器将进一步对x上的行进行排序：

Reducer 1得到以下：

x1

x1

x2

Reducer 2得到以下：

x3

x4

用户可以指定Distribute By和Sort By来代替指定Cluster By，因此分区列和排序列可以不同。通常的情况是分区列是排序列的前缀，但这不是必需的。

DISTRIBUTE BY

DISTRIBUTE BY 控制map的输出在reducer中是如何划分的。

MapReduce job中传输的所有数据都是按照键-值对的方式进行组织的，因此Hive在将用户的查询语句转成MapReduce job时，其必须在内部使用这个功能。

假设我们希望具有相同股票交易码的数据在一起处理。那么我们可以使用DISTRIBUTE BY 来保证具有相同股票交易码的记录会分发到同一个reducer中进行处理，然后使用sort by 来按照我们的期望对数据进行排序。

例：

Select s.ymd,s.symbol,s.price_close

From stocks s

Distribute by s.symbol

Sort by s.symbol asc,s.ymd asc;

Distribute by 和 group by 在其控制着reducer是如何接受一行行数据进行处理这方面是类似的。而sort by 则控制着reducer内的数据是如何进行排序的。

CLUSTER BY

CLUSTER BY 字段1 = DISTRIBUTE BY 字段1 SORT BY 字段1

即distribute by 和sort by 中涉及到的列完全相同，而且采用的是升序排序方式（也就是默认的排序方式），那么在这种情况下，CLUSTER BY 就等价于distribute by 字段 sort by 字段。

相当于order by ,全局排序