[Hive]rankを使ってカテゴリごとに上位数件を抽出するクエリ

hiveでタイトルのようなことを実現するクエリを紹介する。これにはrank関数という関数を利用することで実現できる。rankを利用しない場合は以下のように複数のクエリを発行する必要があり、データ量によってはかなりの時間がかかってしまう。

1. カテゴリのリストを準備
2. 各カテゴリごとに上位数件を抽出するSELECT文(SELECT ～ FROM ～ ORDER BY ～ LIMIT n)を発行
3. （必要であれば）結果をまとめる

しかし、rank関数を利用することで１つのクエリで上記の事が達成できる。クエリのイメージは以下のようになる。

SELECT * FROM ( SELECT *, rank() over (PARTITION BY category_id ORDER BY quantity DESC) as rank FROM sample_table ) a WHERE rank < n;

以降では実際にテストデータを用意して実行してみる。

環境

今回実際に利用した各ソフトウェアのバージョンは以下のとおりである。

• Hadoop
  • 2.6.0.2.2.0.0-2041
• hive
  • 0.14.0.2.2.0.0-2041
• tez
  • 0.5.2.2.2.0.0-2041

テストデータ準備

今回は以下のようなデータを準備して、それに対してクエリを発行させる。
区切り文字はタブで、各カラムはそれぞれ商品ID、ジャンルID、商品名、売上件数を表している。

1       a       book1   100
2       b       fashion1        10
3       a       book2   1000
4       c       food1   1
5       c       food2   10
6       b       fashion2        100
7       d       medicine1       100
8       b       fashion3        1
9       b       fashion4        10
10      b       fashion5        1
11      c       food3   100
12      a       book3   10
13      a       book4   1

次にテーブルを作成してデータをインサートする

$ hive
use test;

CREATE TABLE test_item_order_data(
  item_id   bigint,
  genre_id  string,
  item_name string,
  quantity  bigint
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';

LOAD DATA LOCAL INPATH "/path/to/test data/test.dat" OVERWRITE INTO TABLE test_item_order_data;

SELECT * FROM test_item_order_data;
1       a       book1   100
2       b       fashion1        10
3       a       book2   1000
4       c       food1   1
5       c       food2   10
6       b       fashion2        100
7       d       medicine1       100
8       b       fashion3        1
9       b       fashion4        10
10      b       fashion5        1
11      c       food3   100
12      a       book3   10
13      a       book4   1

クエリ実行

冒頭で紹介したrank関数を使ったクエリをテストデータが入ったテーブルに適用すると以下のようなクエリになる。
ここでは上位3件を抽出している。
ちなみに、tezを使う方が速いのでtezを使っているが必須ではない。

set hive.execution.engine=tez;
SELECT
  *,
  rank() over (PARTITION BY genre_id ORDER BY quantity DESC) as rank
FROM
  test_item_order_data;

3       a       book2   1000    1
1       a       book1   100     2
12      a       book3   10      3
13      a       book4   1       4
6       b       fashion2        100     1
9       b       fashion4        10      2
2       b       fashion1        10      2
8       b       fashion3        1       4
10      b       fashion5        1       4
11      c       food3   100     1
5       c       food2   10      2
4       c       food1   1       3
7       d       medicine1       100     1

SELECT
  item_id,
  genre_id,
  item_name,
  quantity
FROM (
  SELECT
    *,
    rank() over (PARTITION BY genre_id ORDER BY quantity DESC) as rank
  FROM
    test_item_order_data
) io
WHERE io.rank < 3;

3       a       book2   1000
1       a       book1   100
6       b       fashion2        100
9       b       fashion4        10
2       b       fashion1        10
11      c       food3   100
5       c       food2   10
7       d       medicine1       100

結果を見ると、例えばテストデータのように2位が同着で2つある場合は、その次はちゃんと4位となるようだ。