来自两个不同服务器的SQL联接表:R与SAS

Question

我在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)

我在"；connection_ 1"；(table_1(中存储的另一个表；连接_ 2"；(表2(。我想将这两个表连接在一起，并将生成的表保存在"；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")

但我不确定这在R.中是否可行

• 有人知道我写的代码是否可以更改为这样吗？

• 或者将建立"；连接_ 2"；自动取消"；connection_ 1"；？

谢谢！

旁白：如果我使用SAS，我本可以像这样解决上面的问题：

#connection 1
%let NZServer = 'server_1';
$ let NZSchema = 'my_schema_1';
% let NZDatawork = 'database_1';
$ let SAS_LIB = 'LIB_1';
LIBNAME  ....
#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  ...;

# 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

跨两个服务器的连接没有简单的方法。您可以在其中一个serer(具有更多数据(中创建一个临时表，并用另一个表/服务器的数据填充它。这样，您将移动最少的数据量(而不是从两个表中提取(，并利用netezzas共定位联接来加快查询速度。