From the previous two blog posts:

• Creating REST API for reading data from Microsoft SQL Server in web browser
• Writing DAta to Microsoft SQL Server from web browser using REST API and node.js

We have looked into the installation process of Node.js, setup of Microsoft SQL Server and made couple of examples on reading the data from database through REST API and how to insert data back to database.

In this post, we will be looking the R predictions using API calls against a sample dataset.

To zoom out just a little bit, this will be the flow:

• Prediction model will be stored in MSSQL Server (along with train and test dataset)
• Using sp_execute_external_script we will retrieve model and have input parameters pass through the API
• Node server will return the result of prediction based on input parameters back to website
  

In SQL Server we will have all the needed SQL and R scripts.

Getting the data into SQL Server:

USE [APItest];

DROP TABLE IF EXISTS dbo.Iris;

CREATE TABLE dbo.Iris
(ID INT IDENTITY(1,1) NOT NULL
,Sepal_length DECIMAL(10,2)
,Sepal_width	 DECIMAL(10,2)
,Petal_length	 DECIMAL(10,2)
,Petal_width	DECIMAL(10,2)
,Species VARCHAR(20)
)

-- Populate sample data
INSERT INTO dbo.Iris
EXECUTE sp_execute_external_Script
 @language = N'R'
,@script = N'OutputDataSet <- iris'

After we have the data in SQL Table, we will create a procedure to train the model. We will be using RevoscaleR Decision Tree algorithm:

DROP TABLE IF EXISTS dbo.iris_model;
CREATE TABLE dbo.iris_model (
                 model_name VARCHAR(30) NOT NULL DEFAULT('default model') PRIMARY KEY
                ,model VARBINARY(MAX) NOT NULL
				,model_created DATETIME NOT NULL DEFAULT(GETDATE())
);

DROP PROCEDURE IF EXISTS Create_iris_model;
CREATE OR ALTER PROCEDURE Create_iris_model (@trained_model varbinary(max) OUTPUT)
AS
BEGIN
    EXECUTE sp_execute_external_script
      @language = N'R'
    , @script = N'
        require("RevoScaleR");
		iris_train_data$Species = factor(iris_train_data$Species);
        model_dtree <- rxDTree(Species ~ Sepal_length + Sepal_width + Petal_length + Petal_width, data = iris_train_data)
        trained_model <- as.raw(serialize(model_dtree, connection=NULL));'

    , @input_data_1 = N'SELECT [Species],[Sepal_length],[Sepal_width],[Petal_length],[Petal_width] FROM [APItest].[dbo].[Iris]'
    , @input_data_1_name = N'iris_train_data'
    , @params = N'@trained_model varbinary(max) OUTPUT'
    , @trained_model = @trained_model OUTPUT;
END;
GO

The R code that is wrapped in this SQL Procedure is:

       require("RevoScaleR");
		iris_train_data$Species = factor(iris_train_data$Species);
        model_dtree <- rxDTree(Species ~ Sepal_length + Sepal_width + Petal_length + Petal_width, data = iris_train_data)
        trained_model <- as.raw(serialize(model_dtree, connection=NULL));'

And what it does, it createa model that will predict a Species of the Iris flower based on inputed data of Sepal Length, Sepal width, Petal Length and Petal Width.

After we have created a procedure for outputting the model, we need to store the model:

DECLARE @model VARBINARY(MAX);
EXEC Create_iris_model @model OUTPUT;
INSERT INTO dbo.iris_model (model_name, model) VALUES('rxDTree', @model);
SELECT * FROM dbo.iris_model;

And finally, the last part will be the procedure to do the predictions. This will also be a SQL procedure that will serve as a wrapper around R script for delivering a predictions.

DROP PROCEDURE IF EXISTS Predict_iris;

CREATE OR ALTER PROCEDURE Predict_iris (@model VARCHAR(100),@q NVARCHAR(MAX))
AS
BEGIN
    DECLARE @rx_model VARBINARY(MAX) = (SELECT model FROM iris_model WHERE model_name = @model);

    EXECUTE sp_execute_external_script
        @language = N'R'
        , @script = N'
            require("RevoScaleR");
            iris_set = InputDataSet;
		    iris_model = unserialize(rx_model);
            iris_predictions = rxPredict(iris_model, iris_set, type = "class");'
                , @input_data_1 = @q
                , @output_data_1_name = N'iris_predictions'
                , @params = N'@rx_model varbinary(max)'
                , @rx_model = @rx_model
                WITH RESULT SETS ((
						iris_predictions VARCHAR(20)
						));

