HiveでJSON形式のログを構造化する

fluentの勉強会に参加したら、まわりがモヒカンばっかりで消毒されそうになったタムタムです。

先日のMixi Engineers' Seminar #3でMixiがJSONデータをHiveで集計しているよーという発表をしていて、Fluentのtail + apacheもjsonとしてデータが飛んでいるとの事で、相性は悪くないよねーというわけでサンプルを作ってみました。（スライドを見ていた感じではこんな感じだったはず）

まずサンプルデータを用意します。
適当に100件ほど作りました。 (nameが名前でexam{1|2|3}は何かのテストの点数です）

{"name" : "Irma", "exam1": 58, "exam2": 73, "exam3":85}
{"name" : "Dorothy", "exam1": 50, "exam2": 39, "exam3":11}
{"name" : "Echo", "exam1": 95, "exam2": 47, "exam3":96}
{"name" : "Ina", "exam1": 50, "exam2": 68, "exam3":38}
{"name" : "Kay", "exam1": 15, "exam2": 11, "exam3":46}
....

まずはデータをそのまま1つの列に入れます。今回はサンプルなのでPARTITIONは切っていません。

create table raw_data(line string);
load data local inpath '/mnt/iscsi/test1/exam_data1.txt' overwrite into table raw_data;

このJSONデータをテーブル化するクエリは以下のように書けます。

select
  get_json_object(raw_data.line, '$.name'),
  get_json_object(raw_data.line, '$.exam1'),
  get_json_object(raw_data.line, '$.exam2'),
  get_json_object(raw_data.line, '$.exam3')
from raw_data;

もしくは、json_tupleとlateral viewを使います。 (Hiveのマニュアルには、上記のようなクエリを書くなら下記のようなクエリを書きましょうねと書いてあります。)

SELECT t1.*
FROM raw_data LATERAL VIEW json_tuple(raw_data.line, 'name', 'exam1', 'exam2', 'exam3') t1 as nm, exam1, exam2, exam3;

json_tupleはstring型として返ってくるので、これを整数型にします。SELECT句でCastしているだけです。

SELECT t1.name, cast(t1.exam1 as int), cast(t1.exam2 as int), cast(t1.exam3 as int)
FROM raw_data LATERAL VIEW json_tuple(raw_data.line, 'name', 'exam1', 'exam2', 'exam3') t1 as name, exam1, exam2, exam3;

これをView化します。

DROP VIEW IF EXISTS exam_table;
CREATE VIEW IF NOT EXISTS exam_table (
  name,
  exam1,
  exam2,
  exam3
)
AS
SELECT t1.name, cast(t1.exam1 as int), cast(t1.exam2 as int), cast(t1.exam3 as int)
FROM raw_data LATERAL VIEW json_tuple(raw_data.line, 'name', 'exam1', 'exam2', 'exam3') t1 as name, exam1, exam2, exam3;

簡単な集計をしてみます。

SELECT
  count(*),
  avg(exam1),
  avg(exam2),
  avg(exam3)
FROM
  exam_table;

100     50.72   52.71   50.43

集計できました。めでたし、めでたし。
ちなみにHive0.5までは(私は0.6から使い始めたのでわからんですが・・)Where句を書くとクエリがコンパイルできないらしくて、そのときは、「set hive.optimize.ppd=false;」と設定するみたいです。
https://issues.apache.org/jira/browse/HIVE-1056

ちなみにJSON用のSerdeもあります。
http://code.google.com/p/hive-json-serde/
ぶっちゃけ、こっち使った方がスマートに書けますｗ

というわけで、hiveはfluentとの相性も良さそうですね。
（ただし今回は統計情報を見ていないので、上記のサンプルが効率良いのかは調べていません(=ω=)）