【RDS&MySQL】CSVファイルをRDSのテーブルに取り込む方法(scp/load data/utf-8)

# 概要
* ローカルにあるCSVファイルをRDSのテーブルへ取り込む方法を備忘も兼ねてまとめました。取り込むにあたって発生したエラー2件のことも記載しています。

## 前提
* 踏み台サーバー（EC2インスタンス)へSSH接続してからRDSへアクセスする手法を取る。
* RDSはmysql8を利用しています。EC2やRDSのもろもろ設定は完了している前提。

## Mac環境
* 機種ID：MacBookPro18,3
* macOS：Monterey
* チップ：Apple M1 Pro
* メモリ：16GB

# 実行手順
1. ローカルにあるCSVを踏み台サーバーへコピーする
2. SSHトンネルで踏み台サーバーからRDSにアクセスする
3. RDSでデータベース・テーブルを作成する
4. CSVをRDSのテーブルへ取り込む
5. 【エラー対応①】文字コード設定（utf-8関係）のエラー
6. 【エラー対応②】800カラムあるCSVを取り込んだ際のエラー

## ローカルにあるCSVを踏み台サーバーへコピーする
* scpコマンドを使って、踏み台サーバーにCSVファイルをコピーします。以下の場合は直下に秘密鍵もCSVもある状態で実行していますが、異なる場合はディレクトリを指定してあげてください。
```terminal
scp -i sample_ssh.pem sample_data.csv ec2-user@11.222.3.444:
```

## SSHトンネルで踏み台サーバーからRDSにアクセスする

* 以下のコマンドでSSH接続します。
```terminal
ssh -i sample_ssh.pem ec2-user@11.222.3.444
```

* 以下のコマンドでRDSインスタンスへアクセスします。
    * エンドポイントはRDSの画面から確認できますのでコピーしてきましょう。
    * 以下はport3306、ユーザー名がrootの場合です。RDS作成時に設定したものを入力しましょう。

```terminal
mysql -h xxxx-xxxx-mysql8.xxxxxxxxxx.ap-northeast-1.rds.amazonaws.com -P 3306 -u root -p
```
* Passwordの入力画面になるので、RDS作成時に取得しているパスワードを入力します。
* `Welcome to the MySQL monitor.`と頭に表示され、`mysql>`というプロンプト画面になったらアクセス完了です。

## RDSでデータベース・テーブルを作成する
* 以下はターミナルから実行していますが、DBeaverなどのSQLクライアントソフトウェアアプリケーションを使って作成するのもOKです。（もちろんその際は上記のアクセス設定を事前に行う必要があります）
* まずは以下コマンドでデータベースを作成します。
```sql
create database sample_db;
```

* 以下コマンドで作成しているデータベースを表示できます。
```sql
show databases;
+----------------------+
| Database             |
+----------------------+
| sample_data_20230101 |
| information_schema   |
| mysql                |
| performance_schema   |
| sample_db            |
| sys                  |
+----------------------+
6 rows in set (0.05 sec)
```

* 以下コマンドで作成した`sample_db`を選択します。
```sql
use sample_db;
```

* 選択していない場合は以下のエラーが表示されます。
```terminal
ERROR 1046 (3D000): No database selected
```

* 以下コマンドでテーブルを作成します。この時、テーブル定義書を参考にデータ型やバイト（桁数）、NOT NULL値などもあらかじめ決めておくと良いですね。（後でALTERコマンドで変えることも可能です）

```sql
create table samplelist (
sample_no INT NOT NULL,
sample_cd CHAR(2),
sample_nm INT
);
```

* 最後の以下のように`)`の前に`,`を入れると、ERRORになるので要注意。よくやってしまってしまう汗

```sql
create table samplelist (
sample_no INT NOT NULL,
sample_cd CHAR(2),
sample_nm INT, #ここです
);
```
↓こんなエラーが出ます
```terminal
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 5
```

* ちなみに、`int(x)`と指定してあげても、MySQL 8.0.19 からが表示されない仕様になったようです。DBeaverなどのツールからGUI的に明示的に変えてあげることは出来ましたが。

* 作成したテーブルの確認は以下コマンドで可能。
```sql
show tables;
+---------------------+
| Tables_in_sample_db |
+---------------------+
| list                |
| samplelist          |
+---------------------+
2 rows in set (0.00 sec)
```

