😀

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

1 min read

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

状況

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。

まとめ

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

GitHubで編集を提案

Discussion

ログインするとコメントできます