Home | Prev | Next

Building a connection

  • Need a driver name!
  • Need credentials (you can do trusted AD connections too)
  • Need to store the connection for use
library(RODBC)
#host<-"rmsftdp.northeurope.cloudapp.azure.com"
host<-"104.45.80.173"
db<-"AWDW16"
uname<-"Rdemo"
pwd<-"tugait"
connstring<-sprintf("DRIVER={ODBC Driver 11 for SQL Server};Server=%1$s;Database=%2$s;Uid=%3$s;Pwd=%4$s;",host,db,uname,pwd)

conn<-odbcDriverConnect(connstring)
# Test!
if(conn!=-1L) knitr::kable(sqlTables(conn,schema = "dbo"))
TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
AWDW16 dbo AdventureWorksDWBuildVersion TABLE NA
AWDW16 dbo DatabaseLog TABLE NA
AWDW16 dbo DimAccount TABLE NA
AWDW16 dbo DimCurrency TABLE NA
AWDW16 dbo DimCustomer TABLE NA
AWDW16 dbo DimDate TABLE NA
AWDW16 dbo DimDepartmentGroup TABLE NA
AWDW16 dbo DimEmployee TABLE NA
AWDW16 dbo DimGeography TABLE NA
AWDW16 dbo DimOrganization TABLE NA
AWDW16 dbo DimProduct TABLE NA
AWDW16 dbo DimProductCategory TABLE NA
AWDW16 dbo DimProductSubcategory TABLE NA
AWDW16 dbo DimPromotion TABLE NA
AWDW16 dbo DimReseller TABLE NA
AWDW16 dbo DimSalesReason TABLE NA
AWDW16 dbo DimSalesTerritory TABLE NA
AWDW16 dbo DimScenario TABLE NA
AWDW16 dbo FactAdditionalInternationalProductDescription TABLE NA
AWDW16 dbo FactCallCenter TABLE NA
AWDW16 dbo FactCurrencyRate TABLE NA
AWDW16 dbo FactFinance TABLE NA
AWDW16 dbo FactInternetSales TABLE NA
AWDW16 dbo FactInternetSalesReason TABLE NA
AWDW16 dbo FactProductInventory TABLE NA
AWDW16 dbo FactResellerSales TABLE NA
AWDW16 dbo FactResellerSalesXL_CCI TABLE NA
AWDW16 dbo FactResellerSalesXL_PageCompressed TABLE NA
AWDW16 dbo FactSalesQuota TABLE NA
AWDW16 dbo FactSurveyResponse TABLE NA
AWDW16 dbo NewFactCurrencyRate TABLE NA
AWDW16 dbo ProspectiveBuyer TABLE NA
AWDW16 dbo sysdiagrams TABLE NA
AWDW16 dbo vAssocSeqLineItems VIEW NA
AWDW16 dbo vAssocSeqOrders VIEW NA
AWDW16 dbo vDMPrep VIEW NA
AWDW16 dbo vTargetMail VIEW NA
AWDW16 dbo vTimeSeries VIEW NA

RODBC functions

RODBC comes with some built-in functions for working with your database

dt<- sqlFetch(conn, "DatabaseLog")
sqlCopy( conn, "select * from DatabaseLog", "DatabaseLog2")
sqlSave( conn, dt[1:100,], "DatabaseLog2")
sqlClear(conn, "DatabaseLog2")
sqlDrop( conn, "DatabaseLog2")

SQL

Use normal SQL statements using sqlQuery() - must send one statement at a time to get the results back from each.

