Hive中分组取前N个值
来源:互联网 发布:第三方数据 游戏 编辑:程序博客网 时间:2024/05/16 16:14
背景
假设有一个学生各门课的成绩的表单,应用Hive取出每科成绩前100名的学生成绩。
这个就是典型在分组取Top N的需求。
解决思路
对于取出每科成绩前100名的学生成绩,针对学生成绩表,根据学科,成绩做order by排序,然后对排序后的成绩,执行自定义函数row_number(),必须带一个或者多个列参数,如ROW_NUMBER(col1, ....),它的作用是按指定的列进行分组生成行序列。在ROW_NUMBER(a,b) 时,若两条记录的a,b列相同,则行序列+1,否则重新计数。
只要返回row_number()返回值小于100的的成绩记录,就可以返回每个单科成绩前一百的学生。
解决过程
成绩表结构
create table score_table ( subject string, student string, score int)partitioned by (date string)
如果要查询2012年每科成绩前100的学生成绩,sql如下
create temporary function row_number as 'com.blue.hive.udf.RowNumber';select subject,score,student from (select subject,score,student from score where dt='2012' order by subject,socre desc) order_scorewhere row_number(subject) <= 100;
com.blue.hive.udf.RowNumber是自定义函数,函数的作用是按指定的列进行分组生成行序列。这里根据每个科目的所有成绩,生成序列,序列值从1开始自增。
假设成绩表的记录如下:
物理 80 张三数学 100 李一物理 90 张二数学 90 李二物理 100 张一数学 80 李三.....
经过order by全局排序后,记录如下
物理 100 张一物理 90 张二物理 80 张三.....数学 100 李一数学 90 李二数学 80 李三....
接着执行row_number函数,返回值如下
科目 成绩 学生 row_number物理 100 张一 1物理 90 张二 2物理 80 张三 3.....数学 100 李一 1数学 90 李二 2数学 80 李三 3....
因为hive是基于MAPREADUCE的,必须保证row_number执行是在reducer中执行。上述的语句保证了成绩表的记录,按照科目和成绩做了全局排序,然后在reducer端执行row_number函数,如果在map端执行了row_number,那么结果将是错误的。
要查看row_number函数在map端还是reducer端执行,可以查看hive的执行计划:
create temporary function row_number as 'com.blue.hive.udf.RowNumber';explain select subject,score,student from (select subject,score,student from score where dt='2012' order by subject,socre desc) order_scorewhere row_number(subject) <= 100;
explain不会执行mapreduce计算,只会显示执行计划。
只要row_number函数在reducer端执行,除了使用order by全局排序配合,也可以使用distribute by + sort by。distribute by可以让相同科目的成绩记录发送到同一个reducer,而sort by可以在reducer端对记录做排序。
而使用order by全局排序,只有一个reducer,未能充分利用资源,相比之下,distribute by + sort by在这里更有性能优势,可以在多个reducer做排序,再做row_number的计算。
sql如下:
create temporary function row_number as 'com.blue.hive.udf.RowNumber';select subject,score,student from (select subject,score,student from score where dt='2012' distribute by subject sort by subject asc, socre desc) order_scorewhere row_number(subject) <= 100;
如果成绩有学院字段college,要找出学院里,单科成绩前一百的学生,解决方法如下:
create temporary function row_number as 'com.blue.hive.udf.RowNumber';explain select college,subject,score,student from (select college,subject,score,student from score where dt='2012' order by college asc,subject asc,socre desc) order_scorewhere row_number(college,subject) <= 100;
如果成绩有学院字段college,要找出学院里,总成绩前一百的学生,解决方法如下:
create temporary function row_number as 'com.blue.hive.udf.RowNumber';explain select college,totalscore,student from (select college,student,sum(score) as totalscore from score where dt='2012' group by college,student order by college asc,totalscore desc) order_scorewhere row_number(college) <= 100;
row_number的源码
函数row_number(),必须带一个或者多个列参数,如ROW_NUMBER(col1, ....),它的作用是按指定的列进行分组生成行序列。在ROW_NUMBER(a,b) 时,若两条记录的a,b列相同,则行序列+1,否则重新计数。下面的源码就是查出行序列
package com.blue.hive.udf;import org.apache.hadoop.hive.ql.exec.UDF;public class RowNumber extends UDF { private static int MAX_VALUE = 50; private static String comparedColumn[] = new String[MAX_VALUE]; private static int rowNum = 1; public int evaluate(Object... args) { String columnValue[] = new String[args.length]; for (int i = 0; i < args.length; i++) 『 columnValue[i] = args[i].toString(); } if (rowNum == 1) { for (int i = 0; i < columnValue.length; i++) comparedColumn[i] = columnValue[i]; } for (int i = 0; i < columnValue.length; i++) { if (!comparedColumn[i].equals(columnValue[i])) { for (int j = 0; j < columnValue.length; j++) { comparedColumn[j] = columnValue[j]; } rowNum = 1; return rowNum++; } } return rowNum++; }}
编译后,打包成一个jar包,如/usr/local/hive/udf/blueudf.jar
然后在hive shell下使用,如下:
add jar /usr/local/hive/udf/blueudf.jar;create temporary function row_number as 'com.blue.hive.udf.RowNumber';select subject,score,student from (select subject,score,student from score where dt='2012' order by subject,socre desc) order_scorewhere row_number(subject) <= 100;
- hive中分组取前N个值的实现
- hive中分组取前N个值的实现
- Hive中分组取前N个值
- hive中分组取前N个值的实现
- Hive中分组取前N个值
- Hive中分组取前N个值
- Hive中分组取前N个值
- Hive中分组取前N个值
- hive的分组取前n个值
- Mysql取分组后前N个值
- Hive和MySQL分组排序取前n条记录
- hive 分组取Top N
- Hive分组取Top N
- 分组取前N记录
- 分组取前N记录
- 分组取前N记录
- 分组取前N记录
- hive分组排序 取top N
- ssh无密码登录最简单的方法
- 十个经典面试题
- CMSIS-RTOS 复用Multiplex
- maven命令
- 菜单程序(二级指针学习)
- Hive中分组取前N个值
- java-积累
- 微信公众平台java开发详解(工程代码+解析)
- 从IT菜鸟变为 IT骨干开发者 的11个建议
- Sencha Cmd安装笔记
- 页面自动刷新实例
- c# 反射Invoke调用方法获得out带出的值
- Linux Set Up Routing with ip Command
- Toolbar图标颜色和文字颜色设置