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


### はじめに

- <del>RedshiftはJSONのCOPYをサポートしていません。</del>　⇨　2014.03.27更新 [JSON対応した模様](http://aws.amazon.com/releasenotes/8431669082397065)

- なので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）
1. データソース（ホストマシン上）
1. ホストマシンにアクセスできるコンピュータ
1. 稼働しているRedshiftクラスタ

## 手順

1. Redshiftのマネージメントコンソールを開いて、
`Clusters > Configuration > SSH Ingestion Settings`の中にある`Cluster Public Key`と`Node IP Addresses`の`Public IP`を控えます。
![Redshift_Management_Console.png](https://qiita-image-store.s3.amazonaws.com/0/4700/6f4d0817-7216-6d51-1563-c93617b2a512.png "Redshift_Management_Console.png")
1. ホストマシンのEC2インスタンスに、1で調べた`Cluster Public Key`を`~/.ssh/authorized_keys`に追記する。
1. ホストマシンのEC2インスタンスのSecurityGroupにRedshiftクラスタのIP（1の`Public IP`）からのSSHの許可を加える。
1. ホストマシンのEC2インスタンスの公開鍵を控える
`/etc/ssh/ssh_host_rsa_key.pub`

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

```
{ 
   "entries": [ 
     {"endpoint":"＜EC2インスタンスのIP＞", 
       "command": "＜実行するコマンド＞",
       "mandatory":true, 
       "publickey": "＜４．で調べた鍵＞", 
       "username": "<SSHで入るときのユーザー名＞"},
       ・・以下繰り返しで、複数のホストを指定可能
    ] 
}
```
1. マニフェストファイルのアップロード
1. 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
```

https://github.com/jehiah/json2csv


```
$ 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
```

## その他

http://jeroenjanssens.com/2013/09/19/seven-command-line-tools-for-data-science.html