Hive 0.10でROLLUP, CUBE, GROUPING SETSが入ったよ

Hive

最近Hiveをいじることはめっきりなくなりましたが1/11にHive 0.10がリリースされましたね。前の0.9が2012/4/30リリースだったことを考えると約8ヶ月ぶりのリリースですね。ちなみに僕がHiveを一番触っていた頃のバージョンは0.6だったりします。てへ。それに比べるとだいぶ機能追加されていますが今回はROLLUP, CUBE, GROUPING SETSについて書いてみたいと思います。

JIRAはこちら
[HIVE-3433] Implement CUBE and ROLLUP operators in Hive - ASF JIRA

いろんな軸で集計したい場合に商用DBならROLLUP, CUBE, GROUPING SETSといった機能が使えます。これらの機能がなんとHiveでも使えるようになりました。スバラシ。

Pigのほうが早く実装されるかと思いきやHiveのほうが早かったですね。この辺は以下に少し書きました。

SQL, PigのCUBE - wyukawa’s blog

上記ブログでも使ったサンプルデータでHiveのROLLUP, CUBE, GROUPING SETSを見ていきたいと思います。

DDLはこんな感じ

create table a(
  x string,
  y string,
  z int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','

サンプルデータはこんな感じ

$ cat cube.csv 
dog,miami,12
cat,miami,18
turtle,tampa,4
dog,tampa,14
cat,naples,9
dog,naples,5
turtle,naples,1

で、こんな感じでロードします。

LOAD DATA INPATH '.../cube.csv' OVERWRITE INTO TABLE a

じゃあ最初にROLLUPいきます。こんな感じのHiveクエリーを実行します。

select x,sum(z) from a group by x with rollup

実行結果はこんな感じ。第一列、x列で集計した結果に加えて総計もNULL,63(=27+31+5)と表示されていますね。

NULL 63
cat 27
dog 31
turtle 5

ちなみにROLLUPはHive 0.9以下でも以下のようなクエリを実行することでUNIONを使わずに同等のことを実現できます。注意点はxがnullにならないこと。

SELECT xn, SUM(z) FROM a LATERAL VIEW explode(array(x,null)) e as xn group by xn

元ネタ
個別集計と全体集計を1回のHiveクエリで実行する - たごもりすメモ

いきなりこのクエリー見てもなんのこっちゃという感じですが以下のようなクエリーを実行するとイメージがわくと思います。集計用に1列足すわけですね。これがxn列です。これを作るためにarrayで配列作ってexplodeで展開します。全体集計用にキーがnullのものを作り出して全体集計を出す感じです。なのでもとの集計キーxにnullがあるとその値が全体集計のほうに集計されてしまってマズいわけですね。

select * FROM a LATERAL VIEW explode(array(x,null)) e as xn

実行結果

dog miami 12 dog
dog miami 12 NULL
cat miami 18 cat
cat miami 18 NULL
turtle tampa 4 turtle
turtle tampa 4 NULL
dog tampa 14 dog
dog tampa 14 NULL
cat naples 9 cat
cat naples 9 NULL
dog naples 5 dog
dog naples 5 NULL
turtle naples 1 turtle


次にCUBE行ってみましょう。

クエリはこんな感じ

select x,y,count(*),sum(z) from a group by x,y with cube

実行結果はこんな感じ。最初の行は全集計結果で、次の3行はy列での集計結果で、次のcatの3行は最初の行が次の2行の集計行でそれ以降のdog, turtleも同じ感じです。

NULL NULL 7 63
NULL miami 2 30
NULL naples 3 15
NULL tampa 2 18
cat NULL 2 27
cat miami 1 18
cat naples 1 9
dog NULL 3 31
dog miami 1 12
dog naples 1 5
dog tampa 1 14
turtle NULL 2 5
turtle naples 1 1
turtle tampa 1 4


GROUPING SETSの場合のクエリはこんな感じ

select x,y,count(*),sum(z) from a group by x,y grouping sets(x,y)

実行結果はこんな感じ。最初の3行がy列での集計結果で後半の3行がx列での集計結果ですね。

NULL miami 2 30
NULL naples 3 15
NULL tampa 2 18
cat NULL 2 27
dog NULL 3 31
turtle NULL 2 5


CUBE, GROUPING SETSに関してもHive 0.9以下でUNIONを使わずに実現できるかはちょっとわかりません。

いじょ。