In this article I will show you how you can easily extend SQL Server to call the Microsoft Azure Cognitive Sentiment Analysis service, so that you can score customer feedback sentiment in real-time directly from Stored Procedures, Triggers, and Views. This demo is made possible by using the Free 30-day Edition of the Enzo Unified platform, which natively extends SQL Server 2012 and higher.



At the conclusion of this article, you will be able to get a real-time score directly from the Microsoft Azure Cognitive services on the sentiment of a comment; the example above shows a result of 0.99, which means the message analyzed likely indicates a happy customer.

Pre-Requisites

In order to successfully implement this real-time text sentiment analysis you will need the following:

• An Azure account with Cognitive Services
• Enzo Unified installed and configured
• SQL Server and SSMS, with Linked Server configured to Enzo

Setup Cognitive Services in Azure

The steps to configure a Cognitive Service in Azure can be found here.

You will need to access the Cognitive Services URI and API Key shortly; you can find this key in the Azure Portal by select Keys and Endpoint on the left menu.

Install and Configure Enzo

Once configured, your Azure Cognitive Service resource can be called using HTTPS requests as simple REST commands. In order to call HTTPS REST services from SQL Server, you will be using the Enzo platform. Download Enzo Unified here.. Make sure to select the SQLX and HttpEndpoint adapters for this demo. To configure the HttpEndpoint adapter, you will need to use information from the Cognitive Service resource created in Azure.

NOTE: Your URI could be a bit different; the service URI is the first part of the service, while the rest is identical for all regions. So you may need to replace https://southcentralus.api.cognitive.microsoft.com with the ENDPOINT value you saw in the Cognitive Service portal.

Make sure to select the checkbox at the bottom of the screen (Make this the default settings for this login), otherwise you will get an error later.

Linked Server to Enzo

The last step is to ensure you can access Enzo from your SQL Server database through Linked Server. See this article to see how to do this – note that this can be done from any SQL Server machine; Enzo does not need to be installed locally.

Once setup, you can access Enzo from SQL Server. Assuming you named your Enzo Linked Server as [localhost,9550], connect to SQL Server and run this command to test connectivity to Enzo; a list of available commands provided by the HttpEndpoint adapter will be returned:

EXEC [localhost,9550].bsc.HttpEndpoint.help

Calling Azure Cognitive Services from SQL Server

Let’s now call the Azure Cognitive service from SQL Server. The first step is to try the call itself to make sure everything is configured properly. The Azure Cognitive Service expects a specific JSON payload in the format provided below.

EXEC [localhost,9550].bsc.HttpEndpoint.SendHttpRequest
                '{"documents": [ {"id": "1", "language": "en",
                "text": "We have been her for an hour :("}]}'

This looks like a negative comment; as we can see the score is 0.02, which is very low. Once you call this command, Enzo connects to the Cognitive Service, sends the payload, and returns the JSON result to SQL Server in real-time.

Extracting JSON Value using SQL

However, the response of the Cognitive Service is a JSON document, which is hard to consume in SQL Server. So we will use the SQLX adapter to extract the property value using the GetNodeValue method. This method works by passing the node to extract as the first parameter, and the full JSON document as the second parameter. In this case, the JSON payload places the score under a documents array, which contains a single element (index 0). So the score value is found using this path: documents[0].score

EXEC SQLX.GetNodeValue 'documents[0].score', 
    '{"documents":[{"id":"1","score":0.023654669523239136}],"errors":[]}'

This returns 0.02 as expected.

Creating a SQL Server Function

Last but not least, now that we know how to call the Cognitive Service, and extract the score from the JSON document, let’s create a SQL Function that does the job to simplify our SQL code. We will create this function in the master database for demo purposes; however it should be created in an actual database and secured accordingly in a production environment.

CREATE FUNCTION dbo.fGetSentimentFromCognitiveSvc(@text nvarchar(max)) 
RETURNS FLOAT AS
BEGIN 
	DECLARE @response NVARCHAR(max), @jsonRequest NVARCHAR(max)
	SET @text = REPLACE(@text, '''', '')
	SET @jsonRequest = '{"documents": [
            {"id": "1", "language": "en",
            "text": "'
	SET @jsonRequest = @jsonRequest + @text + '"}]}'
	
	SELECT @response = response FROM [localhost,9550].bsc.HttpEndpoint.HttpRequest WHERE payload=@jsonRequest
	RETURN (SELECT TOP 1 [data] FROM [localhost,9550].bsc.sqlx.NodeValue WHERE element='documents[0].score' AND json=@response)
END

The @jsonRequest variable creates the input string the Cognitive Service expects, and places the @text input variable inside the payload (the @text variable is first escaped to make sure single quotes are not sent to the service or a SQL error will be thrown by SQL Server).

The Enzo HttpEndpoint call is made by sending the @jsonRequest payload, and the response JSON document is then sent to the SQLX adapter to extract the score.

Once the above has been created, getting a sentiment analysis score will be as simple as this:

SELECT dbo.fGetSentimentFromCognitiveSvc(
    '@AmericanAir @customerservice #Booking #CustomerService #MIA @iflymia  thank you Lexia! Best customer service!!!  #lexia')

Summary

This article shows you how to call the Azure Cognitive Services directly from SQL Server. The solution leverages the Free 30-day Edition of the Enzo platform and requires minimal setup. Once implemented, you can send virtually any text to the service, including tweets, customer comments and any other relevant text easily, directly from SQL Server in real-time.