How to Copy AWS S3 Data to Redshift Using the COPY Command

When working with Amazon Redshift, you often need to import data from AWS S3 buckets to perform analytics or other operations. Redshift’s COPY command is designed to make this process seamless and efficient. In this blog, we’ll go through the steps to copy data from an S3 bucket into a Redshift table.

AWS S3 Data to Redshift Using the COPY Command

Prerequisites

1. AWS Account: Ensure you have an AWS account with access to both S3 and Redshift.
2. Redshift Cluster: Set up an Amazon Redshift cluster.
3. S3 Bucket: Store the source data file(s) in an S3 bucket.
4. IAM Role: Create an IAM role with S3 read permissions and attach it to your Redshift cluster.

Step-by-Step Guide

1. Prepare Your Data in S3

• Ensure your data is in a CSV, JSON, Parquet, or other supported format.
• Upload the file(s) to an S3 bucket.
• Note the S3 bucket name and the object key (path) of your data file.

2. Create a Table in Redshift

Create a table in your Redshift database that matches the structure of your S3 data.

CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2),
    customer_id INT
);

3. Create an IAM Role for S3 Access

• Go to the IAM Console in AWS.
• Create a role with the AmazonS3ReadOnlyAccess policy.
• Copy the ARN (Amazon Resource Name) of the role.

Attach this role to your Redshift cluster:

• Navigate to your Redshift cluster in the AWS Management Console.
• Choose Modify and attach the IAM role.

4. Use the COPY Command

The COPY command transfers data from S3 into your Redshift table efficiently.

Here’s the basic syntax:

COPY table_name
FROM 's3://your-bucket-name/your-file-path'
IAM_ROLE 'arn:aws:iam::your-account-id:role/your-role-name'
FORMAT AS CSV
IGNOREHEADER 1;

Example:

COPY sales
FROM 's3://my-sales-data/sales.csv'
IAM_ROLE 'arn:aws:iam::123456789012:role/MyRedshiftRole'
FORMAT AS CSV
IGNOREHEADER 1;

Explanation:

• COPY sales: Specifies the Redshift table to load data into.
• FROM 's3://...': Points to the S3 location of the source data.
• IAM_ROLE: Provides Redshift access to S3 using the IAM role.
• FORMAT AS CSV: Specifies the file format.
• IGNOREHEADER 1: Skips the header row in the CSV file.

5. Verify the Data

After running the COPY command, verify the data using a simple query:

SELECT * FROM sales LIMIT 10;

Troubleshooting Tips

1. Access Denied Errors: Ensure the IAM role has proper permissions for the S3 bucket.
2. Data Format Issues: Verify the file format matches the Redshift table structure.
3. Encoding Issues: Use options like ENCODING AS in the COPY command for special cases.

The Redshift COPY command is a powerful and efficient way to load large datasets from S3 into Redshift. By following this guide, you can streamline your data import process and focus on analyzing your data.

Got questions or additional tips? Share them in the comments below!