4. Create External Schema

Steps to create external schema to query external tables using Amazon Redshift Spectrum

StepDo Verify
Create external schema Run the following SQL in Query Editor.

Replace the value for <RedshiftClusterRoleArn> with the value previously determined.

CREATE EXTERNAL SCHEMA IF NOT EXISTS demo
FROM DATA CATALOG DATABASE 'demo' 
IAM_ROLE '<RedshiftClusterRoleArn>';

When the query completes you will see the following in the query results
Check the external schema Run the following SQL

SELECT * FROM svv_external_schemas;

When the query completes you will see the following in the query results

You can review the external table definition by using the following url, open a new tab https://console.aws.amazon.com/glue/home?region=us-east-1#table:name=parquet;namespace=demo

If you see the get started AWS glue page, click on get started. Then click on parquet table.

Observer the following about the table definition:

• The format is parquet

• The Amazon S3 location

• This data set is 160M records

• The AWS glue table lists the column and corresponding data types of columns

Let’s go back to the Amazon Redshift Query editor.