ファイルを利用したAurora MySQLのデータ入出力

#検証環境

* Aurora MySQL
    * バージョン：Aurora 2.07.2（MySQL 5.7）
    * キャパシティタイプ：プロビジョニング済み（サーバーレスでないAuroraという意味）
    * 日本語データも扱えるようにを[Aurora Serverless MySQL(5.6) で日本語データを扱えるようにする](https://qiita.com/tokicat/items/beda4be4f6815e198c76)を参考に文字コード関連のパラメーターの設定値を `utf8mb4` にカスタマイズした
    * Aurora MySQLへの接続は暗号化必須としている
  
* Aurora MySQLに接続するクライアント
    * OS：Cloud9で提供されるAmazon Linux2のEC2インスタンス
    * MySQLクライアント：Amazon Linux2に標準インストールされているMariaDBライブラリのMySQLクライアント


**Cloud9について**

ブラウザで利用できる統合開発環境を提供するAWSサービス。
コードの実行やデータの保管にはEC2インスタンスを利用する。
EC2インスタンスに対するターミナルもそなえている。
EC2インスタンスへの接続手段はSSH経由とSystemsManagerのセッションマネージャー経由とが選択できる。
Cloud9の詳細は[こちら](https://aws.amazon.com/jp/cloud9/)

**システム構成図**

よりセキュアな環境で検証を実施したかったのでCloud9もプライベートサブネットに配置することにした。
Cloud9をプライベートサブネットに配置する場合はEC2インスタンスへの接続手段はSystemsManagerしか選択できないということでSystemsManagerを選択している。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/271327/17dce22f-bbdd-d6d3-0700-287f21cfe730.png)


#テスト用に用意したDB、テーブル

* リソース名

|  DB名  |  テーブル名  |
| ---- | ---- |
|  test  |  tb1_Customer  |

* テーブル構成

|  列名  |  データ型  |　NOT NULL | PRIMARY KEY |
| ---- | ---- | ---- | ---- |
| UserID | Char(4) | Y | Y |
|  FirstName  |  Varchar(16)  | Y |  |
|  LastName  |  Varchar(16)  | Y |  |
|  Sex  |  Char(1)  |  |  |
|  Age  |  Int  |  |  |

* テーブルのCREATE文

```sql
create table tb1_Customer
  (
   UserID char(4) not null,
   FirstName varchar(16) not null,
   LastName varchar(16) not null,
   Sex char(1),
   Age int,
   primary key (UserID)
  );
```

#ファイルを使ったデータ入力
**検証した方式**

|  方式  |  インプットファイルの形式  |　インプットファイルの配置先 | 結果 |
| ---- | ---- | ---- | ---- |
| LOAD DATA LOCAL INFILE文 | テキストファイル | Cloud9のEC2インスタンス | OK |
|  SQLファイルを利用  |  INSERT文を記述したSQLファイル  | Cloud9のEC2インスタンス | OK |
|  LOAD DATA FROM S3文  |  テキストファイル  | S3 | OK |

##1. LOAD DATA LOCAL INFILE文を使ったデータ入力
###1-1. ファイルを用意
以下のCSVファイルをUTF-8で作成。

```csv
U001,花子,山田,f,20
U002,太郎,山田,m,30
U003,太郎,渋谷,m,25
```

###1-2. Cloud9のEC2インスタンスにファイルをアップロード
1. Cloud9のIDEで「File」 > 「Upload Local Files」をクリック


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/271327/df8c231f-25c1-dc9a-f2b4-c2f4161a92ca.png)

2. ポップアップの「Select Files」をクリック
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/271327/60af39f8-e523-5645-7d0b-1061af345ad6.png)

3. ポップアップにてアップロードしたいローカルファイルを選択後、「開く」をクリック
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/271327/bf5a8a74-8127-b7b3-6d1e-03d7e8d72845.png)

4. Cloud9のファイル一覧にアップロードしたファイルが追加される
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/271327/12437b25-59da-c0f4-caf7-c724db48d18b.png)

ちなみにアップロードしたファイルが配置されるEC2のディレクトリは、Cloud9のターミナルのデフォルトのカレントディレクトリの `/home/ec2-user/environment` となる

###1-3. LOAD DATA LOCAL INFILE文を実行

```
MySQL [test]> LOAD DATA LOCAL INFILE '/home/ec2-user/environment/test-data1.csv' INTO TABLE tb1_Customer FIELDS TERMINATED BY ',';            
Query OK, 3 rows affected, 3 warnings (0.02 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 3
```


