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?
Warm regards.
--
Tom Schenk Jr.
tomschenkjr@gmail.com
[[alternative HTML version deleted]]
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?Sounds like you want... a SQL database engine. Some engines provide features to work with external tables... but it is not standardized and it is not usually very efficient. You usually have to have some level of admin privilege to do this. Alternatively, you can create a new database using an engine of your choice, move the data to that common working database, and do your cross-queries to your heart's content. Depending on your working environment, SQLite, MySQL, MS Jet, or MSDE could be options. Or, if your joins are fairly small you can use R's merge function and doBy. It seems a bit much to me to expect a database access library to do double duty as a SQL database engine, though. -- --------------------------------------------------------------------------- Jeff Newmiller The ..... ..... Go Live... DCN:<jdnewmil at dcn.davis.ca.us> Basics: ##.#. ##.#. Live Go... Live: OO#.. Dead: OO#.. Playing Research Engineer (Solar/Batteries O.O#. #.O#. with /Software/Embedded Controllers) .OO#. .OO#. rocks...1k
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}}
Hi. Depending on your requirements, one option would be to do the join in R using merge() If you wish to run SQL joins across multiple databases, then it is not an R problem but a database problem. For a quick solution, I would write scripts that bring all your data together into one database (could be written in any scripting language, and of course R) and then process from there. Bw Mark 2009/5/13 Tom Schenk Jr <tomschenkjr at gmail.com>:> 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? > > Warm regards. > > -- > Tom Schenk Jr. > tomschenkjr at gmail.com > > ? ? ? ?[[alternative HTML version deleted]] > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. > >-- Dr. Mark Wardle Specialist registrar, Neurology Cardiff, UK