From 2016 we able to do machine learning inside the SQL Server 2016 and 2017.
In these post series, I am going to show some tips and trips for that. such as set up R services in SQL Server 2016, how to be sure we able to do that, and also how we can see the list of installed packages there.
First- Installation R server
the detail explanation of how to set up ML services or R services inside SQL Server.
In this post, I am going to show how to set up R server inside SQL Server 2016,
1-Run SQL Server Setup
2-On the Installation tab, select New SQL Server stand-alone installation or add features to an existing installation
3-Feature Selection: please select below features
Select Database Engine Services.
Select R Services (In-Database).
4- On the Consent to Install Microsoft R Open page, select Accept. then select Next, complete installation and restart the SQL Server.
Second- Enable Scripts as below.
Now we are going to see that we able to write R codes or not!
R scripts are external language, so we need a stored procedure that enables us to write R codes as below
so I execute a configure store procedure with the name “external scripts enabled” with variable 1,
then to verify it, I wrote the below scripts
I just run it to see if the column “run_value” is enabled, that means 1 for enable and 0 for not enabled.
now let’s write some simple R codes just get some value from SQL Scripts and then assign it to a variable in R as below :
the first parameters @language is assigned to N’R’, the second one is about the R scripts that is a simple one assign the values in variable “InputDataset” to variable “OutputDataset”, which is going to store in @script input variable for SP.
the third variable is @inout_data_1 get the data from SQL Server via a query like “select 1 as hello”!
finally, the result will be stored in an integer variable in a column name “hello”.
just run the code and see the result you will get a column name “hello” with value 1.
Location of R libraries in R server
there is a possibility to see the location of the libraries for R you need to run a simple R code as below
OutputDataSet <- data.frame(.libPaths());
so I just put the above code in my SP input variable @scripts as below
it gives me the location of the libraries in a folder as below
so as you see we have 47 libraries in my R services.
now I need to see the name of them in SQL Server as well
so I need other codes as below, that help me to find out what packages has been installed. there is a function name” install.packages()” that return these packages to be stored in a variable.
packagematrix <- installed.packages(); NameOnly <- packagematrix[,1]; OutputDataSet <- as.data.frame(NameOnly);
This post was an introduction to how to use R in SQL Server, I will go through some more posts about using R and Python in SQL Server 2016 and 2017. Also, see the below links for more explanation