Creating the database

The Azure Management site changes quite often, therefore these instructions are valid “at the time of this writing” :o)

1. Log on the Azure Management site with your Azure subscription (see previous post),
2. Create a VM: select “New”, “Compute”, “Virtual Machine”, “Quick Create” and then fill-in the information. In our case we chose “SUSE Linux Enterprise” for the image, but most images should work, both Windows and Linux.
3. Once the creation is completed, click on “Virtual Machines”, your machine name, and then “endpoints”. You should already have SSH (port 22) listed if you chose a Linux VM (and if not, add it). Add MySQL (port 3306) to the list.
4. Connect to your VM via ssh.
   • Putty is a free lightweight ssh client for Windows,
   • MobaXterm is a more advanced solution that also provides a free version.
5. Once logged on to the VM via ssh, install MySQL or MariaDB: sudo zypper install mariadb*.
   Note: this command and subsequent ones may vary depending on the choice of Linux distribution. For example the equivalent of “zypper” may instead be called: “yum”, “apt-get”, “synaptic”, “aptitude”, “dpkg-install”, etc.
6. Start the server. sudo rcmysql start
7. Secure the server: sudo mysql_secure_installation

8. Add a user that’s authorized to connect remotely. Below is the full transcript of a session where we add user MyUser with password MyPassword that can connect remotely from any location with all privileges (not recommended), and we also create the MyDatabase database:

   sshuser@MyServer:~> mysql -s --user=root -p
   Enter password:
   MariaDB [(none)]> create user 'MyUser'@'%' identified by 'MyPassword';
   MariaDB [(none)]> grant all privileges on *.* to 'MyUser'@'%' with grant option;
   MariaDB [(none)]> create database MyDatabase;
   MariaDB [(none)]> quit
   sshuser@MyServer:~>

Connecting to the database from outside of R

Optional step, but can be useful for troubleshooting. For example to solve firewall, port and credential issues.

• Install mysql.exe client or MariaDB client.

• Below an example of a successful test session in a command prompt (search for “cmd” in the Start menu or screen to start a command prompt):

  C:>"c:Program FilesMySQLMySQL Workbench 6.2 CEmysql.exe" -s -hMyServer.cloudapp.net -uMyUser -pMyPassword -DMyDatabase
  Warning: Using a password on the command line interface can be insecure.
  mysql> select 1+1 as Answer;
  Answer
  2
  mysql> quit
  C:>

(replace MyServer, MyUser, MyPassword and MyDatabase with your values)

Note: there’s no space between the flags and values; e.g. it’s “-pMyPassword” and not “-p MyPassword”.

Connecting to the database from R on Windows

library(RODBC)

myServer <- "MyServer.cloudapp.net"
myUser <- "MyUser"
myPassword <- "MyPassword"
myDatabase <- "MyDatabase"
myDriver <- "MySQL ODBC 5.3 Unicode Driver"

connectionString <- paste0(
    "Driver=", myDriver,
    ";Server=", myServer,
    ";Database=", myDatabase,
    ";Uid=", myUser,
    ";Pwd=", myPassword)
conn <- odbcDriverConnect(connectionString)
sqlQuery(conn, "SELECT 142857 * 3 AS Cyclic")
##   Cyclic
## 1 428571
close(conn) # don't leak connections !

library(RODBC)

# No need to specify uid and pwd: these are part of the DSN
conn <- odbcConnect("MyMariaDBAzure")
sqlQuery(conn, "SELECT 10001 - 73 * 137 AS Zero")
##   Zero
## 1    0
close(conn)

library(RJDBC)
drv <- JDBC(
    driverClass = "com.mysql.jdbc.Driver",
    classPath = "C:/Program Files (x86)/MySQL/MySQL Connector J/mysql-connector-java-5.1.35-bin.jar")
conn <- dbConnect(drv, "jdbc:mysql://MyServer.cloudapp.net", "MyUser", "MyPassword")
dbGetQuery(conn, "SELECT 1+1")
dbDisconnect(conn)

library(RMySQL)
conn <- dbConnect(RMySQL::MySQL(), host = "MyServer.cloudapp.net", user="MyUser", password="MyPassword")
dbGetQuery(conn, "SELECT 1+1")
dbDisconnect(conn)

Summary

• We’ve tried RODBC, RJDBC and RMySQL to connect to a MariaDB database hosted on a Suse VM in Azure.
• In all cases, we had to install something on the client machine (besides R packages): drivers and/or JDK.
• When it comes to performance, our experience was similar to Microsoft SQL Server’s case: RODBC was faster, both to connect and to get back the query results.