Loading data into an Amazon Aurora MySQL DB cluster from text files in an Amazon S3 bucket

You can use the LOAD DATA FROM S3 or LOAD XML FROM S3 statement to load data from files stored in an Amazon S3 bucket. In Aurora MySQL, the files are first stored on the local disk, and then imported to the database. After the imports to the database are done, the local files are deleted.

Giving Aurora access to Amazon S3

Before you can load data from an Amazon S3 bucket, you must first give your Aurora MySQL DB cluster permission to access Amazon S3.

Granting privileges to load data in Amazon Aurora MySQL

The database user that issues the LOAD DATA FROM S3 or LOAD XML FROM S3 statement must have a specific role or privilege to issue either statement. In Aurora MySQL version 3, you grant the AWS_LOAD_S3_ACCESS role. In Aurora MySQL version 2, you grant the LOAD FROM S3 privilege. The administrative user for a DB cluster is granted the appropriate role or privilege by default. You can grant the privilege to another user by using one of the following statements.

Use the following statement for Aurora MySQL version 3:

GRANT AWS_LOAD_S3_ACCESS TO 'user'@'domain-or-ip-address'

Use the following statement for Aurora MySQL version 2:

GRANT LOAD FROM S3 ON *.* TO 'user'@'domain-or-ip-address'

The AWS_LOAD_S3_ACCESS role and LOAD FROM S3 privilege are specific to Amazon Aurora and are not available for external MySQL databases or RDS for MySQL DB instances. If you have set up replication between an Aurora DB cluster as the replication source and a MySQL database as the replication client, then the GRANT statement for the role or privilege causes replication to stop with an error. You can safely skip the error to resume replication. To skip the error on an RDS for MySQL instance, use the mysql_rds_skip_repl_error procedure. To skip the error on an external MySQL database, use the slave_skip_errors system variable (Aurora MySQL version 2) or replica_skip_errors system variable (Aurora MySQL version 3).

Specifying the path (URI) to an Amazon S3 bucket

The syntax for specifying the path (URI) to files stored on an Amazon S3 bucket is as follows.

s3-region://amzn-s3-demo-bucket/file-name-or-prefix

The path includes the following values:

LOAD DATA FROM S3

You can use the LOAD DATA FROM S3 statement to load data from any text file format that is supported by the MySQL LOAD DATA INFILE statement, such as text data that is comma-delimited. Compressed files are not supported.

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

Parameters

The LOAD DATA FROM S3 statement uses the following required and optional parameters. You can find more details about some of these parameters in LOAD DATA Statement in the MySQL documentation.

You can't use the LOCAL keyword of the LOAD DATA FROM S3 statement if you're loading data from an Amazon S3 bucket.

Using a manifest to specify data files to load

You can use the LOAD DATA FROM S3 statement with the MANIFEST keyword to specify a manifest file in JSON format that lists the text files to be loaded into a table in your DB cluster.

The following JSON schema describes the format and content of a manifest file.

{
    "$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"
}

Each url in the manifest must specify a URL with the bucket name and full object path for the file, not just a prefix. You can use a manifest to load files from different buckets, different regions, or files that do not share the same prefix. If a region is not specified in the URL, the region of the target Aurora DB cluster is used. The following example shows a manifest file that loads four files from different buckets.

{
  "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"
    }
  ]
}

The optional mandatory flag specifies whether LOAD DATA FROM S3 should return an error if the file is not found. The mandatory flag defaults to false. Regardless of how mandatory is set, LOAD DATA FROM S3 terminates if no files are found.

Manifest files can have any extension. The following example runs the LOAD DATA FROM S3 statement with the manifest in the previous example, which is named 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);

After the statement completes, an entry for each successfully loaded file is written to the aurora_s3_load_history table.

Verifying loaded files using the aurora_s3_load_history table

Every successful LOAD DATA FROM S3 statement updates the aurora_s3_load_history table in the mysql schema with an entry for each file that was loaded.

After you run the LOAD DATA FROM S3 statement, you can verify which files were loaded by querying the aurora_s3_load_history table. To see the files that were loaded from one iteration of the statement, use the WHERE clause to filter the records on the Amazon S3 URI for the manifest file used in the statement. If you have used the same manifest file before, filter the results using the timestamp field.

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

The following table describes the fields in the aurora_s3_load_history table.

Examples

The following statement loads data from an Amazon S3 bucket that is in the same region as the Aurora DB cluster. The statement reads the comma-delimited data in the file customerdata.txt that is in the amzn-s3-demo-bucket Amazon S3 bucket, and then loads the data into the table 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);

The following statement loads data from an Amazon S3 bucket that is in a different region from the Aurora DB cluster. The statement reads the comma-delimited data from all files that match the employee-data object prefix in the amzn-s3-demo-bucket Amazon S3 bucket in the us-west-2 region, and then loads the data into the employees table.

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

The following statement loads data from the files specified in a JSON manifest file named q1_sales.json into the sales table.

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

You can use the LOAD XML FROM S3 statement to load data from XML files stored on an Amazon S3 bucket in one of three different XML formats:

Syntax

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

The LOAD XML FROM S3 statement uses the following required and optional parameters. You can find more details about some of these parameters in LOAD XML Statement in the MySQL documentation.