HiveQL DQL5—数据排序

1. ORDER BY [ASC|DESC]

colOrder: ( ASC | DESC )
colNullOrder: (NULLS FIRST | NULLS LAST)           -- (Note: Available in Hive 2.1.0 and later)
orderBy: ORDER BY colName colOrder? colNullOrder? (',' colName colOrder? colNullOrder?)*
query: SELECT expression (',' expression)* FROM src orderBy

  使用ORDER BY时，将对每个reducer的输出维护排序顺序。通过只使用一个reducer实现全局排序，因此返回结果（尤其是对于大数据量）需要更长的时间。使用ASC或DESC来指定排序方式，ASC（ascending）表示从低到高，而DESC（descending）表示从高到低排序，默认是ASC。从Hive 2.1.0 开始，还可以对NULL值指定排序方式 (NULLS FIRST | NULLS LAST)，默认在ASC排序中NULL值在最前面，DESC排序中NULL值在最后。从Hive 3.0.0 开始，如果在子查询或者视图中使用ORDER BY时没有LIMIT语句会被优化器移除，可以将 hive.remove.orderby.in.subquery 设置为false来禁用该功能。

示例：基本用法

> select name from employee_internal ORDER BY name;
...
+----------+
|   name   |
+----------+
| Lucy     |
| Michael  |
| Shelley  |
| Will     |
+----------+

> select name from employee_internal ORDER BY name DESC;
...
+----------+
|   name   |
+----------+
| Will     |
| Shelley  |
| Michael  |
| Lucy     |
+----------+

示例：指定NULL值的排序方向

> select * from test order by b;
...
+---------+---------+
| test.a  | test.b  |
+---------+---------+
| 400     | NULL    |
| 300     | NULL    |
| 200     | NULL    |
| 500     | hello   |
+---------+---------+
> select * from test order by b NULLS LAST;
...
+---------+---------+
| test.a  | test.b  |
+---------+---------+
| 500     | hello   |
| 400     | NULL    |
| 300     | NULL    |
| 200     | NULL    |
+---------+---------+

ORDER BY通常都是指定字段名来进行排序的，但是也可以通过指定字段位置进行排序。

示例：指定字段位置排序

> select name from employee_internal ORDER BY 1;
...
+----------+
|   name   |
+----------+
| Lucy     |
| Michael  |
| Shelley  |
| Will     |
+----------+

> select name from employee_internal ORDER BY 1 DESC;
... 
+----------+
|   name   |
+----------+
| Will     |
| Shelley  |
| Michael  |
| Lucy     |
+----------+

示例：使用表达式排序

> select name from employee_internal ORDER BY
 CASE WHEN name='Michael' THEN 0 ELSE 1 END DESC;
+----------+
|   name   |
+----------+
| Lucy     |
| Shelley  |
| Will     |
| Michael  |
+----------+

  注意：在使用ORDER BY语句时，强烈建议使用LIMIT语句。当 hive.mapred.mode 值为 stric（Hive 1.* 默认是nonstrict，Hive 2.* 默认是 strict）时 ;在Hive 3中, 参数hive.mapred.mode已经被废弃，使用新的参数 hive.strict.checks.orderby.no.limit，当该参数值为true时,如果在使用ORDER BY时没有LIMIT语句，则会抛出异常。如下：

> set hive.strict.checks.orderby.no.limit=true;
> select name from employee_internal ORDER BY 1 DESC;
Error: Error while compiling statement: FAILED: SemanticException 1:44 Order by-s without limit are disabled for safety reasons. If you know what you are doing, please set hive.strict.checks.orderby.no.limit to false and make sure that hive.mapred.mode is not set to 'strict' to proceed. Note that you may get errors or incorrect results if you make a mistake while using some of the unsafe features.. Error encountered near token '1' (state=42000,code=40000)

2. SORT BY [ASC|DESC]

