メニュー
Amazon Relational Database Service
ユーザーガイド (API Version 2014-10-31)

Amazon S3 バケットのテキストファイルから DB クラスターへのデータのロード

注記

Amazon S3 バケットのテキストファイルからテーブルへのデータのロードは、Amazon Aurora バージョン 1.8 以降で可能です。Aurora バージョンの詳細については、「Amazon Aurora データベースエンジンの更新」を参照してください。

LOAD DATA FROM S3 または LOAD XML FROM S3 コマンドを使用して、Amazon S3 バケットに保存されているファイルからデータをロードできます。

Amazon S3 バケットからデータをロードする前に、Amazon S3 へのアクセスの権限を Aurora DB クラスターに付与する必要があります。アクセス権限を付与するには、必要なアクセス権限を持つ AWS Identity and Access Management (IAM) ロールを作成し、そのロールを DB クラスターに関連付けます。Amazon S3 への送信接続を許可するように、Aurora DB クラスターを設定する必要もあります。Aurora DB クラスターにお客様に代わって Amazon S3 とやり取りすることを許可する方法の詳細と手順については、「ユーザーの代わりに Amazon Aurora から他の AWS のサービスにアクセスすることを許可する」を参照してください。

LOAD DATA FROM S3 または LOAD XML FROM S3 コマンドを発行するデータベースユーザーは、どちらかのコマンドを発行するために LOAD FROM S3 権限を付与される必要があります。DB クラスターのマスターユーザー名にはデフォルトで LOAD FROM S3 権限が付与されます。以下のコマンドを使用して他のユーザーに権限を付与できます。

Copy
GRANT LOAD FROM S3 on *.* to user@domain-or-ip-address

LOAD FROM S3権限は Amazon Aurora に固有であり、MySQL データベースまたは RDS MySQL DB インスタンスでは使用できません。レプリケーションマスターとしての Aurora DB クラスターと、レプリケーションクライアントとしての MySQL データベースの間にレプリケーションを設定した場合、GRANT LOAD FROM S3 コマンドはレプリケーションをエラーで停止します。エラーをスキップして、レプリケートを再開できます。RDS MySQL DB インスタンスのエラーをスキップするためには、mysql.rds_skip_repl_error コマンドを使用します。MySQL の外部データベースのエラーをスキップするためには、SET GLOBAL sql_slave_skip_counter コマンドを使用します。

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

Amazon S3 バケットに保存されているファイルへのパスを指定する構文は次のとおりです。

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

パスには以下の値が含まれます。

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

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

  • file-name-or-prefix – ロードする Amazon S3 ファイルの名前または複数のファイルを識別するプレフィックス。

マニフェストを使用したロードするデータファイルの指定

LOAD DATA FROM S3 コマンドの MANIFEST キーワードで、JSON 形式のマニフェストファイルを使用して、DB クラスターのテーブルにロードするテキストファイルを指定します。LOAD DATA FROM S3 コマンドで MANIFEST キーワードを使用するには、Aurora 1.11 以上を使用している必要があります。

マニフェスト内の各 URL ではプレフィックスだけでなく、バケットの名前とファイルの完全なオブジェクトパスを指定しておく必要があります。マニフェストを使用し、異なるバケットやリージョンからファイルをロードしたり、同じプレフィックスを共有しないファイルをロードしたりできます。URL にリージョンを指定していない場合は、ターゲットの Aurora DB クラスターのリージョンが使用されます。以下の例では、異なるバケットから 4 つのファイルをロードするマニフェストファイルを示しています。

Copy
{ "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 S3mandatory フラグはデフォルトで false に設定されています。mandatory の設定方法にかかわらず、ファイルが見つからない場合、LOAD DATA FROM S3 は終了します。

マニフェストファイルには、任意の拡張子を付けることができます。以下の例では、前の例の customer.manifest という名前のマニフェストを指定して、LOAD DATA FROM S3 コマンドを実行しています。

Copy
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 コマンドは、mysql スキーマの aurora_s3_load_history テーブルを、ロードされた各ファイルのエントリで更新します。

LOAD DATA FROM S3 コマンドの実行後、aurora_s3_load_history テーブルを照会することで、どのファイルがロードされたかを確認できます。コマンドの 1 回の実行からロードされたファイルを表示するには、WHERE 句を使用して、コマンドで使用したマニフェストファイルの Amazon S3 URI に基づいてレコードをフィルタリングします。以前に同じマニフェストファイルを使用した場合は、timestamp フィールドを使用して結果をフィルタリングします。

Copy
select * from mysql.aurora_s3_load_history where load_prefix = 'S3_URI';

以下の表では、aurora_s3_load_history テーブルのフィールドについて説明しています。

フィールド 説明

load_prefix

load コマンドで指定した URI。この URI は、LOAD DATA FROM S3 FILE コマンドの 1 つのデータファイル、LOAD DATA FROM S3 PREFIX コマンドの複数のデータファイルにマップされる Amazon S3 プレフィックス、または LOAD DATA FROM S3 MANIFEST コマンドでロードされるファイルの名前を含む 1 つのマニフェストファイルにマップできます。

file_name

load_prefix フィールドで指定した URI を使用して Amazon S3 から Aurora にロードされたファイルの名前。

version_number

Amazon S3 バケットがバージョン管理されている場合、ロードされた file_name フィールドで識別されるファイルのバージョン番号。

bytes_loaded

ロードされたファイルのサイズ (バイト単位)。

load_timestamp

LOAD DATA FROM S3 コマンドが完了したときのタイムスタンプ。

LOAD DATA FROM S3

LOAD DATA FROM S3 コマンドを使用して MySQL LOAD DATA INFILE コマンドでサポートされているすべてのテキストファイル形式からデータをロードできます。圧縮ファイルはサポートされていません。

構文

Copy
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,...]

