Can I alias an external table in redshift to remove the schema name?

Question

We want to migrate tables to Spectrum, which requires defining an external schema

create external schema spectrum 
from data catalog 
database 'spectrumdb' 
iam_role 'my_iam_role'
create external database if not exists;

I created an external table in Redshift like this:

create external table spectrum.my_table(
 id bigint,
 accountId bigint,
 state varchar(65535),
 ) stored as parquet
 location 's3://some_bucket/my_table_files';

Is it possible to alias the table such that when querying it, I can call it my_table_alias instead of spectrum.my_table? Basically, we want to make the change to external tables opaque to clients of our Redshift instance (this means we can't change the table names). Thanks so much for your help!

Answer 1

Redshift does not have aliases, your best option is to create a view.

You need to use WITH NO SCHEMA BINDING option while creating the view since the view is on an external table.

If you like to not specify schema names or you have a requirement like this create the view(s) in public schema or set the users default schema to the schema where the views are

alter user .. set search_path to ..

Additional benefits of using a view to access an external table are, you can

• rename columns to be more user friendly
• add or remove columns with view definition
• change data types and/or date/time formats
• you will have the ability to change name/structure of the external table without effecting user access

Let me know if this answers your question.