Sometimes you just want to know if a particular external table or schema exists in Amazon Redshift (Spectrum). This article outlines various alternatives to achieve that.
First, if you’d like to list the tables that match your criteria, you can do that by querying SVV_EXTERNAL_TABLES.
SELECT * FROM SVV_EXTERNAL_TABLES
This table has some interesting columns that you can use to discover if a particular schema or table exists.
- schemaname: name of the schema
- tablename: name of the table
- location: location of the underlying files on the S3 bucket
In the following code, we filter on the schema name, table name, and even the location of the underlying files. Adjust to your use case.
SELECT * FROM SVV_EXTERNAL_TABLES WHERE location LIKE '%<YOUR_S3_BUCKET_PREFIX>%' -- Filter on bucket prefix AND schemaname = '<YOUR_SCHEMA_NAME>' -- Filter on schema name AND tablename = '<YOUR_TABLE_NAME>' -- Filter on table name
If you’re simply looking for a true/false, instead of the rows that match your criteria, you can wrap everything in an EXISTS or evaluate the COUNT of the rows.
/* Option 1: Evaluate the row count: */ SELECT COUNT(*) > 0 FROM ( SELECT * FROM SVV_EXTERNAL_TABLES WHERE tablename = '<YOUR_TABLE_NAME>' ); /* Option 2: use an EXISTS condition: */ SELECT EXISTS ( SELECT * FROM SVV_EXTERNAL_TABLES WHERE tablename = '<YOUR_TABLE_NAME>' )
There we go. A quick way to check if an external table exists in redshift.