1-RODBC.R

## 1-RODBC.R
### Connect to SQL Server using RODBC

library(RODBC)
library(magrittr)

# Connect to SQL Server using RODBC ------------------

sqlHost <- "DAA136209339.sys-sqlsvr.local"
sqlDatabase <- "RevoTestDB"
dsnString <- "driver={SQL Server};server=%s;database=%s;trusted_connection=true"
dsn <- sprintf(dsnString, sqlHost, sqlDatabase)
dbHandle <- odbcDriverConnect(dsn)

# Send query and return data -------------------------

query <- "SELECT * FROM AirlineDemoSmall"
airDataFrame <- sqlQuery(dbHandle, query)

airDataFrame %>% head
str(airDataFrame)

# Create linear model using lm() ---------------------

day.names <- paste0(c("Mon", "Tues", "Wednes", "Thurs", "Fri", "Satur", "Sun"),
                    "day")
levels(airDataFrame$DayOfWeek) <- day.names

model.lm <- lm(ArrDelay ~ CRSDepTime + DayOfWeek - 1, airDataFrame)
summary(model.lm)

library(coefplot)
coefplot(model.lm, title = "Linear model using lm()")


# Close connection -----------------------------------

# odbcClose(dbHandle)


## 2-RRE-local-compute-context.R
### Use RevoScaleR RxLocalParallel compute context

day.names <- paste0(c("Mon", "Tues", "Wednes", "Thurs", "Fri", "Satur", "Sun"),
                    "day")

# Define input object as local csv file --------------

air.csv <- RxTextData(
  "airdata.csv",
  rowsPerRead = 100e3,
  delimiter = ",",
  colInfo = list(ArrDelay = list(type = "integer"),
                 DayOfWeek = list(type = "factor", levels = day.names)
  )
)

# Convert to xdf -------------------------------------

xdf <- "airdata.xdf"
rxDataStep(air.csv, outFile = xdf, overwrite = TRUE)

# Set local parallel compute context------------------

rxSetComputeContext(
  RxLocalParallel()
)

rxGetInfo(xdf)
rxGetVarInfo(xdf)

# Get data summary and create linear model -----------

rxSummary(~ArrDelay + DayOfWeek, data = xdf, reportProgress = 0)

model.rxLinMod <- rxLinMod(ArrDelay ~ CRSDepTime + DayOfWeek - 1, xdf, reportProgress = 2)
summary(model.rxLinMod)

library(coefplot)
coefplot(model.rxLinMod, title = "Linear model using rxLinMod()")


## 3-RRE-SQL-Server-compute-context.R
### Use RevoScaleR RxInSqlServer compute context

sqlHost <- "DAA136209339.sys-sqlsvr.local"
sqlDatabase <- "RevoTestDB"
sqlUser <- "xxxxxxx"
sqlPw <- "xxxxxxxx"


day.names <- paste0(c("Mon", "Tues", "Wednes", "Thurs", "Fri", "Satur", "Sun"),
                    "day")

# Define SQL Server connection string ----------------

con <- sprintf("Driver=SQL Server;Server=%s;Database=%s;Uid=%s;Pwd=%s",
                            sqlHost, sqlDatabase, sqlUser, sqlPw)

# Define data object, including database table -------

airData <- RxSqlServerData(
  connectionString = con,
  table = "AirlineDemoSmall",
  colInfo = list(ArrDelay = list(type = "integer"),
                 DayOfWeek = list(type = "factor", levels = day.names)
  )
)

# Define compute context -----------------------------

rxSetComputeContext(
  RxInSqlServer(connectionString = con,
                autoCleanup = FALSE,
                consoleOutput = TRUE
  )
)

# Perform analysis inside database -------------------

model.rxLinMod <- rxLinMod(ArrDelay ~ CRSDepTime + DayOfWeek - 1, airData)
summary(model.rxLinMod)

library(coefplot)
coefplot(model.rxLinMod, title = "Linear model using rxLinMod()")

## 4-Stored-procedure-1.sql
EXECUTE   sp_execute_external_script
                @language = N'R'
              , @script = N'OutputDataSet <- InputDataSet'
              , @input_data_1 = N'SELECT 1 as Col'
              WITH RESULT SETS ((col int not null));
go


## 5-Stored-procedure-2.sql
EXECUTE   sp_execute_external_script
                @language = N'R'
              , @script = N'OutputDataSet <- subset(iris, select=-Species);'
              --, @parallel = 0
              , @input_data_1 = N'SELECT 1 as Col'
              WITH RESULT SETS (("Sepal.Length" float not null, "Sepal.Width" float not null
			  , "Petal.Length" float not null, "Petal.Width" float not null));
go