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?

score 12 · Accepted Answer · answered Dec 04 '19 at 07:57

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.

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 — New Alexandria, Dec 05 '19 at 13:56

score 5 · Answer 2 · answered Jan 17 '20 at 15:22

5

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
;

answered Jan 17 '20 at 15:22

John Stark

1,293
1
10
22

this is very interesting – New Alexandria Jan 17 '20 at 20:26
You need have admin schema. What is that ? – bonney Oct 14 '20 at 04:45
The [amazon-redshift-utils repository](https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/README.md) 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. – John Stark Oct 14 '20 at 06:19

score 3 · Answer 3 · edited Feb 18 '23 at 15:19

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

edited Feb 18 '23 at 15:19

Gabe

5,113
11
55
88

answered Feb 18 '23 at 00:37

user433342

859
1
7
26

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

3 Answers3