Amazon Aurora MySQL DB クラスターから Amazon S3 バケット内のテキストファイルへのデータの保存 - Amazon Aurora

Amazon Aurora MySQL DB クラスターから Amazon S3 バケット内のテキストファイルへのデータの保存

SELECT INTO OUTFILE S3 ステートメントを使用して、Amazon Aurora MySQL DB クラスターからクエリしたデータを直接 Amazon S3 バケット内のテキストファイルに保存できます。この機能を使用することで、初期にデータをクライアントに持ち込んだ後でクライアントから Amazon S3 にデータをコピーするという手順を省略できます。LOAD DATA FROM S3 ステートメントにより、このステートメントで作成されたファイルを使用してデータを Aurora DB クラスター内にロードできます。詳細については、「Amazon S3 バケットのテキストファイルから Amazon Aurora MySQL DB クラスターへのデータのロード」を参照してください。

暗号化を使用している場合は、Amazon S3 バケットは AWS マネージドキー で暗号化されている必要があります。現在、カスタマーマネージド型キーを使用して暗号化されたバケットにデータを保存することはできません。

この機能は現在、Aurora Serverless クラスターでは使用できません。

注記

AWS Management Console、AWS CLI、または Amazon RDS API を使用して、DB クラスタースナップショットのデータを Amazon S3 に保存できます。詳細については、「Amazon S3 への DB クラスタースナップショットデータのエクスポート」を参照してください。

Amazon S3 へのアクセスを Aurora MySQL に許可する

データを Amazon S3 バケット内に保存する前に、Amazon S3 へのアクセス権限を Aurora MySQL DB クラスターに付与する必要があります。

Amazon S3 へのアクセス権限を Aurora MySQL に付与するには

  1. バケットおよびオブジェクトのアクセス許可を付与し、Aurora MySQL DB クラスターから Amazon S3 へのアクセスを許可する AWS Identity and Access Management (IAM) ポリシーを作成します。手順については、「Amazon S3 リソースにアクセスするための IAM ポリシーの作成」を参照してください。

  2. IAM ロールを作成して、「Amazon S3 リソースにアクセスするための IAM ポリシーの作成」で作成した IAM ポリシーを新しい IAM ロールにアタッチします。手順については、「Amazon Aurora が AWS のサービスにアクセスすることを許可する IAM ロールの作成」を参照してください。

  3. DB クラスターの aurora_select_into_s3_role パラメータまたは aws_default_s3_role パラメータを、新しい IAM ロールの Amazon リソースネーム (ARN) に設定します。IAM ロールが aurora_select_into_s3_role に指定されていない場合、Aurora は aws_default_s3_role に指定されている IAM ロールを使用します。

    クラスターが Aurora Global Database の一部である場合は、このパラメータをグローバルデータベース内の Aurora クラスターごとに設定します。

    DB クラスターのパラメータの詳細については、「Amazon Aurora の DB クラスターパラメータと DB インスタンスパラメータ」を参照してください。

  4. Aurora MySQL DB クラスター内のデータベースユーザーが Amazon S3 にアクセスできるように、「Amazon Aurora が AWS のサービスにアクセスすることを許可する IAM ロールの作成」で作成したロールをその DB クラスターに関連付けます。

    Aurora Global Database の場合は、グローバルデータベース内の Aurora クラスターごとにロールを関連付けます。

    DB クラスターへの IAM ロールの関連付けの詳細については、「IAM ロールと Amazon Aurora MySQL DB クラスターの関連付け」を参照してください。

  5. Amazon S3 へのアウトバウンド接続を許可するように Aurora MySQL DB クラスターを設定します。手順については、「Amazon Aurora MySQL から他の AWS のサービスへのネットワーク通信の有効化」を参照してください。

    Aurora Global Database の場合は、グローバルデータベース内の Aurora クラスターごとにアウトバウンド接続を有効にします。

Aurora MySQL にデータを保存する権限の付与

SELECT INTO OUTFILE S3 ステートメントを発行するデータベースユーザーは、特定のロールまたは権限を保持している必要があります。Aurora MySQL バージョン 3 では、AWS_SELECT_S3_ACCESS ロールを付与します。Aurora MySQL バージョン 1 または 2 では、SELECT INTO S3 権限を付与します。DB クラスターの管理ユーザーにはデフォルトで適切なロールまたは権限が付与されます。他のユーザーに権限を付与するには、次のいずれかのコマンドが使用できます。

Aurora MySQL バージョン 3 では、次のステートメントを使用します:

GRANT AWS_SELECT_S3_ACCESS TO 'user'@'domain-or-ip-address'
ヒント

Aurora MySQL バージョン 3 でロールテクニックを使用する場合は、SET ROLE role_name またはSET ROLE ALL ステートメントを使ってロールも有効化します。MySQL 8.0 ロールシステムに馴染みがない場合は、ロールベースの特権モデル で詳細を確認頂けます。詳細については、MySQL リファレンスマニュアルロールの使用を参照してください。

