Menu
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?
368
In order to list or show all of the tables in a Redshift database, you'll need to query the PG_TABLE_DEF systems table. An interesting thing to note is the PG_ prefix. This is because Redshift is based off Postgres, so that little prefix is a throwback to Redshift's Postgres origins.
Data is organized across multiple databases in Amazon Redshift clusters to support multi-tenant configurations. However, you often need to query and join across these datasets by allowing read access.
With cross-database queries, you can seamlessly query data from any database in the cluster, regardless of which database you are connected to. Cross-database queries can eliminate data copies and simplify your data organization to support multiple business groups on the same cluster.
To use the query editor on the Amazon Redshift consoleOn the navigation menu, choose Query editor, then connect to a database in your cluster. For Schema, choose public to create a new table based on that schema. Enter the following in the query editor window and choose Run to create a new table.
As of late 2020, this is possible via "cross-database queries". For more information, see the official documentation here.
90
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
