How can I resolve errors I received while uploading data to or downloading data from Amazon Aurora to Amazon S3?

Last updated: 2020-12-14

I want to upload data to Amazon Aurora from Amazon Simple Storage Service (Amazon S3)

-or-

I want to download data from Amazon Aurora and upload it to Amazon S3.

How can I resolve an error I received while uploading or downloading data to or from Amazon Aurora to Amazon S3?

Short description

To load data as a text file from Amazon Aurora into Amazon S3, run the SELECT INTO OUTFILE S3 in Amazon Aurora.

To load data as a text from Amazon S3 to Amazon Aurora, run the LOAD DATA FROM S3 command in Amazon Aurora.

Some of the common errors you can receive when running the SELECT INTO OUTFILE S3 or LOAD DATA FROM S3 commands include:

  • Error Code: 1871. Missing Credentials
  • Incorrect Command: missing file/prefix/manifest keyword
  • Error code: 1045. Access denied error
  • Error code: 1815. Internal error: Unable to initialize
  • Error Code: 1871. S3 API returned error: Access Denied

First, follow the steps below to run the SELECT INTO OUTFILE S3 or LOAD DATA FROM S3 commands using Amazon Aurora. If you get an error, use the troubleshooting steps for the error code you receive.

Resolution

Run the SELECT INTO OUTFILE S3 or LOAD DATA FROM S3 command

To run the SELECT INTO OUTFILE S3 or LOAD DATA FROM S3 commands using Amazon Aurora, follow the steps below:

1. Create an S3 bucket and copy the ARN.

2. Create an IAM policy for the S3 bucket with permissions. Specify the bucket ARN, and then grant permissions to the objects within the bucket (bucket ARN*).

Run a custom policy similar to the one below if you aren't using a FullS3Access policy:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": "s3:*",
            "Resource": [
                "arn:aws:s3:::<your_bucket_name>",
                "arn:aws:s3:::<your_bucket_name>/*"
            ]
        }
    ]
}</your_bucket_name></your_bucket_name>

3. Create an IAM role for the Amazon Relational Database Service (Amazon RDS) service, and then attach the IAM policy that you created to it.

The trust relationship looks like this:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "",
      "Effect": "Allow",
      "Principal": {
        "Service": "rds.amazonaws.com"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

4. Create a custom DB cluster parameter group for Aurora. Then, edit the aurora_select_into_s3_role or aws_default_s3_role/aurora_load_from_s3_role or aws_default_s3_role to the value of the IAM Role ARN.

5. Attach the IAM role to the DB cluster you created. For more information, see Associating an IAM role with an Amazon Aurora MySQL DB cluster.

6. Log in to your Amazon Aurora DB Cluster.

7. Create a new user by running the following command:

CREATE USER 'testsaveload'@'%' IDENTIFIED BY 'somepassword’;
show grants for 'testsaveload'@'%’;

8. Grant permissions to the user by running the following command:

GRANT SELECT, CREATE, INSERT, DROP ON *.* TO 'testsaveload'@'%’;
GRANT SELECT INTO S3 ON *.* TO 'testsaveload'@'%’;
GRANT LOAD FROM S3 ON *.* TO 'testsaveload'@'%’;

9. Run the SELECT INTO OUTFILE S3 or the LOAD DATA INTO S3 command:

SELECT * from test1.test2034 INTO OUTFILE S3 's3://tests3saveloadaurora/testfile1prefix.part_00000';
LOAD DATA FROM S3 PREFIX 's3-us-east-1://tests3saveloadaurora/testfile1prefix.part_00000' INTO TABLE test3001;
LOAD DATA FROM S3 's3-us-east-1://tests3saveloadaurora/prefix3.part_00000' INTO TABLE test3000;

If you get any errors while running the SELECT INTO OUTFILE S3 or LOAD DATA INTO S3 commands, follow the steps below.

Error Code: 1871. Missing Credentials

You can receive the following error if:

  • The IAM role attachment to the DB cluster is missing. See step 5 above to fix this issue.
  • The role ARN is not specified, and only the role name is listed in the parameter group. See step 2 above to fix this issue.
SELECT * from test1.test2034 INTO OUTFILE S3 '"s3://tests3saveloadaurora/testfile1prefix.part_00000';
Error Code: 1871. S3 API returned error: Missing Credentials: Cannot instantiate S3 Client

Incorrect Command: missing file/prefix/manifest keyword

If you receive the error "Incorrect Command: missing file/prefix/manifest keyword", this means you have not entered the correct keyword for the command to run successfully. To fix this error, enter the keyword for the command.

Error code: 1045. Access denied error

You can receive an error like "1045: Access denied for user ''testsaveload'@'%';" if you enter the command incorrectly. For example, the command below is written incorrectly:

SELECT * from test1.test2034 INTO OUTFILE 's3://tests3saveloadaurora/testfile1prefix.part_00000';

Make sure that the keyword S3 has been entered in the query. The command below is written correctly:

SELECT * from test1.test2034 INTO OUTFILE S3 's3://tests3saveloadaurora/testfile1prefix.part_00000';

Error code: 1815. Internal error: Unable to initialize

LOAD DATA FROM S3 's3://tests3saveloadaurora/testfile1prefix.part_00000' INTO TABLE test3000;
Error Code: 1815. Internal error: Unable to initialize S3Stream

There are a number of reasons you can receive the "Error code: 1815. Internal error: Unable to initialize" error, including the following:

  • Your file does not exist. Confirm that the file exists in your S3 bucket, and that the name you specified in your script matches the name of the file. Case sensitivity is important. Confirm that the S3 bucket name, the folder name, and the object name match exactly what is specified in the load command.
  • You lack the appropriate permissions on the file, folder, or bucket level. Review step 2 to make sure you have set the correct permissions.
  • You might have a syntax error in the LOAD script. Review your LOAD script, and then re-run the command.
  • The S3 bucket and DB cluster are in different regions, and the path to the S3 bucket is missing the region value. For more information on AWS regions, see Amazon S3 endpoints.
  • The network configuration for the reader and writer instances are different. For more information, see Amazon Aurora connection management.

Error Code: 1871. S3 API returned error: Access Denied

You receive the "Error Code: 1871. S3 API returned error: Access Denied" error if you have encryption on your S3 bucket, or if there is an encrypted file inside your S3 bucket. If the ServerSideEncryptionConfigurationExists isn't false, add kms:* to the policy you have attached to the IAM role used to perform the LOAD operation. For example, use the example custom policy below if you aren't using a FULLS3Access policy:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": ["s3:*","kms:*"],
            "Resource": [
                "arn:aws:s3:::<your_bucket_name>",
                "arn:aws:s3:::<your_bucket_name>/*"
            ]
        }
    ]
}</your_bucket_name></your_bucket_name>