AWS EMRを使ったhiveのチュートリアル

やりたいこと

以下のような3つの項目を持ったjsonファイルがあります。

id, cookie, date

基本的にはidはcookieと一対一の関係にありますが、たまにあるcookieに対して同じidが振られていることがあります。つまり、cookieは完全にユニークですが、idには重複があります。困りました。

この重複をなくして、idとcookieともに完全にユニークなデータセットにしたいと思います。 idが同じオブジェクトあったときは、dateを参照して新しい方のcookieを残して古い方のcookieは無視します。同じ日に一つのidに対して異なるcookieが生成されることはないものとします。

データはS3から読み込んでS3に保存します。入力 s3://hive-00001111-ap-northeast-1/Cookie_origin/の下に、複数ファイルに別れたtsvファイルがあります。出力 s3://hive-00001111-ap-northeast-1/Cookie_unique/以下に保存したいです。

データセット生成

https://www.json-generator.com/ このサイトで以下のような条件で100レコード分のjsonファイルを生成して、indentをcompactでダウンロードします。

[
  '{{repeat(100, 100)}}',
  {
    _id: '{{objectId()}}',
    cookie: '{{guid()}}',
    date: '{{date(new Date(1000, 0, 1), new Date(), "YYYYMMdd")}}'
  }
]

generated.jsonという名前でダウンロードされました。

若干テキストエディタで整形します。まずは、先頭と最後の[]を削除します。次に },{を

}
{

で置換します。これで、いい感じの元データができました。試しに先頭の5行だけ抜き出してみます。

{"_id":"5a02dcbb94b8be7f1a0fe41e","cookie":"c8be7285-608c-4158-86c6-0c8e03a5a8d3","date":20160720}
{"_id":"5a02dcbbec03abdc3585ba76","cookie":"b744804e-0423-4283-995a-843ce861f305","date":20130423}
{"_id":"5a02dcbb47fe699b5d4f1b4f","cookie":"1fbec6e8-b6a7-41f7-99a2-708e04f86ab8","date":20170912}
{"_id":"5a02dcbb5b3d47ca9c229706","cookie":"31216342-1d61-4f2b-8315-755b163ee225","date":20120325}
{"_id":"5a02dcbbfeb00508901f7261","cookie":"0415a1e9-19c8-4a77-a4c6-7aa85fff733a","date":20140928}

こんな感じです。このファイルから5件だけidのvalueをコピーして他の行のidのvalueにしておきます。

s3の構成

s3のファイル構成は以下のようにします。

S3
└── hive-00001111-ap-northeast-1
    ├── Cookie_origin
    │   └── generated.json
    ├── Cookie_unique
    └── src
        └── uniquify_id.q

generated.jsonはダウンロードしたファイルです。 uniquify_id.qは後ほど説明します。

HiveQL

HiveはHiveQLというSQLによく似たクエリを使っています。そんなにSQLに詳しくないですが、SQLでできる基本的なことはほとんどできるっぽいです。

uniquify_id.q

-- --------------------------------------------------
-- 使用するテーブルを作成
-- --------------------------------------------------

CREATE EXTERNAL TABLE　IF NOT EXISTS　id_origin(line STRING)　ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' LOCATION 's3://hive-00001111-ap-northeast-1/Cookie_origin';

CREATE EXTERNAL TABLE IF NOT EXISTS id_unique ( id STRING, cookie STRING, get_date STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' LOCATION 's3://hive-00001111-ap-northeast-1/Cookie_unique';


-- --------------------------------------------------
-- Jsonをパースする
-- --------------------------------------------------

INSERT INTO TABLE id_unique SELECT json_data.* FROM id_origin LATERAL VIEW json_tuple(id_origin.line, '_id','cookie', 'date') json_data AS id, cookie, get_date;


-- --------------------------------------------------
-- idとcookieを一対一にする。
-- --------------------------------------------------

INSERT OVERWRITE TABLE id_unique SELECT A.id, A.cookie, A.get_date FROM id_unique AS A INNER JOIN  (SELECT id,MAX(get_date) AS NEWEST FROM id_unique GROUP BY id) AS B ON A.id = B.id AND A.get_date = B.NEWEST;

細かいHiveQLの書き方は以下のサイトをとても参考にさせていただきました。 http://www.ne.jp/asahi/hishidama/home/tech/apache/hive/ql.html