Aurora のMySQL で SELECT文の実行結果をS3のファイルに保存する



## 背景
Aurora にあるデータをRedshiftに入れるために テキストファイルに書き出す方法を探していたら、S3に書き出せることを知りました。これは Redshift連携が捗る。

## 必要なリソース

簡単な構文であれば、 `select * from table_name INTO OUTFILE S3 's3://bucket_name/path/to/output-file.csv';` でS3に書き出せるのだけど、そのためには AWS リソースを整えておく必要があります。

### S3バケット

RDSから書き出すファイルを置くバケットを作成します。今回は `my-bucket` バケットを利用します。

### IAMロールとS3の権限

Autoraに割り当てる IAMロールを作成して、S3への権限を付与します。

`SELECT INTO S3`で目的のバケットに書き込みを行えるIAMロールを下の内容で作成しました。

```json:アクセス権限
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:AbortMultipartUpload",
                "s3:ListBucket",
                "s3:DeleteObject",
                "s3:ListMultipartUploadParts"
            ],
            "Resource": [
                "arn:aws:s3:::my-bucket/*",
                "arn:aws:s3:::my-bucket"
            ]
        }
    ]
}
```

```json:信頼関係
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": "rds.amazonaws.com"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}
```

公式ドキュメント: [Amazon S3 リソースにアクセスするための IAM ポリシーの作成](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/AuroraMySQL.Integrating.Authorizing.IAM.S3CreatePolicy.html)


### DB クラスターのパラメータグループとIAMロールの割り付け

DBクラスターのパラメータ `aurora_select_into_s3_role` に
S3に書き込みを行えるIAMロールのARNを指定します。また、作成したIAMロールを AuroraのDBクラスターに割り付けます。

公式ドキュメント: [IAM ロールと Amazon Aurora MySQL DB クラスターの関連付け](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/AuroraMySQL.Integrating.Authorizing.IAM.AddRoleToDBCluster.html)

### S3へのネットワークの経路

[Amazon Aurora MySQL から AWS の他のサービスへのネットワーク通信の有効化]
(https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/AuroraMySQL.Integrating.Authorizing.Network.html) にある通りです。

AuroraがS3へネットワーク的に接続できる経路を準備します。Auroraをprivateなネットワークに配置した場合は、NAT ゲートウェイか VPCエンドポイントを利用して、

Auroraが privateなネットワークにある際には下のエラーがでました。

```
ERROR 1871 (HY000): S3 API returned error: Network Connection:Unable to connect to endpoint
```

### AuroraのDBユーザの権限

制限された権限のDBユーザであれば`SELECT INTO S3` 権限を付与します。

```sql
GRANT SELECT INTO S3 ON *.* TO 'user'@'domain-or-ip-address'
```

公式ドキュメント: [Amazon Aurora MySQL DB クラスターから Amazon S3 バケット内のテキストファイルへのデータの保存](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/AuroraMySQL.Integrating.SaveIntoS3.html#AuroraMySQL.Integrating.SaveIntoS3.Grant)の *Aurora MySQL にデータを保存する権限の付与*


## 書き出すファイルの区切り文字や囲み文字

MySQLと同様に `TERMINATED BY`や `ENCLOSED BY` で指定します。


```
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:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
```

## マニフェストを書き出して、AuoraのデータをS3経由でRedshiftに投入する

### AuroraからS3

マニフェストの書き出しを有効にして `SELECT INTO S3`を実行します。

```sql
SELECT * FROM table_name 
INTO OUTFILE S3 's3://my-bucket/aurora2redshift/data.tsv' 
MANIFEST ON
```

SQL実行後にS3を見ると 下の3ファイルができていました。

- data.tsv.manifest
- data.tsv.part_00000
- data.tsv.part_00001

`data.tsv.manifest` には `LOAD DATA FROM S3 MANIFEST` で指定できる マニフェスト ファイルが出力されています。

```json:data.tsv.manifest
{
   "entries" : [
      {
         "url" : "s3://my-bucket/aurora2redshift/data.tsv.part_00000"
      },
      {
         "url" : "s3://my-bucket/aurora2redshift/data.tsv.part_00001"
      }
   ]
}
```

公式ドキュメント: [Amazon S3 バケットのテキストファイルから Amazon Aurora MySQL DB クラスターへのデータのロード](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/AuroraMySQL.Integrating.LoadFromS3.html#AuroraMySQL.Integrating.LoadFromS3.Manifest)

### Redshiftにロード

Redshiftの `copy`コマンドで データのロードを行います。Auroraで作成したマニフェストを指定し、区切り記号にタグを指定します。

NULL値は、 [copyコマンドのNULL ASオプションのデフォルトが \N なので](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/copy-parameters-data-conversion.html#copy-null-as) Auroraが出力した `\N` をそのまま使います。

日時は [Redshiftのcopyコマンドのデフォルトと同じの `YYYY-MM-DD HH:MI:SS`](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/copy-parameters-data-conversion.html#copy-timeformat) と同じ書式がなので、デフォルト値を使います。

```
copy table_name
from 's3://my-bucket/aurora2redshift/data.tsv.manifest' 
iam_role 'arn:aws:iam::123456789012:role/my-iamrole-Redshift'
manifest
delimiter '\t'
;
```

Redshiftの `copy`コマンドが終了すると処理行数が表示されます。

```
INFO:  Load into table 'table_name' completed, 99340147 record(s) loaded successfully.
COPY
```

Auroraのテーブルと同じ件数なので、無事に全件のロードが行われました。

## 参考

- [Aurora v1.13 から使える SELECT INTO OUTFILE S3 を試した](https://kakakakakku.hatenablog.com/entry/2017/10/02/230828)
- [Amazon Aurora MySQL DB クラスターから Amazon S3 バケット内のテキストファイルへのデータの保存](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/AuroraMySQL.Integrating.SaveIntoS3.html)