パケットTop N問題(三)-sql及びHive実現

前言:
前編Hadoop MapReduce実装パケットTop nの紹介と同様に,今回はHive実装パケットTop nを学習する.
ケース:
データ処理では、テーブルデータをフィールドごとにグループ化し、それぞれのグループ内で最大のレコード状況を特定するシーンがよくあります.このようなパケットTopN問題に対して,Hive,MapReduceなど多様なツールを用いて実現する.
シーンシミュレーション:
クラスは次のusersテーブルで記録し、異なるgradeで最も得点の多い2つの記録を取り出します.

id	grade	score
1	  A    	10
2	  A   	40
3	  B   	30
4	  C   	20
5	  B   	10
6	  D   	40
7	  A   	30
8	  C   	20
9	  B   	10
10	  D   	40
11	  C   	30
12	  D   	20

1、普通SQL実現:
まずusersをgradeに基づいてパーティション化し、scoreに基づいて逆順序にソートしてrow_を適用します.number関数は、各パーティションから各パーティションの最初の2つのレコードをフィルタします. 

WITH groupsort AS (
 SELECT *, rid = ROW_NUMBER() OVER 
 (PARTITION BY grade ORDER BY score DESC) FROM users
)

SELECT id, grade, score FROM groupsort WHERE rid <= 2;

ここではWITH ASイオン抽出クエリーセクションを使用します.SQL文全体で使用されるSQLセグメントを定義することに相当します.ある場合、SQL文の可読性、最適化性を向上させるためです.WITHASフレーズで定義されたテーブル名が2回以上呼び出されると、オプティマイザはWITH ASフレーズで取得したデータを自動的に1つのTEMPテーブルに入れます.1回だけ呼び出されると、できません.
そしてrow_を使いますnumber()over(partition by clo 1 order by clo 2 desc)法は、clo 1をグループ化し、同じグループのclo 2の最大の1つまたは複数のレコードを計算する.
2、Hive利用row_number実装
私たちはHiveがHQL文を実行することを許可していることを知っています.HQLとSQLには多くの共通点がありますが、ROW_では多くの違いがあります.NUMBER()を例にとります.
Hive低バージョンでは、ROW_NUMBER()はHIVEで1つの関数であり、ROW_のような1つ以上のカラムパラメータを持つ必要があります.NUMBER(col1, ....),指定した列にグループ化してロー・シーケンスを生成する役割を果たし、ROW_NUMBER(a,b)の場合,2つのレコードのa,b列が同じであれば行系列+1,そうでなければ再カウントする.高バージョンではこの制限はキャンセルされ、書き方は現在のsqlと一致している.また、HIVEはMAPREADUCEに基づいているため、ROW_を保証しなければならない.NUMBER実行はREDUCEであり、ROW_NUMBERで使用されるカラムで、カラム値が同じレコードを同じreduceに追加します.そうしないとROW_NUMBERの行為は無意味です.
高バージョンの書き方:(通常hiveはrow_numberで生成された擬似列を新しいテーブルに書き込み、各ユニットの各時刻の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;

または記録時刻topの大きなテーブルにデータを追加し、デフォルトの大きなテーブル自動追加時間

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;

低バージョンの書き方(未検証):

select u.*,row_number(u.score) as sn
from
(
select grade,score from users distribute by grade sort by score desc
)u
where row_number(score) > 2;

ここでは2つの説明が必要です.
サブクエリを使用してROW_を保証するNUMBERはreduce側で実行されます.distribute by grade sort by score descを使用して、gradeの同じレコードが同じREDUCEに割り当てられることを保証します.
UDF関数実装:
今日もHive udf関数を用いてTopnを実現する方法を学びました.gradeとscoreをソートし、gradeをカウントし、where条件でgrade列カウンタがnより大きい行をフィルタすればいいです.
(1)UDFを定義する:

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++;
	}
}

(2)jar、建表、導データを登録し、クエリー:

add jar Rank.jar;
create temporary function rank as 'com.example.hive.udf.Rank';
create table users(id int,grade string,score int) row format delimited fields terminated by ' ';
LOAD DATA LOCAL INPATH 'users.txt' OVERWRITE INTO TABLE users;
select grade,score from (
	select grade,rank(grade) cnum,score from (
		select id, grade, score from users distribute by grade sort by score desc
	)u
)b where cnum < 2;

これは一時的なUDF変換であり、永久的に個別に設定する必要がある.
詳細は、次のとおりです.
http://blog.sina.com.cn/s/blog_5ceb51480102wabj.html http://chiyx.iteye.com/blog/1559460