AWSのEMRを使ってHiveの基本的な使い方を確認しておく備忘録

Hiveとは?

Hiveとは？

Hive（ハイブ）とは、オープンソースの大規模分散計算フレームワーク Hadoop上で動作するデータウェアハウス（DWH）向けのプロダクトです。

とあります。
大規模なデータ処理をするときに使うMySQLみたいなやつ。...だと勝手に思ってます。

実際HiveのためのHiveQLという言語は、SQLによく似ています。

Hiveを起動するまで

AWSネジメントコンソールからEMRを作成。マスターノードのEC2インスタンスのipを調べて、sshでログイン。
ユーザー名はhadoopでログインします。
$ ssh -i path/to/your/ssh_key hadoop@[ip address]
ログインしてから$ hiveでHiveを使えるようになっています。

Hive

データベース一覧を表示

まずはデータベースを確認してみる。
SHOW DATABASES;

データベース作成

companyというデータベースを作成する。
CREATE DATABASE company;

作成されていることを確認します。
SHOW DATABASES;

今回、Hiveで扱いたいのは、EMPLOYEEという名前のファイルがjson形式でS3に保存してあるとしましょう。
保存場所はs3://mybucket/EMPLOYEE
中身は、以下です。

{"name":"tanaka","age":24,"section":"sales"}
{"name":"yamada","age":45,"section":"engineer"}
{"name":"yamamoto","age":34,"section":"engineer"}
{"name":"inoue","age":33,"section":"sales"}
{"name":"komori","age":22,"section":"engineer"}
{"name":"takahashi","age":40,"section":"admin"}

テーブルを作成

Hiveでデータの挿入は、1行ずつ入れるみたいなことができないらしい。
ファイルを全部読み込みバルクインサートっていう方法でしかインポートできないって書いてあった気がしました。
なので、予めファイルを作っておいて、それを読み込む形にしておきます。
と、データのインポートの前に、テーブルを定義しておきます。

データの流れとして、
1. カラムが一つのテーブルを作成し、1行1jsonとして格納します
2. jsonをパースして、3つのカラムに分けて新しい別のテーブルに格納します。

json格納用の一時テーブル

CREATE TABLE emp_line(
  line STRING
);

最終的に扱いたいテーブルをつくります

CREATE TABLE employee (
    name STRING,
    age INT,
    section STRING
);

テーブル一覧を表示

テーブルが作成されているか一覧で見てみましょう。
SHOW TABLES;

テーブルの構造を表示

カラム等の定義を確認することもできます。
DESC employee;

jsonファイルを事前に用意

s3://bucket-name/path/to/your/file/employee.json

{"name":"tanaka","age":24,"section":"sales"}
{"name":"yamada","age":45,"section":"engineer"}
{"name":"yamamoto","age":34,"section":"engineer"}
{"name":"inoue","age":33,"section":"sales"}
{"name":"komori","age"22:,"section":"engineer"}
{"name":"takahashi","age":40,"section":"admin"}

s3からデータをロード

データをロードして追加するなら以下
LOAD DATA INPATH 's3://mybucket/EMPLOYEE' INTO TABLE emp_line;

追加ではなく、上書きするなら、OVERWRITEをつけます。
LOAD DATA INPATH 's3://mybucket/EMPLOYEE' OVERWRITE INTO TABLE emp_line;

この状態だと、emp_lineテーブルに1カラム1jsonとして格納されている。

SELECT * FROM emp_line;をすると

OK
{"name":"tanaka","age":24,"section":"sales"}
{"name":"yamada","age":45,"section":"engineer"}
{"name":"yamamoto","age":34,"section":"engineer"}
{"name":"inoue","age":33,"section":"sales"}
{"name":"komori","age"22:,"section":"engineer"}
{"name":"takahashi","age":40,"section":"admin"}
Time taken: 1.4 seconds, Fetched: 6 row(s)

となる。

シンプルなjsonをパース

jsonをパースするには、次のようにします。