これは現在アクティブなセッションにのみ適用されます。再接続するときは、もう一度 SET ROLE ステートメントを実行して、権限を付与する必要があります。詳細については、MySQL リファレンスマニュアルの「SET ROLE ステートメント」を参照してください。

また、ユーザーが DB インスタンスに接続したときに、activate_all_roles_on_login DB クラスターパラメータを使用して、すべてのロールを自動的にアクティブ化することができます。このパラメータを設定すると、SET ROLE ステートメントを明示的に呼び出してロールをアクティブ化する必要はありません。詳細については、MySQL リファレンスマニュアルの「activate_all_roles_on_login」を参照してください。

Aurora MySQL バージョン 1 または 2 では、次のステートメントを使用します。

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

AWS_SELECT_S3_ACCESS ロールまたは SELECT INTO S3 権限は Amazon Aurora MySQL に固有であり、MySQL データベースおよび MySQL DB インスタンスの RDS では使用できません。Aurora MySQL DB クラスターをレプリケーションマスターとし、MySQL データベースをレプリケーションクライアントとして両者間にレプリケーションを設定した場合、ロール及び権限の GRANT ステートメントはエラーとなり、レプリケーションが停止します。エラーをスキップして、レプリケートを再開できます。RDS for MySQL DB インスタンスでエラーをスキップするには、 mysql_rds_skip_repl_error プロシージャを使用します。外部 MySQL データベースでエラーをスキップするには、SET GLOBAL sql_slave_skip_counter ステートメント (Aurora MySQL バージョン 1 と 2) またはSET GLOBAL sql_replica_skip_counter ステートメント (Aurora MySQL バージョン 3) を使用します。

Amazon S3 バケットへのパスの指定

Amazon S3 バケットにデータとマニフェストファイルを保存するためのパスを指定する構文は、次に示すように、LOAD DATA FROM S3 PREFIX ステートメントで使用する構文と似ています。

s3-region://bucket-name/file-prefix

パスに指定する値は以下のとおりです。

  • region (オプション) - データの保存先の Amazon S3 バケットがある AWS リージョン。この値はオプションです。region 値を指定しないと、Aurora は DB クラスターと同じリージョンの Amazon S3 にファイルを保存します。

  • bucket-name - データの保存先である Amazon S3 バケットの名前。仮想フォルダのパスを識別するオブジェクトプレフィックスがサポートされています。

  • file-prefix - Amazon S3 に保存するファイルを識別する Amazon S3 オブジェクトプレフィックス。

SELECT INTO OUTFILE S3 ステートメントで作成したデータファイルでは、次のパスを使用します。00000 はゼロから始まる 5 桁の整数です。

s3-region://bucket-name/file-prefix.part_00000

例えば、SELECT INTO OUTFILE S3 ステートメントでデータファイルの保存先のパスとして s3-us-west-2://bucket/prefix を指定し、3 つのデータファイルを作成したとします。この場合、指定した Amazon S3 バケットに保存されるデータファイルは以下のとおりです。

  • s3-us-west-2://bucket/prefix.part_00000

  • s3-us-west-2://bucket/prefix.part_00001

  • s3-us-west-2://bucket/prefix.part_00002

データファイルをリスト化するマニフェストの作成

SELECT INTO OUTFILE S3 ステートメントで MANIFEST ON オプションを使用すると、このステートメントで作成されるテキストファイルをリストするマニフェストファイルを JSON 形式で作成できます。LOAD DATA FROM S3 ステートメントでは、このマニフェストファイルを使用してデータファイルを逆に Aurora MySQL DB クラスター内にロードできます。マニフェストファイルを使用して Amazon S3 から Aurora MySQL DB クラスター内にデータファイルをロードする方法の詳細については、「マニフェストを使用して、ロードするデータファイルを指定する」を参照してください。

SELECT INTO OUTFILE S3 ステートメントで作成されたマニフェストでは、このステートメントで作成された順にデータファイルがリストされます。例えば、SELECT INTO OUTFILE S3 ステートメントでデータファイルの保存先のパスとして s3-us-west-2://bucket/prefix を指定し、マニフェストファイルを作成したとします。この場合、指定した Amazon S3 バケットに保存されるマニフェストファイルは s3-us-west-2://bucket/prefix.manifest という名前で、以下の内容になります。

{ "entries": [ { "url":"s3-us-west-2://bucket/prefix.part_00000" }, { "url":"s3-us-west-2://bucket/prefix.part_00001" }, { "url":"s3-us-west-2://bucket/prefix.part_00002" } ] }

SELECT INTO OUTFILE S3

