Amazon Redshift に COPY コマンドでCSVをインポートする

cloudpackエバンジェリストの吉田真吾@yoshidashingo)です。

Redshiftに移行する際に既存のデータを投入したいことがあると思います。今日はそのやり方をメモ書き。

0. 前提

  • Redshiftのクラスタは作成済みである
  • EC2からRedshiftに接続できるように Cluster Security Group が設定済みである
  • EC2にawscli導入済みである

1. EC2からS3にデータをアップロードする

1.1 (準備)EC2にクレデンシャルを設定する
$ aws configure
AWS Access Key ID [None]: <アクセスキー>
AWS Secret Access Key [None]: <シークレットキー>
Default region name [None]: us-east-1
Default output format [None]:
  • デフォルトリージョンがus-east-1の場合↑
1.2 (準備)S3にバケットを作る
$ aws s3 mb s3://test-bucket
make_bucket: s3://test-bucket/
$ aws s3 ls
2014-04-24 00:30:00 test-bucket
  • バケット名がtest-bucketの場合↑
1.3 S3にCSVファイルをアップロードする
$ aws s3 cp test.csv s3://test-bucket/ &
upload: ./test.csv to s3://test-bucket/test.csv
[1]-  終了        aws s3 cp test.csv s3://test-bucket/
  • CSVファイル名:test.csv の場合↑

2. EC2からpsqlで接続する

2.1 (準備)psqlをインストールする
$ sudo yum install postgresql
2.2 psqlで接続する
$ psql -h my-dw-instance.xxxx.us-east-1.redshift.amazonaws.com -U awsuser -d mydb -p 5439
ユーザ awsuser のパスワード: <パスワード>
mydb #
  • RedshiftクラスタのエンドポイントURL:my-dw-instance.xxxx.us-east-1.redshift.amazonaws.com の場合↑
  • ユーザー名:awsuser の場合↑
  • データベース名:mydb の場合↑
  • ポート番号:5439 の場合↑

3. CSVをインポートする

3.1 (準備)テーブルを作成する
mydb # create table test(
mydb(#  id integer,
mydb(#  test_date timestamp,
mydb(#  memo VARCHAR(64));
CREATE TABLE

テーブルを作成するときに各カラムの圧縮方式を指定することもできるが、後ほどCSVファイルをCOPYコマンドでインポートするときに、エンコードなしでテーブルが0件だと、ファイルを10万行読んで自動圧縮をしてくれるようなので今回はそちらに任せる。非常に便利な機能ですね。
自動圧縮ありでテーブルをロードする - Amazon Redshift

3.2 CSVファイルをアップロードする
mydb=# COPY test FROM 's3://test-bucket/test.csv' CSV credentials 'aws_access_key_id=<アクセスキー>;aws_secret_access_key=<シークレットキー>'
INFO:  Load into table 'test' completed, 100000000 record(s) loaded successfully.
COPY

※コマンド一行だとこんな感じ↓ただしパスワードは指定できないのでバックグラウンド実行できない。

$ psql -h my-dw-instance.xxxx.us-east-1.redshift.amazonaws.com -U awsuser -d mydb -p 5439 -c "COPY test FROM 's3://test-bucket/test.csv' CSV credentials 'aws_access_key_id=<アクセスキー>;aws_secret_access_key=<シークレットキー>';"
ユーザ awsuser のパスワード:
INFO:  Load into table 'test' completed, 100000000 record(s) loaded successfully.
COPY

最後に

  • 今回はdw2.largeのシングルノードで試しました。上記で例に使ったテーブルよりもっとレコードレングスの長いテーブルを3つほど作って1億行ずつインポートしてみましたが、いずれも10分程度で完了しました。非常に楽ですね。
  • コマンド一行でバックグラウンド実行できないのは少し面倒。なんかいいやり方ないかな。