So far I've been able to do cross-database queries between two databases with data stored inside Redshift (using r3 node type). But when I do a query with a database that has an external schema/table from Redshift Spectrum. I got an error that the relations don't exist (my guess is that it is unreachable) and also I tried to do a late-binding view from the external schema/table and got the following error:
ERROR: Localized temp table cannot be created while getting lbv definition
Creating instead a materialized view for the external table works, but we rather avoid it due to the operability maintenance of those views.
I want to do a cross-database query with data from Redshift Spectrum and Redshift.
Could anyone help me?
You can't add an external table to a data share, even through a late binding view.
https://docs.aws.amazon.com/redshift/latest/dg/considerations.html#limitations-datashare
Amazon Redshift doesn't support adding external schemas, tables, or late-binding views on external tables to datashares.