SQL Joining Tables from 2 Different Servers: R vs SAS

Question

I have set up two different connections in R:

connection_1 <- dbConnect(odbc::odbc(), driver = my_driver, database = "database_1", uid = "my_id", pwd = "my_pwd", server = "server_1", port = "my_port)

connection_2 <- dbConnect(odbc::odbc(), driver = my_driver, database = "database_2", uid = "my_id", pwd = "my_pwd", server = "server_2", port = "my_port)

I have a table stored in "connection_1" (table_1), and another table stored in "connection_2" (table_2) . I would like to join these two tables together and save the resulting table on "connection_1":

dbGetQuery(connection_1, "create table my_table as select * from connection_1.table_1 a inner join connection_2.table_2 B on A.Key_1 = B.Key_2")

But I am not sure if this is possible in R.

• Does anyone know if the code I have written can be changed to do this?

• Or will establishing "connection_2" automatically cancel "connection_1"?

• I am using Netezza SQL.

Thank you!

Aside: If I was using SAS, I could have solved the above problem like this:

#connection 1

%let NZServer = 'server_1';
$ let NZSchema = 'my_schema_1';
% let NZDatawork = 'database_1';
$ let SAS_LIB = 'LIB_1';

LIBNAME  & SAS_LIB netezza server =&NZServer Database = &NZDatawork schema = &NZSchema authdomain = 'NZAuth';

#connection 2

%let NZServe = 'server_2';
$ let NZSchem = 'my_schema_2';
% let NZDatawor = 'database_2';
$ let SAS_LI = 'LIB_2';

#remove last letter from each word to make it different 

LIBNAME  & SAS_LI netezza server =&NZServe Database = &NZDatawor schema = &NZSchem authdomain = 'NZAuth';


# run earlier join:

proc sql outobs = 100;

create table LIB_1.a as select * from LIB_1.table_1 a inner join LIB_2.table_2 B on A.Key_1 = B.Key_2;

quit;

Answer 1

There is no straightforward way to join across two servers. You could create a temp table in one of the serer (that has more data) and populate it with data from the other table/server. That way you will be moving the least amount of data (as opposed to extracting from both tables) and utilizing netezzas colocated join to speed up your query.