Redshiftのリモートロードを使ってJSONをCOPYする

はじめに

• RedshiftはJSONのCOPYをサポートしていません。　⇨　2014.03.27更新 JSON対応した模様

• なのでJSONのデータを入れるには一度CSVなどに変換してS3に置いてからRedshiftにCOPYしている方も多いのではないでしょうか。

• そこで、リモートロードという機能を使えばJSONで保存されているデータでもRedshiftにCOPYすることができるので紹介したいと思います。

本ドキュメントの目的

あるEC2のインスタンス上のJSON形式で保存されてるデータをCSVに変換してRedshiftにCOPYする

このドキュメントの手順通りに行います
http://docs.aws.amazon.com/redshift/latest/dg/loading-data-from-remote-hosts.html

事前準備

1. ホストマシン（EC2）
2. データソース（ホストマシン上）
3. ホストマシンにアクセスできるコンピュータ
4. 稼働しているRedshiftクラスタ

手順

1. Redshiftのマネージメントコンソールを開いて、
   Clusters > Configuration > SSH Ingestion Settingsの中にあるCluster Public KeyとNode IP AddressesのPublic IPを控えます。
   

2. ホストマシンのEC2インスタンスに、1で調べたCluster Public Keyを~/.ssh/authorized_keysに追記する。

3. ホストマシンのEC2インスタンスのSecurityGroupにRedshiftクラスタのIP（1のPublic IP）からのSSHの許可を加える。

4. ホストマシンのEC2インスタンスの公開鍵を控える
   /etc/ssh/ssh_host_rsa_key.pub

5. マニフェストファイルの作成

{ 
   "entries": [ 
     {"endpoint":"＜EC2インスタンスのIP＞", 
       "command": "＜実行するコマンド＞",
       "mandatory":true, 
       "publickey": "＜４．で調べた鍵＞", 
       "username": "<SSHで入るときのユーザー名＞"},
       ・・以下繰り返しで、複数のホストを指定可能
    ] 
}

1. マニフェストファイルのアップロード
2. COPYコマンドの実行

• COPYコマンドのfromをマニフェストファイルを指定し、オプションにsshを付けるだけです。

copy sales
from 's3://mybucket/ssh_manifest' credentials 
'aws_access_key_id=<access-key-id>;aws_secret_access_key=<secret-access-key>'
ssh;

実際にやってみます

$ git clone https://github.com/kwmt/goinstall
$ cd goinstall/
$ sudo ./goinstall.sh ec2-user
$ export GOROOT=/usr/local/go
$ export GOPATH=$HOME/_go
$ export PATH=$PATH:$GOROOT/bin:$GOPATH/bin

$ go get github.com/jehiah/json2csv

$ cat /tmp/test.csv
2014-03-25,1,web
2014-03-26,1,iphone
2014-03-26,2,web

$ cat /tmp/test.json
{"date":"2014-03-25","uid":1,"device":"pc"}
{"date":"2014-03-26","uid":2,"device":"pc","hoge":"moge"}
{"date":"2014-03-26","uid":3,"device":"iphone","hoge":"moge","hage":"fuga"}
{"date":"2014-03-26","uid":100}
{"uid":200,"device":"pc","date":"2014-03-27"}
{"date":"2014-03-28","device":"iphone","uid":300}

$ cat /tmp/test.json|/home/ec2-user/_go/bin/json2csv -k date,uid,device -p
date,uid,device
2014-03-25,1,pc
2014-03-26,2,pc
2014-03-26,3,iphone
2014-03-26,100,""
2014-03-27,200,pc
2014-03-28,300,iphone

create table dau_temp (
 date DATE not null sortkey encode delta,
 uid bigint encode delta,
 device varchar(max) encode lzo
);

COPY dau_temp from 's3://mybucket/manifest-20140325i.json'
CREDENTIALS ''
csv
TIMEFORMAT AS 'auto'
COMPUPDATE OFF
ssh
;

COPY dau_temp from 's3://mybucket/manifest-20140326a.json'
CREDENTIALS ''
csv
TIMEFORMAT AS 'auto'
COMPUPDATE OFF
ssh
NOLOAD
;
INFO:  Load into table 'dau_temp' completed, 0 record(s) loaded successfully.
COPY
Time: 922.599 ms

# COPY dau_temp from 's3://mybucket/manifest-20140325i.json'
CREDENTIALS ''
csv
TIMEFORMAT AS 'auto'
COMPUPDATE OFF
ssh
;
INFO:  Load into table 'dau_temp' completed, 6 record(s) loaded successfully.
COPY
Time: 716.052 ms

# select * from dau_temp;
    date    | uid | device
------------+-----+--------
 2014-03-25 |   1 | pc
 2014-03-26 |   3 | iphone
 2014-03-27 | 200 | pc
 2014-03-25 |   1 | pc
 2014-03-26 |   3 | iphone
 2014-03-27 | 200 | pc
 2014-03-26 |   2 | pc
 2014-03-26 | 100 |
 2014-03-28 | 300 | iphone
 2014-03-26 |   2 | pc
 2014-03-26 | 100 |
 2014-03-28 | 300 | iphone
(12 rows)

Time: 121.124 ms

速度比較

• json

Time: 668.326 ms
Time: 690.624 ms
Time: 630.213 ms
Time: 543.657 ms
Time: 791.359 ms
Time: 668.934 ms
Time: 618.174 ms
Time: 829.587 ms
Time: 559.560 ms
Time: 572.987 ms
Time: 561.357 ms

• csv(gzip)

Time: 786.218 ms
Time: 683.168 ms
Time: 501.840 ms
Time: 520.358 ms
Time: 517.781 ms
Time: 524.366 ms
Time: 594.286 ms
Time: 512.236 ms
Time: 530.166 ms
Time: 500.772 ms

"command": "cat /tmp/dau-20140325.csv"
Time: 1157.993 ms
Time: 1006.971 ms
Time: 1006.659 ms
Time: 1036.481 ms
Time: 1197.493 ms
Time: 1029.445 ms
Time: 977.649 ms
Time: 1069.595 ms
Time: 927.599 ms
Time: 967.063 sm


"command": "cat /tmp/dau-20140325.json|/home/ec2-user/_go/bin/json2csv -k "date,uid,device",
Time: 2359.493 ms
Time: 2420.866 ms
Time: 2133.271 ms
Time: 2314.561 ms
Time: 2241.016 ms
Time: 2083.953 ms
Time: 2220.802 ms
Time: 2228.559 ms
Time: 2147.016 ms
Time: 2173.630 ms

real    0m2.034s
user    0m0.604s
sys     0m0.288s

real    0m2.102s
user    0m0.636s
sys     0m0.332s

real    0m2.102s
user    0m0.572s
sys     0m0.392s

その他