Configure Redshift Spectrum for External Tables

This page explains how to configure Amazon Redshift Spectrum to query Parquet files stored in Amazon S3 using the AWS Glue Data Catalog. Complete this configuration only if you want to persist data quality policy results to Global Storage (S3) while running Redshift Serverless policies in Pushdown engine mode. If you are not using Global Storage (S3) persistence with the Pushdown engine, you can skip this configuration.

If your environment is governed by AWS Lake Formation, also complete the Lake Formation steps in this guide. Otherwise, those steps can be skipped.

1. Create the Spectrum IAM role

1.1 Configure the trust policy

Create an IAM role with the following trust policy:

{ "Version": "2012-10-17", "Statement": [ { "Sid": "AllowRedshiftProvisionedAndServerlessAssume", "Effect": "Allow", "Principal": { "Service": [ "redshift.amazonaws.com", "redshift-serverless.amazonaws.com" ] }, "Action": "sts:AssumeRole", "Condition": { "StringEquals": { "aws:SourceAccount": "<your-account-id>" } } } ] }

1.2 Attach the permissions policy

Attach the following permissions policy to the IAM role:

{ "Version": "2012-10-17", "Statement": [ { "Sid": "GlueCatalogAccess", "Effect": "Allow", "Action": [ "glue:GetDatabase", "glue:GetDatabases", "glue:CreateDatabase", "glue:GetTable", "glue:GetTables", "glue:CreateTable", "glue:UpdateTable", "glue:GetPartition", "glue:GetPartitions", "glue:BatchGetPartition", "glue:CreatePartition", "glue:BatchCreatePartition", "glue:UpdatePartition" ], "Resource": [ "arn:aws:glue:<region>:<account-id>:catalog", "arn:aws:glue:<region>:<account-id>:database/adoc_results", "arn:aws:glue:<region>:<account-id>:table/adoc_results/*" ] }, { "Sid": "LakeFormationCredentialVending", "Effect": "Allow", "Action": [ "lakeformation:GetDataAccess", "lakeformation:GetResourceLFTags", "lakeformation:ListLFTags", "lakeformation:GetLFTag", "lakeformation:SearchTablesByLFTags", "lakeformation:SearchDatabasesByLFTags" ], "Resource": "*" }, { "Sid": "SpectrumBucketAccess", "Effect": "Allow", "Action": [ "s3:GetObject", "s3:PutObject", "s3:ListBucket", "s3:GetBucketLocation", "s3:AbortMultipartUpload" ], "Resource": [ "arn:aws:s3:::<your-bucket-name>", "arn:aws:s3:::<your-bucket-name>/*" ] } ] }

Notes

If AWS Lake Formation is not used, remove the entire LakeFormationCredentialVending statement from the policy.
Replace all placeholder values (<region>, <account-id>, <your-bucket-name>) before creating the role.

2. Associate the IAM role with Redshift

2.1 Provisioned Redshift cluster

Navigate to:

AWS Console → Amazon Redshift → Clusters → Your Cluster → Properties → Cluster permissions → Manage IAM roles → Associate IAM roles

Select the Spectrum IAM role created in Step 1.

2.2 Redshift Serverless

Navigate to:

AWS Console → Amazon Redshift → Serverless → Your Namespace → Security and encryption → Manage IAM roles

Associate the Spectrum IAM role created in Step 1.

3. Configure AWS Lake Formation (optional)

Complete this section only if your Glue Data Catalog is governed by AWS Lake Formation.

3.1 Register the S3 data location

Navigate to:

AWS Console → Lake Formation → Data lake locations → Register location

Configuration:

Amazon S3 path: s3://<your-bucket-name>/<prefix>/
IAM role: Spectrum IAM role created in Step 1
Permission mode: Lake Formation

3.2 Grant database permissions

Navigate to:

Lake Formation → Data lake permissions → Grant

Configuration:

Principal: Spectrum IAM role
Resource type: Named Data Catalog resources
Database: adoc_results
Permissions: DESCRIBE, CREATE_TABLE

Leave Grantable permissions unchecked.

3.3 Grant table permissions

Complete this step after creating the external table (Step 5).

Navigate to:

Lake Formation → Data lake permissions → Grant

Configuration:

Principal: Spectrum IAM role
Database: adoc_results
Table: policy_records (or All tables if preferred)
Permissions: SELECT, INSERT, ALTER

4. Create the external schema

Connect to Redshift using an administrative user and execute:

CREATE EXTERNAL SCHEMA adoc_spectrum FROM DATA CATALOG DATABASE 'adoc_results' IAM_ROLE 'arn:aws:iam::<your-account-id>:role/<spectrum-role-name>' CREATE EXTERNAL DATABASE IF NOT EXISTS;

5. Create the external table

Execute the following SQL statement:

CREATE EXTERNAL TABLE adoc_spectrum.policy_records ( row_id BIGINT, policy_id BIGINT, policy_start_time VARCHAR(20), policy_execution_status VARCHAR(100), policy_type VARCHAR(100), data VARCHAR(65535), rule_result VARCHAR(65535), rule_status SMALLINT ) PARTITIONED BY ( policy_name VARCHAR(1000), policy_execution_date DATE, policy_execution_id BIGINT ) STORED AS PARQUET LOCATION 's3://<your-bucket-name>/<prefix>/' TABLE PROPERTIES ( 'surplus_char_handling'='SET_TO_NULL' );

Notes

The LOCATION must match the S3 prefix that will be configured in the ADOC datasource.
If AWS Lake Formation is enabled, complete Step 3.3 after the table has been created.

Once this configuration is complete, return to Amazon Redshift to configure the Redshift Serverless data source with Pushdown engine mode and enable Global Storage persistence on your data quality policies.

Last updated on

Was this page helpful?