レコードを参照してみるときちんと入っている。

```
MySQL [test]> SELECT * FROM tb1_Customer;
+--------+-----------+----------+------+------+
| UserID | FirstName | LastName | Sex  | Age  |
+--------+-----------+----------+------+------+
| U001   | 花子      | 山田     | f    |   20 |
| U002   | 太郎      | 山田     | m    |   30 |
| U003   | 太郎      | 渋谷     | m    |   25 |
+--------+-----------+----------+------+------+
3 rows in set (0.01 sec)
```

##2. SQLファイルを使ったデータ入力
###2-1. ファイルを用意
以下のSQLファイルをUTF-8で作成。

```sql
INSERT INTO tb1_Customer (UserID,FirstName,LastName,Sex,Age) VALUES ('U004','愛子','佐藤','f',40);
INSERT INTO tb1_Customer (UserID,FirstName,LastName,Sex,Age) VALUES ('U005','一平','高橋','m',20);
INSERT INTO tb1_Customer (UserID,FirstName,LastName,Sex,Age) VALUES ('U006','一郎','鈴木','m',40);
```

###2-2. Cloud9のEC2インスタンスにファイルをアップロード
手順1-2と同じなので詳細は割愛。

###2-3. データを登録
`\. 「対象ファイル」` でSQLファイルの内容を実行。

```
MySQL [test]> \. test-data2.sql
Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)
```

レコードを参照してみるときちんと入っている。

```
MySQL [test]> SELECT * FROM tb1_Customer WHERE UserID IN ('U004', 'U005', 'U006');
+--------+-----------+----------+------+------+
| UserID | FirstName | LastName | Sex  | Age  |
+--------+-----------+----------+------+------+
| U004   | 愛子      | 佐藤     | f    |   40 |
| U005   | 一平      | 高橋     | m    |   20 |
| U006   | 一郎      | 鈴木     | m    |   40 |
+--------+-----------+----------+------+------+
3 rows in set (0.00 sec)
```

##3. LOAD DATA FROM S3文を使ったデータ入力
LOAD DATA FROM S3文は、Aurora MySQLでサポートされるSQL。Aurora Serverlessでは利用不可なので注意。

###3-1. S3へのアクセスをAuroraに許可する
以下の作業が必要になる。

* CSVファイル配置用のS3作成
* AuroraがS3にアクセスするためのサービスロール作成
* サービスロールをAuroraに追加
* Auroraのパラメーター `aws_default_s3_role` の設定値をサービスロールのARNに変更
* AuroraからS3へのアウトバウンド接続を許可するように設定

詳細は[Amazon S3 バケットのテキストファイルから Amazon Aurora MySQL DB クラスターへのデータのロード](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.LoadFromS3.html)を参照。

###3-2. ファイルを用意
以下のSQLファイルをUTF-8で作成。

```csv
U007,美奈子,田中,f,20
U008,太郎,平岡,m,25
U009,航平,木村,m,25
```

###3-3. ファイルをS3へアップロード
1. S3のコンソールから対象のバケットを選択し、「アップロード」をクリック
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/271327/276f1e72-b512-84ac-b079-f4aa867ce96c.png)

2. 「ファイルを追加」をクリックし、ポップアップからアップロードするローカルファイルを選択して「開く」をクリック。最後に「アップロード」をクリック
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/271327/fb607f78-a900-7c55-7046-baef4c2dda28.png)

3. S3バケットにファイルが追加されたことを確認
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/271327/7aedbce4-1d5d-963b-badb-0425d0fa873c.png)

###3-4. LOAD DATA FROM S3文を実行

LOAD DATA FROM S3文のシンタクスは以下のようになっている。

```
LOAD DATA FROM S3 [FILE | PREFIX | MANIFEST] 'S3-URI'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name,...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]
```

詳細は[こちら](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.LoadFromS3.html)を参照。

実際に実行したLOAD DATA FROM S3文は以下。

```
MySQL [test]>  LOAD DATA FROM S3 's3://bucket-name/test-data3.csv' INTO TABLE tb1_Customer COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n';                                                                                                             
Query OK, 3 rows affected, 3 warnings (0.12 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 3
```

レコードを参照してみるときちんと入っている。