SELECT INTO OUTFILE S3 ステートメントを使用して、DB クラスターからクエリしたデータを直接 Amazon S3 バケット内の区切りテキストファイルに保存できます。圧縮ファイルはサポートされていません。暗号化ファイルは Aurora MySQL 2.09.0 以降でサポートされています。

[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}] 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'] ]

パラメータ

SELECT INTO OUTFILE S3 ステートメントで使用される Aurora 専用の必須およびオプションのパラメータを以下に一覧表示します。

  • s3-uri - Amazon S3 のプレフィックスとして使用する URI を指定します。「Amazon S3 バケットへのパスの指定」で説明されている構文を使用して URI を指定します。

  • FORMAT {CSV|TEXT} [HEADER]- オプションで、データを CSV 形式で保存します。この構文は、Aurora MySQL バージョン 2.07.0 以降で使用できます。

    TEXT オプションはデフォルトで、既存の MySQL エクスポート形式を生成します。

    CSV オプションは、カンマ区切りのデータ値を生成します。CSV 形式は、 RFC-4180 の仕様に従います。オプションのキーワード HEADER を指定すると、出力ファイルに 1 つのヘッダー行が含まれます。ヘッダー行のラベルは、SELECT ステートメントの列名に対応します。AWS ML サービスで使用するトレーニングデータモデルに CSV ファイルを使用できます。AWS ML サービスでエクスポートされた Aurora データを使用する方法の詳細については、「SageMaker モデルトレーニング用のデータを Amazon S3 にエクスポートする」を参照してください。

  • MANIFEST {ON | OFF} - Amazon S3 でマニフェストファイルを作成するかどうかを指定します。マニフェストファイルは、JSON (JavaScript Object Notation) ファイルであり、LOAD DATA FROM S3 MANIFEST ステートメントでデータを Aurora DB クラスター内にロードする際に使用できます。LOAD DATA FROM S3 MANIFEST の詳細については、Amazon S3 バケットのテキストファイルから Amazon Aurora MySQL DB クラスターへのデータのロード を参照してください。

    クエリで MANIFEST ON を指定すると、すべてのデータファイルが作成されてアップロードされた後で、Amazon S3 内にマニフェストファイルが作成されます。マニフェストファイルは次のパスで作成されます。

    s3-region://bucket-name/file-prefix.manifest

    マニフェストファイルのコンテンツの詳しい形式については、「データファイルをリスト化するマニフェストの作成」を参照してください。

  • OVERWRITE {ON | OFF} - 指定した Amazon S3 バケット内の既存のファイルを上書きするかどうかを指定します。OVERWRITE ON を指定すると、s3-uri に指定した URI のファイルプレフィックスと一致する既存のファイルは上書きされます。上書きされないと、エラーが発生します。

他のパラメータの詳細については、MySQL ドキュメントの「SELECT 構文」と「LOAD DATA INFILE 構文」を参照してください。

考慮事項

Amazon S3 バケットに書き込まれるファイルの数は、SELECT INTO OUTFILE S3 ステートメントで選択したデータの量と Aurora MySQL のファイルサイズのしきい値によって異なります。デフォルトのファイルサイズのしきい値は 6 GB です。ステートメントで選択したデータがファイルサイズのしきい値より少ない場合は、1 つのファイルが作成されます。それ以外の場合は、複数のファイルが作成されます。このステートメントで作成されるファイルについては、以下の点にも留意してください。

  • Aurora MySQL では、データファイルの行がファイル境界で分割されないことが保証されます。複数のファイルの場合、最後のファイルを除くすべてのデータファイルは、通常、ファイルサイズのしきい値に近いサイズになります。ただし、ファイルサイズのしきい値を常に下回る必要があるために、1 つの行が 2 つのデータファイル間にまたがる場合がまれにあります。この場合、Aurora MySQL では行が分割されないようにデータファイルを作成するため、ファイルサイズのしきい値を上回ることがあります。

  • Aurora MySQL では各 SELECT ステートメントをアトミックトランザクションとして実行するため、SELECT INTO OUTFILE S3 ステートメントで大きなデータセットを選択すると、実行時間が長引く場合があります。何らかの理由でステートメントが失敗すると、ステートメントの発行をやり直す必要が生じる場合があります。ただし、ステートメントが失敗しても、Amazon S3 にアップロード済みのファイルは保存先の Amazon S3 バケット内に残るため、再実行するステートメントでは初期からではなく残りのデータだけをアップロードできます。

  • 選択するデータが 25 GB を超える場合は、複数回の SELECT INTO OUTFILE S3 ステートメントを使用してデータを Amazon S3 に保存することをお勧めします。実行するステートメントごとに、保存するデータ部分を選択し、保存先として file_prefix パラメータに異なる s3-uri を指定します。選択するデータを複数のステートメントでパーティション化すると、1 つのステートメントでエラーから回復しやすくなります。1 つのステートメントでエラーが発生した場合は、データの一部だけを再選択して Amazon S3 にアップロードする必要があります。複数のステートメントを使用すると、1 回のトランザクションの実行時間が短くなり、パフォーマンスも向上します。

  • 複数の SELECT INTO OUTFILE S3 ステートメント間で、file_prefix パラメータに同じ s3-uri を指定した場合、これらのステートメントを同時に実行してデータを Amazon S3 に保存しようとしたときの動作は定義されていません。

  • Aurora MySQL では、テーブルスキーマやファイルメタデータなどのメタデータは Amazon S3 にアップロードされません。

  • 障害から回復する目的などで SELECT INTO OUTFILE S3 を再実行する場合もあります。このような場合は、s3-uri で指定するファイルプレフィックスと同じ既存のデータファイルを Amazon S3 バケットから削除するか、OVERWRITE ON クエリで SELECT INTO OUTFILE S3 を指定します。