dt<- sqlQuery(conn,"
         SELECT 
          DatabaseLogID
          ,DatabaseUser
          ,Event
          ,[Schema]
          ,Object
          FROM DatabaseLog
         ")
knitr::kable(head(dt))
DatabaseLogID DatabaseUser Event Schema Object
1 dbo CREATE_FUNCTION dbo udfBuildISO8601Date
2 dbo CREATE_FUNCTION dbo udfMinimumDate
4 dbo CREATE_TABLE dbo AdventureWorksDWBuildVersion
3 dbo CREATE_FUNCTION dbo udfTwoDigitZeroFill
5 dbo CREATE_TABLE dbo DimAccount
20 dbo CREATE_TABLE dbo DimScenario 
str( sqlQuery(conn,"
        SELECT COUNT(*) as Vol FROM DatabaseLog;
         SELECT DatabaseLogID FROM DatabaseLog;
         ")
)
## 'data.frame':    1 obs. of  1 variable:
##  $ Vol: int 96

Work “at scale”

Microsoft R Server comes with an additional package RevoScaleR that allows you to connect to SQL Server and process very large amounts of data without loading it all into memory. This package is not available in R or Microsoft R Server Open.

Read more about it Getting Started with RevoScaleR

Exercise

  1. Return all FactResellerSales data along with the associated Reseller details

Answer

sales<- sqlQuery(conn,"
        SELECT TOP 10 * FROM FactResellerSales f
        INNER JOIN DimReseller r
          ON f.resellerkey = r.resellerkey
         ")
knitr::kable(head(sales))
ProductKey OrderDateKey DueDateKey ShipDateKey ResellerKey EmployeeKey PromotionKey CurrencyKey SalesTerritoryKey SalesOrderNumber SalesOrderLineNumber RevisionNumber OrderQuantity UnitPrice ExtendedAmount UnitPriceDiscountPct DiscountAmount ProductStandardCost TotalProductCost SalesAmount TaxAmt Freight CarrierTrackingNumber CustomerPONumber OrderDate DueDate ShipDate ResellerKey.1 GeographyKey ResellerAlternateKey Phone BusinessType ResellerName NumberEmployees OrderFrequency OrderMonth FirstOrderYear LastOrderYear ProductLine AddressLine1 AddressLine2 AnnualSales BankName MinPaymentType MinPaymentAmount AnnualRevenue YearOpened
349 20101229 20110110 20110105 676 285 1 100 5 SO43659 1 1 1 2024.994 2024.994 0 0 1898.094 1898.094 2024.994 161.9995 50.6249 4911-403C-98 PO522145787 2010-12-29 2011-01-10 2011-01-05 676 418 AW00000676 967-555-0129 Value Added Reseller Better Bike Shop 19 S 4 2010 2013 Mountain 42525 Austell Road NA 8e+05 United Security NA NA 80000 1979
350 20101229 20110110 20110105 676 285 1 100 5 SO43659 2 1 3 2024.994 6074.982 0 0 1898.094 5694.283 6074.982 485.9986 151.8746 4911-403C-98 PO522145787 2010-12-29 2011-01-10 2011-01-05 676 418 AW00000676 967-555-0129 Value Added Reseller Better Bike Shop 19 S 4 2010 2013 Mountain 42525 Austell Road NA 8e+05 United Security NA NA 80000 1979
351 20101229 20110110 20110105 676 285 1 100 5 SO43659 3 1 1 2024.994 2024.994 0 0 1898.094 1898.094 2024.994 161.9995 50.6249 4911-403C-98 PO522145787 2010-12-29 2011-01-10 2011-01-05 676 418 AW00000676 967-555-0129 Value Added Reseller Better Bike Shop 19 S 4 2010 2013 Mountain 42525 Austell Road NA 8e+05 United Security NA NA 80000 1979
344 20101229 20110110 20110105 676 285 1 100 5 SO43659 4 1 1 2039.994 2039.994 0 0 1912.154 1912.154 2039.994 163.1995 50.9999 4911-403C-98 PO522145787 2010-12-29 2011-01-10 2011-01-05 676 418 AW00000676 967-555-0129 Value Added Reseller Better Bike Shop 19 S 4 2010 2013 Mountain 42525 Austell Road NA 8e+05 United Security NA NA 80000 1979
345 20101229 20110110 20110105 676 285 1 100 5 SO43659 5 1 1 2039.994 2039.994 0 0 1912.154 1912.154 2039.994 163.1995 50.9999 4911-403C-98 PO522145787 2010-12-29 2011-01-10 2011-01-05 676 418 AW00000676 967-555-0129 Value Added Reseller Better Bike Shop 19 S 4 2010 2013 Mountain 42525 Austell Road NA 8e+05 United Security NA NA 80000 1979
346 20101229 20110110 20110105 676 285 1 100 5 SO43659 6 1 2 2039.994 4079.988 0 0 1912.154 3824.309 4079.988 326.3990 101.9997 4911-403C-98 PO522145787 2010-12-29 2011-01-10 2011-01-05 676 418 AW00000676 967-555-0129 Value Added Reseller Better Bike Shop 19 S 4 2010 2013 Mountain 42525 Austell Road NA 8e+05 United Security NA NA 80000 1979