In stephlocke/Rtraining: Training Materials on R

Step 3: Connect to the database

I'll report on global bike sales from our company's database

Database provided by Redgate, hosted on Azure
Use your relevant driver (often the one you'll use in Excel) data
You can also connect to hundreds of other data stores like Excel

Establishing a connection

# Getting data
library("RODBC")

# Manipulating data
library("data.table")

# Build a connection to the DB for reuse
# Driver names can vary eg 
# {ODBC Driver 11 for SQL Server}
azure <- odbcDriverConnect(
          "Driver={SQL Server Native Client 11.0};
          Server=mhknbn2kdz.database.windows.net;
          Database=AdventureWorks2012;
          Uid=sqlfamily;
          Pwd=sqlf@m1ly;")

Getting our starting data

Order    <- data.table( sqlQuery( azure, 
         "SELECT * FROM [Sales].[SalesOrderHeader]"))

Territory<- data.table( sqlQuery( azure, 
         "SELECT * FROM [Sales].[SalesTerritory]"))

Region   <- data.table( sqlQuery( azure, 
         "SELECT * FROM [Person].[CountryRegion]"))

if(azure!=-1L){
Order    <- data.table( sqlQuery( azure, 
         "SELECT * FROM [Sales].[SalesOrderHeader]"))

Territory<- data.table( sqlQuery( azure, 
         "SELECT * FROM [Sales].[SalesTerritory]"))

Region   <- data.table( sqlQuery( azure, 
         "SELECT * FROM [Person].[CountryRegion]"))
}
#Some error handling for if generating purely offline
if(azure==-1L){
library(data.table)
Order<-fread(system.file("extdata","Order.csv",package = "Rtraining"))
Region<-fread(system.file("extdata","Region.csv",package = "Rtraining"))
Territory<-fread(system.file("extdata","Territory.csv",package = "Rtraining"))
}