Getting R to run on Sql Server 2016

 

I have slowly been moving into the R space for a couple of years, and see it as both an exciting DSL as well as a journey from Sql and C# experience into the realm of serious data analysis and data science (and visualization).

It is a giant contribution that Microsoft is making by making the results of it’s Revolution Analytics acquisition more or less freely available to the world. I was able to attend the EARL conference on R applications in London some weeks back, and it was an exciting view into the world of analytics!

Today I got around to fire up some R on an Sql 2016, with an EARL session by Adam Rich as my starting point. It was somewhat laborious and the documentation and samples around the web were really convoluted or sometimes plain misleading. I really had to cut through a lot of extraneous information and so for future reference, here is what I did.

I had already installed Microsoft R and RStudio (and VS R tools, though I didn’t use them for this) as well as Sql Server 2016 with R, but at first I just wanted to check out the server with a very simple script:

EXEC sp_execute_external_script  @language =N’R’,   
    @input_data_1 = N’SELECT 42 AS TheAnswer’,
    @script = N’OutputDataSet <- InputDataSet’
    WITH RESULT SETS (([TheAnswer] int not null));

This failed with the message:

‘sp_execute_external_script’ is disabled on this instance of SQL Server. Use sp_configure ‘external scripts enabled’ to enable it.

The answer to this was to run these commands and then to restart the database service:

sp_configure ‘external scripts enabled’, 1; 
RECONFIGURE; 

 

Now it was time to try my hand at an R script that I would write locally on my PC and run remotely on the Sql Server with a trusted connection – in R parlance in a remote compute context – pulling a bit of data. First, however, the Microsoft R Client had to be installed – the version I had apparently didn’t  include the MRO stuff required to connect to the server – you can check the “c:\Program Files\Microsoft\MRO”  directory to verify that you have both an “MRO” and “R Client” directory.

A major problem was this message when setting up the server-connection (RxInSqlServer):

Error in .rxEnsureOdbcCredentials(connectionString, server, databaseName,  : 
  Trusted_Connection or User must be specified either in the connection string with the uid keyword or as a named parameter.
 

Most samples included a connectionstring for the RxInSqlServer-function, but this seems to be unnecessary.  I had to fiddle quite a bit with the parameters for the various special R server functions, ie those prefixed with RX. Also note that sometimes the prefix is Rx and sometimes rx… It appears that 2 connections are necessary: one for the R server computing context and one when executing Sql in the R context. But, to cut a long story short, this is what I got to work as a sort of minimum solution:

# Set up a trusted connection to R in Sql 2016

sqlCompute <- RxInSqlServer(
    server = “LC_SQL”,
    #wait = TRUE,
    #consoleOutput = FALSE,
    #shareDir = “e:/”,

    user = “Trusted_Connection”
    )

rxSetComputeContext(sqlCompute) # Switch script execution to Sql server

sqlConnString <- “Driver=Sql Server; Server=localhost; Database=master; Trusted_Connection = True”;

sqldata <- RxSqlServerData(sqlQuery = “select name, database_id from sys.databases”, connectionString = sqlConnString)
rxGetVarInfo(data = sqldata) # Show our variables
myframe <- rxImport(inData = sqldata) # Get data as a dataframe
print(myframe)
rxSetComputeContext(RxLocalSeq()) # Switch script execution back to local machine

 

Hope this can help someone struggling with the same challenges Smile

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s