How to integrate SQL Server and Azure Machine Learning

Good, clean and reliable data is important for every company, because well-analyzed data will open new possibilities to success. When we look at successful companies, we see that they’ve analyzed customers, sales, and finance data very well. At this point, a game changer enters: machine learning.

Machine learning

Machine learning is a type of artificial intelligence which analyzes data in order to predict future and discover hidden patterns. The output helps us make decisions. Machine learning is widely used in finance, healthcare and marketing.

Machine learning …

Develops itself by continuous learning
Provides a forecast for the future
Finds out hidden patterns in data
Supports more effective algorithms than traditional algorithms

Azure Machine Learning

Azure Machine Learning aka Azure ML is a cloud-based computer driven learning environment developed by Microsoft. Azure Machine Learning helps us to make decisions by analyzing data and using it to predict future patterns and outcomes. Suppose you have sales data and you want to make some decisions, for next year, based on it. You don’t need experienced employees, advanced algorithms or large-capacity computers. Azure Machine Learning allows making these calculations on the cloud.

In this article we will integrate Microsoft SQL Server and Azure Machine Learning. So we will have artificial intelligence code on the SQL Server Engine which machine learning model created by us. This code can be used in Power BI, SQL Server Reporting Service or SQL Server Mobile Reports. With this integration, SQL Server gains machine learning ability.

Shortly, in this article we will:

Create an Azure Machine Learning experiment on Azure Machine Learning Studio
Publish an Azure Machine Learning model on web service
Enable a SQL Server external script
Call this service from SQL Server with R script support

Our first Azure Machine Learning experiment

In this experiment we will create a linear regression model on Azure ML. Linear regression is the most widely known algorithm. In first step we will login into Azure Machine Learning Studio and create a blank experiment.

We will add sample dataset which name is “Energy Efficiency Regression Data” and then add “Select Columns in Dataset” component. This sample dataset contains cooling data and the conditions which these values are generated. Such as surface area, wall area etc.

We will select columns for the machine learning model. These columns are “Surface Area”, “Wall Area”, ”Roof Area” and “Cooling Load”. Based on our theoretical company, these are variables that are used to determine the load and costs of cooling for our building structures.

We will use the first three variables: “Surface Area”, “Wall Area”, “Roof Area” to predict the “Cooling Load”

We will add “Split Data” component. This component allows us to divide dataset into training data ((training data is used as part of the machine learning process to build the model that will produce our results) and test data (test data is used to evaluate output of algorithm). We will set “Fraction of rows in the first output dataset” to 0.8. This value defines that 80% of data will be used for training and 20% of data for testing.

In this step, we will now create our machine learning model. We will add “Linear Regression” component and the “Train Model” component. The Linear regression component will create our algorithm and the train model component includes which column we will predict, which will be “Cooling Load”.

We will add “Score Model” and “Evaluate Model”. These components help us to evaluate our machine learning model. Some values in these components indicate the accuracy of model. Each algorithm will not be suitable for each data set. For this reason, we will evaluate our model results. The Score model shows us our dataset values and a predicted values.

Below you can see the metric, “Coefficient of Determination”. This value defines the accuracy of our model. A value of 1 defines our model is perfect and nearest the “real” result.

Publish web service

Our model is ready for publishing. We will publish “Predictive Web Service [Recommended]” then publish the web service.

Note: In this part I added a second duplicate “Select Columns in Dataset” component for reduce web service parameters. So the web service will not request unnecessary columns of the dataset.

In the predictive experiment screen, we will hook up web service input to the second select columns dataset component and then run our predictive experiment and finally deploy the web service.

Our experiment web service will be created on the web service tab. In this tab, we can test our web service. But we don’t use this screen – we’ll click General New Web Services Experience and then Test endpoint link.

Test web service

Now we will test our Azure Machine Learning model web service. We will not send the “Cooling Load” parameter to web service because this parameter is output of our web service.

In this step, we will use web service on R script and integrate it into SQL Server. We can find the prepared code under the use endpoint tab and make some changes to it. I will describe it, in detail, later in the article.

Up to this part of the article, we created machine learning model on Azure Machine Learning Studio and created a web service for this model.

Configure SQL Server for R Script

In this next step, we will enable external scripts on SQL Server for R script integration and then install RCurl, which provides functions to allow one to compose general HTTP requests and provides convenient functions to fetch URIs, get & post forms, etc., and rjson, which converts R objects into JSON objects and vice-versa) packages for R script.)

