ズッキーニのプログラミング実験場

プログラミング + アカデミック + 何か面白いこと。 記載されているものは基本的に私が所属する団体とは関係がありません。

   Aug 07

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

by zuqqhi2 at 2016年8月7日
Pocket

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

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

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

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位となるようだ。

Related Posts

  • 2013年5月31日 [Hadoop][Hive]EC2上のUbuntu12.04にインストールしてみる Hiveをインストールしてみる。 とりあえず、動くところまで。 なんとか動いた。 hive-default.xml.templateになんでこんな閉じタグミスが多いんだろう?I'll try to install Hive and type a […]
  • 2013年8月4日 [node.js][express]RESTful API part2 やりたいこと mongoDBとつなげてGETパラメタやDBのデータ内容に応じて出力を変える。 ソース […]
  • <!--:ja-->[API][MySQL][Ruby][JSON]ATNDからAPIでイベント情報を取得してmysqlに入れてみる<!--:--><!--:en-->[API][MySQL][Ruby][JSON]Insert Event Information from ATND via ATND API<!--:-->2014年11月2日 [API][MySQL][Ruby][JSON]ATNDからAPIでイベント情報を取得してmysqlに入れてみる 概要 タイトルのまま。 ATNDというイベント開催支援サイトから イベント情報を取り出してMySQLに入れてみる。 環境 OSLinux version 3.2.0-67-generic (buildd@brownie) (gcc […]
  • 2013年6月16日 [Hadoop][Ruby]Hadoop Streaming 練習 どのユーザがどの検索ワードを使用したかを表すログデータを 集計して検索ワードランキング用のデータを生成することを考える。 次にMapperをrubyで書いてみる。 次にReducerを書く。 […]
  • <!--:ja-->[mysql]バージョン確認方法<!--:--><!--:en-->[mysql]How to check version<!--:-->2013年7月21日 [mysql]バージョン確認方法 It's easy to check. Login Type "select version();" That's all. It's easy to check. Login Type "select version();" That's all.
  • 2013年7月8日 [Haskell]PGMファイルを読み込む part1 やりたいこと HaskellでPGMファイルを生成することができたから、 今度は入力出来るようにしてみたい。 プログラム とりあえずヘッダーを読み込んで出力するところまで。 実行してみる。 Target I could output […]
Pocket

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.