Using Azure as an R datasource Part 3: Pulling data from MySQL/MariaDB to Linux

Connecting to the server

We’re connecting to the server from the client Ubuntu machine. In addition to creating the database and table, this is useful for troubleshooting, for example to solve firewall, port and credential issues.

From a command prompt on the client VM, connect to the server:

azureuser@MyAzureUbuntu:~$ mysql -hMyServer.cloudapp.net -uMyUser -pMyPassword

(replace MyServer, MyUser and MyPassword with your values)

Creating the sample table

Once successfully connected to the server, run the following script in the MySQL client:

create database MyDatabase;
use MyDatabase;
create table MyTable (x float not null, y float not null);
drop procedure if exists PopulateMyTable;
delimiter $$
create procedure PopulateMyTable()
begin
declare i int default 0;
while i < 500 do
    insert into MyTable(x, y) values(rand(), rand());
    set i = i+1;
end while;
end $$
delimiter ;
call PopulateMyTable();
select count(*) from MyTable;

The last select statement should return 500, confirming we have created a table called MyTable consisting of 500 rows of 2 columns (x and y) with random values.

Using RODBC’s odbcDriverConnect function

MySQL’s ODBC drivers need to be registered in order to connect using RODBC. The following shows how to do it and how to see the contents of the configuration file.

azureuser@MyAzureUbuntu:~$ cd /etc
azureuser@MyAzureUbuntu:/etc$ cat odbcinst.ini
azureuser@MyAzureUbuntu:/etc$ sudo odbcinst -i -d -f /usr/share/libmyodbc/odbcinst.ini
odbcinst: Driver installed. Usage count increased to 1.
    Target directory is /etc
azureuser@MyAzureUbuntu:/etc$ cat odbcinst.ini
[MySQL]
Description=MySQL driver
Driver=libmyodbc.so
Setup=libodbcmyS.so
CPTimeout=
CPReuse=
UsageCount=1

Once installed, our table can be queried from R with an ODBC connection string using RODBC:

library(RODBC)

myServer <- "MyServer.cloudapp.net"
myUser <- "MyUser"
myPassword <- "MyPassword"
myDatabase <- "MyDatabase"
myDriver <- "MySQL" # same as the name in the [] in the odbcinst.ini file

connectionString <- paste0(
    "Driver=", myDriver,
    ";Server=", myServer,
    ";Database=", myDatabase,
    ";Uid=", myUser,
    ";Pwd=", myPassword)
conn <- odbcDriverConnect(connectionString)
ds <- sqlQuery(conn, "SELECT * FROM MyTable")
close(conn)
plot(ds, col = "red", pch = 19, main = "Querying MySQL with RODBC odbcDriverConnect")

Using RODBC’s odbcConnect function

odbc.ini is the file used to store DSNs. If you have isql installed, you can use it to test the connection. Below we show both the contents of the file and a test using isql:

azureuser@MyAzureUbuntu:/etc$ cat odbc.ini
[Azure-MySQL]
Description = Testing MySQL Azure
Trace       = Off
TraceFile   = stderr
Driver      = MySQL
SERVER      = MyServer.cloudapp.net
USER        = MyUser
PASSWORD    = MyPassword
DATABASE    = MyDatabase

azureuser@MyAzureUbuntu:/etc$ echo select 1+1 | isql Azure-MySQL -b
+---------------------+
| 1+1                 |
+---------------------+
| 2                   |
+---------------------+
SQLRowCount returns 1
1 rows fetched

Setting up a DSN greatly simplifies the R code:

library(RODBC)

conn <- odbcConnect("Azure-MySQL") 
ds <- sqlQuery(conn, "SELECT * FROM MyTable")
close(conn)
hist(ds$x + ds$y, col = "blue", main = "Querying MySQL with RODBC odbcConnect")

Using RJDBC

To use RJDBC to connect to MySQL or MariaDB, you need to:

• From a command prompt on the client VM:
  • Install Java: sudo apt-get install openjdk-8-jdk libmysql-java sqlline
  • (Optional) Test the connection outside of R: echo select count(*) from MyTable | sqlline -u "jdbc:mysql://MyServer.cloudapp.net/MyDatabase" -n MyUser -p MyPassword
  • Register Java in R: sudo R CMD javareconf
• Install the R package from an R terminal on the client VM (no Ubuntu packages via apt-get are provided): install.packages("RJDBC")

library(RJDBC)
## Loading required package: DBI
## Loading required package: rJava
drv <- JDBC(
  driverClass = "com.mysql.jdbc.Driver", # check driver's documentation name
  classPath = "/usr/share/java/mysql-connector-java.jar", # use locate and updatedb to find location
  identifier.quote="`")
conn <- dbConnect(drv, 
  "jdbc:mysql://MyServer.cloudapp.net/MyDatabase", 
  "MyUser",
  "MyPassword")
ds <- dbGetQuery(conn, "select * from MyTable")
dbDisconnect(conn)
## [1] TRUE
plot(ds, col=rgb(200,0,0,50, maxColorValue=255), main = "Querying MySQL with RJDBC", pch = 19, cex = 3)

Using RMySQL

Much easier to use than RJDBC (no url connector, driverClass and classpath to figure out):

library(RMySQL)
conn <- dbConnect(
  RMySQL::MySQL(),
  host = "MyServer.cloudapp.net",
  user = "MyUser",
  password = "MyPassword",
  dbname = "MyDatabase")
ds <- dbGetQuery(conn, "select * from MyTable")
dbDisconnect(conn)
plot(ds, col = "green", main = "Querying MySQL with RODBC RMySQL")