HiveでJSON形式のデータを扱うようにして変更に強くする話

Hiveでは常識の部類に入る話なのかもしれませんが、僕が最近やったことなのでメモっておきます。

たとば以下のCSVファイルがあったとします。なお今回の例はタムタムの日記 - HiveでJSON形式のログを構造化するからいただきました。m( )m

name,exam1,exam2,exam3
Irma,58,73,85
Doroty,50,39,11
Echo,95,47,96
Ina,50,68,38
Kay,15,11,46

この場合に単純に以下のようなHiveのテーブルを用意してデータをロードするやり方だとカラム追加がおきたときに対応しなければなりません。

CREATE TABLE sample(
  name STRING,
  exam1 INT,
  exam2 INT,
  exam3 INT
)
PARTITIONED BY (yyyymmdd STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','

そうではなくてCSVファイルから下記のようなJSON形式に変換します。

{"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}

CSVからJSONへは例えば以下のようなpythonプログラムで変換できます。

with open(json_file_path, "w") as o:
    with open(input_file_path) as f:
        reader = csv.DictReader(f)
        for data in reader:
            json.dump(data, o)
            o.write("\n")

そして以下のようなテーブルにJSONデータをロードします。

CREATE TABLE sample_json(
  json STRING
)
PARTITIONED BY (yyyymmdd STRING)

それで以下のようなHiveのビューを作ります。もしカラムが増えた場合でもこのビューは有効です。追加されたカラムに対して集計したい場合はビューを作り直せばOKです。

CREATE VIEW exam_view (
  name,
  exam1,
  exam2,
  exam3,
  yyyymmdd
)
PARTITIONED ON (yyyymmdd)
AS
SELECT
  t1.name, cast(t1.exam1 as int), cast(t1.exam2 as int), cast(t1.exam3 as int), yyyymmdd
FROM
  sample_json
LATERAL VIEW json_tuple(sample_json.json, 'name', 'exam1', 'exam2', 'exam3') t1 as name, exam1, exam2, exam3