SELECT INTO OUTFILE S3 ステートメントの成否に応じて、一般的な MySQL エラー番号およびレスポンスが返されます。MySQL エラー番号およびレスポンスにアクセスできない場合は、最も簡単な確認方法として MANIFEST ON をステートメントで指定します。マニフェストファイルは、ステートメントで最後に書き込まれるファイルです。つまり、マニフェストファイルがあれば、ステートメントが完了したことになります。

現在、実行中に SELECT INTO OUTFILE S3 ステートメントの進行状況を直接モニタリングする方法はありません。ただし、このステートメントを使用して Aurora MySQL から Amazon S3 に大量のデータを書き込む際に、ステートメントで選択されるデータのサイズがわかっている場合があります。このような場合、Amazon S3 でデータファイルの作成をモニタリングすることで、進行状況を推測できます。

そのためには、ステートメントで選択する約 6 GB のデータごとにデータファイルが指定先の Amazon S3 バケットに作成されることに注目します。選択対象のデータのサイズを 6 GB で割り、作成されるデータファイルの推定数を割り出します。次に Amazon S3 にアップロードされたファイルの数をステートメントの実行中にモニタリングして、ステートメントの進行状況を推測できます。

次のステートメントでは、employees テーブルからすべてのデータを選択し、そのデータを Aurora MySQL DB クラスターとは異なるリージョンにある Amazon S3 バケット内に保存します。このステートメントで作成されるデータファイルでは、各フィールドの末尾にカンマ (,) 文字、各行の末尾に改行 (\n) 文字が付きます。指定先の Amazon S3 バケットに sample_employee_data ファイルプレフィックスと一致するファイルがあると、ステートメントからエラーが返されます。

SELECT * FROM employees INTO OUTFILE S3 's3-us-west-2://aurora-select-into-s3-pdx/sample_employee_data' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

次のステートメントでは、employees テーブルからすべてのデータを選択し、そのデータを Aurora MySQL DB クラスターと同じリージョンにある Amazon S3 バケット内に保存します。このステートメントで作成されるデータファイルでは、各フィールドの末尾にカンマ (,) 文字、各行の末尾に改行 (\n) 文字が付きます。このステートメントでは、マニフェストファイルも作成されます。指定先の Amazon S3 バケットに sample_employee_data ファイルプレフィックスと一致するファイルがあると、ステートメントからエラーが返されます。

SELECT * FROM employees INTO OUTFILE S3 's3://aurora-select-into-s3-pdx/sample_employee_data' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' MANIFEST ON;

次のステートメントでは、employees テーブルからすべてのデータを選択し、そのデータを Aurora DB クラスターとは異なるリージョンにある Amazon S3 バケット内に保存します。このステートメントで作成されるデータファイルでは、各フィールドの末尾にカンマ (,) 文字、各行の末尾に改行 (\n) 文字が付きます。指定先の Amazon S3 バケットで sample_employee_data ファイルプレフィックスと一致する既存のファイルは上書きされます。

SELECT * FROM employees INTO OUTFILE S3 's3-us-west-2://aurora-select-into-s3-pdx/sample_employee_data' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' OVERWRITE ON;

次のステートメントでは、employees テーブルからすべてのデータを選択し、そのデータを Aurora MySQL DB クラスターと同じリージョンにある Amazon S3 バケット内に保存します。このステートメントで作成されるデータファイルでは、各フィールドの末尾にカンマ (,) 文字、各行の末尾に改行 (\n) 文字が付きます。このステートメントでは、マニフェストファイルも作成されます。指定先の Amazon S3 バケットで sample_employee_data ファイルプレフィックスと一致する既存のファイルは上書きされます。

SELECT * FROM employees INTO OUTFILE S3 's3://aurora-select-into-s3-pdx/sample_employee_data' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' MANIFEST ON OVERWRITE ON;

関連トピック