[R] SQL Queries from Multiple Servers

[Paul Gilbert]

Tom Schenk Jr wrote:
> I use RODBC as my conduit from R to SQL. It works well when the tables are
> stored on one channel, e.g.,
>
> channel <- odbcConnect("data_base_01", uid="....", dsn="....")
>
> However, I often need to match tables across multiple databases, e.g.,
> "data_base_01" and "data_base_02". However, odbcConnect() appears limited
> insofar as you may only query from tables within a single channel, e.g.,
> database. I do not have access to write and create new tables on the SQL
> servers, which is a possible solution (e.g., copy all tables into a single
> database).
>
> Is there any way, in RODBC or another R-friendly SQL package, to perform SQL
> operations across multiple databases?
>   
I'm not sure if this can be done with odbc, but with MySQL it is 
possible to do joins across multiple databases, and creating temporary 
tables may be possible even without the write access you would need for 
a permanent table. I'm not sure if you can pass this kind of statement 
from R, because the connection usually specifies the database. However, 
I have constructed temporary tables with a simple mysql client and then 
queried them from R. They stay around as long as you don't quit the 
simple client.  I am not really sure this is suppose to work.  Another 
option is two connections and do some of the comparison in R, or write 
the results to an SQLite connection, on which you usually have write 
access. This might be slow  and you may have to deal with chunks if you 
have big tables.  Joins across databases are also possible with 
PostgreSQL, I'm told, but they are more difficult.

Paul
> Warm regards.
>
>   
====================================================================================

La version française suit le texte anglais.

------------------------------------------------------------------------------------

This email may contain privileged and/or confidential in...{{dropped:26}}