Connect to SQL SERVER in R

R is an OPEN SOURCE software environment for statistical computing and graphics. It is easy to use and create valuable data visualization in a minute. Learn more about R.

This blog post is to explain the steps to connect to SQL Server database in R and display simple graphics.

Over all steps

1. Create ODBC Data Source
2. Setup R environment to connect to SQL Server
3. Query the SQL table, display data and graphics

Step 1: Create ODBC Data Source:

Goto, Control Panel -> Administrative Tools -> Data Sources (ODBC)

Click Add button

select SQL Server Native Client and click Finish

Give data source name and SQL Server name to connect and Click Next

Select an appropriate SQL Server authentication option and give valid credential. In this example, we are using Integrated Windows Authentication. Click next

Select the database to work with and modify the application intent option to Read-only. These settings are for this exercise only. We may use default setting and click next.

Click finish and TEST connection

Step 2: Setup R environment to connect to SQL Server.  Please install R software.

Install RODBC package to R:

• install.packages(“RODBC”)

Load the RODBC library to current environment to use:

• library(RODBC)

Connect to SQL Server using ODBC connection:

• wrpt <-odbcConnect(“weatherdata”)

We have just connected to SQL Server and the R connection variable name is wrpt. We can use this variable to query a table and more.

Step 3: Query the SQL table to display data and graphics

This statement loads the entire table “tblTemp” in to the variable weather.

weather <- sqlFetch(wrpt, “tblTemp”)  

This statement displays the entire table

print(weather)

This statement displays the summary of the data-set

summary(weather)

This statement displays a graph with daily max temperature for a  month

plot(weather$maxtemp, type=”l”)

R manuals

Thanks for reading this post.