END;
GO

And again, the R code is in the SQL procedure:

            require("RevoScaleR");
            iris_set = InputDataSet;
		    iris_model = unserialize(rx_model);
            iris_predictions = rxPredict(iris_model, iris_set, type = "class");'

A simple class prediction with previously created decision trees model. Because the model is stored in database, it needs to be serialized to be saved and every time, you want to access the model, it needs to be unserialized for doing predictions.

The last part to do is, to call the predictions with new input parameters that we want to get the predictions for. In SQL, we simply call the procedure with input parameters as:

EXEC dbo.Predict_iris 
		@model = 'rxDTree'
	   ,@q ='SELECT CAST( 4.45 AS DECIMAL(10,2)) AS Sepal_length, 
			CAST(2.94 AS DECIMAL(10,2)) AS Sepal_width, 
			CAST(1.34 AS DECIMAL(10,2)) AS Petal_length, 
			CAST(0.41 AS DECIMAL(10,2)) AS Petal_width';
GO

Moving on to Node.js, we will create a new file, called predictRapp.js and is also available in Github. If you want to get on board with how to do it, please follow the previous two blog posts, you can grab the code there or fork the repository. The content of the predictRapp.js file is:

const express = require('express'); 
const app = express();
const sql = require('mssql/msnodesqlv8') 
var beautify = require("json-beautify");
 
var env = process.env.NODE_ENV || 'production';
var sqlConfig = require('./config')[env];
const path = require('path')
app.set("views", path.join(__dirname))
app.set("view engine", "ejs")
  
 

var server = app.listen(2908, function() {
  var host = server.address().address
  var port = server.address().port
 
  console.log("app listening at http://%s:%s", host, port)
});
 
const connection = new sql.ConnectionPool(sqlConfig, function(err){
      if (err){
      console.log(err);
      }
    }
)

app.get('/Predict', function(req,res){

   var Sepal_length = req.query.Sepal_length;
   var Sepal_width = req.query.Sepal_width;
   var Petal_length = req.query.Petal_length;
   var Petal_width = req.query.Petal_width;

   console.log("Value for Sepal_length: ",Sepal_length);
   console.log("Value for Sepal_width: ",Sepal_width);
   console.log("Value for Petal_length: ",Petal_length);
   console.log("Value for Petal_width: ",Petal_width);

  
 connection.connect(function(err) {
 connection.query("EXEC dbo.Predict_iris  @model = 'rxDTree',@q ='SELECT CAST( "+Number(Sepal_length)+" AS DECIMAL(10,2)) AS Sepal_length, CAST("+Number(Sepal_width)+" AS DECIMAL(10,2)) AS Sepal_width, CAST("+Number(Petal_length)+" AS DECIMAL(10,2)) AS Petal_length, CAST("+Number(Petal_width)+" AS DECIMAL(10,2)) AS Petal_width';",function(err,result){

 if(!!err){
 console.log(err);
 res.send('Error in reading');
 }
 else{
   res.status(200).type('JSON').send(beautify(result, null, 2, 100));
   console.log(result);  
 
 }});});

 });

We have created a app.get() function that calls procedure with parametrized input parameters that will come from website, through this section of code:

connection.query("EXEC dbo.Predict_iris  @model = 'rxDTree',@q ='SELECT CAST( "+Number(Sepal_length)+" AS DECIMAL(10,2)) AS Sepal_length, CAST("+Number(Sepal_width)+" AS DECIMAL(10,2)) AS Sepal_width, CAST("+Number(Petal_length)+" AS DECIMAL(10,2)) AS Petal_length, CAST("+Number(Petal_width)+" AS DECIMAL(10,2)) AS Petal_width';",function(err,result)

Once you store your file to your previously initialized node environment, you run the predict javascript file:

node predictRapp.js

And in web browser, run the following URL:

http://localhost:2908/Predict?Sepal_length=1&Sepal_width=3&Petal_length=1&Petal_width=0

We have tested another API call, with different input parameters, using URL:
http://localhost:2908/Predict?Sepal_length=6.45&Sepal_width=2.94&Petal_length=5.34&Petal_width=2.41

And got the result through API:

You can further integrate this/these predictions with your front-end application or use it in any other fashion.

In next blog post, we will look into the ability to do the Predictions with Python model and SQL Server using API and Power BI.

As always, all the code is available on Github – tomaztk/MSSQLServerRestAPI.