MessageLetters <- str_split(Message, "")\r\n\r\nMessageEncoded <- list(1:length(MessageLetters))\r\n\r\nListOfDots <- lapply(lapply(c(MaxCharacterLength:1), function(x) rep.int(".", times = x)), function(x) str_c(x, collapse=''))\r\nListOfDashes <- lapply(lapply(c(MaxCharacterLength:1), function(x) rep.int("-", times = x)), function(x) str_c(x, collapse=''))\r\n<\/pre>\nIf you’re interested in learning R, I found the Learning R<\/a> book to be very good.<\/p>\nSQL 2016 offers the ability to run R from a SQL Server session. It\u2019s not that SQL suddenly understands R, it doesn\u2019t. Instead it can call out to the R runtime, pass data to it and get data back<\/p>\n
Installing the R components are very easy.<\/p>\n
![\"2016-05-23_09-39-43\"](\"https:\/\/www.sqlinthewild.co.za\/wp-content\/uploads\/2016\/05\/2016-05-23_09-39-43_thumb.png\" "\\"2016-05-23_09-39-43\\"")
<\/a><\/p>\nAnd there\u2019s an extra licence to accept.<\/p>\n
![\"2016-05-23_09-44-48\"](\"https:\/\/www.sqlinthewild.co.za\/wp-content\/uploads\/2016\/05\/2016-05-23_09-44-48.png\")
<\/a><\/p>\nIt\u2019s 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\u2019d suggest installing manually for now.<\/p>\n
Once installed, it has to be enabled with sp_configure.<\/p>\n
EXEC sp_configure 'external scripts enabled', 1\r\nRECONFIGURE<\/pre>\nIt\u2019s 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.<\/p>\n
EXEC sp_execute_external_script\r\n @language = N'R',\r\n @script = N'data(iris)\r\n OutputDataSet <- head(iris)'\r\n 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]\u00a0 NUMERIC(4,2) NOT NULL, [Species] VARCHAR(30)));\r\ngo<\/pre>\nIt\u2019s possible to pass data in as well, using a parameter named @input_data_1 (there\u2019s 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\u2019s no examples using these that I can find, so it\u2019s a little unclear how they precisely work.<\/p>\n
See https:\/\/msdn.microsoft.com\/en-us\/library\/mt604368.aspx<\/a> and https:\/\/msdn.microsoft.com\/en-us\/library\/mt591993.aspx<\/a> for more details.<\/p>\nIt’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.<\/p>\n
I hope this external_script method is temporary. It\u2019s 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\u2019d much rather see something like<\/p>\n
CREATE PROCEDURE GetIrisData\r\n WITH Language = 'R' -- or USQL or Python or \u2026\r\n AS\r\n\u2026\r\nGO<\/pre>\nMaybe in SQL Server 2020?<\/p>\n","protected":false},"excerpt":{"rendered":"