背景
假设有一个学生各门课的成绩的表单,应用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_score where 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_score where 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_score where 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_score where 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_score where 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_score where row_number(subject) <= 100;
相关推荐
hive不直接支持分组取TopN的操作,需要自定义udf函数打成jar包添加到hive运行环境中
hive中分组取topN、row_number、rank和dense_rank使用介绍
hive udaf 实现按位取与或 hive udaf 实现按位取与或 hive udaf 实现按位取与或
部分普通sql查询在hive中的实现方式详细说明;
用于多行合并和分组limit输出的udf工具包,已编译配置好,直接调用即可
hive hive hive hive hive hive hive hive hive hive hive hive
Apache Hive 是基于Hadoop的一个数据仓库工具,用来进行数据提取、转化、加载,这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。hive数据仓库工具能将结构化的数据文件映射为一张数据库表,并提供...
02_Hive表批量加载数据的脚本实现(一) 03_Hive表批量加载数据的脚本实现(二) 04_HIve中的case when、cast及unix_timestamp的使用 05_复杂日志分析-需求分析 06_复杂日志分析-需求字段讲解及过滤 07_复杂日志分析...
Hive原理与实现 详细介绍了hive的原理
Hive中SQL详解
hive实现并发机制:hive里,同一sql里,会涉及到n个job,默认情况下,每个job是顺序执行的。 如果每个job没有前后依赖关系,可以并发执行的话,可以通过设置该参数 set hive.exec.parallel=true,实现job并发执行...
pyflink将mysql数据直接插入hive,由此可以延伸出pyflink实现hive关联mysql
大数据hive实现原理.zip
如果系统语言不是utf-8的话,通过jdbc调用hive的数据时,中文会是乱码,通过修改jdbc包加上utf8标识后就好了,这个是改好了的包
1.数据迁移的过程中,由于hive的Null值存储为"\N",Mysql存储为NULL值,二者转换时需要注意,再数据迁移json模板中,"nullFormat": "\\N", 该配置信息就是解决了数据迁移是存在的Null值。 2.如果hive中没有数值DataX...
pdf文件讲述hive实现原理,图文并茂。
· 将数据载入表中以及从表中抽取数据,并使用查询、分组、过滤、连接和其他常规查询方法。 · 获得创建用户自定义函数(UDF)的最佳方法。 · 了解应该使用的Hive模式以及应该避免的反模式。 · 将Hive和其他数据...
二、Distribute by 五、Cluster by 六、如何使用sort by实现全局排序
(” n”)以及读取文件数据的方法( Hive 中默认有三个文件格式 TextFile , SequenceFile 以及 RCFile )。由于在加载数据的过程中,不需要从用户数据格式到 Hive 定义的数据格式的转换,因此, Hive 在加载的...
《Hive编程指南》是一本Apache Hive的编程指南 旨在介绍如何使用Hive的SQL方法 HiveQL来汇总 查询和分析存储在Hadoop分布式文件系统上的大数据集合 全书通过大量的实例 首先介绍如何在用户环境下安装和配置Hive 并对...