DEV Community

Md. Mizanur Rahman
Md. Mizanur Rahman

Posted on

Data Export: Aurora-s3-Redshift

#database #aws #tutorial #architecture

Exporting Data from Amazon Aurora MySQL to S3 and Loading It into Amazon Redshift

This guide explains how to export data from Amazon Aurora MySQL into Amazon S3 and then import those CSV files into Amazon Redshift.

The examples use generic testing resources:

  • S3 bucket: test-etl-bucket
  • IAM role: test-etl-role
  • Aurora user: TestEtlUser
  • AWS account ID: 123456789012
  • Aurora version: MySQL-compatible Aurora 3.x
  • AWS Region: us-east-1

Architecture

The data flow is:

Aurora MySQL
    ↓ SELECT INTO OUTFILE S3
Amazon S3
    ↓ Redshift COPY
Amazon Redshift
Enter fullscreen mode Exit fullscreen mode

The IAM role is used by Aurora to write files to S3 and by Redshift to read those files.

Step 1: Create the IAM permissions policy

Create an IAM policy with access to the testing bucket:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "ListBucket",
      "Effect": "Allow",
      "Action": [
        "s3:ListBucket"
      ],
      "Resource": [
        "arn:aws:s3:::test-etl-bucket"
      ]
    },
    {
      "Sid": "ReadWriteObjects",
      "Effect": "Allow",
      "Action": [
        "s3:GetObject",
        "s3:PutObject",
        "s3:AbortMultipartUpload"
      ],
      "Resource": [
        "arn:aws:s3:::test-etl-bucket/*"
      ]
    }
  ]
}
Enter fullscreen mode Exit fullscreen mode

Attach this policy to the IAM role:

test-etl-role
Enter fullscreen mode Exit fullscreen mode

For stricter production security, separate roles are preferable:

  • Aurora export role with PutObject
  • Redshift import role with GetObject

A shared role is acceptable for initial testing.

Step 2: Configure the IAM trust policy

Use the following trust policy if the role needs to support Aurora, provisioned Redshift, and Redshift Serverless:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": [
          "rds.amazonaws.com",
          "redshift.amazonaws.com",
          "redshift-serverless.amazonaws.com"
        ]
      },
      "Action": "sts:AssumeRole"
    }
  ]
}
Enter fullscreen mode Exit fullscreen mode

The role ARN in this example is:

arn:aws:iam::123456789012:role/test-etl-role
Enter fullscreen mode Exit fullscreen mode

If the S3 bucket uses a customer-managed KMS key, also grant the role kms:Decrypt and kms:GenerateDataKey as appropriate, and allow the role in the KMS key policy.

Step 3: Associate the role with Aurora

Creating the IAM role is not enough. It must also be associated with the Aurora cluster.

Open:

Amazon RDS
→ Databases
→ Select the Aurora cluster
→ Connectivity & security
→ Manage IAM roles
→ Add test-etl-role
Enter fullscreen mode Exit fullscreen mode

Wait until the role status becomes Available.

Be sure to select the Aurora cluster, not only its writer instance.

Step 4: Configure the Aurora cluster parameter

For Aurora MySQL version 3, set this DB cluster parameter:

aws_default_s3_role
Enter fullscreen mode Exit fullscreen mode

Set its value to:

arn:aws:iam::123456789012:role/test-etl-role
Enter fullscreen mode Exit fullscreen mode

The parameter must be configured in a custom DB cluster parameter group.

Verify it from MySQL:

SHOW VARIABLES LIKE 'aws_default_s3_role';
Enter fullscreen mode Exit fullscreen mode

Expected result:

arn:aws:iam::123456789012:role/test-etl-role
Enter fullscreen mode Exit fullscreen mode

Reboot the Aurora writer if the parameter change is waiting for a reboot.

Step 5: Grant the Aurora database role

Grant S3 export access to the testing database user:

GRANT AWS_SELECT_S3_ACCESS
TO `TestEtlUser`@`%`;
Enter fullscreen mode Exit fullscreen mode

Make all granted roles active automatically when the user reconnects:

SET DEFAULT ROLE ALL
TO `TestEtlUser`@`%`;
Enter fullscreen mode Exit fullscreen mode

For the current MySQL session, activate the role immediately:

SET ROLE ALL;
Enter fullscreen mode Exit fullscreen mode

Verify it:

SELECT CURRENT_USER(), CURRENT_ROLE();
Enter fullscreen mode Exit fullscreen mode

The result should include:

`AWS_SELECT_S3_ACCESS`@`%`
Enter fullscreen mode Exit fullscreen mode

Application connection pools

