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?

Gregor

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.

Larson

listing only returned one field for the table, but you are otherwise correct. I had to write select columnname || ' ' || external_type instead. Thanks. Really helpful – Gregor

Answer 2

I had been doing something similar to @botchniaque's answer in the past, but recently stumbled across a solution in the AWS-Labs' amazon-redshift-utils code package that seems to be more reliable than my hand-spun queries:

amazon-redshift-utils: v_generate_external_tbl_ddl

If you don't have the ability to create a view backed with the ddl listed in that package, you can run it manually by removing the CREATE statement from the start of the query. Assuming you can create it as a view, usage would be:

SELECT ddl
FROM admin.v_generate_external_tbl_ddl
WHERE schemaname = '<external_schema_name>'
    -- Optionally include specific table references:
    --     AND tablename IN ('<table_name_1>', '<table_name_2>', ..., '<table_name_n>')
ORDER BY tablename, seq
;

Insurer

this is very interesting – Gregor

You need have admin schema. What is that ? – Narcotism

The amazon-redshift-utils repository I linked is designed to create views within a schema named admin. There is nothing specific to the admin schema, you would just run CREATE SCHEMA admin; as a user with adequate permissions to create schemas within your cluster if the admin schema doesn't already exist. Realistically you could provision the views outlined in the redshift-utils repository within any schema you desire, assuming you have permissions to provision view objects in that schema. – Insurer

Answer 3

They added show external table now.

SHOW EXTERNAL TABLE external_schema.table_name [ PARTITION ]

SHOW EXTERNAL TABLE my_schema.my_table;

https://docs.aws.amazon.com/redshift/latest/dg/r_SHOW_EXTERNAL_TABLE.html

Dynamite