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

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 でパフォーマンスが良くなるケース

row_numberの場合は関係ない要素全部にも数字を振る必要がありますが、row_numberの場合は必要な分を振ったらそのあとは処理をしなくてよいので、グループ内の要素数の差が大きいほどパフォーマンスに差が出るかなと思います。

— torut (@nora96o) August 8, 2018

どちらもrow_numberになってました。後者がeach_top_kです。

— torut (@nora96o) August 8, 2018