MySQL roles are session-specific unless they are configured as default roles. If the application uses a connection pool, SET ROLE ALL and the export must run on the same physical connection.

Example with Node.js:

const connection = await pool.getConnection();

try {
  await connection.query("SET ROLE ALL");

  const [roles] = await connection.query(`
    SELECT CURRENT_USER() AS current_user,
           CURRENT_ROLE() AS current_roles
  `);

  console.log(roles);

  await connection.query(`
    SELECT *
    FROM test_database.test_table
    INTO OUTFILE S3
      's3://test-etl-bucket/exports/test_table/run_001'
    FORMAT CSV HEADER
    OVERWRITE ON
  `);
} finally {
  connection.release();
}
Enter fullscreen mode Exit fullscreen mode

Do not run SET ROLE ALL with one pooled connection and the export with another.

Step 6: Export Aurora data to S3

Run:

SELECT *
FROM test_database.test_table
INTO OUTFILE S3
  's3://test-etl-bucket/exports/test_table/run_001'
FORMAT CSV HEADER
OVERWRITE ON;
Enter fullscreen mode Exit fullscreen mode

Aurora treats the S3 destination as an object prefix. It automatically creates files such as:

exports/test_table/run_001.part_00000
exports/test_table/run_001.part_00001
Enter fullscreen mode Exit fullscreen mode

This naming is expected. The files contain CSV data even though their names do not end with .csv.

Aurora does not provide an option to remove the .part_00000 suffix.

If the selected dataset is empty, Aurora can produce a zero-byte object. Avoid including unwanted empty objects when loading data into Redshift.

Step 7: Associate the role with Redshift

Provisioned Redshift

Open:

Amazon Redshift
→ Provisioned clusters
→ Select the cluster
→ Properties
→ Cluster permissions
→ Manage IAM roles
Enter fullscreen mode Exit fullscreen mode

Associate:

arn:aws:iam::123456789012:role/test-etl-role
Enter fullscreen mode Exit fullscreen mode

The equivalent AWS CLI command is:

aws redshift modify-cluster-iam-roles \
  --cluster-identifier test-redshift-cluster \
  --add-iam-roles \
  arn:aws:iam::123456789012:role/test-etl-role
Enter fullscreen mode Exit fullscreen mode

Redshift Serverless

Open:

Amazon Redshift
→ Redshift Serverless
→ Namespace configuration
→ Select the namespace
→ Security and encryption
→ Permissions
Enter fullscreen mode Exit fullscreen mode

Associate test-etl-role with the namespace. It can optionally be configured as the default IAM role.

Step 8: Create the Redshift destination table

The Redshift table must already exist before running COPY.

Its column order and compatible data types must match the Aurora export:

CREATE SCHEMA IF NOT EXISTS staging;

CREATE TABLE staging.test_table (
    record_id       BIGINT,
    record_name     VARCHAR(500),
    record_status   VARCHAR(100),
    created_at      TIMESTAMP
);
Enter fullscreen mode Exit fullscreen mode

Replace these example columns with the actual columns returned by the Aurora SELECT.

Step 9: Validate the files without loading

Use NOLOAD to validate the CSV format and data types:

COPY staging.test_table
FROM 's3://test-etl-bucket/exports/test_table/run_001'
IAM_ROLE 'arn:aws:iam::123456789012:role/test-etl-role'
FORMAT AS CSV
IGNOREHEADER 1
REGION 'us-east-1'
NOLOAD;
Enter fullscreen mode Exit fullscreen mode

Use IGNOREHEADER 1 only when the Aurora export used:

FORMAT CSV HEADER
Enter fullscreen mode Exit fullscreen mode

If the export did not contain a header, remove IGNOREHEADER 1.

If S3 and Redshift are in the same Region, the REGION option is optional. It is required when the bucket and Redshift are in different Regions.

Step 10: Load the data into Redshift

After NOLOAD succeeds, remove it and run the actual import:

COPY staging.test_table
FROM 's3://test-etl-bucket/exports/test_table/run_001'
IAM_ROLE 'arn:aws:iam::123456789012:role/test-etl-role'
FORMAT AS CSV
IGNOREHEADER 1
REGION 'us-east-1';
Enter fullscreen mode Exit fullscreen mode

If the IAM role is configured as Redshift’s default role, the command can use:

IAM_ROLE default
Enter fullscreen mode Exit fullscreen mode

For example:

COPY staging.test_table
FROM 's3://test-etl-bucket/exports/test_table/run_001'
IAM_ROLE default
FORMAT AS CSV
IGNOREHEADER 1;
Enter fullscreen mode Exit fullscreen mode

Redshift loads every S3 object whose key matches the specified prefix.

Therefore:

