DynamoからRedshiftに大量のDataをCOPYした時にハマった話

## はじめに

DynamoDBにある約150GBのDataをRedshiftにImportしようとしてハマったので、解決方法をチラ裏しておきます。  

以下の順番で試したのですが、結局#3が正解でした。#2の方法でも2日掛かりでImport自体はできましたが、かなり力技な感は否めません。

1. `copy redshift-table-name from 'dynamodb://dynamo-table-name'`と実行
1. DynamoからS3に一旦Exportし、そのFileをRedshiftにImportする
1. Redshift Clusterを32 nodeで立ち上げ、1と同様の処理を実行、Import完了後にnode数を減らす


## ハマリのポイント

今回ハマッた理由は、大量のDataをDynamo→RedshiftにImportする場合に、大量のnodeのRedshift clusterを用意しておかないと、Copyに時間がかかり、timeoutでerrorになりますよ、という事です。

最初、1 nodeのRedshift Clusterにcopy処理を実行すると、40時間近く掛かったあげく最後にCopyがerrorで失敗となりました。逆に32 node等でRedshift Clusterを立ち上げCopy処理をすると、(Dynamo Readで見る限り)20-40倍近くのThroughputでCopyがされ、1時間弱で完了します。Copy完了後、Clusterのnode数を適宜減らせば終了です。

## 1. `copy redshift-table-name from 'dynamodb://dynamo-table-name'`と実行

