7

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?

Andre Ramos da Silva
  • 83
  • 1
  • 1
  • 5
  • 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 Jun 23 '18 at 04:33
  • 1
    FYI.. https://forums.aws.amazon.com/thread.jspa?threadID=143821 – hadooper Jun 23 '18 at 04:39
  • 1
    See: [Is it possible to run a join between two different AWS Redshift Databases in the same cluster?](https://stackoverflow.com/a/50959302/174777) – John Rotenstein Jun 23 '18 at 08:11

2 Answers2

3

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

Jeff Evans
  • 1,257
  • 1
  • 15
  • 31
1

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
Ouss Gh
  • 53
  • 6