Creating the client VM

We ended creating an Ubuntu Virtual Machine (VM) in Azure, using the same process as the previous post in the series. We named it MyAzureUbuntu.cloudapp.net and are accessing it using account azureuser.

Accessing the client VM

To access the client VM from your local machine, you can type ssh azureuser@MyAzureUbuntu.cloudapp.net from a command prompt if you have an ssh client installed. If that fails, verify that ssh (TCP port 22) is one of the endpoints of the VM.

If your local machine runs Windows, you can use PuTTY or MobaXterm as an ssh client.

Using RODBC’s odbcDriverConnect function

To install R and the tools/libraries to connect to SQL Server via RODBC, type:

sudo apt-get install r-cran-rodbc unixodbc-bin unixodbc odbcinst freetds-bin tdsodbc

Note: if not using Ubuntu, you may have to use an alternative to apt-get, such as yum, zypper, etc.

To test connecting to SQL Server, you can use the tsql utility, which is included in the FreeTDS package.

FreeTDS defaults are for another database vendor (Sybase) that uses a different port and TDS version than SQL Server. There are a few complications around the TDS version when connecting to SQL Server using FreeTDS. You need to know:

1. that the TDS version needs to be explicitly specified (not obvious since tsql’s help and manual don’t mention it),
2. what TDS version should be used, and
3. how to specify it (tsql’s manual is silent about this).

In our case, 7.1 ended up being the TDS version that worked (although the documentation suggests instead 7.3) and 1433 is the port used by our SQL Azure instance.

Below we’re successfully connecting to our SQL Server instance using tsql and getting back the version of SQL Server:

azureuser@MyAzureUbuntu:~$ TDSVER=7.1 tsql -H MySqlAzure.database.windows.net -p 1433 -U MyUser -P MyPassword
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> version
using TDS version 7.1
1> select serverproperty('Edition'), serverproperty('ProductVersion')
2> go

SQL Azure       12.0.2000.8
(1 row affected)
1> quit

Notes:

• tsql does not appear to support specifying the default database and SQL Azure does not allow for changing the database once connected; therefore using tsql we only have access to the master database when connecting to SQL Azure. Not ideal but still useful to test the connection.
• When not specifying TDSVER or specifying an incorrect TDSVER, you’ll get an error message similar to Adaptive Server connection failed

FreeTDS’s ODBC drivers need to be registered in order to connect using RODBC. Below we show how to do it and how to see the contents of the configuration file.

azureuser@MyAzureUbuntu:~$ cd /etc
azureuser@MyAzureUbuntu:/etc$ wc -l odbcinst.ini
0 odbcinst.ini
azureuser@MyAzureUbuntu:/etc$ sudo odbcinst -i -d -f /usr/share/tdsodbc/odbcinst.ini
odbcinst: Driver installed. Usage count increased to 1.
    Target directory is /etc
azureuser@MyAzureUbuntu:/etc$ head -2 odbcinst.ini
[FreeTDS]
Description=TDS driver (Sybase/MS SQL)
azureuser@MyAzureUbuntu:/etc$

Now we can connect to our SQL Azure instance using RODBC’s odbcDriverConnect() function. We just need to make sure we pass the correct driver name (the name in the square brackets in odbcinst.ini), port number and TDS version in the connection string in addition to the regular settings: server, database, login and password.

azureuser@MyAzureUbuntu:~$ cat test.R
library(RODBC)
connStr <- "Server=MySqlAzure.database.windows.net;uid=MyUser;pwd=MyPassword;Database=MyDatabase" # regular info
connStr <- paste0(connStr, ";Driver=FreeTDS;TDS_Version=8.0;Port=1433") # FreeTDS specific info
conn <- odbcDriverConnect(connStr)
sqlQuery(conn, "select serverproperty('ProductLevel') as [Product Level], db_name() as [Database name]")
close(conn)

azureuser@MyAzureUbuntu:~$ R --slave -f test.R
  Product Level Database name
1           RTM    MyDatabase
azureuser@MyAzureUbuntu:~$

Using RODBC’s odbcConnect function

Some of this information can be persisted in a Data Source Name (DSN), allowing for shorter connection strings. Unfortunately, login and password cannot be persisted in the DSN for SQL Server whereas they can in the case of MySQL.

Below we show how to set up a SQL Azure/SQL Server DSN with FreeTDS, and how to test the changes using isql:

azureuser@MyAzureUbuntu:~$ cat /etc/odbc.ini
[Azure-MSSQL-FreeTDS]
# Matches the name in odbcinst.ini
Driver = FreeTDS
Description = Testing MSSQL Azure
Trace = No
Server = MySqlAzure.database.windows.net
Database = MyDatabase
# Port must be specified as default corresponds to Sybase
PORT=1433
# Sometimes incorrectly documented as "TDS Version" (space instead of underscore)
# Silently ignored when incorrect. Must be specified as default corresponds to Sybase
TDS_Version=7.1
# UID and PWD can only be specified in connection strings
# and are silently ignored in odbc.ini

azureuser@MyAzureUbuntu:~$ echo select 1 + 1 as Answer | isql Azure-MSSQL-FreeTDS -b MyUser MyPassword
+------------+
| Answer     |
+------------+
| 2          |
+------------+
SQLRowCount returns 1
1 rows fetched
azureuser@MyAzureUbuntu:~$

While we’re at it, we use isql to run a script that will create a table with 2 columns and 10K rows, all with random values between 0 and 1. isql does not support batch delimiters (such as GO), therefore each batch must fit on a single line:

