Using Azure as an R data source, Part 1

by Gregory Vandenbrouck
Software Engineer at Microsoft

This post is the first in a series that covers pulling data from various Windows Azure hosted storage solutions (such as MySQL, or Microsoft SQL Server) to an R client on Windows or Linux.

We’ll start with a relatively simple case of pulling data from SQL Azure to an R client on Windows.

Creating the database

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

1. You need an Azure subscription. If you don’t have one, free trials are available here.
2. Log on to the Azure Management site.
3. Create a SQL Azure database: select “New”, “Data Services”, “SQL Database”, “Quick Create” and then fill-in the information.
4. Once the database creation is completed, click on “SQL databases” and then on your database name. There should be a way to view the server name and the connection strings for ODBC, JDBC, etc.

Connecting to the database outside of R

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

• Install Microsoft® ODBC Driver 11 for SQL Server® - Windows
• Install Microsoft® Command Line Utilities 11 for SQL Server (also available in the Feature Pack )

• 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:\> sqlcmd -S MyServer.database.windows.net -U MyUser -P MyPassword -d MyDatabase -Q "print 'Hello World'"
  Hello World

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

Connecting to the database from R on Windows

Viewing and adding ODBC drivers

To view the list of available drivers, open the “ODBC Data Sources” dialog:

• Type “ODBC” in the Start menu or screen.
• Select the “ODBC Data Sources” that corresponds to the R console you’ve started: on a 64bit OS there’s both 32bit or 64bit. If you can’t find it, it’s here:
  • 32bit for 64bit OS: %windir%\syswow64\odbcad32.exe
  • 32bit for 32bit OS, 64bit for 64bit OS: %windir%\system32\odbcad32.exe
• Click on the “Drivers” tab:

 

“SQL Server” was present by default with the versions of Windows I tested (8.1 and Server 2012) and was sufficient. If you want to try the other drivers, these are:

• SQL Server Native Client 11.0: search for “ODBC” on the page.
• ODBC Driver 11 for SQL Server.

Using RODBC’s odbcDriverConnect function

This is the simplest option as ODBC is available by default on Windows.

If the ODBC connection string suggested by the Azure Management site doesn’t work (usually: missing driver) try using one similar to the simplified version below.

library(RODBC)
myServer <- "MyServer.database.windows.net"
myUser <- "MyUser"
myPassword <- "MyPassword"
myDatabase <- "MyDatabase"
myDriver <- "SQL Server" # Must correspond to an entry in the Drivers tab of "ODBC Data Sources"

connectionString <- paste0(
    "Driver=", myDriver, 
    ";Server=", myServer, 
    ";Database=", myDatabase, 
    ";Uid=", myUser, 
    ";Pwd=", myPassword)
# This query simulates a table by generating a rowset with one integer column going from 1 to 1000
sqlQuery <- "
WITH nb AS (SELECT 0 AS n UNION ALL SELECT n+1 FROM nb where n < 9)
SELECT n1.n+10*n2.n+100*n3.n+1 FROM nb n1 CROSS JOIN nb n2 CROSS JOIN nb n3
"
conn <- odbcDriverConnect(connectionString)
df <- sqlQuery(conn, sqlQuery)
close(conn) # don't leak connections !

str(df)

## 'data.frame':    1000 obs. of  1 variable:
##  $ : int  1 2 3 4 5 6 7 8 9 10 ...

summary(df)

##                  
##  Min.   :   1.0  
##  1st Qu.: 250.8  
##  Median : 500.5  
##  Mean   : 500.5  
##  3rd Qu.: 750.2  
##  Max.   :1000.0

Using RODBC’s odbcConnect function

This function allows for using a Data Source Name (DSN). The pros of the DSN is that you can hide some of the complexity from the R script, for example the choice of driver. To create a DSN, open the “ODBC Data Sources” dialog and then click on “Add” under the “User DSN” or “System DSN” tab.

Notes:

• If connecting to Azure, choose “SQL Server authentication”.
• The credentials (Login ID and Password) are not persisted, meaning even if you specify them in the dialog, you’ll have to specify them again in R.
• If you can’t specify a database besides master for the “SQL Server” driver, then don’t specify credentials (you won’t be able to test the connection anymore).

In the example below, I’ve created a “User DSN” (that is: only available to current user) called MySqlAzure:

 

library(RODBC)