DynamoのDataをRedshiftにImportをしたい！となると、まずは[AmazonのDocumentにある様に](http://docs.aws.amazon.com/redshift/latest/dg/r_COPY_command_examples.html#r_COPY_command_examples-load-favoritemovies-from-an-amazon-dynamodb-table)、Redshift Clusterを立ち上げて、Redshift commandで`copy xx from 'dynamodb://yy'`とやるのが王道だと思います(DynamoのData量が少ない場合は、これで十分に動きました)。

なので、以下の手順をとりました。

1.`dynamo_test_table`というDynamo TableのRead Capacity Unit(RCU)を10,000に設定
2.1 nodeの`ds2.xlarge` instanceを立ち上げる
3.RedshiftにPostgres commandで入って、Redshiftのtableを作っておく

```
CREATE TABLE redshift-table-name(
  end_user VARCHAR(36) encode raw,
  created_at VARCHAR(36) encode text255,
  lang VARCHAR(10) encode raw,
)
  distkey(end_user)
  compound sortkey(created_at);
```

4.Postgres commandでCopyコマンドを実行

```
copy redshift-table-name from 'dynamodb://dynamo_test_table' credentials 'aws_access_key_id=AKISELIJGISEILIJSGS2;aws_secret_access_key=abcdefghijklmn1234567890ABC' MAXERROR AS 100000 readratio 100
```

注：API/Secret Keyは適当です

すると、Dynamo Readは250 RCUぐらいしかThroughputが出ず、その状態が30時間近く続きます。その後、10時間ぐらいDynamo Readが無い状態が続き、最終的にCopyがTerminatedで失敗しました(泣)。sshのConnectionが切れないようにしても、ダメでした。

## 2. DynamoからS3に一旦Exportし、そのFileをRedshiftにImportする

次に試したのが、Dynamoの150GBのDataを一旦S3にExportし、そのFileをRedshiftにImportする、という手順です。これ自体は上手く行ったのですが、DynamoからのExportでハマリ所が1箇所あるのと、Python script等でFormat変換をゴリゴリ書く必要が有ります。

手順は以下の様になります。

1. DynamoのS3 export機能を使ってS3 bucketにfileとして書き出す
1. S3に書き出されたfileをEC2にCopyする
1. S3に書き出されたfileは(csvやjsonではない)特殊なformatなので、csv等にformat変換する
1. `ds2.xlarge` instanceを1 nodeで立ち上げる
1. csvに変換されたfileを、gzip圧縮して、S3に再度uploadする
1. S3にuploadされたgzip fileをプチプチRedshift clusterにCopyしていく


### DynamoのS3 export機能を使ってS3 bucketにfileとして書き出す

Amazon標準の機能なので、操作に迷う事は無いと思いますが、[この記事にあるような罠](http://ijin.github.io/blog/2015/07/02/dynamodb-export-with-datapipeline/)が存在するため、S3 exportの為のEMRの設定を色々Tuningする必要が有ります。150GBのDynamo Dataを14時間かかって何とかExport完了、150GBのDataは200MB * 900個ぐらいのfileに分割され、S3に保存されます。


### S3に書き出されたfileをEC2にCopyする

S3に書き出されたDynamo dataは、[特殊なformatなので、このままRedshiftへImportは出来ません!](http://docs.aws.amazon.com/datapipeline/latest/DeveloperGuide/dp-importexport-ddb-pipelinejson-verifydata2.html)。

Dynamo 1 recordが、`created_at.{"n":"123456789012345"}.end_user.{"s":"12345-67890-112345-67890"}`みたいなformatで書き出されます。この仕様を知った時は割と泣きそうになりましたが、ともあれFormat変換が必要なので、一旦EC2にfileを全てCopyします。

### fileをcsv等にformat変換する

この特殊なFormatが何者なのかは不明ですが、[この記事から](http://stackoverflow.com/questions/18896329/export-data-from-dynamodb)、以下のshell commandでjsonに変換できます。

以下、`1234-5678-abcd-efghij0123456-000000`と言う特殊formatなfileを`000000.json`というjson fileに変換してくれます。

```
sed -e 's/$/}/' -e $'s/\x02/,"/g' -e $'s/\x03/":/g' -e 's/^/{"/' 1234-5678-abcd-efghij0123456-000000 > 000.json
```

が、綺麗なkey-valueのjsonに成らず、`{"created_at":{"n":"12345678901234"},"end_user":{"s":"12345-67890-112345-67890"}}`みたいな変なjsonになります。このままではRedshiftに読み込ませられないので、Python等でゴリゴリcsv等に書き出します。


### csvに変換されたfileを、gzip圧縮して、S3に再度uploadする

csv変換されたfileは、このままRedshiftに読み込ませる事が可能ですが、gzip圧縮してS3に置いた方が、読み込みが早いので、gzip圧縮してS3に再度uploadします。


#### S3にuploadされたgzip fileをプチプチRedshift clusterにCopyしていく

ついに、RedshiftへのImportです。[この記事にあるように](http://docs.aws.amazon.com/redshift/latest/dg/r_COPY_command_examples.html#r_COPY_command_examples-load-listing-from-an-amazon-s3-bucket)、以下のPostgres Copy commandを実行します。

```
copy redshift-table-name from 's3://Bucket-name/folder/000.gz' credentials 'aws_access_key_id=AKISELIJGISEILIJSGS2;aws_secret_access_key=abcdefghijklmn1234567890ABC' MAXERROR AS 100000
```

なお、`s3://Bucket-name/folder/`以下に、`000.gz`, `001.gz`, `002.gz`...とある場合に、`s3://Bucket-name/folder/00`として指定してあげると`000.gz`から`009.gz`をまとめてCopyしてくれます。

## 3. Redshift Clusterを32 nodeで立ち上げ、1と同様の処理を実行、Import完了後にnode数を減らす

こちらがたぶん正解です。#2において、32 nodeでRedshift Clusterを立ち上げる以外は、やる事は1と同様です。

1. `dynamo_test_table`というDynamo TableのRead Capacity Unit(RCU)を10,000に設定
2. 32 nodeの`dc1.large` instanceを立ち上げる
3. RedshiftにPostgres commandで入って、Redshiftのtableを作っておく
4.Postgres commandでCopyコマンドを実行

Copyを実行すると、Dynamo的には8000 RCUぐらいのThroughputが出て、50分ぐらいでCopyが完了しました。Copy完了後、Redshift Clusterのresizeで、node数を減らせばOKです。resize自体にも30分-1時間ぐらいかかるので注意です。



## おまけ：Redshift操作をshellから行う
Redshiftの処理を、shellから連続して行いたい、という場合には、以下の手順に従えばOKです。

1. [Postgres commandのPassword入力をBypassする](http://qiita.com/ozash/items/48b7701724f189c48814)
1. 実行したいpostgresコマンドを`-c`以下で渡す(ただし最後の`;`は削除する)。例えば以下でTable一覧を表示します。

```
psql -h redshift-test.c12345abcdefg.ap-northeast-1.redshift.amazonaws.com -U redshift_test -d redshiftest -p 5439 -c "SELECT DISTINCT tablename  FROM pg_table_def WHERE schemaname = 'public'"
```

`-U`はPassword bypassを設定されているUsername, `-h`はRedshiftのURL, `-d`はDatabase Nameです。
postgresコマンド内で、`'(コンマ)`を使う場合には、`-c`を`"`で囲んであげる必要があります。


nohupと組み合わせれば、Copyコマンドをshellのbackgroundで実行可能です。

```
nohup psql -h redshift-test.c12345abcdefg.ap-northeast-1.redshift.amazonaws.com -U redshift_test -d redshiftest -p 5439 -c "copy redshift-table-name from 's3://Bucket-name/folder/000.gz' credentials 'aws_access_key_id=AKISELIJGISEILIJSGS2;aws_secret_access_key=abcdefghijklmn1234567890ABC' MAXERROR AS 100000" > copylog.log &
```