AWS › Amazon Redshift & Data Warehousing

What is Redshift Spectrum, and how does it let you query data outside your cluster?

Intermediate

Answer

Redshift Spectrum lets you run SQL queries directly against data stored in Amazon S3 without first loading it into your Redshift cluster, using the same Redshift query engine and syntax, which is especially useful for querying huge historical datasets that would be too expensive to store permanently inside the cluster itself, while still allowing you to join that external data with tables that do live inside Redshift.

CREATE EXTERNAL TABLE spectrum_schema.sales (
  sale_id INT,
  amount DECIMAL
) STORED AS PARQUET LOCATION 's3://my-bucket/sales-data/';

Real-world example A company keeps its most recent year of transaction data directly in Redshift for fast frequent queries, while using Redshift Spectrum to query many years of older archived data sitting in S3 only when a long term historical report is needed.

Common follow-ups: How does Redshift Spectrum pricing differ from standard Redshift query costs?;What file formats does Redshift Spectrum support for external tables?

Amazon Athena;S3 & Storage