conn <- odbcConnect("MySqlAzure", uid = myUser, pwd = myPassword)
sqlQuery(conn, "SELECT POWER(2, 10) AS Answer")

##   Answer
## 1   1024

close(conn)

Using RJDBC

To use RJDBC to connect to SQL Server, you need to:

• Install:
  • JDK
  • JDBC drivers for SQL Server
• Know the following:
  • driverClass: the class name. See the driver-specific documentation.
  • classPath: the location of the jar file. If you don’t know where it is, try running dir /s /b %systemdrive%\*sql*.jar from a command prompt.
  • url connector prefix. Again, driver-specific.

In my specific setup:

library(RJDBC)
drv <- JDBC(
    driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver",
    classPath = "C:/JDBC/Microsoft JDBC Driver 4.1 for SQL Server/sqljdbc_4.1/enu/sqljdbc41.jar")
conn <- dbConnect(drv, "jdbc:sqlserver://MyServer.database.windows.net", "MyUser", "MyPassword")
dbGetQuery(conn, "SELECT 1+1")
dbDisconnect(conn)

Using RSQLServer

RSqlServer also needs the JDK installed. It’s slighter easier to use than RJDBC:

library(RSQLServer)
conn <- dbConnect(
    RSQLServer::SQLServer(),
    "MyServer.database.windows.net",
    "useNTLMv2=false;user=MyUser;Password=MyPassword")
dbGetQuery(conn, "SELECT 1+1")
dbDisconnect(conn)

Summary

• We’ve tried RODBC, RJDBC and RSQLServer to connect to a SQL Server hosted on Azure.
• RODBC is the easiest to setup (no install required besides the package) and use (simple connection string).
• RODBC was faster in our tests, both to connect and to get back the query results.

So you may ask: why use RJDBC or RSQLServer? One answer may be: because it’s much easier to port if you need to run your R scripts on both Windows and Linux, as we’ll see in subsequent posts.

  

Comments

[Yolanda Ceron]

Great post.

Is it possible to connect to Azure's DocumentDb from R?

[Carlos Crosetti]

Nice article. I liked the technical approach showing the steps to get it running. Please indicate what is your recommendation for the client-side platform. Would Windows 7 64-bit be OK?

[Grégory Vandenbrouck]

@Carlos
Windows 7 64bit should be OK. The clients I tested are: Windows 8.1, 10 "insider preview" and Server 2012 (all 64bit).

@Yolanda
I didn't find a DocumentDB R package or ODBC driver. It does however provide a REST API. So it should be possible to query DocumentDB from R, maybe with the rjson package. Sounds like a great topic for a subsequent post :)

[jangorecki]

your RSQLServer example didn't work on my azure db, could you provide reproducible example? e.g. using db from: http://sqlblog.com/blogs/jamie_thomson/archive/2012/03/27/adventureworks2012-now-available-to-all-on-sql-azure.aspx

[Grégory Vandenbrouck]

Nice; I didn't know about the public AdventureWorks. I was not able to get RSQLServer to work with it, while it works fine with my DB. Not sure why. If anyone has ideas, let me know. I was however able to get RODBC and RJDBC to work. Here are the corresponding code snippets:

library(RODBC)
conn <- odbcDriverConnect("Driver=SQL Server;Server=mhknbn2kdz.database.windows.net;Database=AdventureWorks2012;Uid=sqlfamily;Pwd=sqlf@m1ly")
sqlQuery(conn, "SELECT TOP 2 * FROM Person.Person")
close(conn)

library(RJDBC)
drv <- JDBC(
driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver",
classPath = "C:/JDBC/Microsoft JDBC Driver 4.1 for SQL Server/sqljdbc_4.1/enu/sqljdbc41.jar")
conn <- dbConnect(drv, "jdbc:sqlserver://mhknbn2kdz.database.windows.net", database = "AdventureWorks2012", "sqlfamily", "sqlf@m1ly")
dbGetQuery(conn, "SELECT TOP 2 * FROM Person.Person")
dbDisconnect(conn)

[SteffLocke]

@Yolanda / @Carlos Crossetti

As you say there is an API. I haven't yet played with the DocumentDB API but I've started looking at blob storage with Azure so it might work similarly. I can get a list of blobs but haven't yet cracked the IO. If you'd like to take a look on how you do the authentication etc then it might be a good example. Also, if you need help or want to to help, let me know!
https://github.com/stephlocke/Rtraining/tree/master/R