* 作成したテーブルのカラムの確認は以下コマンド。
```sql
show columns from samplelist;
+-----------------------+---------+------+-----+---------+-------+
| Field                 | Type    | Null | Key | Default | Extra |
+-----------------------+---------+------+-----+---------+-------+
| sample_no             | int     | NO   |     | NULL    |       |
| sample_cd             | CHAR(2) | YES  |     | NULL    |       |
| sample_nm             | int     | YES  |     | NULL    |       |
```

* 作成したテーブルのデータ（レコード）確認は以下コマンド。
    * まだ何も入っていない場合はemptyと出ます。
```sql
select * from samplelist;
Empty set (0.00 sec)
```

* 作成したテーブルのデータ（レコード）を選定して確認したい場合は以下コマンド。
    * データを取り込む手順（CSVを取り込む方法）は次のステップで解説します
```sql
select sample_no from samplelist;
+------------+
| sample_no  |
+------------+
| 1232133151 |
| 1233456778 |
| 1234567891 |
| 1023455678 |
+------------+
4 rows in set (0.00 sec)
```

* レコード（データ）を消す時は以下コマンド。ちゃんと消していいか確認してから実行しましょう。
```sql
DELETE FROM samplelist;
```

* テーブルごと消す時は以下コマンド。ちゃんと消していいか確認してから実行しましょう。
```sql
DROP TABLE samplelist;
```


## CSVをRDSのテーブルへ取り込む
* 以下のLOAD DATA 文でCSVファイルを作成したテーブル`samplelist`に取り込むことができます。下記の場合は直下にあるCSVですが、ディレクトリに保存している場合はそちらを指定してあげましょう。
```sql
load data local infile "sample_data.csv " into table samplelist fields
terminated by ',' optionally enclosed by '"';
```

* これにより、CSVデータがテーブルに取り込みができました。中身を確認する場合は上述したコマンドで確認してみましょう。
* LOAD DATA構文の詳細は以下から：
    * 参考：[13.2.6 LOAD DATA INFILE 構文](https://dev.mysql.com/doc/refman/5.6/ja/load-data.html)
* ちなみに、かなり高速にテキストファイルを読み込めますね。30万レコードあっても数秒で終わりました。
* CSV取り込み時にデータ型のエラーなどが起きた場合は、`warning`の数が表示されています。その時は直後に以下コマンドを実行して警告内容を確認しましょう。（直後に打たないと消えてしまうので要注意）

```sql
show warnings;
```

## 【エラー対応①】文字コード設定（utf-8関係）のエラー
* CSVファイルを取り込んだときに表示された第1のエラー。

```terminal
mysql ERROR 1300 (HY000): Invalid utf8mb4 character string: ''
```

* 文字コード設定が問題かな？と思って以下を一応確認。

```sql
mysql> show variables like 'character\_set\_%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_client     | utf8mb3 |
| character_set_connection | utf8mb3 |
| character_set_database   | utf8mb4 |
| character_set_filesystem | binary  |
| character_set_results    | utf8mb3 |
| character_set_server     | utf8mb4 |
| character_set_system     | utf8mb3 |
+--------------------------+---------+
7 rows in set (0.03 sec)
```

* ただ、この辺いじる前に以下記事を発見。確かに以前からCSVファイルの文字化けは悩みの種。同じような対応を以前別件でしたなぁ〜と思って念の為以下の手順で実行したら解決。
    * CSVファイルを「開く」からNumbersで読み込む→ファイルメニューの「書き出し」でCSVを選択→「utf-8」を選択してエクスポートしたものを、再度読み込むと、無事にOKが表示。助かった。
    * 参考：[MySQL関連_備忘録](https://tacosvilledge.hatenablog.com/entry/2020/09/16/065528)

## 【エラー対応②】800カラムあるCSVを取り込んだ際のエラー
* 800カラム近くあるCSVを取り込んだ時、以下のエラーメッセージが返ってきました。

```terminal
SQLエラー [1118] [42000]: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
````

* `ROW_FORMAT=DYNAMIC`や`ROW_FORMAT=COMPRESSED`をつけてSQL実行する解決したという記事もあったのですが、自分の場合は同じエラーでした。`my.cnf`の`innodb_file_format`設定を変える必要があるという記事もありましたが、RDSの場合はパラメータグループからいじってあげる必要がありそう。mysql8で作成したRDSでしたが、パラメータグループに`innodb_file_format`が存在していない...うーむ、これは解決できず。そのまま打ち込むのではなく、必要なカラムだけ選定して取り込むことにしました。
    * どなたか対応方法をご存知でしたら教えてくださいm(__)m