azureuser@MyAzureUbuntu:~$ cat createTable.sql
set nocount on
select 'Creating table ' + db_name() + '..MyTable on machine ' + @@servername+ '...'
create table MyTable(x float not null, y float not null)
declare @i int = 0; while @i < 10000 begin insert into dbo.MyTable values(rand(), rand()); set @i += 1; end
select count(*) as [Total number of rows] from dbo.MyTable

azureuser@MyAzureUbuntu:~$ cat createTable.sql | isql Azure-MSSQL-FreeTDS -b MyUser MyPassword | grep -v +-
SQLRowCount returns -1
|
|
| Creating table MyDatabase..MyTable on machine MySqlAzure...
|
SQLRowCount returns 1
1 rows fetched
SQLRowCount returns -1
SQLRowCount returns 1
| Total number of rows|
| 10000            |
SQLRowCount returns 1
1 rows fetched
azureuser@MyAzureUbuntu:~$ 

Note: script may take a while to complete. Be patient 🙂

We can now query our table using the DNS and RODBC’s odbcConnect() function. Here we’re querying the x column of MyTable and showing the impact of increasing the sample size: the distribution of the samples mean gets more concentrated around the population mean (0.5).

library(RODBC)
conn <- odbcConnect("Azure-MSSQL-FreeTDS", "MyUser", "MyPassword")
ds <- sqlQuery(conn, "SELECT x FROM MyTable")
close(conn)
randomSamples <- function(times, sampleSize) {
    replicate(n = times, expr = mean(sample(ds$x, size = sampleSize, replace = FALSE)), simplify = TRUE)
}
par(mfrow=c(2,2))

numberSamples <- 100
mean <- .5

for (i in 2:5) {
    sampleSize <- i ^ 2
    plot(density(randomSamples(numberSamples, sampleSize)),
        main = paste0("Sample size: ", sampleSize),
        col = i,
        xlab = "",
        xlim = c(.2, .8))
    abline(v = mean)
}

 


par(mfrow=c(1,1))

Using RJDBC

library(RJDBC)
## Loading required package: DBI
## Loading required package: rJava
drv <- JDBC(
  driverClass = "net.sourceforge.jtds.jdbc.Driver", # check driver's documentation name
  classPath = "/usr/share/java/jtds.jar", # use locate and updatedb to find location
  identifier.quote="`")
conn <- dbConnect(drv, 
  "jdbc:jtds:sqlserver://MySqlAzure.database.windows.net/MyDatabase", 
  "MyUser",
  "MyPassword")
# only query 1/4 of the records otherwise we get a solid color graph 
ds <- dbGetQuery(conn, "select top 25 percent * from MyTable")
dbDisconnect(conn)
## [1] TRUE
plot(ds, 
     col=rgb(200,0,0,50, maxColorValue=255), 
     main = "Querying SQL Server with RJDBC", 
     pch = 19, cex = 1)

Using RSQLServer

Much easier to use than RJDBC (no url connector, driverClass and classpath to figure out). The prerequisites are the same as for RJDBC (RSSQLServer is a wrapper on top of RJDBC): install and register Java, then install the package.

    library(RSQLServer)
    conn <- dbConnect(
        RSQLServer::SQLServer(), 
        "MySqlAzure.database.windows.net", 
        "useNTLMv2=false;user=MyUser;Password=MyPassword",
        database = "MyDatabase")
    ds <- dbGetQuery(conn, "select * from MyTable")
    dbDisconnect(conn)
## [1] TRUE
    plot(
         density(ds$x*ds$y),
         col = "salmon",
         lwd = 3,
         main = "Querying SQL Server with RSQLServer")

Using Microsoft’s ODBC drivers

Microsoft provides Linux ODBC drivers for SQL Server that can be used with RODBC.

MyAzureSuse:/tmp # zypper install gcc 
MyAzureSuse:/tmp # tar zxvf msodbcsql-11.0.2260.0.tar.gz
[...]
MyAzureSuse:/tmp # cd msodbcsql-11.0.2260.0/
MyAzureSuse:/tmp/msodbcsql-11.0.2260.0 # ./build_dm.sh
[...]

MyAzureSuse:/tmp/msodbcsql-11.0.2260.0 # ./install.sh verify
[...]
MyAzureSuse:/tmp/msodbcsql-11.0.2260.0 # ./install.sh install
[...]
MyAzureSuse:/tmp/msodbcsql-11.0.2260.0 # odbcinst -q -d -n "ODBC Driver 11 for SQL Server"
[...]
MyAzureSuse:/tmp/msodbcsql-11.0.2260.0 # cat /etc/odbcinst.ini
[ODBC Driver 11 for SQL Server]
Description=Microsoft ODBC Driver 11 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2260.0
Threading=1
UsageCount=1

[Azure-MSSQL-Microsoft]
Driver = ODBC Driver 11 for SQL Server
Server = tcp:MySqlAzure.database.windows.net
Database = MyDatabase
Description = Testing MSSQL Azure using Microsoft's drivers

azureuser@MyAzureSuse:~> echo select 'Server=' + @@servername | isql Azure-MSSQL-Microsoft MyUser MyPassword | grep Server
| Server=MySqlAzure          

library(RODBC)
conn <- odbcConnect("Azure-MSSQL-Microsoft", "MyUser", "MyPassword")
sqlQuery(conn, "SELECT COUNT(*) AS CountRows FROM dbo.MyTable")
##   CountRows
## 1     10000
close(conn)