最初に
今までMySQL workbench を使っていて「RedshiftのテーブルにCSVからインポートするだけ？ 簡単でしょ」と思っていましたが、初心者には低いハードルではありませんでした。。。
エラーとの闘いと完遂までの軌跡を、自分用のメモも兼ねて引っかかった部分をまとめます。
前提
- TeratermからEC2にSSH接続
- EC2からRedshiftにログイン
- コピー先のテーブルは "CREATE TABLE" であらかじめ作成済
1. EC2から直接COPYするのが難しかった
最初に試したのは、EC2にCSVファイルを置いて、COPYコマンドで指定する方法でした。ですがパスを指定しても一向に動かない……
ドキュメントを確認したところ、Redshiftにインポートするには
データファイルまたは Amazon DynamoDB テーブルから、テーブルにデータをロードします。ファイルは Amazon Simple Storage Service (Amazon S3) バケット、Amazon EMR クラスターまたは Secure Shell (SSH) 接続を使用したリモートホストに配置できます。（参考）
とあるように、一部のサービスに限られます。
一応EC2でもできるようでした（後から気づいた）が、マニュフェストファイルをJsonで作成してS3に置いて、それを読み込んでSSHで取得する、という比較的生々しい手法なようです。
SSHを用いた接続方法は下記リンクに説明が記載されていました。
ここでSSHでインポートするという手段に気づかなかった私は、S3からデータをインポートする方法を探ることにしました。
2. COPYコマンドについて調べてみた
公式ドキュメントに、COPYコマンドの基本的な文法が記載されていました。
COPY table_name [ column_list ] FROM data_source CREDENTIALS access_credentials [options]
さて、credential とはなんぞや？
となった私に、AWS公式は下記のようなドキュメントを提示してくれました。
copy table from 's3:///load/key_prefix'
credentials 'aws_iam_role=arn:aws:iam:::role/'
options;
ー
各 COPY コマンドで、以下の作業を行います。
① <your-bucket-name> を、お使いのクラスターと同じリージョンにあるバケットの名前に置き換えます。
このステップでは、バケットとクラスターが同じリージョンにあることを前提としています。代わりに、COPY コマンドで REGION オプションを使用してリージョンを指定できます。
② <aws-account-id> および <role-name> は、使用している AWS アカウント ならびに IAM ロールに置き換えます。一重引用符で囲まれた認証情報文字列に、スペースまたは改行を含めることはできません。
…………はい。
"aws-account-id" と "rolw-name" って何？
それはどこを参照すればいいの？
と初心者の私はなったわけです。
色々試したり調べたのですが、結論だけ書くと「Redshiftに割り当てられているロール」を参照すればよいです。RedshiftからS3にアクセスするのですから当然といえば当然ですが、まっさらな状態からたどり着くのは骨でした。
ちなみにRedshiftがどのIAMロールを使っているかは、「Redshift -> (対象のRedshiftクラスター) -> アクション -> IAMロールの管理」 の場所に見つかりました（直接IAMページに遷移できなかったので、IAMページからで名前検索して確認しました）。
下画像の赤い部分が"aws-account-id" と "rolw-name"でした。この部分をCOPYコマンドの "<aws-account-id>:role/<role-name>" と置き換えればOKです。
これでCredentialの問題が解決しました。
※ロールにS3へのアクセス権限が無い場合、うまくいかないものと思われます。
※本項は下記のリンクを参考にしました。
3. COPYコマンドのエラー調査について
権限問題が解決しようやくかと思いましたが、エラーはいつまでも直らず「何故だぁあああああ！」と発狂しかけました。
が、ここでRedshiftがエラーメッセージを残してくれることに気づきました。
参考にしたのは2つのページです。
↓ COPYコマンドでエラーが発生したときの原因調査
↓ エラー要因が格納されるSTL_LOAD_ERRORSの中身について
上記2つより得た、エラーメッセージ取得方法をまとめますと、Redshift上で
// 全部取得する場合
select * from stl_load_errors limit 1;
// 特定のカラムだけ取得する場合
select s.err_reason from stl_load_errors as s limit 1;
のように打ち込めば、最新のエラーログが取得できました。これは便利ですね。
今後はこのログを見ながら進めています。
4. CSVファイルの先頭行を無視しない
ここで1つのエラーに悩まされました。
Invalid Date Format - length must be 10 or more
Dateの形式が違うのか？
という仮定で調べてみて、DATEFORMAT 'YYYY-MM-DD' というDateのフォーマットを指定するコマンドを挿入してみましたが、効果なし。
copy rs_input from 's3://\<S3-path>/input.csv' credentials 'aws_iam_role=arn:aws:iam::\<account-id>:role/\<role-name>' FORMAT CSV DATEFORMAT 'YYYY-MM-DD';
先ほどのエラーログをよくよく見てみると、エラーが発生した行を出力してくれるパラメータがありました。
select s.raw_line from stl_load_errors as s limit 1;
# -> url,title,metainfo,updated_at,expire_in
先頭行が読み込まれとるやないかい！！
ということで、IGNOREHEADER as 1 とパラメータを追加して解決です。
なお、COPYコマンドに使用できる追加パラメータは下記にまとめられていました。
5. データ型が一致しない！ (OOOO value did not match format specified...)
さて、修正を重ねてきましたが、まだまだうまくいきません。
Invalid digit, Value 'U', Pos 0, Type: Integer
どうやら型エラーのようですが、COPYコマンドにちゃんとCSVと書いてあるのになぜ動かない……
原因は、RedshiftのようなDBにはカラムに順番が無いということでした。
CSVの先頭行にカラム名が入っていたので、いい感じにやってくれているだろう……と思ったのですが、ちゃんと指定しないとダメみたいです。
というわけで修正して
copy rs_input (url,title,metainfo,updated_at,expire_in) from 's3://\<S3-path>/input.csv' credentials 'aws_iam_role=arn:aws:iam::\<account-id>:role/\<role-name>' IGNOREHEADER as 1 FORMAT CSV DATEFORMAT 'YYYY-MM-DD';
というように、FROM句の前に列名とその順番を規定しました。（この列名群はCSV上の列の順番と一致させています）
列の順序指定については、下記サイトを参考にしました。
これで、列の順番に関係なくCSVを結合することができます。これで問題は解決しました！
6. 空白行があると怒られる
補足的になってしまいますが、デフォルトでは空白行が存在するとエラーを吐きます。
私の場合CSVファイルの最後の行に改行が入っており、その行を空白行として認識してしまってエラーが発生していました。
わざわざファイルを置きなおすのも面倒なので、IGNOREBLANKLINES のパラメータを追記して問題を回避しました。
完成したコマンド
というわけで、最終的に下記のようなコマンドでS3からRedshiftにデータをインポートすることができました。
copy rs_input (url,title,metainfo,updated_at,expire_in) from 's3://\<S3-path>/input.csv' credentials 'aws_iam_role=arn:aws:iam::\<account-id>:role/\<role-name>' IGNOREHEADER as 1 IGNOREBLANKLINES FORMAT CSV DATEFORMAT 'YYYY-MM-DD';
最後に
正直わからないところも多く、おそらく無駄な部分もあるかと思います。
おかしな点やもっと最適化できる点がありましたら、遠慮なくご教示いただけますと幸いです。