【初心者】CSVファイルをRedshiftにインポートするまでの軌跡

# 最初に
今まで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) 接続を使用したリモートホストに配置できます。（[参考](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_COPY.html)）

とあるように、一部のサービスに限られます。
一応EC2でもできるようでした（後から気づいた）が、マニュフェストファイルをJsonで作成してS3に置いて、それを読み込んでSSHで取得する、という比較的生々しい手法なようです。
SSHを用いた接続方法は下記リンクに説明が記載されていました。

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/copy-parameters-data-source-ssh.html

ここでSSHでインポートするという手段に気づかなかった私は、S3からデータをインポートする方法を探ることにしました。

## 2. COPYコマンドについて調べてみた
公式ドキュメントに、COPYコマンドの基本的な文法が記載されていました。
> COPY table_name [ column_list ] FROM data_source CREDENTIALS access_credentials [options] 

さて、credential とはなんぞや？
となった私に、AWS公式は下記のようなドキュメントを提示してくれました。
> copy table from 's3://<your-bucket-name>/load/key_prefix' 
credentials 'aws_iam_role=arn:aws:iam::<aws-account-id>:role/<role-name>'
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です。
![IAM画面.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2618997/ad012882-44ed-33b0-f7c1-5eedd548a1c1.png)
これでCredentialの問題が解決しました。

※ロールにS3へのアクセス権限が無い場合、うまくいかないものと思われます。
※本項は下記のリンクを参考にしました。

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/tutorial-loading-run-copy.html#tutorial-loading-load-part

## 3. COPYコマンドのエラー調査について
権限問題が解決しようやくかと思いましたが、エラーはいつまでも直らず「何故だぁあああああ！」と発狂しかけました。
が、ここでRedshiftがエラーメッセージを残してくれることに気づきました。
参考にしたのは2つのページです。

↓ COPYコマンドでエラーが発生したときの原因調査

https://aws.amazon.com/jp/premiumsupport/knowledge-center/redshift-stl-load-errors/

↓ エラー要因が格納されるSTL_LOAD_ERRORSの中身について

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_STL_LOAD_ERRORS.html

上記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コマンドに使用できる追加パラメータは下記にまとめられていました。

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/copy-parameters-data-conversion.html#copy-ignoreblanklines


## 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上の列の順番と一致させています）

列の順序指定については、下記サイトを参考にしました。

https://dev.classmethod.jp/articles/amazon-redshift-specify-column-names-when-copying/

これで、列の順番に関係なく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';
```

# 最後に
正直わからないところも多く、おそらく無駄な部分もあるかと思います。
おかしな点やもっと最適化できる点がありましたら、遠慮なくご教示いただけますと幸いです。