こんなデータを簡単に集計したいって事。
{"name":"hoge","age":30,"city":"tokyo"}
{"name":"huga","age":41,"city":"chiba"}
{"name":"hige","age":26,"city":"kanagawa"}
世の中的にはfluentdで飛んでくるログフォーマットがjsonだったりとかで、json形式で保存されたデータをHiveで集計したいってニーズは沢山あるみたい。例に漏れず当方もしたいので、やり方を調査。
Hiveではいくつかやり方があるみたいなので、それぞれをまとめてみる。
以降のサンプルはjson_tuple, get_json_objectを使う場合のtableは上記のようなjsonが1行1jsonという体で保存されていることとし、下記のように生成されている事とする。
CREATE TABLE raw_json(line STRING);
とりあえずこれをselectすると、
hive> select * from raw_json;
OK
{"name":"hoge","age":30,"city":"tokyo"}
{"name":"huga","age":41,"city":"chiba"}
{"name":"hige","age":26,"city":"kanagawa"}
Time taken: 0.415 seconds, Fetched: 3 row(s)
となる。
json_tuple
jsonの構造をviewとして定義してselectする。このやり方を推奨する記述を結構目にする。
SELECT user.* FROM raw_json
LATERAL VIEW json_tuple(
raw_json.line,
'name',
'age',
'city'
) user as name, age, city;
この結果は、
Total MapReduce CPU Time Spent: 1 seconds 230 msec
OK
hoge 30 tokyo
huga 41 chiba
hige 26 kanagawa
Time taken: 8.9 seconds, Fetched: 3 row(s)
となる。指定したjsonのkeyがちゃんとFieldにバインドされた。
これを使ってwhere句も実行出来る。
SELECT user.* FROM raw_json
LATERAL VIEW json_tuple(
raw_json.line,
'name',
'age',
'city'
) user as name, age, city
WHERE user.age >= 30;
この結果が、
Total MapReduce CPU Time Spent: 1 seconds 580 msec
OK
hoge 30 tokyo
huga 41 chiba
うむ。簡単。
get_json_object
任意のSTRINGデータをjson->object変換して利用する。
SELECT
get_json_object(raw_json.line, '$.name'),
get_json_object(raw_json.line, '$.age'),
get_json_object(raw_json.line, '$.city')
FROM raw_json;
結果は同じく
Total MapReduce CPU Time Spent: 1 seconds 650 msec
OK
hoge 30 tokyo
huga 41 chiba
hige 26 kanagawa
となる。
上記のselectのField一つづつjson->object変換しているっぽく見えるね。
この書き方でwhere句も付け加えてみると、
SELECT
get_json_object(raw_json.line, '$.name'),
get_json_object(raw_json.line, '$.age'),
get_json_object(raw_json.line, '$.city')
FROM raw_json
WHERE
get_json_object(raw_json.line, '$.age') >=30;
となる。勿論結果は、
Total MapReduce CPU Time Spent: 1 seconds 560 msec
OK
hoge 30 tokyo
huga 41 chiba
となって正しい結果が得られる。
が、書き方も面倒な上におそらく効率は良くないと思われる。
json_tupleとの比較はまた後日。
SerDe Hive-JSON-Serde
ここまでではとりあえずjson_tupleを使っておけばある程度は出来そうだなって感じなのだが、実は複雑なJSON構造を解析しようと思うと途端使えない。
特にListを持つJSONの場合、json_tupleやget_json_objectでは複雑な要求に答えるのは難しい(簡単なListであれば出来ないこともない)。
ということで最後はHive SerDe(Serialize/Deserialize)を使ったJSON解析。
いくつかあるようだったけど、これが良さそうだった。
https://github.com/rcongiu/Hive-JSON-Serde
cloneしてpackage。testでfailになるのでとりあえずテストをスキップ。
failになっている理由は後で調査。
git clone https://github.com/rcongiu/Hive-JSON-Serde.git
cd Hive-JSON-Serde
mvn -Dmaven.test.skip=true package
target以下にjson-serde-1.1.9.1-jar-with-dependencies.jarが出来上がる。
このjarをhive実行環境でadd jarする。
hive> add jar /tmp/json-serde-1.1.9.1-jar-with-dependencies.jar
その後こんな感じでテーブルを定義。
CREATE TABLE serde_json(
name STRING,
age INT,
city STRING
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
STORED AS TEXTFILE;
このうえでselectをすると、
hive> SELECT * FROM json_serde;
OK
hoge 30 tokyo
huga 41 chiba
hige 26 kanagawa
あら簡単。
とりあえず初級編はこんな感じかな。次はあんなJSONやこんなJSONをHiveで集計しちゃうぞ。