cannot create a view in redshift spectrum external schema

Question

I am facing an issue in creating a view in an external schema on a spectrum external table. Below is the script I am using to create the view

create or replace view external_schema.test_view as
select id, name from external_schema.external_table with no schema binding;

I'm getting below error

ERROR: Operations on local objects in external schema are not enabled.

Please help in creating view under spectrum external table

Answer 1

External tables are created in an external schema. An Amazon Redshift External Schema references a database in an external Data Catalog in AWS Glue or in Amazon Athena or a database in Hive metastore, such as Amazon EMR.

External schemas are not present in Redshift cluster, and are looked up from their sources. External tables are also only read only for the same reason.

As a result, you will not be able to bind a view that you are creating to a schema not is not stored in the cluster. You can create a view on top of external tables (WITH NO SCHEMA BINDING clause), but the view will reside in a schema local to Redshift.

TL;DR Redshift doesn’t support creating views in external schemas yet, so the view can only reside in a schema local to Redshift.

Replace external_schema with internal_schema as follows:

create or replace view internal_schema.test_view as
select id, name from external_schema.external_table with no schema binding;