Amazon Aurora MySQLとS3間でデータをロード&アンロードする

Amazon Aurora MySQLとS3間でデータをロード&アンロードする

#Amazon S3
#Amazon RDS
#Amazon Aurora
#MySQL
#AWS
quiver

quiver

facebook logohatena logotwitter logo
Clock Icon2018.11.04

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

Amazon Aurora MySQL は、データを

  • S3 にアンロード(SELECT INTO OUTFILE S3)
  • S3 からロード(LOAD DATA FROM S3)

できます。

実際にやってみます。

他のDBエンジンについては、以下の記事を参照ください。

セットアップ

構成イメージ

手順概要

  1. RDS が S3 にアクセスするための IAM ロールを作成
  2. DB クラスターパラメーターグループのパラメーター(aws_default_s3_role)に IAM ロールの ARN を指定
  3. Aurora クラスターを起動。IAM ロールをアタッチ
  4. 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 がかぶらないようにする。かぶった場合の挙動は未定義です

詳細は以下のドキュメントを参照ください。

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 ...)

詳細は以下のドキュメントを参照ください。

データ検証

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 連携に対応していません。

それでは。

関連情報

Share this article

facebook logohatena logotwitter logo

関連記事

S3バケットへのアクセスをオンプレミスサーバーのプライベートIPアドレスで制限する方法2選

S3バケットへのアクセスをオンプレミスサーバーのプライベートIPアドレスで制限する方法2選

User avatar
平井裕二
2024.11.19
ログファイルの共有に困っていませんか? 署名付き URL で S3 にアップロードしよう

ログファイルの共有に困っていませんか? 署名付き URL で S3 にアップロードしよう

User avatar
Shiina
2024.11.18
IAM ポリシーにおける S3 アクションの対象範囲について

IAM ポリシーにおける S3 アクションの対象範囲について

AWS Organizations のRoot access management を使ってすべてのアクセスを拒否したS3 バケットを復旧させてみた

AWS Organizations のRoot access management を使ってすべてのアクセスを拒否したS3 バケットを復旧させてみた

User avatar
あしざわ
2024.11.18
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.