colOrder: ( ASC | DESC )
sortBy: SORT BY colName colOrder? (',' colName colOrder?)*
query: SELECT expression (',' expression)* FROM src sortBy

  SORT BY 用以在将数据发送给reducer处理前对指定的字段进行排序。也就是说在数据在reduer处理前已经完成了排序。SORT BY不会使整个数据集全局有序（ORDER BY是全局有序的），只是确保每个reducer处理的本地数据集有序。不过如果只有一个reducer任务(set mapred.reduce.tasks=1)，也就可以实现全局有序了，此时就等价于 ORDER BY了 。大多数情况中，SORT BY不会单独使用，通常会和 DISTRIBUTE BY 一起使用。从Hive 3.0.0 开始，如果在子查询或者视图中使用ORDER BY时没有LIMIT语句会被优化器移除，可以通过将 hive.remove.orderby.in.subquery 设置为false来禁用该功能。

示例

> SET mapred.reduce.tasks=2;
> select name from employee_internal SORT BY name DESC;
+----------+
|   name   |
+----------+
| Shelley  |
| Michael  |
| Lucy     |  --使用多个reducer时，返回客户端的结果就是无序的
| Will     |
+----------+

> SET mapred.reduce.tasks=1;
> select name from employee_internal SORT BY name DESC;
+----------+
|   name   |
+----------+
| Will     | 
| Shelley  |
| Michael  | --使用1个reducer时，返回客户端的结果和ORDER BY相同
| Lucy     |
+----------+

3. DISTRIBUTE BY

在mapper决定将哪些数据发送给那个reducer处理时，DISTRIBUTE BY 的功能和 GROUP BY 非常相似。与GROUP BY 相比，DISTRIBUTE BY不用于数据聚合，如count（*），而只指示数据的去向。在这种情况下，DISTRIBUTE BY通常用于按指定列重新组织文件中的数据。例如，我们可能需要在UNION结果集之后使用DISTRIBUTE BY来重新组织更高粒度的数据。与SORT BY 一起用于对指定组中的数据进行排序时，可以在一个查询中的sort by之前使用DISTRIBUTE BY。此外，DISTRIBUTE BY之后的字段必须在select 字段列表中。

示例：基本用法

-- DISTRIBUTE BY之后的字段employee_id并不在SELECT字段列表中，所以会报错
> SELECT name FROM employee_hr DISTRIBUTE BY employee_id;
Error: Error while compiling statement: FAILED: SemanticException [Error 10004]: Line 1:43 Invalid table alias or column reference 'employee_id': (possible column names are: name) (state=42000,code=10004)

--正确的使用
> SELECT name, employee_id FROM employee_hr DISTRIBUTE BY employee_id;
+----------+--------------+
|   name   | employee_id  |
+----------+--------------+
| Lucy     | 103          |
| Steven   | 102          |
| Will     | 101          |
| Michael  | 100          |
+----------+--------------+

示例：与SORT BY一起使用

> SELECT name, start_date
FROM employee_hr
DISTRIBUTE BY start_date SORT BY name;
+----------+-------------+
|   name   | start_date  |
+----------+-------------+
| Lucy     | 2010-01-03  |
| Michael  | 2014-01-29  |
| Steven   | 2012-11-03  |
| Will     | 2013-10-02  |
+----------+-------------+

4. CLUSTER BY

  CLUSTER BY 是一个快捷的操作符，用于对同一组字段执行 DISTRIBUTE BY 和 SORT BY 操作。CLUSTER BY 语句不允许指定 ASC 或 DESC，因此排序指定是从低到高。与全局排序 ORDER BY 相比，CLUSTER BY会对每个分布组中的数据排序。

示例

> SELECT name, employee_id FROM employee_hr CLUSTER BY name;
+----------+--------------+
|   name   | employee_id  |
+----------+--------------+
| Lucy     | 103          |
| Michael  | 100          |
| Steven   | 102          |
| Will     | 101          |
+----------+--------------+

使用技巧：当进行全局排序时，我们可以先进行 CLUSTER BY ，然后再进行 ORDER BY。这样可以提前充分利用所有可用的reducer，并有更好的性能。如：

SELECT * FROM (SELECT * FROM employee CLUSTER BY name) base ORDER BY name;

上述四种语句都用于数据的排序，他们的区别如下图所示：

参考

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
书籍 Apache Hive Essentials Second Edition (by Dayong Du) Chapter 5