In SSMS you will need to check the configuration for “external scripts enabled”. If this option is “0” we have to change it to 1.

1	EXEC SP_CONFIGURE 'external scripts enabled';

Now we will enable R script run value.

EXEC SP_CONFIGURE 'external scripts enabled',1

RECONFIGURE

EXEC SP_CONFIGURE 'external scripts enabled'

Now that we have enabled external scripts, we will test R script execution.

EXEC sp_execute_external_script

@language = N'R'

,@script = N'print("Hello SQLShack")

print("R Script is working")'

Now we will install rcurl and rjson packages on SQL Server R Service:

Open the SQL Server R Services installation path. You can find it in this folder “C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\bin”
Run console with administrator rights

We will install packages

Create R Script for SQL Server and Execute

In this step, we will use ready R script code generated by Azure ML. But we have to make some changes on it.

We have to replace the API key on R script
We have to replace all single quotes to double quotes for SQL Server

In this step, we will create an R script and call it from SQL Server. This code will return the result of the web service. We can implement this T-SQL code to Server Reporting Service, Power BI etc. The main architecture of this integration is an R script call Azure ML web service over SQL Server.

Now we will test this sample.

DECLARE @SurfArea AS FLOAT='735'

DECLARE @WallArea AS FLOAT = '294'

DECLARE @RoofArea AS FLOAT = '220.5'

DROP TABLE IF EXISTS #Pl_Work

CREATE TABLE #Pl_Work

(SurfArea float , WallArea float ,RoofArea Float)

INSERT INTO #Pl_Work VALUES(@SurfArea,@WallArea,@RoofArea)

EXEC sp_execute_external_script

@language = N'R'

,@script = N'

library("RCurl")

library("rjson")

# Accept SSL certificates issued by public Certificate Authorities

options(RCurlOptions = list(cainfo = system.file("CurlSSL", "cacert.pem", package = "RCurl")))

h = basicTextGatherer()

hdr = basicHeaderGatherer()

req = list(

Inputs = list(

"input1"= list(

list(

''Surface Area'' = InputDataSet$SurfArea,

''Wall Area'' = InputDataSet$WallArea,

''Roof Area'' = InputDataSet$RoofArea,

''Cooling Load'' = "1"

)

GlobalParameters = setNames(fromJSON(''{}''), character(0))

)

body = enc2utf8(toJSON(req))

api_key = "You have to write your key here" # Replace this with the API key for the web service

authz_hdr = paste(''Bearer'', api_key, sep='' '')

h$reset()

curlPerform(url = "https://ussouthcentral.services.azureml.net/workspaces/baf2298a7a714a8dbf56b1ac845e8b8e/services/bac89845b11741cea639723a1bbc4f66/execute?api-version=2.0&format=swagger",

httpheader=c(''Content-Type'' = "application/json", ''Authorization'' = authz_hdr),

postfields=body,

writefunction = h$update,

headerfunction = hdr$update,

verbose = TRUE

)

headers = hdr$value()

httpStatus = headers["status"]

if (httpStatus >= 400)

{

}

result = h$value()

SQLOut <- data.frame(result)

@input_data_1 = N'SELECT * FROM #Pl_Work' ,

@output_data_1_name = N'SQLOut'

These two pictures are showing the same results.

Conclusion

In this article, we discussed Azure Machine Learning integration to SQL Server. SQL Server R script provides this integration. The Azure Machine Learning framework that runs in the cloud brings us very flexible environment, which has a high capability with web service integration.

Author
Recent Posts

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec