Hiveで複数の要素を行として抽出する

例えば次のような param 列だけを持つテーブルがあるとします。
param は string として user_id と複数の要素からなる user_chars を持っています。

param
{"user_id":1,"user_chars":[{"id":1,"char_id":1},{"id":2,"char_id":2}]}
{"user_id":2,"user_chars":[...]}

今、あなたはユーザ毎の user_char_id (id), char_id を集計することになりました。いったいどうすれば良いでしょうか？

指定したパターンで文字列を分割する split

組み込み関数の split を使えば指定したパターンで文字列を分割し、分割された要素を array として受け取ることができます。
次の文字列を例に split で user_chars の要素を分割してみましょう。

{"user_id":1,"user_chars":[{"id":1,"char_id":1},{"id":2,"char_id":2}]}

user_chars 部分を抜き出すには regexp_extract を用います。抜き出した文字列に対して split で分割します。次のようになるでしょう。

split(regexp_extract(param, '"user_chars":(.*)$', 1), '[^0-9],')

regexp_extract は第2引数にパターンを、第3引数にグループのインデックスを渡します。この例ではグループは1つしかないので1となります。
JSONの場合、もっと良い例として regexp_extract の代わりに get_json_object を利用すべきです。get_json_object を使うと次のようになります。

get_json_object(param, '$.user_chars')

split のパターンは「直前が整数でないカンマ」としています。単純にカンマだけだと4つに分割されてしまうためです。

array を受け取り要素を行にして返す explode

split で分割した array の要素を行として返すには explode を使います。

SELECT
  explode(split(get_json_object(param, '$.user_chars'), '[^0-9],')) AS user_char
FROM t1

これだけで }, で分割された歪な user_chars の要素を行として抽出することができますが、user_id も欲しいし user_char_id, char_id も列として欲しいところでしょう。
そのためには既存テーブルに横付けで仮想テーブル付け加える LATERAL VIEW を使います。LATERAL VIEW を使うと次のようになります。

SELECT
  get_json_object(param, '$.user_id') AS user_id,
  regexp_extract(user_char, '"id":([0-9]*)', 1) AS user_char_id,
  regexp_extract(user_char, '"char_id":([0-9]*)', 1) AS char_id
FROM t
LATERAL VIEW
  explode(split(get_json_object(param, '$.user_chars'), '[^0-9],')) a AS user_char

split し explode した歪な user_char を LATERAL VIEW で横付けしています。これは下記のようなイメージです。

param	user_char
{"user_id":1,"user_chars":[...]}	[{"id":1,"char_id":3
{"user_id":1,"user_chars":[...]}	{"id":2,"char_id":5}]

"lateral" が表す「横の」や「側面の」といった意味を知ればイメージしやすいかもしれませんね。

さらに歪な user_char から regexp_extract を使って user_char_id (id), char_id を抽出しています。最終的に得られる結果は次のようになります。

user_id	user_char_id	char_id
1	1	3
1	2	5

今回紹介した関数は全て下記の Hive マニュアルに載っています。
Apache Hive LanguageManual UDF

おわりに

Hive で split, explode, LATERAL VIEW を使って複数の要素を行として抽出する方法について説明しました。
今回は説明を省きましたが、サブクエリや WHERE と組み合わせることでさらに複雑な条件にも対応できるようになります。
ぜひこれらの関数を使いこなして楽しい Hive ライフを送ってください！Have a nice Hive!