get top k elements of the same key in hive
key points:
1. group by key and sort by using distribute by and sort by.
2. get top k elements by a UDF (user defined function) RANK
---------Here is the source code.--------------
package com.example.hive.udf;
import org.apache.hadoop.hive.ql.exec.UDF;
public final class Rank extends UDF{
private int counter;
private String last_key;
public int evaluate(final String key){
if ( !key.equalsIgnoreCase(this.last_key) ) {
this.counter = 0;
this.last_key = key;
}
return this.counter++;
}
}
The details are as the following.
---original data, table region(region_nbr, region_id)---
100 10
200 12
300 33
100 4
100 8
200 20
300 31
300 3
400 4
200 2
-----what I need is as below-----
100 10
100 8
200 20
200 12
300 33
300 31
400 4
---
1. step1. compile java with a shell, compile_udf.sh.
#!/bin/bash
if [ $# != 1 ]; then
echo "Usage: $0 <java file>"
exit 1
fi
CNAME=${1%.java}
JARNAME=$CNAME.jar
JARDIR=/tmp/hive_jars/$CNAME
HIVE_HOME2="/usr/local/hive-0.9.0"
CLASSPATH=$(ls $HIVE_HOME2/lib/hive-serde-*.jar):$(ls $HIVE_HOME2/lib/hive-exec-*.jar):$(ls /home/oicq/hadoop/hadoop-1.0.2/hadoop-core-*.jar)
function tell {
echo
echo "$1 successfully compiled. In Hive run:"
#echo "$> add jar $JARNAME;"
#echo "$> create temporary function $CNAME as 'com.example.hive.udf.$CNAME';"
echo
}
mkdir -p $JARDIR
javac -classpath $CLASSPATH -d $JARDIR/ $1 && jar -cf $JARNAME -C $JARDIR/ . && tell $1
step 2. run hive
hive -e "add jar /data/ginobili/UDF/Rank.jar; create temporary function Rank as 'com.example.hive.udf.Rank'; select region_nbr, region_id from ( select region_nbr, region_id, Rank(region_nbr) as rank from (select * from test_gino.region distribute by region_nbr sort by region_nbr, region_id desc)a )b where rank < 2"
REFERENCE
http://stackoverflow.com/questions/9390698/hive-getting-top-n-records-in-group-by-query
