R in SQL Server – write R Scripts- Part 1

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);

so

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

https://docs.microsoft.com/en-us/sql/advanced-analytics/r/set-up-sql-server-r-services-in-database

https://docs.microsoft.com/en-us/sql/advanced-analytics/r/installing-and-managing-r-packages

Leila Etaati

Trainer, Consultant, Mentor

Leila is the first Microsoft AI MVP in New Zealand and Australia, She has Ph.D. in Information System from the University Of Auckland. She is the Co-director and data scientist in RADACAD Company with more than 100 clients in around the world. She is the co-organizer of Microsoft Business Intelligence and Power BI Use group (meetup) in Auckland with more than 1200 members, She is the co-organizer of three main conferences in Auckland: SQL Saturday Auckland (2015 till now) with more than 400 registrations, Difinity (2017 till now) with more than 200 registrations and Global AI Bootcamp 2018. She is a Data Scientist, BI Consultant, Trainer, and Speaker. She is a well-known International Speakers to many conferences such as Microsoft ignite, SQL pass, Data Platform Summit, SQL Saturday, Power BI world Tour and so forth in Europe, USA, Asia, Australia, and New Zealand. She has over ten years’ experience working with databases and software systems. She was involved in many large-scale projects for big-sized companies. She also AI and Data Platform Microsoft MVP. Leila is an active Technical Microsoft AI blogger for RADACAD.