Many of you would like to explore the data from the MySql database with R. This would help to analyze data with relationship which exists in R. This post will talk about the steps to connect to MySQL from R. This post is a step by step approach similar to the to the steps outlined in this PDF Document, though there are some additional information and easy to follow sequence.
- MySQL is already installed on your System
- Operating System is Windows 7 (64-Bit)
We will use the Employee dataset which is available in the http://www.eclipse.org/birt/phoenix/db/ which can be used for testing applications and database with the fictious names “classicmodels”. (approximately 3.1 MB). Make sure you import the dataset to MySQL as outlined here. It has the following tables Customers, Employees, Orders, OrderDetails, Payments, Products and Product Lines. You can look at what is available in the database using the SQLYOG Community Edition.
Various ways to connect to MySQL from R:
There following are the ways in which we can connect to MySQL from R:
- Using RODBC Library
- Using RMySQL Library
In this post we will see the steps for connecting to MySQL from R using ODBC. Also refer to this PDF Attachment on the ODBC connectivity with R.
Download the ODBC Driver from the site https://dev.mysql.com/downloads/connector/odbc/ to make sure if you have the driven if you don’t have one. I have downloaded mysql-connector-odbc-5.3.2-winx64.msi as My operating and System is 64-bit. Please proceed with the installation of the same.
Going forward we will do the ODBC setup related steps.
Goto Control Panel->Administrative Tools->ODBC
Step 3: Click Add to add a new ODBC Setup for MySQL
Step 4: Once you click finish you will get the below given screen where in you need to feed the IP Address/Hostname of the MySQL Server, Username and Password credentials and database and click on the Test button to make sure you are able to connect to the database without any problems. Once the Test is successful click OK to added to the List of ODBC connections.
After the MySQL ODBC connection is added:
Now we will move to R to invoke this Datasource and try to access any one of the table in R
Step 5: Now load the library RODBC using the following command, if its not getting loaded you can look at the instructions in the link to install the RODBC Library.
Step 6: Now having the RODBC installed now we can connect to the ClassicModels database in the MySQL and test the RODBC Library
Hope now we have got an idea of how to bring on the MySQL database to R for further processing, in the next post we will try to attempt of using RMySQL Library. Thanks, please share your feedback.