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:
JSON
{
"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": ""
}
}
}
]
}
1.2 Attach the permissions policy
Attach the following permissions policy to the IAM role:
JSON
{
"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:::catalog",
"arn:aws:glue:::database/adoc_results",
"arn:aws:glue:::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:::",
"arn:aws:s3:::/*"
]
}
]
}
Notes:
- If AWS Lake Formation is not used, remove the entire
LakeFormationCredentialVendingstatement 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:
SQL
CREATE EXTERNAL SCHEMA adoc_spectrum
FROM DATA CATALOG
DATABASE 'adoc_results'
IAM_ROLE 'arn:aws:iam:::role/'
CREATE EXTERNAL DATABASE IF NOT EXISTS;
5. Create the external table
Execute the following SQL statement:
SQL
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:////'
TABLE PROPERTIES (
'surplus_char_handling'='SET_TO_NULL'
);
Notes:
- The
LOCATIONmust 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.
Send a comment