Hiveをつかってクエリを書く際に、配列データのクエリでの操作についてハマったのでメモ。
複数の配列データを持ったテーブルの操作
ユーザ毎に複数のアイテムIDとアイテム名をもたせた配列を それぞれitem_ids, item_namesとして、 以下のような構造のuser_itemというテーブルがあるとします。
user_itemテーブル
| user_id | item_ids | item_names |
|---|---|---|
| user1 | [item_a, item_b, item_c] | [“アイテムA”, “アイテムB”, “アイテムC”] |
ここでは、item_idsとitem_namesの配列の同じインデックスの要素同士で1対1に対応するとします。
例えば、 item_aに紐づくアイテム名は “アイテムA” とします。
このデータを以下のような感じで 対応するアイテムIDとアイテム名毎に1行ずつ出力させたいとします。
出力させたい構造
| user_id | itemId | itemName |
|---|---|---|
| user1 | item_a | “アイテムA” |
| user1 | item_b | “アイテムB” |
| user1 | item_c | “アイテムC” |
この場合 hiveにデフォルトで用意されているビルトイン関数のexplodeを使ってitem_idsの各要素に対してテーブル操作ができますが、
item_idsの各要素のインデックス番号が取れないため、 item_names配列から対応するアイテム名を取ることができません。
posexplode 関数を使って 複数配列間で同一のインデックス番号を対応させる
複数の配列データに対して、配列のインデックスの順序を保ったまま処理させたい場合、
Hive0.13.0から導入された posexplodeというビルトイン関数を使えば実現できます。
LanguageManual UDF - Apache Hive - Apache Software Foundation
LATERAL VIEW句で、 以下のようにposexplodeを使うことで、item_idsの各要素とインデックス番号を取得できます。
LATERAL VIEW (item_ids) item_ids_table AS item_index, itemId
ここで仮に指定している item_ids_table は、posexplode実行時にできる内部テーブルのテーブル名となります。
本記事では特にこの内部テーブルを使わないので、特に説明はしません。
LATERAL VIEW句で posexplode関数を使うことで、カンマ区切りで item_idsの各要素と、そのインデックスを取ることができます。
下記のような感じでitem_names配列に対して取得したインデックスを指定すると
対応したアイテム名も取得できます。
SELECT itemId, item_names[item_index] AS itemName # itemIdのインデックスに対応したアイテム名を item_names配列から取得 FROM user_item LATERAL VIEW (item_ids) item_ids_table AS item_index, itemId
