背景
假设有一个学生各门课的成绩的表单,应用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开始自增。
执行row_number函数,返回值如下
科目 成绩 学生 row_number 物理 100 张一 1 物理 90 张二 2 物理 80 张三 3 ..... 数学 100 李一 1 数学 90 李二 2 数学 80 李三 3 ....
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;
同样,这个函数可以用作去重操作。
可以替代大批量数据的DISTINCT
通过执行如:
select * from(
select type,value,row_number() as rn
from log_table
distribute by type,value
sort by type,value
)
where rn = 1;
===============注意!============================
但是使用row_number()函数需要注意一点,必须使用sort by。
测试的时候必须使用order by。
row_number()函数会假设数据有序的基础上进行的。
相关推荐
02.hive内置函数--窗口分析函数--row_number_over.mp4
hive中分组取topN、row_number、rank和dense_rank使用介绍
赠送jar包:flink-connector-hive_2.11-1.10.0.jar; 赠送原API文档:flink-connector-hive_2.11-1.10.0-javadoc.jar; 赠送源代码:flink-connector-hive_2.11-1.10.0-sources.jar; 赠送Maven依赖信息文件:flink-...
赠送jar包:flink-connector-hive_2.11-1.13.2.jar; 赠送原API文档:flink-connector-hive_2.11-1.13.2-javadoc.jar; 赠送源代码:flink-connector-hive_2.11-1.13.2-sources.jar; 赠送Maven依赖信息文件:flink-...
flink-sql-connector-hive-2.3.6_2.11-1.11.0.jar
flink-sql-connector-hive-3.1.2_2.11-1.11.6.jar 已经解决guava冲突亲测可以
TPC-H_on_hive_2009-08-14.tar.gz,用于tpc hive测试工具
赠送jar包:flink-connector-hive_2.11-1.12.7.jar; 赠送原API文档:flink-connector-hive_2.11-1.12.7-javadoc.jar; 赠送源代码:flink-connector-hive_2.11-1.12.7-sources.jar; 赠送Maven依赖信息文件:flink-...
由于jar包的下载速度很慢,我想也有很多下载慢的同学,分享下
spark-hive_2.11-2.3.0...spark-hive-thriftserver_2.11-2.3.0.jar log4j-2.15.0.jar slf4j-api-1.7.7.jar slf4j-log4j12-1.7.25.jar curator-client-2.4.0.jar curator-framework-2.4.0.jar curator-recipes-2.4.0.jar
flink-connector-hive_2.12-1.12.0.jar
spark-hive_2.11-2.1.4-SNAPSHOT.rar
hive不直接支持分组取TopN的操作,需要自定义udf函数打成jar包添加到hive运行环境中
hive学习实战-guli_video_orc-guli_video_user_orc-相关资料-包含数据-我自己写的sql文件,可以直接点开运行的文件,数据是在尚硅谷学习时提供的!
spark-hive_2.11-2.1.3-SNAPSHOT.jar
dbeaver/Hive 数据库工具/最新版
赠送jar包:flink-connector-hive_2.11-1.13.2.jar; 赠送原API文档:flink-connector-hive_2.11-1.13.2-javadoc.jar; 赠送源代码:flink-connector-hive_2.11-1.13.2-sources.jar; 赠送Maven依赖信息文件:flink-...
--Hive_SQL.sql
修改过源码的jar包
密码:qazwsx