Redshift Spectrum: Querying S3 from Redshift

A warehouse and a data lake usually sit side by side on AWS: modelled, frequently used tables in Amazon Redshift, and a much larger body of files in Amazon S3. Redshift Spectrum is the bridge between them. It lets Redshift query structured and semi-structured data in S3 files without loading it into Redshift tables, with most of the data staying in S3 (Redshift docs: Redshift Spectrum).

The practical value is a single SQL statement that joins last night's loaded sales with five years of archived events, without a copy of those five years in the warehouse.

External schemas and tables

A Spectrum table is an external table: a definition of the files' structure and location, registered in an external data catalog. The data is not in Redshift. The catalog can be the AWS Glue Data Catalog, the catalog Athena uses, or a Hive metastore (Redshift docs: Spectrum overview).

That shared catalog is the important detail. A table defined by a Glue crawler, or created in Athena, is the same table Redshift sees once an external schema points at the catalog database. There is one definition of the lake, not one per tool.

Written from the documentation and not run; names are placeholders:

-- Point Redshift at a Glue Data Catalog database
CREATE EXTERNAL SCHEMA lake
FROM DATA CATALOG
DATABASE 'analytics_lake'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftSpectrumRole'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

-- Describe partitioned Parquet files in S3
CREATE EXTERNAL TABLE lake.trips (
  vendor_id      INTEGER,
  pickup_at      TIMESTAMP,
  pickup_zone_id INTEGER,
  payment_type   SMALLINT,
  fare_amount    DOUBLE PRECISION
)
PARTITIONED BY (pickup_month CHAR(7))
STORED AS PARQUET
LOCATION 's3://amzn-s3-demo-bucket/trips/'
TABLE PROPERTIES ('numRows'='35000000');

-- Register a partition
ALTER TABLE lake.trips ADD
PARTITION (pickup_month = '2024-01')
LOCATION 's3://amzn-s3-demo-bucket/trips/pickup_month=2024-01/';

-- Join lake data with a table stored in Redshift
SELECT z.borough,
       COUNT(*)           AS trips,
       SUM(t.fare_amount) AS fares
FROM lake.trips AS t
JOIN dim_zone AS z ON z.zone_id = t.pickup_zone_id
WHERE t.pickup_month = '2024-01'
GROUP BY z.borough;

Four points in that script come straight from the guide:

• The IAM role gives Redshift permission to read the catalog and the S3 files on your behalf. It is attached to the cluster.
• The partition key is not a column in the files. The guide states that it cannot be the name of a table column; its value comes from the folder.
• Partitions must be added, with ALTER TABLE ... ADD PARTITION, up to 100 in one statement when using the Glue catalog. Until then the files are invisible to queries.
• The row count is a hint you supply through the numRows table property. Redshift does not analyse external tables, so without it the planner assumes external tables are the large ones and local tables the small ones.

External tables are read-only from the query side: Spectrum does not support update operations on them.

Where the work happens

On the node types that use Spectrum, the scan of S3 runs on servers outside your cluster. Redshift pushes compute-intensive tasks such as predicate filtering and aggregation down to that layer, so a Spectrum query uses much less of the cluster's capacity than its data volume suggests. Only the rows that survive come back to the cluster for joins and final processing.

What can be pushed down therefore decides performance (Redshift docs: Spectrum query performance):

The query plan shows it. Steps named S3 Seq Scan and S3 HashAggregate ran against the data in S3; a large row count flowing out of the S3 Query Scan step into the cluster means filters or aggregation were not pushed down, and the cluster is doing the heavy work.

A newer arrangement

The description above applies to DC2 and RA3 nodes. The current documentation notes that RG instances and Redshift Serverless include an integrated data lake query engine that runs on their own compute resources instead of a separate Spectrum fleet. The SQL is the same; what changes is where the scan runs and how it is billed.

Making Spectrum queries efficient

The guide's recommendations are short:

• Use Parquet. Spectrum can eliminate unneeded columns; with text files it has to scan the entire file.
• Select few columns.
• Partition on your most common filter and filter on the partition column, so partitions are pruned.
• Use multiple files of similar size, larger than 64 MB, to allow parallel processing and avoid size skew.
• Keep large fact tables in S3 and small, frequently used dimensions in Redshift.
• Supply statistics: set numRows, or generate column statistics in Glue, so the planner orders joins sensibly.

These are the same habits that make Athena queries cheap; see partitioning data in S3 and Parquet and compression.

What it costs

Redshift Spectrum is priced by the number of bytes scanned in S3, in addition to the cluster. On RG nodes Spectrum is not required, and on Redshift Serverless, queries on open file formats in S3 are billed as part of the RPU-hours the query consumes (AWS: Redshift pricing). Whichever model applies, scanning less costs less, so the practices above reduce the bill as well as the run time.

The cluster and the bucket must be in the same Region.

Load it or leave it in S3?

A common pattern is hot and cold. Recent months live in a Redshift table; older months are unloaded to partitioned Parquet in S3; a view created WITH NO SCHEMA BINDING unions the two. Dashboards hit the fast local table, and the occasional long-range analysis still works. A materialized view over an external table is another option the documentation suggests for repeated queries.

For loading the hot side, see loading data into Redshift with COPY. If there is no warehouse at all and the need is only to query S3, Athena does that without a cluster; Athena vs Redshift compares them. For the warehouse itself, see Amazon Redshift explained.

Amazon Redshift, S3 data lakes and the Glue Data Catalog are covered in the Data Engineering course.

Sources

• Amazon Redshift Spectrum (Amazon Redshift Database Developer Guide) — AWS Documentation
• Amazon Redshift Spectrum overview (Amazon Redshift Database Developer Guide) — AWS Documentation
• Amazon Redshift Spectrum query performance (Amazon Redshift Database Developer Guide) — AWS Documentation
• External tables for Redshift Spectrum (Amazon Redshift Database Developer Guide) — AWS Documentation
• Amazon Redshift pricing (Amazon Web Services) — Amazon Web Services