How to select tables from different databases in Redshift

6
votes

I need to get select data from 2 different tables that they are in 2 different databases. Using SQL Server I would do SELECT * FROM database.schema.table, but I tried that and it didn't work using Amazon Redshift.

Does anyone knows how to achieve that?

2
sqlamazon-redshift
I don't think there is a way, when you connect to Redshift you pads database name in connection string, which means you are bound to access data only from that database in that Redshift cluster. – hadooper
FYI.. forums.aws.amazon.com/thread.jspa?threadID=143821 – hadooper
See: Is it possible to run a join between two different AWS Redshift Databases in the same cluster? – John Rotenstein

2 Answers

1
votes

Redshift does not support cross db references. i.e : you can't call anything outside of the current database. Two solutions here :

  1. move to another DWH that supports cross db references : snowflake or BQ mainly
  2. copy data to s3 and then load it to your target db
1
votes

As of late 2020, this is possible via "cross-database queries". For more information, see the official documentation here.