Help us understand the problem. What is going on with this article?

HiveでJSONデータを処理するあれこれ(中級編)

More than 5 years have passed since last update.

前回初級編と書いてしまったけど、上級な知識も無いのでこの中級編で終わり(・∀・)

今回はこんなJSONを集計したいのだ!

{
    "a": "abc", 
    "b": 123, 
    "c": {
        "aa": "xyz", 
        "bb": 987
    }, 
    "d": [1,2,3], 
    "e": [
        {
            "aaa": "ABC", 
            "bbb": 12345
        }, 
        {
            "aaa": "XYZ", 
            "bbb": 98765
        }
    ]
}

JSON中のa,bに関しては初級編で行った通り、json_tupleもしくはget_json_objectを使えば簡単に集計出来る。
cに関しても上記の応用でなんとかなる。deに関してはそれら応用ではなんともしがたいのだが、JSON SerDeを使えばあっというま。

多階層JSONにおけるjson_tupleの利用

cに含まれる要素の取得集計については下記の例のとおりjson_tupleを段階的に使う必要がある。

SELECT json_c.* FROM raw_json 
    LATERAL VIEW json_tuple(
        raw_json.line,
        'c'
    ) json as c
    LATERAL VIEW json_tuple(
        json.c,
        'aa',
        'bb'
    ) json_c as aa, bb;

json.cで取得出来るのは、{"aa":"xyz","bb":987}という文字列なので、これを更にLATERAL VIEW json_tupleしてあげればとれるってわけ。LATERAL VIEWで新たにjson_cというFieldを別名で定義しているので、aaを参照するには、json.c.aaではなくてjson_c.aaとなることに注意(個別に参照する場合は単に'aa'や'bb'でもOKだと思う)。

Listの処理

dのListを処理するにはこれまでのjson_tuple,get_json_objectだけではダメ。
そもそも、一レコードに複数のレコードが入っているとい状態で、通常のRDBであればdは別テーブルになっているはずのところ。ETL処理でdを別ファイルに書き出し、JOINすれば出来そうだが、なんだか手間だ。何か方法があるはずと思い調べてみた。

問題点としてはjson_tuple, get_json_objectが返す値はPrimitive Typeだけって事。
json_tupleに関してはListな値については"[1,2,3]"という文字列で取得出来るだけ、get_json_objectに関してはget_json_object(raw_json.line, '$.d[0]')とList内にアクセスは可能だが、これだとListのLengthが事前にわからないと集計が出来ない事になるし、そもそも各レコード毎にListのLengthが違えばそもそも集計なんて出来ない。ARRAYとしてのアクセスが可能であればexplodeを利用する事によってARRAYを展開して集計が可能になるのに。

split,substr,length & explode

組み込み関数を駆使して強引にやってみるとこうなるのかな?

dに含まれる値の合計値を出すクエリ

SELECT sum(number) FROM raw_json 
    LATERAL VIEW json_tuple(
        raw_json.line,
        'd'
    ) json_list as numbers
    LATERAL VIEW explode(
        split(
            substr(
                json_list.numbers,
                2,
                length(json_list.numbers)-2
            ),
            ','
        )
    ) numbers as number;

イヤだ、これはイヤだ...。
explodeの中でやっている事が必死過ぎるのでイヤだ...。

JSON SerDe & explode

ということで、もっとスマートに行こう!

JSON SerDeを使ってListを持つテーブルを定義すると

CREATE TABLE serde_json(
    d ARRAY<INT>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
STORED AS TEXTFILE;

となる。
これに対して

SELECT sum(number) FROM serde_json
    LATERAL VIEW explode(d)
        numbers as number;

これでOK。スッキリ。

Listの処理 - 応用

Listの処理で行った応用でeの項目も定義してみる。
eのARRAYはclassのARRAYとなっているので、下記のように書くこととなる。

CREATE TABLE serde_json(
    e ARRAY<STRUCT<aaa:STRING,bbb:INT>>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
STORED AS TEXTFILE;

Listの処理と同じく、eの集計を行うにも同じくexplodeを使うと良い。

SELECT sum(data.bbb) FROM serde_json
    LATERAL VIEW explode(e) datas as data;

すべてをまとめると下記のようにしておくと良いのかな。

CREATE TABLE serde_json(
    a STRING,
    b INT,
    c STRUCT<aa:STRING,bb:STRING>,
    d ARRAY<INT>,
    e ARRAY<STRUCT<aaa:STRING,bbb:INT>>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
STORED AS TEXTFILE;

ここまで出来ればどんなJSONであってもある程度はHiveで集計できそうだ(・∀・)

SerDeだったりUDFを自分で作り始めたら上級編かな...?

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした