Amazon S3 バケットのテキストファイルから Amazon Aurora MySQL DB クラスターへのデータのロード
LOAD DATA FROM S3
または LOAD XML FROM S3
ステートメントを使用して、Amazon S3 バケットに保存されているファイルからデータをロードできます。Aurora MySQL の場合、ファイルは最初にローカルディスクに保存され、次にデータベースにインポートされます。データベースへのインポートが完了すると、ローカルファイルは削除されます。
注記
テキストファイルからテーブルへのデータのロードは、Aurora Serverless v1 ではサポートされていません。Aurora Serverless v2 に対してサポートされています。
目次
Amazon S3 へのアクセスを Aurora に許可する
Amazon S3 バケットからデータをロードする前に、まず Amazon S3 へのアクセス権限を Aurora MySQL DB クラスターに付与する必要があります。
Amazon S3 へのアクセス権限を Aurora MySQL に付与するには
-
バケットおよびオブジェクトのアクセス許可を付与し、Aurora MySQL DB クラスターから Amazon S3 へのアクセスを許可する AWS Identity and Access Management (IAM) ポリシーを作成します。手順については、Amazon S3 リソースにアクセスするための IAM ポリシーの作成 を参照してください。
注記
Aurora MySQL バージョン 3.05 以降では、カスタマーマネージド AWS KMS keys を使用して暗号化されたオブジェクトをロードできます。そのためには、IAM ポリシーに
kms:Decrypt
アクセス許可を含めてください。詳細については、「AWS KMS リソースにアクセスするための IAM ポリシーの作成」を参照してください。AWS マネージドキー または Amazon S3 マネージドキー (SSE-S3) を使用して暗号化されたオブジェクトをロードする場合、このアクセス許可は必要ありません。
-
IAM ロールを作成して、「Amazon S3 リソースにアクセスするための IAM ポリシーの作成」で作成した IAM ポリシーを新しい IAM ロールにアタッチします。手順については、「Amazon Aurora が AWS のサービスにアクセスすることを許可する IAM ロールの作成」を参照してください。
-
DB クラスターがカスタム DB クラスターパラメータグループを使用していることを確認します。
カスタム DB クラスターパラメータグループの作成の詳細については、「Amazon Aurora での DB クラスターパラメータグループの作成」を参照してください。
-
Aurora MySQL バージョン 2 の場合、
aurora_load_from_s3_role
またはaws_default_s3_role
DB クラスターパラメータを、新しい IAM ロールの Amazon リソースネーム (ARN) に設定します。IAM ロールがaurora_load_from_s3_role
に指定されていない場合、Aurora はaws_default_s3_role
に指定されている IAM ロールを使用します。Aurora MySQL バージョン 3 の場合、
aws_default_s3_role
を使用します。クラスターが Aurora Global Database の一部である場合は、このパラメータをグローバルデータベース内の Aurora クラスターごとに設定します。Aurora Global Database 内のプライマリクラスターのみがデータをロードできますが、フェイルオーバー機構によって別のクラスターが昇格されてプライマリクラスターになる場合があります。
DB クラスターのパラメータの詳細については、「Amazon Aurora の DB クラスターパラメータと DB インスタンスパラメータ」を参照してください。
-
Aurora MySQL DB クラスター内のデータベースユーザーが Amazon S3 にアクセスできるように、「Amazon Aurora が AWS のサービスにアクセスすることを許可する IAM ロールの作成」で作成したロールをその DB クラスターに関連付けます。Aurora Global Database の場合は、グローバルデータベース内の Aurora クラスターごとにロールを関連付けます。DB クラスターへの IAM ロールの関連付けの詳細については、「IAM ロールと Amazon Aurora MySQL DB クラスターの関連付け」を参照してください。
-
Amazon S3 へのアウトバウンド接続を許可するように Aurora MySQL DB クラスターを設定します。手順については、Amazon Aurora から他の AWS のサービスへのネットワーク通信の有効化 を参照してください。
DB クラスターがパブリックアクセス可能ではなく、VPC パブリックサブネットにある場合は、プライベートです。S3 バケットにアクセスするための S3 ゲートウェイエンドポイントを作成することができます。詳細については、「Amazon S3 のゲートウェイエンドポイント」を参照してください。
Aurora Global Database の場合は、グローバルデータベース内の Aurora クラスターごとにアウトバウンド接続を有効にします。
Amazon Aurora MySQL でデータをロードするための権限の付与
LOAD DATA FROM S3
またはLOAD XML FROM S3
ステートメントを発行するデータベースユーザーは、いずれかのステートメントを発行するための特定のロールまたは特権を持っている必要があります。Aurora MySQL バージョン 3 では、AWS_LOAD_S3_ACCESS
ロールを付与します。Aurora MySQL バージョン 2 では、LOAD FROM S3
権限を付与します。DB クラスターの管理ユーザーにはデフォルトで適切なロールまたは権限が付与されます。他のユーザーに権限を付与するには、次のいずれかのコマンドが使用できます。
Aurora MySQL バージョン 3 では、次のステートメントを使用します:
GRANT AWS_LOAD_S3_ACCESS TO '
user
'@'domain-or-ip-address
'
ヒント
Aurora MySQL バージョン 3 でロールテクニックを使用する場合は、SET ROLE
または role_name
SET ROLE
ALL
ステートメントを使用してロールを有効化することもできます。MySQL 8.0 ロールシステムに馴染みがない場合は、ロールベースの特権モデル で詳細を確認頂けます。詳細については、「MySQL リファレンスマニュアル」の「Using roles
これは現在アクティブなセッションにのみ適用されます。再接続するときは、SET ROLE
ステートメントを再度実行して権限を付与する必要があります。詳細については、MySQL リファレンスマニュアルの「SET ROLE ステートメント
ユーザーが DB インスタンスに接続したときに、activate_all_roles_on_login
DB クラスターパラメータを使用して、すべてのロールを自動的に有効化できます。このパラメータを設定すると、通常、SET ROLE
ステートメントを明示的に呼び出してロールをアクティブ化する必要はありません。詳細については、MySQL リファレンスマニュアルの「activate_all_roles_on_login
ただし、ストアドプロシージャを別のユーザーから呼び出す場合は、ストアドプロシージャの先頭で SET ROLE ALL
を明示的に呼び出してロールをアクティブ化する必要があります。
Aurora MySQL バージョン 2 では、次のステートメントを使用します:
GRANT LOAD FROM S3 ON *.* TO '
user
'@'domain-or-ip-address
'
AWS_LOAD_S3_ACCESS
ロールと LOAD FROM S3
権限は Amazon Aurora に固有であり、外部の MySQL データベースまたは RDS for MySQL DB インスタンスでは使用できません。レプリケーションソースとしての Aurora DB クラスターと、レプリケーションクライアントとしての MySQL データベースの間でレプリケーションを設定した場合、ロールまたは権限の GRANT
ステートメントはエラーとなり、レプリケーションは停止します。エラーをスキップして、レプリケートを再開できます。RDS for MySQL インスタンスでエラーをスキップするには、 mysql_rds_skip_repl_error プロシージャを使用します。外部 MySQL データベースでエラーをスキップするには、slave_skip_errors
注記
データベースユーザーには、データをロードする先のデータベースに対する INSERT
権限が必要です。
Amazon S3 バケットへのパス (URI) の指定
Amazon S3 バケットに保存されているファイルへのパスを指定する構文は次のとおりです。
s3
-region
://amzn-s3-demo-bucket
/file-name-or-prefix
パスに指定する値は以下のとおりです。
-
region
(オプション) - ロード元の Amazon S3 バケットがある AWS リージョン。この値はオプションです。region
値を指定しないと、Aurora は DB クラスターと同じリージョンの Amazon S3 からファイルをロードします。 -
bucket-name
- ロードするデータが含まれている Amazon S3 バケットの名前。仮想フォルダのパスを識別するオブジェクトプレフィックスがサポートされています。 -
file-name-or-prefix
- Amazon S3 テキストファイルまたは XML ファイルの名前、あるいはロードする 1 つ以上のテキストファイルまたは XML ファイルを識別するプレフィックス。ロードするテキストファイルを識別するマニフェストファイルを指定することもできます。マニフェストファイルを使用して Amazon S3 からテキストファイルをロードする方法の詳細については、「マニフェストを使用して、ロードするデータファイルを指定する」を参照してください。
S3 バケット内のファイルの URI をコピーするには
AWS Management Console にサインインし、Amazon S3 コンソール (https://console.aws.amazon.com/s3/
) を開きます。 -
ナビゲーションペインで [バケット] を選択し、URI をコピーするバケットを選択します。
-
S3 からロードするプレフィックスまたはファイルを選択します。
-
[S3 URI をコピー] を選択します。
LOAD DATA FROM S3
LOAD DATA FROM S3
ステートメントを使用して MySQL LOAD DATA INFILE
注記
Aurora MySQL DB クラスターが S3 へのアウトバウンド接続を許可していることを確認してください。詳細については、「Amazon Aurora から他の AWS のサービスへのネットワーク通信の有効化」を参照してください。
構文
LOAD DATA [FROM] S3 [FILE | PREFIX | MANIFEST] '
S3-URI
' [REPLACE | IGNORE] INTO TABLEtbl_name
[PARTITION (partition_name
,...)] [CHARACTER SETcharset_name
] [{FIELDS | COLUMNS} [TERMINATED BY 'string
'] [[OPTIONALLY] ENCLOSED BY 'char
'] [ESCAPED BY 'char
'] ] [LINES [STARTING BY 'string
'] [TERMINATED BY 'string
'] ] [IGNOREnumber
{LINES | ROWS}] [(col_name_or_user_var
,...)] [SETcol_name
=expr
,...]
注記
Aurora MySQL バージョン 3.05 以降では、キーワード FROM
はオプションです。
パラメータ
LOAD DATA FROM S3
ステートメントでは、次の必須パラメータとオプションパラメータを使用します。これらのパラメータの一部の詳細については、MySQL ドキュメントの「LOAD XML Statement
- ファイル | プレフィックス | マニフェスト
-
データを読み取るのが、1 つのファイルからか、特定のプレフィックスに一致するすべてのファイルからか、指定されたマニフェスト内のすべてのファイルからかを指定します。デフォルトは
FILE
です。 - S3-URI
-
ロードするテキストファイルまたはマニフェストファイルの URI、または、使用する Amazon S3 プレフィックスを指定します。「Amazon S3 バケットへのパス (URI) の指定」で説明されている構文を使用して URI を指定します。
- 置換 | 無視
-
入力行とデータベーステーブルの既存の行で一意のキー値が同じである場合、どのアクションを実行するかを決定します。
-
テーブル内の既存の行を入力行で置き換える場合は、
REPLACE
を指定します。 -
入力行を破棄する場合は、
IGNORE
を指定します。
-
- テーブルに
-
入力行をロードする先のデータベーステーブルの名前を指定します。
- PARTITION
-
すべての入力行を、指定されたカンマ区切りのパーティション名のリストに記載されているパーティション内に挿入することを要求します。指定されたパーティションのいずれかに入力行を挿入できない場合、ステートメントは失敗し、エラーが返されます。
- 文字セット
-
入力ファイルのデータの文字セットを指定します。
- フィールド | 列
-
入力ファイルのフィールドまたは列を区切る方法を指定します。デフォルトでは、フィールドはタブで区切られます。
- LINES
-
入力ファイルの行を区切る方法を指定します。デフォルトでは、行は改行文字 (
'\n'
) で区切られます。 number
行を無視 | 行-
入力ファイルの先頭の特定の数の行または列を無視することを指定します。例えば、
IGNORE 1 LINES
では列名を含む初期のヘッダー行がスキップされます。IGNORE 2 ROWS
では、入力ファイルの先頭から 2 行のデータがスキップされます。また、PREFIX
を使用すると、IGNORE
は初期の入力ファイルの先頭にある特定の行数または行をスキップします。 - col_name_or_user_var, ..。
-
1 つ以上の列名、またはロードする列を名前で指定するユーザー変数のカンマ区切りのリストを指定します。この目的で使用されるユーザー可変の名前は、プレフィックスを @ とするテキストファイルの要素名と一致する必要があります。フィールド値を対応するユーザー可変に保存して後で再利用できます。
例えば、次のステートメントでは、入力ファイルの初期の列を
table1
の初期の列内にロードし、さらにtable_column2
のtable1
列の値として、2 列目の入力値を 100 で割った値を設定します。LOAD DATA FROM S3 's3://
amzn-s3-demo-bucket
/data.txt' INTO TABLE table1 (column1, @var1) SET table_column2 = @var1/100; - SET
-
テーブル内の列の値を入力ファイルに含まれていない値に設定する代入操作のカンマ区切りのリストを指定します。
例えば、次のステートメントは
table1
の初期の 2 列を入力ファイルの初期の 2 列の値に設定し、さらにcolumn3
のtable1
の値を現在のタイムスタンプに設定します。LOAD DATA FROM S3 's3://
amzn-s3-demo-bucket
/data.txt' INTO TABLE table1 (column1, column2) SET column3 = CURRENT_TIMESTAMP;SET
割り当ての右側のサブクエリを使用できます。列に割り当てる値を返すサブクエリとしては、スカラーサブクエリのみ使用できます。また、サブクエリを使用してロード中のテーブルから選択することはできません。
Amazon S3 バケットからデータをロードする場合、LOAD DATA FROM S3
ステートメントの LOCAL
キーワードは使用できません。
マニフェストを使用して、ロードするデータファイルを指定する
LOAD DATA FROM S3
ステートメントの MANIFEST
キーワードで、JSON 形式のマニフェストファイルを指定して、DB クラスターのテーブルにロードするテキストファイルをリストできます。
次の JSON スキーマはマニフェストファイルの形式と内容を示しています。
{ "$schema": "http://json-schema.org/draft-04/schema#", "additionalProperties": false, "definitions": {}, "id": "Aurora_LoadFromS3_Manifest", "properties": { "entries": { "additionalItems": false, "id": "/properties/entries", "items": { "additionalProperties": false, "id": "/properties/entries/items", "properties": { "mandatory": { "default": "false", "id": "/properties/entries/items/properties/mandatory", "type": "boolean" }, "url": { "id": "/properties/entries/items/properties/url", "maxLength": 1024, "minLength": 1, "type": "string" } }, "required": [ "url" ], "type": "object" }, "type": "array", "uniqueItems": true } }, "required": [ "entries" ], "type": "object" }
マニフェスト内の各 url
では、プレフィックスだけでなく、バケットの名前とファイルの完全なオブジェクトパスを使用して URL を指定する必要があります。マニフェストを使用し、異なるバケットやリージョンからファイルをロードしたり、同じプレフィックスを共有しないファイルをロードしたりできます。URL にリージョンを指定していない場合は、ターゲットの Aurora DB クラスターのリージョンが使用されます。以下の例では、異なるバケットから 4 つのファイルをロードするマニフェストファイルを示しています。
{ "entries": [ { "url":"s3://aurora-bucket/2013-10-04-customerdata", "mandatory":true }, { "url":"s3-us-west-2://aurora-bucket-usw2/2013-10-05-customerdata", "mandatory":true }, { "url":"s3://aurora-bucket/2013-10-04-customerdata", "mandatory":false }, { "url":"s3://aurora-bucket/2013-10-05-customerdata" } ] }
ファイルが見つからない場合に、オプションの mandatory
フラグによって コマンドがエラーを返すかどうかを指定します。LOAD DATA FROM S3
mandatory
フラグはデフォルトで false
に設定されています。mandatory
の設定方法にかかわらず、ファイルが見つからない場合、LOAD DATA FROM S3
は終了します。
マニフェストファイルには、任意の拡張子を付けることができます。次の例では、前の例にあった「LOAD DATA FROM S3
」という名前のマニフェストで customer.manifest
ステートメントを実行しています。
LOAD DATA FROM S3 MANIFEST 's3-us-west-2://aurora-bucket/customer.manifest' INTO TABLE CUSTOMER FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (ID, FIRSTNAME, LASTNAME, EMAIL);
ステートメントが完了すると、正常にロードされた各ファイルのエントリが aurora_s3_load_history
テーブルに書き込まれます。
aurora_s3_load_history テーブルを使用してロード済みのファイルを確認する
LOAD DATA FROM S3
ステートメントが成功するたびに、aurora_s3_load_history
スキーマの mysql
テーブルが、ロードされた各ファイルのエントリで更新されます。
LOAD DATA FROM S3
ステートメントの実行後、aurora_s3_load_history
テーブルをクエリすることで、どのファイルがロードされたかを確認できます。ステートメントの 1 回のイテレーションからロードされたファイルを確認するには、WHERE
句を使用して Amazon S3 URI のレコードをフィルタリングし、ステートメントで使用されたマニフェストファイルを見つけます。以前に同じマニフェストファイルを使用した場合は、timestamp
フィールドを使用して結果をフィルタリングします。
select * from mysql.aurora_s3_load_history where load_prefix = '
S3_URI
';
以下の表では、aurora_s3_load_history
テーブルのフィールドについて説明しています。
フィールド | 説明 |
---|---|
|
load ステートメントで指定した URI。この URI は、次のいずれかにマッピングできます。
|
|
|
|
Amazon S3 バケットにバージョン番号がある場合、ロードされた |
|
ロードされたファイルのサイズ (バイト単位)。 |
|
|
例
次のステートメントでは、Aurora DB クラスターと同じリージョンにある Amazon S3 バケットからデータをロードします。このステートメントは、amzn-s3-demo-bucket
Amazon S3 バケットにあるファイル customerdata.txt
のカンマ区切りデータを読み取り、そのデータをテーブル store-schema.customer-table
にロードします。
LOAD DATA FROM S3 's3://
amzn-s3-demo-bucket
/customerdata.csv' INTO TABLE store-schema.customer-table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (ID, FIRSTNAME, LASTNAME, ADDRESS, EMAIL, PHONE);
次のステートメントでは、Aurora DB クラスターとは別のリージョンにある Amazon S3 バケットからデータをロードします。このステートメントは、us-west-2
リージョンの amzn-s3-demo-bucket
Amazon S3 バケットで employee-data
オブジェクトプレフィックスに一致するすべてのファイルからカンマ区切りのデータを読み取り、そのデータを employees
テーブルにロードします。
LOAD DATA FROM S3 PREFIX 's3-us-west-2://
amzn-s3-demo-bucket
/employee_data' INTO TABLE employees FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (ID, FIRSTNAME, LASTNAME, EMAIL, SALARY);
次のステートメントでは、q1_sales.json という名前の JSON マニフェストファイルに指定されているファイルから sales
テーブルにデータをロードします。
LOAD DATA FROM S3 MANIFEST 's3-us-west-2://
amzn-s3-demo-bucket1
/q1_sales.json' INTO TABLE sales FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (MONTH, STORE, GROSS, NET);
LOAD XML FROM S3
LOAD XML FROM S3
ステートメントを使用して、Amazon S3 バケットに保存されている XML ファイルのデータを以下の 3 種類の XML フォーマットのいずれかでロードできます。
-
<row>
要素の属性としての列名。属性値はテーブルフィールドの内容を指定します。<row column1="value1" column2="value2" .../>
-
<row>
要素の子要素としての列名。子要素の値は、テーブルフィールドの内容を指定します。<row> <column1>value1</column1> <column2>value2</column2> </row>
-
name
要素で<field>
要素の<row>
属性の列名。<field>
要素の値は、テーブルフィールドの内容を指定します。<row> <field name='column1'>value1</field> <field name='column2'>value2</field> </row>
構文
LOAD XML FROM S3 '
S3-URI
' [REPLACE | IGNORE] INTO TABLEtbl_name
[PARTITION (partition_name,...)] [CHARACTER SET charset_name] [ROWS IDENTIFIED BY '<element-name>
'] [IGNOREnumber
{LINES | ROWS}] [(field_name_or_user_var,...)] [SET col_name = expr,...]
パラメータ
LOAD XML FROM S3
ステートメントでは、次の必須パラメータとオプションパラメータを使用します。これらのパラメータの一部の詳細については、MySQL ドキュメントの「LOAD XML Statement
- ファイル | プレフィックス
-
データを単一のファイルからロードするか、特定のプレフィックスに一致するすべてのファイルからロードするかを指定します。デフォルトは
FILE
です。 - 置換 | 無視
-
入力行とデータベーステーブルの既存の行で一意のキー値が同じである場合、どのアクションを実行するかを決定します。
-
テーブル内の既存の行を入力行で置き換える場合は、
REPLACE
を指定します。 -
入力行を破棄する場合は、
IGNORE
を指定します。デフォルトはIGNORE
です。
-
- テーブルに
-
入力行をロードする先のデータベーステーブルの名前を指定します。
- PARTITION
-
すべての入力行を、指定されたカンマ区切りのパーティション名のリストに記載されているパーティション内に挿入することを要求します。指定されたパーティションのいずれかに入力行を挿入できない場合、ステートメントは失敗し、エラーが返されます。
- 文字セット
-
入力ファイルのデータの文字セットを指定します。
- 行の識別
-
入力ファイルの行を識別する要素名を指定します。デフォルト:
<row>
。 number
行を無視 | 行-
入力ファイルの先頭の特定の数の行または列を無視することを指定します。例えば、
IGNORE 1 LINES
ではテキストファイルの初期の行がスキップされます。IGNORE 2 ROWS
では、入力 XML の初期の 2 行のデータがスキップされます。 - field_name_or_user_var, ..。
-
ロードする要素を名前で指定する 1 つ以上の XML 要素名またはユーザー変数のカンマ区切りのリストを指定します。この目的で使用されるユーザー可変の名前は、プレフィックスを @ とする XML ファイルの要素名と一致する必要があります。フィールド値を対応するユーザー可変に保存して後で再利用できます。
例えば、次のステートメントでは、入力ファイルの初期の列を
table1
の初期の列内にロードし、さらにtable_column2
のtable1
列の値として、2 列目の入力値を 100 で割った値を設定します。LOAD XML FROM S3 's3://
amzn-s3-demo-bucket
/data.xml' INTO TABLE table1 (column1, @var1) SET table_column2 = @var1/100; - SET
-
テーブル内の列の値を入力ファイルに含まれていない値に設定する代入操作のカンマ区切りのリストを指定します。
例えば、次のステートメントは
table1
の初期の 2 列を入力ファイルの初期の 2 列の値に設定し、さらにcolumn3
のtable1
の値を現在のタイムスタンプに設定します。LOAD XML FROM S3 's3://
amzn-s3-demo-bucket
/data.xml' INTO TABLE table1 (column1, column2) SET column3 = CURRENT_TIMESTAMP;SET
割り当ての右側のサブクエリを使用できます。列に割り当てる値を返すサブクエリとしては、スカラーサブクエリのみ使用できます。また、サブクエリを使用してロード中のテーブルから選択することはできません。