Is there a way to describe an external/spectrum table via redshift?

Question

In AWS Athena you can write

SHOW CREATE TABLE my_table_name;

and see a SQL-like query that describes how to build the table's schema. It works for tables whose schema are defined in AWS Glue. This is very useful for creating tables in a regular RDBMS, for loading and exploring data views.

Interacting with Athena in this way is manual, and I would like to automate the process of creating regular RDBMS tables that have the same schema as those in Redshift Spectrum.

How can I do this through a query that can be run via psql? Or is there another way to get this via the aws-cli?

Answer 1

Redshift Spectrum does not support SHOW CREATE TABLE syntax, but there are system tables that can deliver same information. I have to say, it's not as useful as the ready to use sql returned by Athena though.

The tables are

• svv_external_schemas - gives you information about glue database mapping and IAM roles bound to it
• svv_external_tables - gives you the location information, and also data format and serdes used
• svv_external_columns - gives you the column names, types and order information.

Using that data, you could reconstruct the table's DDL.

For example to get the list of columns and their types in the CREATE TABLE format one can do:

select distinct
       listagg(columnname || ' ' || external_type, ',\n') 
             within group ( order by columnnum ) over ()
from svv_external_columns
where tablename = '<YOUR_TABLE_NAME>'
and schemaname = '<YOUR_SCHEM_NAME>'

the query give you the output similar to:

col1 int, 
col2 string,
...

*) I am using listagg window function and not the aggregate function, as apparently listagg aggregate function can only be used with user defined tables. Bummer.