order、sort、distribute和cluster by（Spark/Hive）
1. abstract
ORDER BY：完整查询结果的全局行排序。与SORT BY、CLUSTER BY、DISTRIBUTE BY互斥，不能同时使用。
示例SELECT * FROM table_name ORDER BY column_name;SORT BY：只在每个分区内排序，局部排序结果不是全局有序。与ORDER BY、CLUSTER BY互斥，不能同时指定。
示例SELECT * FROM table_name SORT BY column_name;
sort by和order by的区别：二者都是进行排序，区别在于sort by仅在partition中进行排序，而order by在全局进行排序，因此sort by无法保证整体有序性。DISTRIBUTE BY：按指定列对数据进行重新分区，并不控制排序。通常和sort by一起使用，实现分区+排序。该参数与ORDER BY和CLUSTER BY互斥，不能同时指定。
示例SELECT * FROM table_name DISTRIBUTE BY column_name;，SELECT * FROM table_name DISTRIBUTE BY column_name sort by column_name;CLUSTER BY：等价于DISTRIBUTE BY+SORT BY，按指定列对数据进行重新分区并且每个分区内排序。
示例SELECT * FROM table_name CLUSTER BY column_name;
2. spark sql文档内容
2.1. ORFER BY
原文连接
全局排序。
syntax
ORDER BY { expression [ sort_direction | nulls_sort_order ] [ , ... ] }
- sort_direction
ASC或DESC。默认为升序 - nulls_sort_order
可选项，NULLS FIRST（null值始终排在最前） 或NULLS LAST（null值始终排在最后）。指定null值在non-null值之后还是之前返回。当未显示设置时，升序排序时null值排在最前，降序时null值排在最后。
2.2. SORT BY
原文连接
分区内排序。
syntax
SORT BY { expression [ sort_direction | nulls_sort_order ] [ , ... ] }
sort_direction和nulls_sort_order用法同order by。
2.3. DISTRIBUTE BY
原文连接
对数据进行重新分区。
注意：distribute by rand()的用法，rand()返回一个随机数，即保证每个分区的数据量基本一致。
syntax
DISTRIBUTE BY { expression [ , ... ] }
- expression
值、运算符和 SQL 函数。
2.4. CLUSTER BY
使用CLUSTER BY子句首先根据输入表达式对数据进行重新分区，然后对每个分区中的数据进行排序。这个子句只确保结果行在每个分区内排序，而不保证输出的总顺序。
这在语义上等同于执行一个DISTRIBUTE BY后面跟着一个SORT BY，即cluster by col等同于distribute by col sort by col。
syntax
CLUSTER BY { expression [ , ... ] }
- expression
值、运算符和 SQL 函数。
3. Hive文档中内容
原文连接
语法同spark保持一致，仅补充hive中注意项。
[WITH CommonTableExpression (, CommonTableExpression)*] (Note: Only available starting with Hive 0.13.0)
SELECT [ALL | DISTINCT] select_expr, select_expr, ...FROM table_reference[WHERE where_condition][GROUP BY col_list][ORDER BY col_list][CLUSTER BY col_list| [DISTRIBUTE BY col_list] [SORT BY col_list]][LIMIT [offset,] rows]
3.1. Syntax of Order By
在hive中，当hive.mapred.mode=strict时必须添加limit限制，当nonstrict时可以没有limit限制。原因是为了保证全部结果的有序，需要通过一个reducer对最终结果进行排序。如果输出结果太大则需要较长的时间才能完成。
在Hive 0.11.0之前的版本排序列必须通过列名来指定。从0.11.0版本开始通过启用参数支持按列位置来指定排序列。在Hive 0.11.0 到 2.1.x版本中，将 hive.groupby.orderby.position.alias 设置为true时，可排序也可以只用列位置来指定。在Hive 2.2.0及更高版本hive.orderby.position.alias参数默认为true。hive.groupby.orderby.position.alias从2.2.0废弃，被hive.orderby.position.alias参数取代。
3.2. Syntax of Sort By
在 Hive 2.1.0 版本对order by和sort by支持nulls_sort_order选项，可以指定null值的排序。 ASC 顺序的默认空排序顺序是 NULLS FIRST，而 DESC 顺序的默认空排序顺序是 NULLS LAST。
在 Hive 3.0.0 版本开始hive.remove.orderby.in.subquery为true时，优化器将删除子查询和视图中的无limit限制的order by或sort by从句。
3.3. Syntax of Cluster By and Distribute By
注意，从HIVE-28572开始，同时设置hive.orderby.position.alias=true和hive.cbo.enable=true参数后支持按列位置来指定重分区列。列位置从1开始。
set hive.orderby.position.alias=true;
set hive.cbo.enable=true;
SELECT age, name, birthdate FROM author DISTRIBUTE BY 3, 1;