Query S3 Data with Redshift Spectrum

1. What is Redshift Spectrum?

Redshift Spectrum is not a separate service but a feature of Amazon Redshift. It allows you to run SQL queries against data stored in S3 as if it were in local Redshift tables. This provides tremendous flexibility in how you manage and access your data.

To understand the motivation and common use cases, let's watch a short video.

As the video explained, you can combine "local" tables (data stored on Redshift nodes) and "external" tables (data in S3) in a single query. This is incredibly powerful.

Given your proficiency with Amazon Athena, you can think of Spectrum as a similar concept, but with a key difference:

• Athena is a standalone, serverless query service for S3.
• Redshift Spectrum is a feature within a Redshift cluster that allows it to query S3, crucially enabling joins between your Redshift data warehouse tables and your S3 data lake tables.

2. The Architecture: How It All Works

To query S3 data from Redshift, several AWS services must work together. The implementation relies on creating a logical link between your Redshift cluster and your data lake's metadata catalog.

Here are the core components:

1. Amazon Redshift Cluster: The query engine.
2. Amazon S3: Your data lake, where the data files (e.g., Parquet, ORC, CSV) are stored.
3. AWS Glue Data Catalog: A central metadata repository. A Glue Crawler typically scans your S3 data to automatically infer the schema and create table definitions in the catalog.
4. IAM Role: The security glue that grants the Redshift cluster permission to access the Glue Data Catalog and the underlying data in S3.

This diagram illustrates the relationship between the components:

3. Implementing Redshift Spectrum: A Step-by-Step Guide

Implementing Spectrum involves a clear, four-step process. The AWS documentation provides a definitive guide, and a video demonstration will walk us through the practical application of these steps.

First, let's get familiar with the official process.

Now that you have reviewed the official steps, let's see them in action. The same video we watched earlier provides a complete hands-on demonstration.

Let's recap the most critical part: the SQL command that creates the bridge.

The CREATE EXTERNAL SCHEMA Command

This is the command that connects your Redshift cluster to your Glue Data Catalog.

CREATE EXTERNAL SCHEMA spectrum_schema_name
FROM DATA CATALOG
DATABASE 'glue_database_name'
IAM_ROLE 'arn:aws:iam::123456789012:role/YourRedshiftSpectrumRole'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

Let's break it down:

• CREATE EXTERNAL SCHEMA spectrum_schema_name: This defines a new schema inside your Redshift database. This schema will contain pointers to your S3 tables, not the actual data.
• FROM DATA CATALOG DATABASE 'glue_database_name': This tells Redshift which database in your AWS Glue Data Catalog to map to.
• IAM_ROLE '...': This specifies the ARN of the role you created, which gives Redshift the necessary permissions to perform the action.

Once this command is executed, all the tables defined in your Glue database 'glue_database_name' will appear as tables within the Redshift schema spectrum_schema_name. You can then query them directly.

CREATE EXTERNAL SCHEMA sales_external
FROM DATA CATALOG
DATABASE 'sales_db'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftS3Access';

SELECT
    SUM(t.sale_amount) as total_sales
FROM
    sales_external.transactions t
JOIN
    public.stores s ON t.store_id = s.store_id
WHERE
    s.store_city = 'Boston';

Conclusion

In this lesson, we explored how to break down the barrier between your data warehouse and your data lake. Redshift Spectrum is a powerful feature for data engineers, enabling flexible, cost-effective, and powerful analytical queries across your entire data estate.

Key Takeaways:

• Redshift Spectrum allows you to query data in S3 directly from your Redshift cluster, enabling a "lake house" architecture.
• It is ideal for querying large, infrequently accessed datasets and for joining "hot" data in Redshift with "cold" data in S3.
• The implementation requires four key steps: cataloging S3 data in AWS Glue, creating an IAM role with S3 and Glue permissions, associating the role with your cluster, and creating an EXTERNAL SCHEMA that points to the Glue database.
• Once the external schema is created, you can query S3 data and join it with local Redshift tables using standard SQL.

Next Up:
Now that we can execute both fast, local queries and potentially long-running external queries against S3, managing cluster resources becomes even more important. In our next lesson, we will dive into how to "Configure workload management (WLM) queues for query prioritization and resource allocation," ensuring that critical queries get the resources they need to meet performance SLAs.