Loading Redshift from S3 with COPY

1. Why COPY? The Power of Parallel Ingestion

Before diving into the syntax, it's essential to understand why the COPY command is the standard for loading data into Redshift. While you can use standard SQL INSERT statements, they are highly inefficient for loading data at scale.

• INSERT statements are processed by the leader node as single-threaded operations. Loading millions of rows one by one (or even in small batches) creates a massive bottleneck.
• The COPY command is designed to leverage Redshift's Massively Parallel Processing (MPP) architecture. It instructs each compute node to load a portion of the data from S3 directly and in parallel.

The performance difference is not subtle; it's orders of magnitude.

2. The Anatomy of a COPY Command

At its core, a COPY command requires just three pieces of information: the target table, the data source, and the authorization to access that source. Let's watch a practical video that walks through the entire process of building and executing a COPY command to load a CSV file from S3.

As you saw in the video, the basic structure looks like this:

COPY table_name
FROM 's3://your-bucket-name/folder/file_prefix'
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/YourRedshiftRole'
[options];

The most critical parts are:

• FROM: Specifies the S3 path to your data. This can be a specific file, a prefix to load multiple files, or a manifest file.
• CREDENTIALS: Provides the authorization for Redshift to access S3. Using an IAM role is the secure, recommended best practice. It avoids hardcoding access keys and simplifies permission management.

3. Optimizing COPY for Maximum Performance

To truly unlock the power of the COPY command, you need to prepare your data in a way that maximizes parallelism.

Split Your Files

The single most effective optimization is to split your source data into multiple files in S3. The ideal number of files is a multiple of the number of slices in your Redshift cluster. This allows each slice to work on a file concurrently.

Let's watch a short clip that explains this concept perfectly.

Key Guidelines for File Splitting:

• Aim for a file size between 1 MB and 1 GB after compression.
• The number of files should be a multiple of your cluster's slice count. You can find the slice count for different node types in the AWS documentation.

Compress Your Data

Always compress your data files using a supported format like GZIP, ZSTD, or BZIP2. The COPY command automatically uncompresses the files during the load. This has two benefits:

1. Reduces storage costs in S3.
2. Reduces the amount of data transferred from S3 to Redshift, which speeds up the load.

4. Handling Diverse Formats with COPY Parameters

Real-world data is rarely perfect. The COPY command provides a rich set of parameters to handle different file formats, data quirks, and errors. The official AWS tutorial on this topic is the best resource for learning these options.

The tutorial you just read is dense with practical information. For the exam, you should be comfortable combining these parameters to solve a specific data loading problem.

COPY product_reviews
FROM 's3://my-data-lake/reviews/product_reviews.csv.gz' -- The prefix will match all files starting with this name
CREDENTIALS 'aws_iam_role=arn:aws:iam::[ACCOUNT_ID]:role/RedshiftS3AccessRole'
FORMAT AS CSV
GZIP
IGNOREHEADER 1
NULL AS 'N/A'
DATEFORMAT 'YYYY-MM-DD';

{
  "entries": [
    {"url":"s3://my-data-lake/reviews/file_01.csv.gz", "mandatory":true},
    {"url":"s3://my-data-lake/reviews/file_02.csv.gz", "mandatory":true},
    ...
    {"url":"s3://my-data-lake/reviews/file_50.csv.gz", "mandatory":true}
  ]
}

COPY product_reviews
FROM 's3://my-data-lake/reviews/reviews.manifest'
CREDENTIALS 'aws_iam_role=arn:aws:iam::[ACCOUNT_ID]:role/RedshiftS3AccessRole'
FORMAT AS CSV
GZIP
IGNOREHEADER 1
NULL AS 'N/A'
DATEFORMAT 'YYYY-MM-DD'
MANIFEST;

Conclusion

In this lesson, we focused on the most efficient and scalable method for getting data into Amazon Redshift. By mastering the COPY command and its rich set of parameters, you can build robust and performant data ingestion pipelines.

Key Takeaways:

• The COPY command is the standard for bulk data loading due to its use of Redshift's MPP architecture.
• Always use an IAM role for secure, manageable credentials.
• For optimal performance, split data into multiple, compressed files to enable parallel loading.
• Use parameters like FORMAT AS CSV, DELIMITER, IGNOREHEADER, NULL AS, and DATEFORMAT to match the structure of your source data.
• For troubleshooting, use MAXERROR in combination with querying the STL_LOAD_ERRORS table. For precise control over which files are loaded, use a MANIFEST file.

Next Up:
We have now designed our tables and loaded them with data from S3. But what if you want to query data in S3 without loading it into Redshift first? This is a common requirement in a modern data lakehouse architecture. In our next lesson, we will explore this by learning how to "Implement Redshift Spectrum to query data directly in your S3 data lake." This will bridge the gap between your data warehouse and your data lake.