Join with different database and cloud

  • 24 March 2021
  • 2 replies

How can I join a table in Bigquery with a table in Redshift? I did it using merge in a look, but there is a limit of 5000 lines and I have more data than that.




2 replies

Userlevel 1

I don’t think you can join two databases with different dialects. You can join two different databases using federated joins (assuming they are the same dialect, same physical machine and same connection). Per the article I think the best bet would be to duplicate the datasets into the desired database

Userlevel 3

Other posters are correct, this isn’t directly possible outside merged results or federated joins. 

That said, there is a dark magic called a foreign data wrapper which should be elaborated on in the next community mic drop. I’m not 100% sure if Redshift and BigQuery specifically can utilize this though.