s3://test-etl-bucket/exports/test_table/run_001
Enter fullscreen mode Exit fullscreen mode

can load:

run_001.part_00000
run_001.part_00001
run_001.part_00002
Enter fullscreen mode Exit fullscreen mode

Loading multiple files safely

If every object under a folder has the same table structure, the complete folder can be loaded:

COPY staging.test_table
FROM 's3://test-etl-bucket/exports/test_table/'
IAM_ROLE default
FORMAT AS CSV
IGNOREHEADER 1;
Enter fullscreen mode Exit fullscreen mode

Do not load an entire folder when it contains:

  • Files for different destination tables
  • Different column structures
  • Temporary files
  • Previous exports
  • Unwanted zero-byte files
  • Manifest files mixed with data files

In these cases, use a specific prefix or a Redshift manifest.

Example manifest:

{
  "entries": [
    {
      "url": "s3://test-etl-bucket/exports/test_table/run_001.part_00000",
      "mandatory": true
    },
    {
      "url": "s3://test-etl-bucket/exports/test_table/run_001.part_00001",
      "mandatory": true
    }
  ]
}
Enter fullscreen mode Exit fullscreen mode

Load it with:

COPY staging.test_table
FROM 's3://test-etl-bucket/manifests/test_table_run_001.json'
IAM_ROLE default
FORMAT AS CSV
IGNOREHEADER 1
MANIFEST;
Enter fullscreen mode Exit fullscreen mode

Avoiding duplicate data

Redshift COPY appends rows. It does not automatically replace existing rows or perform an upsert.

For a complete refresh:

BEGIN;

TRUNCATE TABLE staging.test_table;

COPY staging.test_table
FROM 's3://test-etl-bucket/exports/test_table/run_001'
IAM_ROLE default
FORMAT AS CSV
IGNOREHEADER 1;

COMMIT;
Enter fullscreen mode Exit fullscreen mode

For incremental imports, load into a staging table first and then use MERGE based on the business key.

Common errors

Aurora error 1227: SELECT INTO S3 privilege required

Example:

Access denied; you need the SELECT INTO S3 privilege
Enter fullscreen mode Exit fullscreen mode

Cause:

The AWS_SELECT_S3_ACCESS role is granted but not active.

Solution:

SET ROLE ALL;
SELECT CURRENT_ROLE();
Enter fullscreen mode Exit fullscreen mode

For a permanent solution:

SET DEFAULT ROLE ALL TO `TestEtlUser`@`%`;
Enter fullscreen mode Exit fullscreen mode

Aurora error 63985: Missing Credentials

Example:

S3 API returned error:
Missing Credentials: Cannot instantiate S3 Client
Enter fullscreen mode Exit fullscreen mode

Possible causes:

  • aws_default_s3_role is empty or incorrect
  • The role is not associated with the Aurora cluster
  • The associated role is not in Available status
  • The IAM trust policy does not allow rds.amazonaws.com
  • The parameter was changed but the writer still requires a reboot

Export works in terminal but fails from application code

Cause:

The terminal session ran SET ROLE ALL, but the application opened a different connection where the role was inactive.

Solution:

  • Configure the AWS role as a default database role, or
  • Execute SET ROLE ALL on every new application connection

Redshift S3 AccessDenied

Check the following:

  • The role is associated with the correct Redshift cluster or namespace
  • The COPY command uses the correct role ARN
  • The role has s3:ListBucket and s3:GetObject
  • The bucket policy does not contain an explicit deny
  • The KMS key policy allows the role when SSE-KMS is used
  • Redshift Serverless trust includes redshift-serverless.amazonaws.com

Redshift data-format errors

Validate first:

COPY staging.test_table
FROM 's3://test-etl-bucket/exports/test_table/run_001'
IAM_ROLE default
FORMAT AS CSV
IGNOREHEADER 1
NOLOAD;
Enter fullscreen mode Exit fullscreen mode

For provisioned Redshift, inspect recent load errors:

SELECT *
FROM stl_load_errors
ORDER BY starttime DESC
LIMIT 20;
Enter fullscreen mode Exit fullscreen mode

Final checklist

Before running the pipeline, confirm:

  • The IAM role has S3 read and write permissions
  • The role trusts Aurora and the relevant Redshift service
  • The role is associated with the Aurora cluster
  • aws_default_s3_role contains the correct role ARN
  • AWS_SELECT_S3_ACCESS is active for TestEtlUser
  • The role is associated with Redshift
  • The Redshift destination table already exists
  • The column order matches the CSV data
  • Header handling is consistent
  • The selected S3 prefix contains only the intended files
  • Duplicate-load behavior has been considered

Official references:

Top comments (0)