One of the more interesting features in SQL 2016 is the integration of the R language.
For those who haven’t seen it before, R is a statistical and data analysis language. It’s been around for ages, and has become popular in recent years.
R looks something like this (and I make no promises that this is well-written R). Taken from a morse-code related challenge
MessageLetters <- str_split(Message, "") MessageEncoded <- list(1:length(MessageLetters)) ListOfDots <- lapply(lapply(c(MaxCharacterLength:1), function(x) rep.int(".", times = x)), function(x) str_c(x, collapse='')) ListOfDashes <- lapply(lapply(c(MaxCharacterLength:1), function(x) rep.int("-", times = x)), function(x) str_c(x, collapse=''))
If you’re interested in learning R, I found the Learning R book to be very good.
SQL 2016 offers the ability to run R from a SQL Server session. It’s not that SQL suddenly understands R, it doesn’t. Instead it can call out to the R runtime, pass data to it and get data back
Installing the R components are very easy.
And there’s an extra licence to accept.
It’s worth noting that the pre-installed Azure gallery image for RC3 does not include the R services. Whether the RTM one will or not remains to be seen, but I’d suggest installing manually for now.
Once installed, it has to be enabled with sp_configure.
EXEC sp_configure 'external scripts enabled', 1 RECONFIGURE
It’s not currently very intuitive to use. The current way R code is run is similar to dynamic SQL, with the same inherent difficulties in debugging.
EXEC sp_execute_external_script @language = N'R', @script = N'data(iris) OutputDataSet <- head(iris)' WITH RESULT SETS (([Sepal.Length] NUMERIC(4,2) NOT NULL, [Sepal.Width] NUMERIC(4,2) NOT NULL, [Petal.Length] NUMERIC(4,2) NOT NULL, [Petal.Width] NUMERIC(4,2) NOT NULL, [Species] VARCHAR(30))); go
It’s possible to pass data in as well, using a parameter named @input_data_1 (there’s no @input_data_2) and from what I can tell from the documentation @parameter1, which takes a comma-delimited list of values for parameters defined with @params. There’s no examples using these that I can find, so it’s a little unclear how they precisely work.
See https://msdn.microsoft.com/en-us/library/mt604368.aspx and https://msdn.microsoft.com/en-us/library/mt591993.aspx for more details.
It’s not fast. The above piece of T-SQL took ~4 seconds to execute. This is on an Azure A3 VM. Not a great machine admittedly, but the R code, which just returns the first 6 rows of a built-in data set, ran in under a second on my desktop. This is likely not something you’ll be doing as part of an OLTP process.
I hope this external_script method is temporary. It’s ugly, hard to troubleshoot, and it means I have to write my R somewhere else, probably R Studio, maybe Visual Studio, and move it over once tested and working. I’d much rather see something like
CREATE PROCEDURE GetIrisData WITH Language = 'R' -- or USQL or Python or … AS … GO
Maybe in SQL Server 2020?