Amazon Aurora MySQLとS3間でデータをロード&アンロードする
Amazon Aurora MySQL は、データを
- S3 にアンロード(
SELECT INTO OUTFILE S3
) - S3 からロード(
LOAD DATA FROM S3
)
できます。
実際にやってみます。
他のDBエンジンについては、以下の記事を参照ください。
セットアップ
構成イメージ
手順概要
- RDS が S3 にアクセスするための IAM ロールを作成
- DB クラスターパラメーターグループのパラメーター(aws_default_s3_role)に IAM ロールの ARN を指定
- Aurora クラスターを起動。IAM ロールをアタッチ
- mysql クライアントから
SELECT INTO OUTFILE S3
/LOAD DATA FROM S3
を実行
RDS 向け IAM ロールの作成
Aurora インスタンスに IAM ロールをアタッチし、 S3 を操作できるようにします。 そのためのロールを作成します。
assume するのは RDS のため、Trusted entities は RDS です。
IAM ポリシーに関して
- ロード時は S3 への参照権限
- アンロード時は更新権限
が必要です。具体的には、下記表の通りです。
Feature | Bucket Permissions | Object Permissions |
---|---|---|
LOAD DATA FROM S3 | ListBucket | GetObject GetObjectVersion |
SELECT INTO OUTFILE S3 | ListBucket | AbortMultipartUpload DeleteObject GetObject ListMultipartUploadParts PutObject |
ミニマリスティックに攻めずに、まるっとマネージドポリシーを利用する場合、
- ロードには AmazonS3ReadOnlyAccess
- アンロードには AmazonS3FullAccess
を使えます。
ロード・アンロードで別のロールを割り当てることも可能ですが、今回は、AmazonS3FullAccess を許可した 1ロール(rds-s3-role)で済ませました。
DBクラスターパラメーターグループの作成
クラスターパラメーターグループのカスタマイズが発生するため、新規クラスターパラメーターグループを作成します。
RDS<->S3連携では以下の値を利用します。
Name | Description |
---|---|
aurora_load_from_s3_role | IAM role ARN used to load data from AWS S3 |
aurora_select_into_s3_role | IAM role ARN used to select data into AWS S3 |
aws_default_s3_role | Default IAM role ARN used to access AWS S3 |
ロード(aurora_load_from_s3_role)・アンロード(aurora_select_into_s3_role)で異なる IAM ロールを指定可能で、フォールバックとして aws_default_s3_role が利用されます。
ロード・アンロードで異なる IAM ロールを利用する場合、下記表のようになります。
Name | Value |
---|---|
aurora_load_from_s3_role | arn:aws:iam::123:role/rds-s3-load-role |
aurora_select_into_s3_role | arn:aws:iam::123:role/rds-s3-unload-role |
aws_default_s3_role |
同じ IAM ロールを利用し、下記表のようになります。
Name | Value |
---|---|
aurora_load_from_s3_role | |
aurora_select_into_s3_role | |
aws_default_s3_role | arn:aws:iam::123:role/rds-s3-unload-rds-s3-role |
Aurora MySQL クラスターの起動
先程作成したDBクラスターパラメーターグループを利用して Aurora MySQL インスタンスを起動します。
クラスター起動後、クラスターの "Manage IAM roles" から、先程作成した IAM ロール(rds-s3-role)を追加します。
Aurora に接続確認
Aurora クラスターに MySQL クライアントから接続します
$ mysql -h HOST -u USERNAME -p DBNAME
DB クラスターパラメーターグループの確認
S3 系パラメーターが期待通りであることを確認します。
MySQL [dbname]> show variables like '%s3_role%'; +----------------------------+--------------------------------------------+ | Variable_name | Value | +----------------------------+--------------------------------------------+ | aurora_load_from_s3_role | | | aurora_select_into_s3_role | | | aws_default_s3_role | arn:aws:iam::123456789012:role/rds-s3-role | +----------------------------+--------------------------------------------+ 3 rows in set (0.00 sec)
ユーザーの権限の確認
クラスター作成時に作成されたユーザーは
- ロード(
LOAD FROM S3
) - アンロード(
SELECT INTO S3
)
権限が許可されています。
MySQL [dbname]> SHOW GRANTS FOR 'username'@'%' \G *************************** 1. row *************************** Grants for username@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, LOAD FROM S3, SELECT INTO S3, INVOKE LAMBDA ON *.* TO 'username'@'%' IDENTIFIED BY PASSWORD '***' WITH GRANT OPTION 1 row in set (0.00 sec)
別途ユーザーを作成した場合、権限があるか確認し、なければ GRANT してください。
MySQL> GRANT SELECT INTO S3 ON *.* TO 'user'@'domain-or-ip-address'
テスト用データの作成
テーブルの作成
MySQL [dbname]> create table bar(a text, b int); Query OK, 0 rows affected (0.05 sec)
データの投入
改行を含めたデータも投入してみます。
MySQL [dbname]> insert into bar values('a', 1); Query OK, 1 row affected (0.03 sec) MySQL [dbname]> insert into bar values('a,b', 1); Query OK, 1 row affected (0.01 sec) MySQL [dbname]> insert into bar values('a,b\nc', 1); Query OK, 1 row affected (0.02 sec) MySQL [dbname]> select * from bar; +-------+------+ | a | b | +-------+------+ | a | 1 | | a,b | 1 | | a,b c | 1 | +-------+------+ 3 rows in set (0.00 sec)
Aurora のデータを S3 に保存
SYNTAX
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'] ]
実行例
bar テーブルを s3-eu-central-1://BUCKETNAME/20181103/bar 以下に出力します。
MySQL [dbname]> SELECT * FROM bar INTO OUTFILE S3 's3-eu-central-1://BUCKETNAME/20181103/bar' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' MANIFEST ON OVERWRITE OFF; Query OK, 3 rows affected (0.11 sec)
S3 のデータを確認します。
$ aws s3 sync s3://BUCKET/ . download: s3://BUCKET/20181103/bar.part_00000 to 20181103/bar.part_00000 download: s3://BUCKET/20181103/bar.manifest to 20181103/bar.manifest $ cat 20181103/bar.manifest { "entries" : [ { "url" : "s3-eu-central-1://BUCKET/20181103/bar.part_00000" } ] } $ cat 20181103/bar.part_00000 a,1 a\,b,1 a\,b\ c,1 $ od -tx1z 20181103/bar.part_00000 0000000 61 2c 31 0a 61 5c 2c 62 2c 31 0a 61 5c 2c 62 5c >a,1.a\,b,1.a\,b\< 0000020 0a 63 2c 31 0a >.c,1.< 0000025
注意
オンプレ MySQL の SELECT INTO OUTFILE
に相当する操作ができます。
RDS-S3 連携固有の重要なオプションには以下があります。
- アンロードする S3 の URI を指定(s3-region://bucket-name)
- ロード時に利用可能なマニフェストファイルの出力オプション(MANIFEST)
- 出力先の上書きオプション(OVERWRITE)。OFF なのにファイルが存在する場合はエラーが発生
また、他にも以下のような注意事項があります
- テーブルスキーマなどのメタ情報は出力されない
- デフォルトでは1ファイルの最大サイズは6GB
- SELECT 対象データが 25 GB を超える場合は、複数回に分けて出力する
- 並列で走らせるときは、 s3-uri がかぶらないようにする。かぶった場合の挙動は未定義です
詳細は以下のドキュメントを参照ください。
- https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.SaveIntoS3.html
- https://dev.mysql.com/doc/refman/5.7/en/select.html
S3 のデータを Aurora にロード
テーブルのデータを一旦削除
データロード前にテーブルを truncate します。
MySQL [dbname]> truncate table bar; Query OK, 0 rows affected (0.02 sec) MySQL [dbname]> select * from bar; Empty set (0.01 sec)
SYNTAX
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,...]
実行例
マニフェストファイル s3-eu-central-1://BUCKETNAME/20181103/bar に関連するデータを、bar テーブルにロードします。
MySQL [dbname]> LOAD DATA FROM S3 MANIFEST 's3-eu-central-1://BUCKET/20181103/bar.manifest' INTO TABLE bar FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (a, b); Query OK, 3 rows affected (0.16 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 MySQL [dbname]> select * from bar; +-------+------+ | a | b | +-------+------+ | a | 1 | | a,b | 1 | | a,b c | 1 | +-------+------+ 3 rows in set (0.00 sec)
無事投入できています。
注意
オンプレ MySQL の LOAD DATA INFILE
に相当する操作ができます。
RDS-S3 連携固有の重要なオプションには以下があります。
- ロードする S3 の URI を指定(
S3 [FILE | PREFIX | MANIFEST] 'S3-URI'
)
また、他にも以下のような注意事項があります
- S3 のデータ形式は XML にも対応しています。(
LOAD XML INFILE ...
)
詳細は以下のドキュメントを参照ください。
- https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.LoadFromS3.html
- https://dev.mysql.com/doc/refman/5.7/en/load-data.html
データ検証
LOAD DATA FROM S3
に成功すると、aurora_s3_load_history
テーブルに取り込み結果が書き込まれます。
LOAD 時に指定した S3 URI をキーにロード結果を確認できます。
MySQL [dbname]> select * from mysql.aurora_s3_load_history where load_prefix = 's3-eu-central-1://BUCKET/20181103/bar.manifest' \G *************************** 1. row *************************** load_prefix: s3-eu-central-1://BUCKET/20181103/bar.manifest file_name: 20181103/bar.manifest version_number: bytes_loaded: 129 load_timestamp: 2018-11-03 11:21:08 *************************** 2. row *************************** load_prefix: s3-eu-central-1://BUCKET/20181103/bar.manifest file_name: s3-eu-central-1://BUCKET/20181103/bar.part_00000 version_number: bytes_loaded: 21 load_timestamp: 2018-11-03 11:21:08 2 rows in set (0.00 sec)
トラブルシュート
DBクラスターパラメーターグループ を変更していない
MySQL [dbname]> SELECT * FROM foo INTO OUTFILE S3 's3-eu-central-1://BUCKET/20181103/foo' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' MANIFEST ON; ERROR 1871 (HY000): S3 API returned error: Both aurora_select_into_s3_role and aws_default_s3_role are not specified, please see documentation for more details
パラメーター
- aurora_select_into_s3_role
- aurora_load_from_s3_role
- aws_default_s3_role
を適切に設定してください。
DB インスタンスの S3 アクセスポリシー不足
MySQL [dbname]> SELECT * FROM bar INTO OUTFILE S3 's3-eu-central-1://BUCKET/20181103/ba2r' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' MANIFEST ON; ERROR 1871 (HY000): S3 API returned error: Missing Credentials: Cannot instantiate S3 Client
DB インスタンスに IAM ロールがアタッチされていない、あるいは、ポリシーの権限不足が考えられます。
最後に
Aurora MySQL <-> S3 間のデータ連携方法を紹介しました。
- Aurora MySQL のデータを S3 に出力して他システムで利用
- 他システムが生成したデータを Aurora MySQL に取り込む
といったケースで役に立ちそうです。
Aurora から S3 への出力では、出力フォーマットのコントロールが限定的なことから、S3 への出力後に再整形するケースも多いと思われます。
なお
- RDS MySQL
- RDS PostgreSQL/Aurora PostgreSQL
は今回紹介したような S3 連携に対応していません。
それでは。
関連情報
- AWS Documentation » Amazon Relational Database Service (RDS) » User Guide for Aurora » Working with Amazon Aurora MySQL » Integrating Amazon Aurora MySQL with Other AWS Services » Saving Data from an Amazon Aurora MySQL DB Cluster into Text Files in an Amazon S3 Bucket
- AWS Documentation » Amazon Relational Database Service (RDS) » User Guide for Aurora » Working with Amazon Aurora MySQL » Integrating Amazon Aurora MySQL with Other AWS Services » Loading Data into an Amazon Aurora MySQL DB Cluster from Text Files in an Amazon S3 Bucket