Hiveの使い方（ちょっと実践）

hortonworksの「Tutorial 3: How to Process Data with Apache Hive」を行ったメモ
- http://hortonworks.com/hadoop-tutorial/how-to-process-data-with-apache-hive/

pigとHiveの違い

pig
- すべてのデータオブジェクトは、スクリプト内で保持される
- スクリプトが完了すると、それらをストレージに保存していない限り、すべてのデータ·オブジェクトが削除される
Hive
- 作成したテーブルなどは消去されない
  - データが消えないので、１行ごとに結果を確認しながらクエリを記述していくことができる
  - pigではストレージに結果をコピーしない限り、スクリプト内にのみ結果がある状態

データの保存の挙動をみてみる

create table temp_batting (col_value STRING);のコマンドを実行
- 中身がない空のテーブル
Tableのビューに保存されている
Hiveは実行するとストレージに書き込まれる

データの準備(前のTutorial２と同じ)

元データをここからダウンロード
- http://seanlahman.com/files/database/lahman591-csv.zip
- 解凍して、 master.csv と batting.csvをuploadする

データの内容は下記の通り

野球のデータっぽい

      playerID,yearID,stint,teamID,lgID,G,G_batting,AB,R,H,2B,3B,HR,RBI,SB,CS,BB,SO,IBB,HBP,SH,SF,GIDP,G_old
      aardsda01,2004,1,SFN,NL,11,11,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,11
      aardsda01,2006,1,CHN,NL,45,43,2,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,45
      aardsda01,2007,1,CHA,AL,25,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,2
      aardsda01,2008,1,BOS,AL,47,5,1,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,5
      aardsda01,2009,1,SEA,AL,73,3,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,

データの読み込み

LOAD DATA INPATH '/user/hue/Batting.csv' OVERWRITE INTO TABLE temp_batting;

temp_battingテーブルに Batting.csvをinsertする

テーブルの作成

create table batting (player_id STRING, year INT, runs INT);

battingという名前のテーブルを作成する
- String型のplayer_id、int型のyear、int型のrunsをフィールドにもつように

SELECT/GROUP BY

SELECT year, max(runs) FROM batting GROUP BY year;

yearでまとめた時の、最大のrunsを出力する

以下、結果はこんな感じ

  0   NULL    NULL
  1   1871    66
  2   1872    94
  3   1873    125
  4   1874    91
  5   1875    115
  6   1876    126

JOIN

    SELECT a.year, a.player_id, a.runs from batting a
    JOIN (SELECT year, max(runs) runs FROM batting GROUP BY year ) b
    ON (a.year = b.year AND a.runs = b.runs)

battingのAテーブルと上記のようなyearごとの最大のrunのBテーブルをJOINする

結果はこんな感じ

  0   1871    barnero01   66
  1   1872    eggleda01   94
  2   1873    barnero01   125
  3   1874    mcveyca01   91
  4   1875    barnero01   115
  5   1876    barnero01   126

コマンドの流れを最初からまとめると

    LOAD DATA INPATH '/user/hue/Batting.csv' OVERWRITE INTO TABLE temp_batting;

    create table batting (player_id STRING, year INT, runs INT);

    insert overwrite table batting

    SELECT
      regexp_extract(col_value, '^(?:([^,]*)\,?){1}', 1) player_id,
      regexp_extract(col_value, '^(?:([^,]*)\,?){2}', 1) year,
      regexp_extract(col_value, '^(?:([^,]*)\,?){9}', 1) run
    from temp_batting;

    SELECT year, max(runs) FROM batting GROUP BY year;

    SELECT a.year, a.player_id, a.runs from batting a
    JOIN (SELECT year, max(runs) runs FROM batting GROUP BY year ) b
    ON (a.year = b.year AND a.runs = b.runs)