amazon-web-services - List of external schemas and tables from Amazon Redshift

Question

I have created external schema and external table in Redshift. I'm able to see external schema name in postgresql using \dn.

What will be query to do it so that i can run it in java? and also the query to get list of external table?

I tried

select * from information_schema.tables where 
table_schema not in ('pg_catalog', 'information_schema') 
and table_schema not like 'pg_toast%' 

but it didn't get the list of external tables.

标签： amazon-web-servicesamazon-redshift

Answer 1

Redshift Spectrum 外部数据库、模式和表有自己的目录视图。

• SVV_EXTERNAL_DATABASES
• SVV_EXTERNAL_SCHEMAS
• SVV_EXTERNAL_TABLES
• SVV_EXTERNAL_PARTITIONS
• SVV_EXTERNAL_COLUMNS

您可以从任何连接查询这些：SELECT * FROM svv_external_schemas;