SELECT emp.* FROM emp_line 
    LATERAL VIEW json_tuple(
    emp_line.line,
    'name',
    'age',
    'section'
    ) emp AS name, age, section;

とすると、

OK
tanaka  24  sales
yamada  45  engineer
yamamoto    34  engineer
inoue   33  sales
komori  22  engineer
takahashi   40  admin
Time taken: 0.092 seconds, Fetched: 6 row(s)

となる。これを別のテーブルに格納すれば完成。
employeeテーブルに格納するには

INSERT INTO TABLE employee
SELECT emp.* FROM emp_line 
    LATERAL VIEW json_tuple(
    emp_line.line,
    'name',
    'age',
    'section'
    ) emp AS name, age, section;

と、SELECTの結果をINSERT INTO TABLE [table名]として新たに代入するだけです。

ネストしたjsonをパース

余談ですが、jsonがネストしているパターンを考えてみます。
ネストしているjsonを用意する

{"name":{"str":"tanaka"},"age":{"int":24},"section":{"str":"sales"}}
{"name":{"str":"yamada"},"age":{"int":30},"section":{"str":"engineer"}}
{"name":{"str":"katou"},"age":{"int":50},"section":{"str":"admin"}}
{"name":{"str":"yamamoto"},"age":{"int":44},"section":{"str":"sales"}}
{"name":{"str":"suzuki"},"age":{"int":29},"section":{"str":"engineer"}}
{"name":{"str":"hirose"},"age":{"int":48},"section":{"str":"sales"}}

シンプルなjsonと同じように記述する

SELECT data.* FROM emp_line
    LATERAL VIEW json_tuple(
        emp_line.line,
        'name',
        'age',
        'section',
    ) data AS name, age, section;

すると

OK
{"str":"tanaka"}  {"int":24}  {"str":"sales"}
.
.
.

となり、余計な文字が残ってしまう。

もう一つネストを外したい。

参考: https://qiita.com/unksato/items/42405305c28e5a788cd7

literal viewを連続で使う。

SELECT data.* FROM emp_line
    LATERAL VIEW json_tuple(
        emp_line.line,
        'name',
        'age',
        'section'
    ) json_data AS name, age, section
    lateral view json_tuple(
        json_data.name,
        'str'
    ) data as name
    lateral view json_tuple(
        json_data.age,
        'int'
    ) data as age
    lateral view json_tuple(
        json_data.section,
        'str'
    ) data as section;

これで正しく出力される。

勝手に意味を補ってみる。

SELECT data.* FROM emp_line[(後に作られる仮の)dataテーブルのすべてのカラムを選択][元になるのはemp_lineテーブル]
    LATERAL VIEW json_tuple(
        emp_line.line,[emp_lineテーブルのlineカラムをjsonパース]
        'name',[nameがキーのものを]
        'age',[ageがキーのものを]
        'section',[sectionがキーのものを]
    ) json_data AS name, age, section[仮のjson_dataテーブルにname,age,sectionというカラムで生成する。]
    lateral view json_tuple(
        json_data.name,[json_dateテーブルのnameカラムをjsonパース]
        'str'[sがキーのものを]
    ) data as name[仮のdataテーブルにnameというカラムで生成する。]
    [以下重複]
    lateral view json_tuple(
        json_data.age,
        'int'
    ) data as ttl
    lateral view json_tuple(
        json_data.section,
        'str'
    ) data as section;

ちなみにカラム名にdateを使うと、なんかだめっぽい。

整形先のテーブルを作成

create table emp_all(
    name string,
    age int,
    section string,
);

データのインサート

insert into table emp_all
SELECT data.* FROM emp_line
    LATERAL VIEW json_tuple(
        emp_line.line,
        'name',
        'age',
        'section',
    ) data AS name, age, section;

重複データを無視してSELECT

すべてのカラムが重複するデータを削除して表示する。

select distinct *  from emp_all;

これで、同一データはなくして軽くできる。

こんな感じで基本的な操作はMySQLで使えるものはほとんど使えるっぽい。

やったもん勝ち

主にプログラミングのこと。生産性向上の某とかも。