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

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

今回はこんな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に関しても上記の応用でなんとかなる。dとeに関してはそれら応用ではなんともしがたいのだが、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を自分で作り始めたら上級編かな...？