TreasureDataでHiveクエリを書くとき - テーブルを結合しよう

はじめに

HiveではRDBのSQLのように複数のテーブルを組み合わせて利用することができます。

Joinの種類

身も蓋もない話ですが、Hiveのドキュメントにあるように結合しましょう。

等価結合

テーブルaとテーブルbのidが共通しているデータ中のテーブルaのidとテーブルbのidを表示します。

SELECT a.id,b.name FROM a JOIN b ON (a.id = b.id)

外部結合

テーブルaの全データに対して、テーブルbにテーブルaのkeyと同じkeyがあった場合はテーブルbのデータを利用します。RIGHT JOINも使えますが、ややこしくなるの私はよっぽどじゃないと使いません。てか使わなくてもよくない？

SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)

LEFT SEMI JOIN

Hiveですと0.13以前までは下記のように条件のinの後ろにサブクエリを書けなかったので、LEFT SEMI JOINで代用してました。

SELECT a.key, a.value FROM a
WHERE a.key in (SELECT b.key FROM B)

SELECT a.key, a.val FROM a 
LEFT SEMI JOIN b ON (a.key = b.key)

初めて見た時LEFT SEMI JOINってなんだよ！って思ったんですが、公式ドキュメントにご丁寧にサンプルまでバッチリ書いてありました。SQLはかけると自信持っていると足元すくわれたりするので気をつけて、初心を忘れずにドキュメントを読みましょう。（自分にいい聞かせている。。）

Hive Version 0.13から

RDBのSQLのようにfromの後ろにテーブルを並べることによって等価結合に利用できるようになりました。便利なんですが、順序や結合理由を示すためにもJoinを利用した方がのちほろ読みやすいクエリになるようにも思えますがどうでしょ？

SELECT * 
FROM table1 t1, table2 t2, table3 t3 
WHERE t1.id = t2.id AND t2.id = t3.id AND t1.zipcode = '02535'

条件句のサブクエリも利用できる場合も出てきました。

SELECT *
FROM A
WHERE A.a IN (SELECT foo FROM B);

EXISTもサポートされたみたいです。ひー。

SELECT A
FROM T1
WHERE EXISTS (SELECT B FROM T2 WHERE T1.X = T2.Y)

まとめ

あまりTreasureData独自という感じじゃなくなりました。私の周りのTD初心者の人に伝えたくて書いているんで、ただのHiveの説明の時もありますよ。ははは。