読者です 読者をやめる 読者になる 読者になる

kakts-log

programming について調べたことを整理していきます

Hive posexplode関数を使った配列操作について (配列のインデックスを保持したまま処理を行う方法)

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

参考

stackoverflow.com

qiita.com