Reading a MySQL table into R
On Jan 31, 2011, at 2:14 PM, Marc Schwartz wrote:
On Jan 31, 2011, at 12:56 PM, Luca Meyer wrote:
Hello, I should first mention that I am not familiar with database management tools, nor with database solutions for Mac OS X. I currently have some data sitting on a MySQL Server version 5.0.51a-24+lenny4 I access through a web site using phpMyAdmin - 2.11.5.1. That is, the database is sitting remotely and I have to access the table containing the data several times during each day. How can I read this data into a R data.frame without having to use phpMyAdmin to download a csv file to my machine and then import it from there? Is there some SQL query I can run directly from R into MySQL? I have been looking at RMySQL, am I on the right track? Or should I be looking at something else? Thanks, Luca
Luca, There are essentially two methods: 1. RMySQL 2. RODBC (also on CRAN), which would allow you to connect to MySQL via an ODBC driver, which is available from: http://www.mysql.com/downloads/connector/odbc/
3. RJDBC (also on CRAN) Since OS X already has the matching Java all you need is the MySQL JDBC driver Connector/J - see ?JDBC for an example. Nonetheless I was going to interject that all the methods above assume that the database is accessible form the outside, which is usually not the case. So you should check first that the MySQL is actually running on a externally accessible port so you can connect to it remotely. If it doesn't, you can use ssh + port forwarding if you have ssh access to the machine, otherwise you're in trouble. Cheers, Simon
Be aware that for the second option, you will need to obtain the correct 32 or 64 bit version of the ODBC driver, depending upon which version of R you are running on your Mac when you want to connect to the server.
If you elect to use RODBC, Prof. Ripley has created a very helpful vignette, which can be read after installation by using:
vignette("RODBC")
Lastly, for detailed assistance on R and databases, there is the r-sig-db list. More info here:
https://stat.ethz.ch/mailman/listinfo/r-sig-db HTH, Marc Schwartz _______________________________________________ R-SIG-Mac mailing list R-SIG-Mac at r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-mac