Edited at

Hive で「グループごとに1件だけ取得」する

More than 1 year has passed since last update.

Hive で「グループごとに1件だけ取得」をやろうとしたときに、今まで ROW_NUMBER を使ってやっていたが、Hivemall の EACH_TOP_K を使うという方法もあると知ったのでメモ。

参考: Top N 件をとる効率的なHive / Prestoクエリ

今回は Treasure Data のサンプルデータセットにある NASDAQ の株価テーブルを使って「各銘柄の最新の株価を取得したい」とする。


ROW_NUMBER

普通に ROW_NUMBER を使って書くとこう。

SELECT time, symbol, volume

FROM (
SELECT
time, symbol, volume,
ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY time DESC) AS rank
FROM sample_datasets.nasdaq
) AS t
WHERE rank = 1


EACH_TOP_K

Hivemall の EACH_TOP_K 関数 を使うと、このように書ける。

SELECT EACH_TOP_K(

1, -- 上位 1 件のみを取得
symbol, -- symbol (銘柄) ごとに取得
time, -- time が大きい順に
symbol, volume -- 追加で出力するカラム
) AS (rank, time, symbol, volume)
FROM (
SELECT time, symbol, volume
FROM sample_datasets.nasdaq
CLUSTER BY symbol -- EACH_TOP_K に渡す前に CLUSTER BY でまとめておく必要がある
) AS t


パフォーマンス比較


ROW_NUMBER

Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 115.69 sec

Total MapReduce CPU Time Spent: 1 minutes 55 seconds 690 msec

MapReduce time taken: 124.516 seconds



EACH_TOP_K

Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 76.0 sec

Total MapReduce CPU Time Spent: 1 minutes 16 seconds 0 msec

MapReduce time taken: 85.171 seconds


EACH_TOP_K の方が約 1.46 倍速い。

NASDAQ のサンプルデータは約880万行だったが、約2億行ある別のデータで同様に比較した場合は EACH_TOP_K の方が約 1.1 倍速いという結果になった。

データによってはそれほど差が大きわけではないが、Hivemall が使える場合は EACH_TOP_K を検討してみるのもいいかもしれない。


[追記] EACH_TOP_K でパフォーマンスが良くなるケース