Hive TOP N 实现方法

https://my.oschina.net/u/3204727/blog/1506599

https://cloud.tencent.com/developer/article/1403400

 

 

1).Hive中Select Top N的实现

Hive中使用 Order by + Limit 可以很容易地实现Select Top N。

hive默认的order by实现只会用1个reduce做全局排序，这在数据量大的时候job运行效率非常低。hive在0.12版本引入了parallel order by，也就是通过sampling的方式实现并行（即基于TotalOrderPartitioner）。具体开关参数是hive.optimize.sampling.orderby。但是如果使用这个参数还是很可能碰到问题的：

• 首先如果order by字段本身取值范围过少，会造成Split points are out of order错误。这是因为，假设job中reduce数量为r的话，那么TotalOrderPartitioner需要order by字段的取值至少要有r - 1个。那么这样一来还需要关心reduce数量，增加了开发负担，而且如果把reduce数量设的很小，优化的效果就不太明显了。
• 其次，设置这个参数还可能造成聚会函数出错，这个问题只在比较新的hive版本中解决了。

实际上，如果只是取top n而非全局排序，只需要使用sort by col limit n的写法就能达到很好的效果。sort by语法本身保证每个reduce内数据有序，这样就等于是做并行排序。而limit n则保证两件事：一方面是使得并行排序时每个reduce的输出记录数只是n，也就是先在每个reduce内部做top n（可以explain一下看看执行计划更加清楚）；另外一方面，等局部top n完成之后，再起一轮job，用1个reduce做全局top n，这个时候虽然不是并行排序，但是处理的数据量也已经大大减少，不会造成效率问题了。当然，如果自己实现mapreduce，可以在mapper任务内维护最小最大堆，直接在map端实现并行的top n，再输出给1个reducer做全局top n，只需要一轮job即可完成。不过如果n的大小没有限制很可能会撑爆内存，而且即使没有内存问题，实现也比较复杂，所以hive中没有实现这样的Operator，而是用上面描述的方式解决。毕竟在reduce端做top n，排序问题就已经在MR框架层面解决了，只需要考虑limit即可。

从执行计划explain中可以看出Sort by Limit N启动了两个MR Job。第一个Job是在每个Reduce中做局部排序，然后分别取Top N。假设启动了M个reduce，第二个Job再对M个Reduce分别局部排好序的总计M * N条数据做全局排序，再取最终的Top N，从而得到想要的结果。这样就可以大大提高Select Top N的效率。

set mapred.reduce.tasks=3;
select * from tea sort by age limit 3;

除了对全部数据取top n，分组top n也是常见场景，比如学生成绩表取每个学科前三名，用户点击流数据取每个用户最早的几个点击等等。如果每个分组需要排序的数据量不大，那么可以用窗口函数解决，或者在不支持窗口函数的比较老的hive版本自己实现udf。但是如果每个分组本身很大，还是会很慢。如果追求性能的话，同样可以借鉴sort by limit的写法，在分组个数不多且固定的情况下直接将分组写死。比如“取每个性别访问次数最多的10人”类似这样的情景，就可以拆解为“男性访问次数最多的10人 + 女性访问次数最多的10人”。

 

2).Hive中分组  Select Top N的实现；

drop table tmp_users_time;  
create table tmp_users_time   
as  
select * from  
(  
  select u.*,row_numwer() over(distribute by grade sort by score desc) sn  
  from users u  
)tu  
where tu.sn > 2;  

或者

insert into table users_time_top  
select tu.grade,tu.score  
from  
(  
  select u.*,row_number() over(distribute by grade sort by score desc) sn  
  from users u  
)tu  
where tu.sn > 2;  

 

3).hive中的分号字符 -- 此部分内容在最新版的HIVE中已修复；

分号是SQL语句结束标记，在HiveQL中也是，但是在HiveQL中，对分号的识别没有那么智慧，例如：

hive> select concat(';','a');
--会报异常NoViableAltException(-1@[]),解决方案：
最新版的HIVE输出的结果为：';a'

hive> select concat('\073','a');
--分号的ASCII值是59,八进制为073.只能使用八进制,对于十六进制无效
--concat(str1,str2)作用为拼接字符串
最新版Hive的输出结果：';a'

3).Hive客户端默认配置

以下操作均可在$HIVE_HOME/bin/.hiverc文件中保存，设置为默认参数:

set hive.mapred.mode=strict;
//开启strict模式,以下情况报错：(1)没有limit限制的order by语句.(2)动态分区插入

set hive.cli.print.current.db=true;
//显示当前工作的数据库

set hive.cli.print.header=true;
//显示列名

set mapred.reduce.tasks=3;
//设置reduce的个数

set hive.exec.reducers.bytes.per.reducer;
//多少字节开一个reducer，默认256000000

set hive.exec.dynamic.partition.mode=strict;
//该模式下必须指定一个静态分区