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 クラスターへのデータのロード」を参照してください。

注記

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

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

データを 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) に設定します。aurora_select_into_s3_role に IAM ロールを指定しないと、aws_default_s3_role に指定した IAM ロールが Aurora で使用されます。

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

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

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

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

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

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

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

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

SELECT INTO OUTFILE S3 を発行するデータベースユーザーに対しては、このステートメントを発行するための SELECT INTO S3 権限を付与する必要があります。DB クラスターのマスターユーザー名にはデフォルトで SELECT INTO S3 権限が付与されます。他のユーザーに権限を付与するには、次のコマンドを使用できます。

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

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

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 以降でサポートされています。

構文

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

パラメータ

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

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

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

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

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;

関連トピック