Connect R to SQL Server 2012 and “14”

This post will demonstrate how to connect R to Microsoft SQL Server, so that data can be extracted directly from a database by using SQL-statements. The approach described in this post is supported by both SQL Server 2012 and the upcoming SQL Server “14”. You can connect to SQL Server using different techniques – one of them is by using ODBC. This post will use ODBC.

The first time you need to connect to a database, you need to perform some one-time tasks, which are:

  • Create a ODBC DSN data source
  • Install necessary R-packages from CRAN

The screenshot below shows a table containing the well-known data set ‘weather.nomnial’. The table is part of a database named ‘MiningDataSets’. The goal of this tutorial is to load all this data into R.

SQLR00
Create DSN
First we need to setup a user DSN data source pointing at our SQL Server using ODBC. The data source will be called from R using the package “RODBC”

  1. Open “Administrative Tools” and “ODBC Data Sources (32 bit)”

WEKASQL02

  1. On the tab “User DSN” press “Add”
  2. Select “SQL Server” in the provider list
  3. Now give the data source a name and a description. Remember this name – you will need it later on in the process. Last provide the name of the server where the SQL Server is installed. Press “Next”.

WEKASQL03

  1. Select the way in which you will authenticate against the SQL Server. In this case we use Integrated Security. Press “Next”.

WEKASQL04

  1. You now have the possibility to select the default database for the data source. Here we choose to point at the ‘MiningDataSets’. Press “Next” and the “Finish”.

WEKASQL05

  1. On the last page remember to press the “Test Connection” button and ensure that the connection could be established.
  2. The User DSN is now created and active.

WEKASQL06


Install and load RODBC

Support for SQL Server is not possible using native R – therefore we have to install the RODBC package from CRAN.

  1. Open up R and in the console window type: install.packages(“RODBC”)

    SQLR01

  2. The RODBC packages is now downloaded and installed on your system. Next step is to load the package into R so the functions of the package can be used. In the console window type: library(“RODBC”)

    SQLR02

  3. The RODBC package is now loaded and ready

Connect with R
Now it is time to connect to the SQL Server database from R and retrieve the nominal weather dataset.

  1. When calling the database you first have to open a connection to the database. And after you have performed you operations, you have to close the connection again. This is done by using the commands odbcConnect() and odbcClose(). The name specified as the parameter is the name of the ODBC user data source.

    SQLR03

  2. First we want to read an entire table into R – this can be done by using the sqlFetch command.

    SQLR04

  3. If you want to load the data into a data.frame, this can easily be done

    SQLR05

  4. If you want to execute a specific SQL-query, this can be done using the sqlQuery-command.

    SQLR06

You can use many more SQL-statements against the database by using different RODBC-functions. You can get more help about the RODBC-package by typing RShowDoc(“RODBC”, package=”RODBC”)

SQLR07

I hope this little tutorial helped you in connecting R to SQL Server 2012 / “14”.

If you are interested in using the data mining workbench WEKA together with SQL Server, the see my tutorial on that here.

Advertisements
This entry was posted in Analytics and Data Mining, Microsoft SQL Server and tagged . Bookmark the permalink.

18 Responses to Connect R to SQL Server 2012 and “14”

  1. Pingback: Connect WEKA to SQL Server 2012 and “14” | Anders Spur Hansen

  2. Nithin says:

    thank you. this worked for me.

  3. gillberke says:

    Thanks, very helpfull!

  4. IcyBricks says:

    I was able to add the DSN and tested my connection successfully but it appears R needs to know my username?

    > odbcChannel <- odbcConnect("rp")
    Warning messages:
    1: In odbcDriverConnect("DSN=rp") :
    [RODBC] ERROR: state 28000, code 18456, message [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ''.
    2: In odbcDriverConnect("DSN=rp") : ODBC connection failed

    how do I fix this?

  5. IcyBricks says:

    I figured it out
    > odbcChannel <- odbcConnect("rp", uid="myuserid", pwd="mypassword")

  6. Tim says:

    thanks, nicely done!

    one note, if using SQLServer Express, make sure to include “\SQLEXPRESS” in the Server name when creating the DSN.

  7. Tim says:

    Works great. And thanks, IcyBrick, for the additional troubleshooting info that also solved my issue.

  8. Pingback: MS SQL notes | ThinkValley

  9. Paul G says:

    Fabulous tutorial. Exactly what I wanted!

    Thanks

  10. KHK says:

    Thank you!

  11. Louis says:

    Amazing, thank you!

  12. can I connect R-Server to SQL Server 2012 or 2014 using remote execution by setting the context to the R-server ? and are there any other approach ?

  13. Ram says:

    This worked for me very well! Thanks!

  14. HK Nhan says:

    Exactly what I was looking for. Thanks so much!

  15. mohsen says:

    thank you very much indeed…
    worked for me .

  16. kendiflawed says:

    Thank you so much!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s