[Hive排序]--4种排序方式介绍

一、官方文档

二、译文

LanguageManual SortBy

跳到元数据的末尾

由Confluence管理员创建，最后由Lefren Leverenz于2017年7月8日修改

转到元数据的开始

order by，sort by ，distribute by 和 cluster by

集群和分布的语法

order by ，sort by，cluster by 和 distribute by

这描述了SELECT子句ORDER BY，SORT BY，CLUSTER BY和DISTRIBUTE BY的语法。有关常规信息，请参阅选择语法。

命令的语法

该ORDER BY在Hive SQL语法类似的语法ORDER BY在SQL语言。

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

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”子句有一些限制。在严格模式（即hive.mapred.mode = strict）中，order by子句必须后跟一个“limit”子句。如果将hive.mapred.mode设置为非限制，则不需要限制子句。原因是为了强加所有结果的总顺序，必须有一个redcue对最终输出进行排序。如果输出中的行数太大，则单个还原器可能需要很长时间才能完成。

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

在Hive 3.0.0及更高版本中，优化器将删除子查询和视图中的无限制排序。要禁用它，请将hive.remove.orderby.in.subquery设置为false。

排序方式的语法

该排序语法类似于语法ORDER BY在SQL语言。

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

Hive使用SORT BY中的列对行进行排序，然后将行提供给reducer。排序顺序将依赖于列类型。如果列是数字类型，则排序顺序也以数字顺序排列。如果列是字符串类型，则排序顺序将是字典顺序。

在Hive 3.0.0及更高版本中，优化器将删除子查询和视图中的无限制排序。要禁用它，请将hive.remove.orderby.in.subquery设置为false。

order by 与 sort by之间的差异

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

注意：单独列出的单独列与单独的列之间的区别可能令人困惑。区别在于CLUSTER BY按字段分区，如果有多个reducer分区，则可以随机分配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

sort by 方式的设置类型

变换后，变量类型通常被认为是字符串，这意味着数字数据将按字典顺序分类。为了克服这一点，在使用SORT BY之前，可以使用带有转换的第二个SELECT语句。

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

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主要用于转换/映射 - 减少脚本。但是，如果需要对后续查询的查询输出进行分区和排序，那么在SELECT语句中有时是有用的。

Cluster By是分发和排序的快捷方式。

Hive使用Distribute By中的列在reducer之间分配行。具有相同分布列的所有行将转到同一个reducer。但是，Distribute By不能保证分布式密钥上的集群或排序属性。

例如，我们将以下5行的“ 分布式x ” 分配给2个reducer：

x1 x2 x4 x3 x1

reducer1得到

x1 x2 x1

reducer2得到

x4 x3

请注意，具有相同键x1的所有行都保证分发到同一个reducer（在这种情况下为reducer 1），但是它们不能保证在相邻位置中聚类。

相比之下，如果我们使用Cluster By x，则两个reducers将进一步排序x上的行：

reducer1得到

x1 x1 x2

reducer2得到

x3 x4

用户可以指定“ CLUSTER BY 和排序”，而不是指定“ 群集”，因此分区列和排序列可以不同。通常的情况是分区列是排序列的前缀，但不是必需的。

SELECT col1, col2 FROM t1 CLUSTER BY col1

SELECT col1, col2 FROM t1 DISTRIBUTE BY col1 SELECT col1, col2 FROM t1 DISTRIBUTE BY col1 SORT BY col1 ASC, col2 DESC

FROM ( FROM pv_users MAP ( pv_users.userid, pv_users.date ) USING 'map_script' AS c1, c2, c3 DISTRIBUTE BY c2 SORT BY c2, c1) map_output INSERT OVERWRITE TABLE pv_users_reduced REDUCE ( map_output.c1, map_output.c2, map_output.c3 ) USING 'reduce_script' AS date, count;

FROM (
FROM pv_users
MAP ( pv_users.userid, pv_users.date )
USING 'map_script'
AS c1, c2, c3
DISTRIBUTE BY c2
SORT BY c2, c1) map_output
INSERT OVERWRITE TABLE pv_users_reduced
REDUCE ( map_output.c1, map_output.c2, map_output.c3 )
USING 'reduce_script'
AS date, count;

三、有网友已经总结如下

参考：https://www.bbsmax.com/A/q4zVVj2GzK/

order by

　　order by 会对输入做全局排序，因此只有一个reducer（多个reducer无法保证全局有序）只有一个reducer，会导致当输入规

模较大时，需要较长的计算时间。

　　set hive.mapred.mode=nonstrict; (default value / 默认值)

　　set hive.mapred.mode=strict;

　　order by 和数据库中的Order by 功能一致，按照某一项&几项排序输出。

　　与数据库中 order by 的区别在于在hive.mapred.mode = strict 模式下必须指定 limit 否则执行会报错。

　　hive> select * from test order by id;

　　FAILED: Error in semantic analysis: 1:28 In strict mode, if ORDER BY is specified, LIMIT must also be specified.

Error encountered near token 'id'

　　原因：在order by 状态下所有数据会到一台服务器进行reduce操作也即只有一个reduce，如果在数据量大的情况下会出现无

法输出结果的情况，如果进行 limit n ，那只有 n * map number 条记录而已。只有一个reduce也可以处理过来。

sort by

　　sort by不是全局排序，其在数据进入reducer前完成排序.

　　因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局

有序。

　　sort by 不受 hive.mapred.mode 是否为strict ,nostrict 的影响

　　sort by 的数据只能保证在同一reduce中的数据可以按指定字段排序。

　　使用sort by 你可以指定执行的reduce 个数（set mapred.reduce.tasks=<number>）,对输出的数据再执行归并排序，即

可以得到全部结果。

　　注意：可以用limit子句大大减少数据量。使用limit n后，传输到reduce端（单机）的数据记录数就减少到n* （map个数）。

否则由于数据过大可能出不了结果。

distribute by

　　按照指定的字段对数据进行划分到不同的输出reduce / 文件中。

insert overwrite local directory '/home/hadoop/out' select * from test order by name distribute by length(name);

　　此方法会根据name的长度划分到不同的reduce中，最终输出到不同的文件中。

　　length 是内建函数，也可以指定其他的函数或这使用自定义函数。

cluster by

　　cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。

　　但是排序只能是倒序排序，不能指定排序规则为asc 或者desc。

秒客网