Bringing the powers of SQL into R

One of the big flaw of R is that data loaded into it are stored in the memory (on the RAM) and not on the disk. As you are working in an analysis with large (big) data the processing time of simple and more complex functions can become very long or even crash your computer. SQL enters here, it is a powerful language designed to work with (large) database and to perform simple operation (like subsetting, sorting …) on them. It is particularly useful to explore very large dataset and format the data for further analysis. There are many programs for doing database management using SQL. I decided to start looking at MySQL since it has an R package and is rather easy to set up (one could also use PostgreSQL …). In this post I will show you step by step how to create a database in MySQL, to upload data from R into it, then to do some queries to look at the power of SQL. Before I start note that the data.table package was developed to perform fast operation on big data (have a look here).

Create a database

First you need to download MySQL from this website or from synaptic for the ubuntu users. Then you need to open a shell window (type cmd for windows users, terminal for Linux), type this:

> mysql -p -u root

This will ask you for the password of the root user if it worked you will see some text and mysql> appearing. Then if you don’t want to bother with different users and their rights you can directly create a database using:

mysql> CREATE DATABASE intro_to_sql;

That’s it you created a database named intro_to_sql. At this point it is very important to remember that every time you are in the shell with mysql you need to use semi-colon (;) at the end of your statement, otherwise it doesn’t work. You can look at all the databases in your system using:

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| intro_to_sql       |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

Then we create a user having all rights on the intro_to_sql database:

mysql> GRANT ALL ON data.* TO 'user1'@'localhost' IDENTIFIED BY '12345';

Once a database and a user have been created we don’t need the shell interface everything else can be done from R.

Uploading datasets from R

We could directly create tables in the database from the shell interface but let’s see how to transfer data from R into the database:

library(RMySQL)
#connect to the database
con<-dbConnect(MySQL(),user='user1',password='12345',dbname='intro_to_sql')

#load some data
library(ggplot2)
data(diamonds)

#have a look at them
summary(diamonds)

     carat               cut        color        clarity          depth           table           price             x                y         
 Min.   :0.2000   Fair     : 1610   D: 6775   SI1    :13065   Min.   :43.00   Min.   :43.00   Min.   :  326   Min.   : 0.000   Min.   : 0.000  
 1st Qu.:0.4000   Good     : 4906   E: 9797   VS2    :12258   1st Qu.:61.00   1st Qu.:56.00   1st Qu.:  950   1st Qu.: 4.710   1st Qu.: 4.720  
 Median :0.7000   Very Good:12082   F: 9542   SI2    : 9194   Median :61.80   Median :57.00   Median : 2401   Median : 5.700   Median : 5.710  
 Mean   :0.7979   Premium  :13791   G:11292   VS1    : 8171   Mean   :61.75   Mean   :57.46   Mean   : 3933   Mean   : 5.731   Mean   : 5.735  
 3rd Qu.:1.0400   Ideal    :21551   H: 8304   VVS2   : 5066   3rd Qu.:62.50   3rd Qu.:59.00   3rd Qu.: 5324   3rd Qu.: 6.540   3rd Qu.: 6.540  
 Max.   :5.0100                     I: 5422   VVS1   : 3655   Max.   :79.00   Max.   :95.00   Max.   :18823   Max.   :10.740   Max.   :58.900  
                                    J: 2808   (Other): 2531                                                                                    
       z         
 Min.   : 0.000  
 1st Qu.: 2.910  
 Median : 3.530  
 Mean   : 3.539  
 3rd Qu.: 4.040  
 Max.   :31.800  


#write the table into the database
dbWriteTable(con,"diamonds",diamonds_data)

#remove the dataset from R
rm(diamonds)

We now have one table named diamonds_data in our database.

Performing queries from R

Now that our intro_to_sql database has one table we can start playing with some SQL queries from R:

#count the number of diamonds that are more than 2000$ expensive
dbGetQuery(con,"select count(*) from diamonds_data where price>2000")

  count(*)
1    29733

#make a new data frame with diamonds of color ‘D’ and a depth less than 60%
subs<-dbGetQuery(con,"select * from diamonds_data where color='D' AND depth<60")
unique(subs$color)

[1] "D"

#make a new data frame only with the column x,y,z and order them by ascending x
subs<-dbGetQuery(con,"select x,y,z from diamonds_data order by x")
head(subs)

 x    y z
1 0 6.62 0
2 0 0.00 0
3 0 0.00 0
4 0 0.00 0
5 0 0.00 0
6 0 0.00 0

#from this dataset let’s create a new variable which is the mean of x,y,z
subs$Mean<-apply(sub,1,mean)

#write the results in a new table
dbWriteTable(con,"XYZMean",subs)

#check that it has been created
dbListTables(con)

[1] "XYZMean"       "diamonds_data"

As you can see it is fairly easy to work with RMySQL, there are many advantages in working with this tool: (i) all the powers of SQL are at your command from within R, so it is easy to include this in your workflow (i.e. using the script window from RStudio …), (ii) no need to load big chunk of unprocessed data into R, use SQL to process efficiently the data (I did not talk about how to directly load a table into a MySQL database, have a look here).

There are many helpful ressources online about this topic here are a few that I found interesting: A working guide to MySQL. A nice introduction into some other SQL-platform supported in R (SQLite). A blog post about the issue of big data in R.