[MUSIC]
Hi, I'm Nellie, a program manager in the SQL Server team.
In this video, I'm going to cover how you can deploy a predictive
model in SQL Server using R Services.
I will begin with a quick intro to R Services before diving into a demo.
With SQL Server 2016, we released R services,
which basically integrated the R runtime with the database engine.
R is a very popular statistical and
machine learning programming language.
The biggest advantage with R is its big open source community and
thousands of packages available.
R is great, but there can be some challenges using R.
For example, when you execute an R script,
the data is moved to the R runtime.
This can cause problems if you have large volumes of data.
The movement of data also can pose security risks.
And how do you call your R script from an application in production?
Sometimes you have to recode the R script in some other language
to make this possible, and this can be time consuming.
And most R functions are single threaded and
only accommodate data sets that fit into available memory, and
this limits scale and performance.
SQL Server R Services provides in-database
advanced analytics capabilities.
This means that we are
pushing the Intelligence to where the data lives.
This is a big win for developers.
Now you don't have to build all of this logic separately in
the application layer.
And just like you can share data from databases across multiple
applications.
You can now share predictive models.
By pushing the intelligence closer to the data,
we also address the challenges I mentioned before.
We reduce or eliminate the data movement since you can push
the compute to execute on SQL Server.
You can now deploy your R scripts and easily access them by
invoking a T-SQL stored procedure from your production application.
And when it comes to scale and performance, you can leverage
SQL Server capabilities like parallel query processing.
ColumnStore indexes, and
in-memory tables alongside your R scripts to boost performance.
In addition to that, you can also leverage RevoScaleR functions for
parallel execution.
Before going into a demo of how to build your first predictive model,
let's go through the process of deploying a predictive model
with SQL Server.
You can basically start developing and
experimenting in your favorite development tool.
It's easier to debug and
experiment this way until you're happy with the model.
Once you're ready to deploy, you can train the model in the database
by moving your R code into SQL Server inside a special
stored procedure called sp_execute_external_script.
Once the model is generated, you save the model to a table and
now you can use that model to predict an outcome.
Now let's go through a demo where we create a predictive model in R and
deploy it using R services.
We're going to start developing our R script in an R development
enviroment.
I'm using R Tools for Visual Studio, but
you can of course use any tool that you prefer.
The first step is to connect to the database and
get the data we wanna use.
Let's look at the data.
As you can see here, this data is ski rental data,
just giving us the number of rentals that happen on a specific day.
And we are actually gonna predict the number of rentals
for the future.
The first step after that is to prepare our data.
And we are changing some of the columns here to factor types, since
we want to tell the model that these columns contain categorical values.
It's now time to train our data.
Or train our model.
Before we can do that,
we're splitting the data set into two different sets,
a training data set and a testing or validation data set.
Now we can generate two different models,
the reason why I'm generating two different models in this case.
Is that we want to experiment, we want to derive the best results.
So we're gonna generate two different models and
then pick the model that's the most accurate.
One of these models is the linear regression model and
the other one is the decision tree model.
It's now time for our prediction.
To predict, we're gonna use the model objects that we
just generated and we're gonna call the rx predict function.
Down here you can see the results from the two different predictions.
You can see the predicted value of the rental counts and
you can see the actual values and the rest of the columns.
Now, to compare the accuracy of the two different models,
there are different methods that you can use.
To keep things simple in this case,
I am just going to plot the difference between the actual and
predicted values for each of the models.
And try to get an understanding of which one that is more accurate.
And it seems like the decision tree model
looks more accurate since the values are less scattered in the plots.
Let's use that and deploy it in SQL Server.
I'm moving to SQL Server Management Studio.
I already have my data in the database here.
It's the same rental data that we used in the Rscript.
Before we can generate the model,
we have to create a table where we can actually store the model.
Now, that we have that table, let's create a stored procedure.
And inside of that stored procedure,
a call to sp_execute_external_script,
where we can copy some of the R code that we used in Visual Studio.
And we're gonna create this stored procedure in order to train our
model.
But the training doesn't happen until we execute it.
So let's execute the stored procedure and save the output from
it, which is a model, into the table that we created.
As you can see down here,
this is the actual model object saved in the database.
It's now time to predict the number of rentals.
So we need to generate another store procedure that contains
some R code for predicting the rental counts.
>> [INAUDIBLE].
>> Now let's create a stored procedure to predict the RentalCounts.
In this stored procedure similar to the previous one, you can insert
your R code inside the sp_execute_ external_script call.
And the outputs from this store procedure is going to be
a result set containing our predicted values.
Before we get our results from the prediction store procedure,
let's create a table where we can store the results.
Now in this step, I'm going to execute the rental prediction stored
procedure, and I'm gonna save the results into a table.
I'm doing this because I happen to have a web application that is built
with Node JS.
And this application expects JSON types to be returned to it.
So I'm going to create an additional store procedure where I select from
my table, and return the types as JSON.
You can see the results from the store procedure here.
Now, if I go to my application I can simply do an identical
call to the stored procedure to get my JSON results back.
I'm going to start my application here and go to the browser and
refresh.
And as you can see I know have the predicted results
in my web application.
And I can use this to make my application more intelligent.
Now you know how to build a predictive model in R and
deploy it using R services.
If you would like to walk through this demo on your own,
please visit aka.ms/sqldev/r.
Thank you for watching.
[MUSIC]