"file:\\C:\Users\v-thbeta\Downloads\9781509304318_Data%20Science%20with%20Microsoft%20SQL%20Server%202016_pdf.pdf"
library(RevoScaleR)
#connStr
instance_name <- "TRB_MICROSOFT"
database_name <- "WideWorldImportersDW"
connStr <- paste("Driver={SQL Server Native Client 11.0};Server=",
instance_name, ";Database=",database_name,";Trusted_Connection=yes;",sep="");
## Open a connection with SQL Server to be able to write queries with the rxExecuteSQLDDL function.
outOdbcDS <- RxOdbcData(table = "NewData", connectionString = connStr, useFastRead=TRUE)
rxOpen(outOdbcDS, "w")
# Create a variable to store the data returned from the SQL Server, with the user’s name,
# a variable for the parameters to pass to the SQL Server,
# the values you can pass to the RxSQLServerdata constructor
sqlShareDir <- paste("C:\\temp\\",Sys.getenv("USERNAME"),sep="")
sqlWait <- TRUE
sqlConsoleOutput <- FALSE
sqlShareDir
# Now we’ll set the compute context for the data object, using all the variables
# we just created.
cc <- RxInSqlServer(connectionString = connStr, shareDir = sqlShareDir, wait = sqlWait, consoleOutput =
sqlConsoleOutput)
# Next we can set the compute context to point to SQL Server R Services, defined earlier.
rxSetComputeContext(cc)
# We can then construct the T-SQL query. This one simply brings back three columns.
sampleDataQuery <- "select Col1, Col2, Col3 from MyTableName"
# Finally we run the query, using all of the objects set up in the script.
# Note that we’re using a colClasses variable to convert the data types to something
# R understands, since SQL Server has more datatypes than R, and we’re reading 500 rows
# at a time.
inDataSource <- RxSqlServerData(sqlQuery = sampleDataQuery, connectionString = connStr,
colClasses = c(Col1 = "numeric", Col2 = "numeric", Col3 = "numeric"), rowsPerRead=500)
# If I am a new user I might assume at the end of this exercise, I would have extracted rows and columns from SQL/Server
# and expected an R dataframe to be returned. They would have guessed wrong.
is.data.frame(inDataSource)
# For a new user, they need to begin understanding the object that was just created.
str(inDataSource)
Formal class 'RxSqlServerData' [package "RevoScaleR"] with 23 slots ..@ inSqlServer : logi(0) ..@ computeSqlQueryOnly : logi(0) ..@ table : NULL ..@ sqlQuery : chr "select Col1, Col2, Col3 from MyTableName" ..@ useFastRead : logi TRUE ..@ trimSpace : logi TRUE ..@ server : NULL ..@ dbmsName : NULL ..@ databaseName : NULL ..@ dsn : NULL ..@ user : NULL ..@ password : NULL ..@ connectionString : chr "Driver={SQL Server Native Client 11.0};Server=TRB_MICROSOFT;Database=WideWorldImportersDW;Trusted_Connection=yes;" ..@ rowBuffering : logi TRUE ..@ writeFactorsAsIndexes: logi FALSE ..@ isolationLevel : NULL ..@ id :<externalptr> ..@ colClasses : Named chr [1:3] "numeric" "numeric" "numeric" .. ..- attr(*, "names")= chr [1:3] "Col1" "Col2" "Col3" ..@ colInfo : NULL ..@ returnDataFrame : logi TRUE ..@ stringsAsFactors : logi FALSE ..@ rowsOrBlocksPerRead : int 500 ..@ compatibilityRequest :Classes 'CompatibilityRequest', 'R6' <CompatibilityRequest> Public: assertServerCapability: function (capability, notSupported, notKnown) clone: function (deep = FALSE) deferredAssertServerCapability: function (capability, notSupported, notKnown) getRequestedCapabilities: function () initialize: function (server, notSupported = capabilityNotSupported, notKnown = serverNotKnown) merge: function (request) requestCapability: function (capability) runDeferredAssertions: function (server) serialize: function (file) Private: deferredRequests: list notKnown: function (server, capability, warningMessage) notSupported: function (server, capability, errorMessage) requestedCapabilities: runCallback: function (type, server, capability, userHandler) server: ServerDefinition, AbstractServerDefinition
# A new user can't really do anything with the "inDataSource" object and since this chapter ends on page 14, a logical guess
# is to call rxImport on the object. Unfortunately, the user is not warned the example references a non-existent table object.
# And as part of the overall set-up, they were not told to load 'MyTableName' into SQL/Server or alter the example that maps
# to an existing table!
df1 <-rxImport(inDataSource)
[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'MyTableName'. [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared. [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'MyTableName'. [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared. Could not open data source.
Error in doTryCatch(return(expr), name, parentenv, handler): Could not open data source. Traceback: 1. rxImport(inDataSource) 2. rxImportBase(inSource = inData, outSource = outFile, rowSelection = rowSelection, . transforms = transforms, transformFunc = transformFunc, transformVars = transformVars, . transformEnvir = transformEnvir, transformPackages = transformPackages, . transformObjects = transformObjects, append = append, overwrite = overwrite, . numRows = numRows, reportProgress = reportProgress, verbose = verbose, . maxRowsByCols = maxRowsByCols, xdfCompressionLevel = xdfCompressionLevel, . createCompositeSet = createCompositeSet, blocksPerCompositeFile = blocksPerCompositeFile) 3. rxCall("RxImportDataSource", params) 4. tryCatch(.Call(sym, ..., PACKAGE = PACKAGE), interrupt = function(x) { . .C("RxUserBreak", ..., PACKAGE = PACKAGE) . stop("RevoScaleR function interrupted", call. = FALSE) . }) 5. tryCatchList(expr, classes, parentenv, handlers) 6. tryCatchOne(expr, names, parentenv, handlers[[1L]]) 7. doTryCatch(return(expr), name, parentenv, handler)