MySQL is world's most popular open source database. Many of the world's largest and fastest growing organizations including Facebook, Google, Adobe, and Zappos rely on MySQL databases, to save time and money powering high-volume websites, business critical systems, and software packages.
Since both R and MySQL both are open source, they can be used for building the interactive web analytic applications. Also simple data analytics activities can be performed for existing web applications with this unique package.
To install MySQL on your Linux machine, you need to follow the given steps in sequence:
- Install MySQL
- Install RMySQL
We will see how to get MySQL installed on Linux:
// Updating the linux package list sudo apt-get update // Upgrading the updated packages sudo apt-get dist-upgrade //First, install the MySQL server and client packages: sudo apt-get install mysql-server mysql-client
Now, we have installed MySQL on our Linux machine. It's time to install RMySQL – R library from CRAN via the following R commands:
# to install RMySQL library install.packages("RMySQL") #Loading RMySQL library(RMySQL)
After the RMySQL library is installed on R, perform MySQL database connection by providing the user privileges as provided in MySQL administration console:
mydb = dbConnect(MySQL(), user='root', password='', dbname='sample_table', host='localhost')
Now, the database connection has been done successfully. To list the available tables and their structure of data base in MySQL database, look at the following commands. To return the available tables created under mydb database, use the following command:
dbListTables(mydb)
To return a list of data fields created under the sample_table table, use the following command:
dbListFields(mydb, 'sample_table')
We know how to check MySQL tables and their fields. After identification of useful data tables, we can import them in R using the following RMySQL command. To retrieve the custom data from MySQL database as per the provided SQL query, we need to store it in an object:
rs = dbSendQuery(mydb, "select * from sample_table")
The available data-related information can be retrieved from MySQL to R via the fetch command as follows:
dataset = fetch(rs, n=-1)
Here, the specified parameter n = -1 is used for retrieving all pending records.
To perform the data operation with MySQL database, we need to fire the SQL queries. But in case of RMySQL, we can fire commands with the dbSendQuery function.
Creating a new table with the help of available R dataframe into MySQL database can be done with the following command:
dbWriteTable(mydb, name='mysql_table_name', value=data.frame.name)
To insert R matrix data into the existing data table in MySQL, use the following command:
# defining data matrix datamatrix <- matrix(1:4, 2, 2) # defining query to insert the data query <- paste("INSERT INTO names VALUES(",datamatrix [1,1], ",", datamatrix [1,2], ")") # command for submitting the defined SQL query dbGetQuery(con, query)
Sometimes we need to delete a MySQL table when it is no longer of use. We can fire the following query to delete the mysql_some_table table:
dbSendQuery(mydb, 'drop table if exists mysql_some_table').