```
MySQL [test]> SELECT * FROM tb1_Customer WHERE UserID IN ('U007', 'U008', 'U009');
+--------+-----------+----------+------+------+
| UserID | FirstName | LastName | Sex  | Age  |
+--------+-----------+----------+------+------+
| U007   | 美奈子    | 田中     | f    |   20 |
| U008   | 太郎      | 平岡     | m    |   25 |
| U009   | 航平      | 木村     | m    |   25 |
+--------+-----------+----------+------+------+
3 rows in set (0.00 sec)
```

#ファイルへのデータ出力

|  方式  |  インプットファイルの形式  |　インプットファイルの配置先 | 結果 |
| ---- | ---- | ---- | ---- |
| クライアントコマンドを利用 | テキストファイル | Cloud9のEC2インスタンス | OK |
|  SELECT INTO OUTFILE S3文  |  テキストファイル  | S3 | OK |

##1. LOAD DATA LOCAL INFILE文を使ったデータ出力

###1-1. クライアントコマンドを利用してデータをファイルに出力
Auroraに未接続の状態で以下のMySQLコマンドを実行

```
$ mysql -h <Aurora MySQLのホスト名> -P <Aurora MySQLのポート番号> -u <DB接続ユーザー名> --ssl-ca=<証明書ファイルの絶対パス> -p test -e "select * from tb1_Customer;" > output1.csv
```

**MySQLコマンドの内容**

* `-e "sql" > <ファイル名>` で実行したSQLの結果を指定したファイル名のファイルに出力できる。ファイルは事前に作成しておく必要はない
* -pオプションでSQLを実行したいテーブルのDBを指定
* --ssl-caオプションはAurora接続を暗号化する場合のみ必要

###1-2. ファイルにデータが出力されたことを確認
Cloud9のEC2インスタンスにファイルが追加され、レコードが格納されている。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/271327/70042741-7273-f379-0600-cfbaa0cb62d1.png)


##2. SELECT INTO OUTFILE S3文を使ったデータ出力

SELECT INTO OUTFILE S3文もLOAD DATA FROM S3文と同様に、Aurora MySQLでサポートされるSQLで、Aurora Serverlessでは利用不可。

###2-1. S3へのアクセスをAuroraに許可する
LOAD DATA FROM S3文を使ったデータ入力の際と手順は同じ。

###2-2. SELECT INTO OUTFILE S3文を実行

SELECT INTO OUTFILE S3文のシンタクスは以下。

```
SELECT
    [ALL | DISTINCT | DISTINCTROW ]
        [HIGH_PRIORITY]
        [STRAIGHT_JOIN]
        [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
        [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
        [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
        [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
         [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
INTO OUTFILE S3 's3_uri'
[CHARACTER SET charset_name]
    [export_options]
    [MANIFEST {ON | OFF}]
    [OVERWRITE {ON | OFF}]

export_options:
    [FORMAT {CSV|TEXT} [HEADER]]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
]
```

詳細は[こちら](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.SaveIntoS3.html)を参照。

実際には以下のSQLを実行。

```
MySQL [test]> SELECT * FROM tb1_Customer INTO OUTFILE S3 's3://bucket-name/outfile1' FORMAT CSV COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n';
Query OK, 9 rows affected (0.17 sec)
```


###2-3. S3に出力されたデータを確認

S3のコンソールから対象のバケットのオブジェクトを確認すると、ファイル `outfile1.part_00000` が追加されている。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/271327/99b6b49a-31d6-0f80-c2ad-56d99c471428.png)

ファイルをクリックし、「ダウンロード」をクリック
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/271327/bc07556f-c83c-9b41-dde3-61ebb4912de7.png)

ダウンロードしたファイルを開くと以下の内容となっている。

```
"U001","花子","山田","f",20,
"U002","太郎","山田","m",30,
"U003","太郎","渋谷","m",25,
"U004","愛子","佐藤","f",40,
"U005","一平","高橋","m",20,
"U006","一郎","鈴木","m",40,
"U007","美奈子","田中","f",20,
"U008","太郎","平岡","m",25,
"U009","航平","木村","m",25,
```


#参考
https://qiita.com/IysKG213/items/994e9f4ad12ff8aee322

[PHP & JavaScript Room データのインポート・エクスポート ファイル読込](https://phpjavascriptroom.com/?t=mysql&p=mysqlimport)
[PHP & JavaScript Room データのインポート・エクスポート ファイル出力](https://phpjavascriptroom.com/?t=mysql&p=file)