[R] SQL Database
m@cqueen1 @end|ng |rom ||n|@gov
Thu Jul 26 17:25:51 CEST 2018
From my point of view, the logic is this:
If the external database is Oracle, use ROracle
If the external database is MySQL, use RMySQL
and similarly for other databases
If there is no R package specific to the database, then you drop back to RODBC or RJDBC. Hopefully you can get the necessary drivers or java files to support the database
Your steps look good (I do them all the time with Oracle and MySQL), and realize that you don't have to grab an entire table; you can send SQL queries that join tables and subset rows, etc. You can also write results back to the database if that's useful.
I prefer to use packages that are based on the DBI package.
Lawrence Livermore National Laboratory
7000 East Ave., L-627
Livermore, CA 94550
Lab cell 925-724-7509
On 7/25/18, 7:57 AM, "R-help on behalf of Doran, Harold" <r-help-bounces using r-project.org on behalf of HDoran using air.org> wrote:
I'm doing some work now to learn which SQL database package is the most optimal for the task I am working on. There are many packages, and I'm reviewing the documentation on some of them now. I am seeking advice from those of you who might suggest a package to use for the task I am currently working with.
The work is currently as follows. My users currently use another tool to extract tables from a server, save those tables as .csv files, and then those csv files are read into R and stuff is done on the data in those files. This adds overhead that can be bypassed if users instead can directly access the database from within R and grab the tables they need and then those tables are data frames in the R session and available to do stuff.
The sequence of work (I think) I just this:
Step 1: Connect to the remote server (connection string and authenticate the user)
Step 2: Have a SQL query statement that grabs the tables from the remote server
Step 3: Close the connection
The two packages I have narrowed my studies to are Dbplyr and RODBC, both of which seem to be similar.
Any experiences out there to suggest these two packages are in fact right for this task, or would there be other packages that might be more optimal for this?
R-help using r-project.org mailing list -- To UNSUBSCRIBE and more, see
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
More information about the R-help