hive优化一

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。

 

假设一个场景：存在表user_score,该表的数据如下
idratescore1'0-4'102'0-4'403'0-4'304'0-4'205'5-10'106'5-10'407'5-10'308'5-10'209'11-20'1010'11-20'4011'11-20'3012'11-20'20
现在要求用一条查询语句取出每种rate下score最大的两条记录，也就算取出id为：2，3，6，7，10，11的记录，要求分别给出SQL和HIVESQL的查询语句

以下是我的想到的方案：

SQL：对user_score根据rate进行分区后根据score进行倒序排序，然后应用row_number函数于每个分区从而筛选出每个分区的前两条记录。

Sql代码

1. WITH pus AS (   
2.  SELECT *, rid = ROW_NUMBER() OVER    
3.  (PARTITION BY rete ORDER BY score DESC) FROM user_score   
4. )   
5.   
6. SELECT id, rate, score FROM pus WHERE rid <= 2  

WITH pus AS ( SELECT *, rid = ROW_NUMBER() OVER  (PARTITION BY rete ORDER BY score DESC) FROM user_score)SELECT id, rate, score FROM pus WHERE rid <= 2

HIVESQL: HIVE中不存在以上SQL的那种实现方式，ROW_NUMBER（）在HIVE中是一个函数，必须带一个或者多个列参数，如ROW_NUMBER(col1, ....)，它的作用是按指定的列进行分组生成行序列，在ROW_NUMBER(a,b) 时，若两条记录的a，b列相同，则行序列+1，否则重新计数。因为HIVE是基于MAPREADUCE的，必须保证ROW_NUMBER执行是在REDUCE中，并且ROW_NUMBER中使用的列中，列值相同的记录要再同一个reduce中，否则ROW_NUMBER的行为是无意义的。

Sql代码

1. SELECT id, rate, score FROM(SELECT * FROM user_score DISTRIBUTE BY rate SORT BY rete ASC, score DESC) WHERE ROW_NUMBER(rete) <= 2  

 SELECT id, rate, score FROM(SELECT * FROM user_score DISTRIBUTE BY rate SORT BY rete ASC, score DESC) WHERE ROW_NUMBER(rete) <= 2

这里有两点需要说明：

• 使用子查询保证ROW_NUMBER在reduce端执行。
• 使用BY rate SORT BY rete ASC, score DESC 来保证rate相同的记录被分配到相同的REDUCE中。