[SOLVED] Is it feasible to perform a cross database federated query in redshift?

Is it feasible to perform a cross database federated query in redshift?

while cross database queries (in ra3 node based clusters) and federated queries individually seem to work, together it seems that they won't work. I am most probably missing something, hence the question. Below are the details:

RDS Aurora MySQL has a database say DB1
Redshift cluster say c1 has databases say RSDB1 and RSDB2
I am able to configure DB1 as external schema in RSDB1 say ext_mdb and able to query as well ex: as below

select count(1) from ext_mdb.Table2;

select count(1) from RSDB1.ext_mdb.Table2; # this also works

I am also able to use the cross databases query schema RSDB1.public.Table1 while I am connected to RSDB2.
When I try querying RSDB1.ext_mdb.Table2 from RSDB2 fails with below error

select count(1) from RSDB1.ext_mdb.Table2; # this doesn't from RSDB2

ERROR: Relation Table2 does not exist in the database.

Solution

It seems like a limitation for cross database queries in redshift at the moment from what I understood reading this : https://docs.aws.amazon.com/redshift/latest/dg/cross-database_limitation.html

where it says

Amazon Redshift doesn't support query catalog objects on AWS Glue or federated databases. To query these objects, first create external schemas that refer to those external data sources in each database.