Redshift__在一个external schema外部架构下创建external table外部表后，其他external schema也自动生成了一样的external table外部表

官方文档
https://docs.aws.amazon.com/zh_cn/redshift/latest/dg/c-using-spectrum.html#c-spectrum-considerations

Amazon Redshift 外部 schema 引用了外部数据目录中的外部数据库。您可在 Amazon Redshift、Amazon Athena、AWS Glue Data Catalog 或在 Apache Hive 元存储（如 Amazon EMR）中创建外部数据库。

只有创建external schema时使用不同的database，则在这些不同的external schema下创建一个外部表，其他的external schema不会自动出现生成相同的外部表，也就是说，其实external table外部表是和external database对应的，同一个external database下所有的external schema有相同的external table
create external schema spectrum_schema from data catalog
database ‘spectrum_db’
iam_role ‘arn:aws:iam::123456789012:role/MySpectrumRole’
create external database if not exists;

官方技术支持的回复
Dear Customer,

Warm greetings from AWS Support Engineering. I hope you are doing well. Thank you for reaching out to us with your concern. My name is Gurpreet and I will be helping you with your case.

From the case notes and our discussion, I could understand that you found an issue where, when user creates or drops an external table under specific external schema (like spectrum), then other external schema will also generate or lose the same table. Please correct me if I misunderstood your concern in any way.

In this context, I would like to inform you that I have tested a similar scenario at my test environment and was able to replicate the similar scenario at my end. Please find below the quick summary steps:

1. Created 2 external schemas over the same Athena database - spectrum_schema_1 & spectrum_schema_2

2. Created an external table in spectrum_schema_1 and it was created in both the external schemas:

dev=# create external table spectrum_schema_1.newtbl(id int, doj varchar) location 's3://<test-buck>/f1';
CREATE EXTERNAL TABLE

dev=# select redshift_database_name, schemaname, tablename, location from svv_external_tables where tablename='newtbl';
 redshift_database_name |    schemaname     | tablename |      location
------------------------+-------------------+-----------+--------------------
 dev                    | spectrum_schema_1 | newtbl    | s3://<test-buck>/f1
 dev                    | spectrum_schema_2 | newtbl    | s3://<test-buck>/f1
(2 rows)

2. Dropped the external table from spectrum_schema_1 and it was dropped from both the external schemas:

dev=# drop table spectrum_schema_1.newtbl;
DROP TABLE

dev=# select redshift_database_name, schemaname, tablename, location from svv_external_tables where tablename='newtbl';
 redshift_database_name | schemaname | tablename | location
------------------------+------------+-----------+----------
(0 rows)

===================

However, the above behavior is not observed if the external schemas are created over two different Athena databases. Please find below the quick summary steps:

1. Created another external schema over a different Athena database - spectrum_schema_3.

2. Created an external table in spectrum_schema_3 and it is only created in one external schema (i.e. - spectrum_schema_3):

dev=# select redshift_database_name, schemaname, tablename, location from svv_external_tables where tablename='newtbl';
 redshift_database_name |    schemaname     | tablename |      location
------------------------+-------------------+-----------+--------------------
 dev                    | spectrum_schema_3 | newtbl    | s3://<test-audit-logs>/f1/logs

(1 row)

Therefore, from the above replications it can be inferred that in case the external schemas over the same database and we create a table in one of the external schema then it is created in other external schemas as well because the external table is being created at the Athena database from where other schemas are fetching/reading the data.

However, in case the external schemas created over the different Athena databases, it doesn’t exhibit the above behavior because the external table is being created in the Athena database which is not scanned by other external schema. So, I would like to inform you that the behavior being observed is expected.

With that said, in case you have any other queries/concerns related to this issue. I would be happy to hop on a call to discuss this further. And, I request you to please provide your available time slots (Preferably 2-3 time slots) along with timezone so that we can schedule a call at your convenience.