Parameters

以下に、LOAD DATA FROM S3 コマンドで使用される必須およびオプションのパラメーターのリストを示します。以下のパラメーターの一部については、MySQL ドキュメントの「LOAD DATA INFILE 構文」に詳しい説明があります。

  • FILE | PREFIX | MANIFEST – データを読み取るのが、1 つのファイルからか、特定のプレフィックスに一致するすべてのファイルからか、特定のマニフェスト内のすべてのファイルからかを指定します。FILE はデフォルトです。

  • S3-URI – ロードするテキストファイルまたはマニフェストファイルの URI か、使用する Amazon S3 プレフィックスを指定します。「Amazon S3 バケットへのパスの指定」で説明されている構文を使用して URI を指定します。

  • REPLACE | IGNORE – 入力行とデータベーステーブルの既存の行で一意のキー値が同じである場合、どのアクションを実行するかを決定します。

    • テーブル内の既存の行を入力行で置き換える場合は、REPLACE を指定します。

    • 入力行を破棄する場合は、IGNORE を指定します。IGNORE はデフォルトです。

  • INTO TABLE – 入力行をロードする先のデータベーステーブルの名前を指定します。

  • PARTITION – すべての入力行を、指定されたカンマ区切りのパーティション名のリストに記載されているパーティション内に挿入することを要求します。指定されたパーティションのいずれかに入力行を挿入できない場合、コマンドは失敗し、エラーが返されます。

  • CHARACTER SET – 入力ファイルのデータの文字セットを指定します。

  • FIELDS | COLUMNS – 入力ファイルのフィールドまたは列を区切る方法を指定します。デフォルトでは、フィールドはタブで区切られます。

  • LINES – 入力ファイルの行を区切る方法を指定します。デフォルトでは、改行で区切られます。

  • IGNORE number LINES | ROWS – 入力ファイルの先頭で特定の行数を無視することを指定します。たとえば、IGNORE 1 LINES では列名を含む最初のヘッダー行がスキップされます。IGNORE 2 ROWS では、入力ファイルの先頭から 2 行のデータがスキップされます。

  • col_name_or_user_var, ... – 1 つ以上の列名またはユーザー変数のカンマ区切りのリストにより、ロードする列を名前で指定します。この目的で使用されるユーザー変数の名前は、プレフィックスを @ とするテキストファイルの要素名と一致する必要があります。フィールド値を対応するユーザー変数に保存して後で再利用できます。

    たとえば、次のコマンドは入力ファイルの最初の列を table1 の最初の列内にロードし、さらに table1table_column2 の値として、2 列目の入力値を 100 で割った値を設定します。

    Copy
    LOAD DATA FROM S3 's3://mybucket/data.txt' INTO TABLE table1 (column1, @var1) SET table_column2 = @var1/100;
  • SET – 割り当てオペレーションのカンマ区切りのリストにより、テーブル内の列の値を入力ファイルに含まれていない値に設定します。

    たとえば、次のコマンドは table1 の最初の 2 列を入力ファイルの最初の 2 列の値に設定し、さらに table1column3 の値を現在のタイムスタンプに設定します。

    Copy
    LOAD DATA FROM S3 's3://mybucket/data.txt' INTO TABLE table1 (column1, column2) SET column3 = CURRENT_TIMESTAMP;

    SET 割り当ての右側のサブクエリを使用できます。列に割り当てる値を返すサブクエリとしては、スカラーサブクエリのみ使用できます。また、サブクエリを使用してロード中のテーブルから選択することはできません。

Amazon S3 バケットからデータをロードしている場合、LOAD DATA FROM S3 コマンドの LOCAL キーワードは使用できません。

次のコマンドでは、Aurora DB クラスターと同じリージョンにある Amazon S3 バケットからデータをロードします。dbbucket Amazon S3 バケットにあるファイル customerdata.txt のカンマ区切りデータを読み取り、さらにそのデータをテーブル store-schema.customer-table 内にロードします。

