Hiveに文字列で格納されたJSON形式の配列を行として展開する方法

タイトルだけだと何を言っているのかよくわからないかもしれませんが、下の例を見ていただけるとわかりやすいかと思います。

状況

Hiveに入っているのは以下のようなテーブル（items）で、idはint、nameとattrはstring。

id	name	attr
1	aaa	[{"key": "age", "value": 20}, {"key": "gender", "value": 1}, {"key": "location", "value": 12}]
2	bbb	{"key": "age", "value": 30}, {"key": "gender", "value": 2}

実際に欲しいのは下のようなデータ。

id	name	key	value
1	aaa	age	20
1	aaa	gender	1
1	aaa	location	12
2	bbb	age	30
2	bbb	gender	2

attrはstring（大事なことなので再確認）。
もしこれがarrayとかであればもう少し簡単だったはず。

実際に書いたクエリ

select
  id,
  name,
  key,
  value
from
  items
lateral view
  explode(
    split(
      substr(attr, 2, length(attr) - 2),
      '(?<=}),\s*'
    )
  ) exploded as json
lateral view
  json_tuple(
    json,
    'key',
    'value'
  ) extracted as key, value

解説

lateral view
  explode(
    split(
      substr(attr, 2, len(attr) - 2),
      '(?<=}),\s*'
    )
  ) exploded as json

まず、attrの2文字目から（文字数 - 2）文字分までを取り出すことで、両端の[と]を取り除く。

次に、splitで配列化する。このとき、単純に,で区切ってしまうと、JSONの中身のカンマを拾ってしまったり、余計な空白が残ってしまったりするので、正規表現で区切りを指定する。

そして、explodeで配列の要素（JSON）を行として展開し、lateral viewで既存のテーブルの横に配置する。

lateral view
  json_tuple(
    json,
    'key',
    'value'
  ) extracted as key, value

さらに、上で展開したJSONからjson_tupleでkeyとvalueと取り出し、lateral viewで既存のテーブルの横に配置する

後は普通にselectすればOK。

まとめ

処理にとても悩んだのでメモ。
こんなデータの持ち方している事例が他にあるかと言われると微妙な気もしますが。