Copy
LOAD DATA FROM S3 's3://dbbucket/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 リージョンの my-data Amazon S3 バケットで employee-data オブジェクトプレフィックスと一致するすべてのファイルからカンマ区切りのデータを読み取り、さらにそのデータを employees テーブル内にロードします。

Copy
LOAD DATA FROM S3 PREFIX 's3-us-west-2://my-data/employee_data' INTO TABLE employees FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (ID, FIRSTNAME, LASTNAME, EMAIL, SALARY);

以下のコマンドは、q1_sales.json という名前の JSON マニフェストファイルで指定したファイルから sales テーブルにデータをロードします。

Copy
LOAD DATA FROM S3 MANIFEST 's3-us-west-2://aurora-bucket/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> 要素の属性としての列名。属性値はテーブルフィールドの内容を指定します。

    Copy
    <row column1="value1" column2="value2" .../>
  • <row> 要素の子要素としての列名。子要素の値は、テーブルフィールドの内容を指定します。

    Copy
    <row> <column1>value1</column1> <column2>value2</column2> </row>
  • <row> 要素で <field> 要素の name 属性の列名。<field> 要素の値は、テーブルフィールドの内容を指定します。

    Copy
    <row> <field name='column1'>value1</field> <field name='column2'>value2</field> </row>

構文

Copy
LOAD XML FROM S3 'S3-URI' [REPLACE | IGNORE] INTO TABLE tbl_name [PARTITION (partition_name,...)] [CHARACTER SET charset_name] [ROWS IDENTIFIED BY '<element-name>'] [IGNORE number {LINES | ROWS}] [(field_name_or_user_var,...)] [SET col_name = expr,...]

Parameters

以下に、LOAD DATA FROM S3 コマンドで使用される必須およびオプションのパラメーターのリストを示します。以下のパラメーターの一部については、MySQL ドキュメントの「LOAD XML 構文」に詳しい説明があります。

  • FILE | PREFIX – 単一のファイルまたは特定のプレフィックスに一致するすべてのファイルからデータをロードするかどうかを指定します。FILE はデフォルトです。

  • REPLACE | IGNORE – 入力行とデータベーステーブルの既存の行で一意のキー値が同じである場合、どのアクションを実行するかを決定します。

    • テーブル内の既存の行を入力行で置き換える場合は、REPLACE を指定します。

    • 入力行を破棄する場合は、IGNORE を指定します。IGNORE はデフォルトです。

  • INTO TABLE – 入力行をロードする先のデータベーステーブルの名前を指定します。

  • PARTITION – すべての入力行を、指定されたカンマ区切りのパーティション名のリストに記載されているパーティション内に挿入することを要求します。指定されたパーティションのいずれかに入力行を挿入できない場合、コマンドは失敗し、エラーが返されます。

  • CHARACTER SET – 入力ファイルのデータの文字セットを指定します。

  • ROWS IDENTIFIED BY – 入力ファイルの行を識別する要素名を指定します。デフォルト: <row>

  • IGNORE number LINES | ROWS – 入力ファイルの先頭で特定の行数を無視することを指定します。たとえば、IGNORE 1 LINES ではテキストファイルの最初の行がスキップされます。IGNORE 2 ROWS では、入力 XML の最初の 2 行のデータがスキップされます。

  • field_name_or_user_var, ... – 1 つ以上の XML 名またはユーザー変数のカンマ区切りのリストにより、ロードする要素を名前で指定します。この目的で使用されるユーザー変数の名前は、プレフィックスを @ とする XML ファイルの要素名と一致する必要があります。フィールド値を対応するユーザー変数に保存して後で再利用できます。

    たとえば、次のコマンドは入力ファイルの最初の列を table1 の最初の列内にロードし、さらに table1table_column2 の値として、2 列目の入力値を 100 で割った値を設定します。

    Copy
    LOAD XML FROM S3 's3://mybucket/data.xml' INTO TABLE table1 (column1, @var1) SET table_column2 = @var1/100;
  • SET – 割り当てオペレーションのカンマ区切りのリストにより、テーブル内の列の値を入力ファイルに含まれていない値に設定します。

    たとえば、次のコマンドは table1 の最初の 2 列を入力ファイルの最初の 2 列の値に設定し、さらに table1column3 の値を現在のタイムスタンプに設定します。

    Copy
    LOAD XML FROM S3 's3://mybucket/data.xml' INTO TABLE table1 (column1, column2) SET column3 = CURRENT_TIMESTAMP;

    SET 割り当ての右側のサブクエリを使用できます。列に割り当てる値を返すサブクエリとしては、スカラーサブクエリのみ使用できます。また、サブクエリを使用してロード中のテーブルから選択することはできません。

関連トピック