[read this post on Mr. Fox SQL blog]

Azure Cognitive Services is relatively new functionality within Azure that exposes some truly amazing APIs that have the ability to do some truly amazing things.

Before I dive into SQL and DLL code to make use of Cognitive Services, lets take a second to understand what I am talking about – imagine this;

• A customer walks to an electronic kiosk in a shopping centre and says “Hi Cortana, I want to book a holiday, I really need a break from this bad weather.  Any ideas on where I should go?“
• The kiosk is run by a “bot” capable of conversing in 30 languages and which has been trained on understanding context and intent.
• It recognises you as a 35 yo male who looks and sounds unhappy, and because it recognises your face and voice it knows it has talked to you before in another shopping centre last week when you asked for directions to a Surf Shop clothing store.
• It also recognises a beach image on your T-Shirt, making note of the link between your previously asked directions and your clothing.
• As you spoke in English, it replies in English – “Good to see you again.  Now, would you consider a beach holiday to Bali or Thailand?” – Why beach? Well the recommendations engine has determined that is where 35yo male surfer types go when the local weather is bad!
• You negotiate a package using natural language, and close out the conversation.
• The “bot” visualises your increased sentiment from the initial baseline and says “I’m glad I could make your day better! Enjoy your flight next week!“

It may sound futuristic – but this is exactly what Cognitive Services (API’s) can do right now – and in my example I have only used 6 out of the 21 Azure Cognitive Services!  Microsoft Research has built these powerful Azure ML Models and wrapped them up into a single, simple, consumable publicly available API.

Some other amazing deployments for Cognitive Services…

• At a trade show, or even a window display at a shopping centre, a company could use emotion detection to see how people are reacting to their products.
• Facial recognition could be used to find missing children quickly at an amusement park.
• The APIs can determine the male:female ratio and ages of patrons at a nightclub, and identify VIPs or banned guests.
• The object recognition capabilities can enable a blind person to read a menu in a restaurant or have their surroundings described to them

For those not familiar with Azure Cognitve Services APIs, check out this link which has online demos you can try – https://www.microsoft.com/cognitive-services/en-us/apis

For those not familiar with the Azure Bot Framework, check out this link – https://dev.botframework.com/

Anyway – despite all this, for this post today we’ll just focus on something pretty simple – making usage of the Text Analytics API right within SQL Server 2016.

And so, lets get to scoring some sentiment!

How Does the Text Analytics API Work?

The Text Analytics API is pretty simple to use, it has a number of different API endpoints that each perform different functions on the text you post into the API (JSON Request).  It will then Respond with the outcome of that call (JSON Response).

The Text Analytics API provides 4 different functions;

1. Language – it can detect 120 different languages from 0 to 1 (0=uncertain / 1=certain)
2. Topics – it can detect a range of topics (needs min 100 submitted documents)
3. Key Phrases – it can detect the key talking points in the text
4. Sentiment – it can detect sentiment from 0 to 1 (0=negative / 1=positive)

So if I were to send the text “The hotel was fantastic, and the staff were very nice.  We would definitely stay again!” then the API would respond in JSON with the result to whatever function I called;

• Sentiment = [0.89]
• Key Phrases = [hotel, staff]

Each call to the API costs money, however the Text Analytics API (and others) allow free calls, in our case up to 5K calls are free.  Up to 100K calls costs $150/month (or $0.0015 per call), and so on.  So the above example used 2 calls – one for Sentiment, one for Phrases.  The API Pricing – is here – https://www.microsoft.com/cognitive-services/en-us/pricing

 

Deploy the Azure Text Analytics API

To deploy the Text Analytics API you need to have an Azure Subscription.  If you dont have one you can get a free account here – https://azure.microsoft.com/en-us/free/

You then login to the Azure Portal https://ms.portal.azure.com/ and click…

• [NEW] >>> [DATA + ANALYTICS] >>> [COGNITIVE SERVICES]

You then fill out the details of your API and the desired Pricing Tier.

 

 

 

 

 

 

 

 

 

 

 

Once its deployed you can change the Pricing Tier, etc but the most important bit is the API Key as this is what we’ll need to connect to the API.  You can find it here.

 

Further Reading and Demos…

• Overview – If you want to read about the Text Analytics API, checkout this link – https://azure.microsoft.com/en-us/documentation/articles/machine-learning-apps-text-analytics/
• API Definition – If you want to know the Text Analytics API definition and/or download the Swagger/WADL files, then checkout this link – https://westus.dev.cognitive.microsoft.com/docs/services/TextAnalytics.V2.0
• Live Demo – If you want to try out the Text Analytics API with your own text, check out this demo – https://azure.microsoft.com/en-au/services/cognitive-services/text-analytics/

 

Setting up the SQL Database, Data and Procedures

I wanted to leverage data which I already had in SQL Server.  I also didn’t want to write an application (such as a c# webapp) to pull the data out and call the API.  Instead I wanted to do this all in the SQL Server DB Engine itself.

So the process I went though was the following;

1. Created a DLL in Visual Studio which makes the call to the Text Analytics API
2. Set up the SQL Server and Database to host my SQL CLR procedure
3. Setup the my DLL in SQL Server as a SQL CLR Procedure
4. Ran the SQL CLR procedure and capture/parse the JSON response

Lets step through each one of these in a bit more detail – and also show the code!

 

Create Azure Cognitive Services API Call DLL

To make a call to the API we need code which can do a http post.  Rather helpfully the Microsoft team give you the C# code to make this call, so you can just copy it from here – https://text-analytics-demo.azurewebsites.net/Home/SampleCode

You can create a Visual Studio Class Library (DLL) solution, copy in the code and compile it!  Once its compiled ensure to copy the DLL to the SQL Server machine somewhere that you can get to it when you create the SQL assembly, such as C:\Temp\Microsoft

I made a number of special modifications to this code – so I have put my C# DLL code at the end of this post.

The code is also here as a Visual Studio solution on Github – https://github.com/rolftesmer/AzureTextAnalyticsAPISQLDLL

 

Setting up the SQL Server and Database

Now we need to ensure the SQL Server is ready to run CLR, and also create a SQL database to store all our code.  Also, as my DLL references an assembly which is not natively within SQL Server we need to add it via the CREATE ASSEMBLY command.

NOTE – this reference assembly is created with the UNSAFE setting – please ensure to understand what this means for your database and SQL Server here – https://msdn.microsoft.com/en-us/library/ms189524.aspx

NOTE – you might need to change the path to point to your Net Framework location.

use [master]
GO
execute dbo.sp_configure 'clr enabled', 1
GO
RECONFIGURE WITH OVERRIDE
GO

CREATE DATABASE [TextAnalyticsAPI]
GO
USE [TextAnalyticsAPI]
GO
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
GO
ALTER DATABASE [TextAnalyticsAPI] SET TRUSTWORTHY ON
GO

CREATE ASSEMBLY [System.Net.Http] from 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Net.Http.dll' WITH PERMISSION_SET = UNSAFE
GO

Setup the Cognitive Services DLL as a CLR Procedure

Finally we can now create our SQL CLR Stored Procedure based on our created assembly (DLL).  I have already taken the liberty to default some of the parameters such the Text Analytics API URL, however you can change these once the API’s are available in other regions (besides West US).

I also created the DLL and SQL Procedure to allow calls to 2 different Text Analytics API functions – so all you need to do is pass the desired parameter and it will return the valid response;  S = Sentiment  or  P = Key Phrases

CREATE ASSEMBLY TextAnalyticsAPIAssembly from 'C:\temp\Microsoft\TextAnalyticsAPI.dll' WITH PERMISSION_SET = UNSAFE
GO

CREATE PROCEDURE dbo.p_TextAnalyticsAPI
(
 @APIBaseURL nvarchar(4000) = 'https://westus.api.cognitive.microsoft.com/', -- URL of the Text Analytics API Service - BASE
 @APIVersionURL nvarchar(4000) = 'text/analytics/v2.0/', -- URL of the Text Analytics API Service - VERSION
 @APIKey nvarchar(4000) = '', -- Key to access the deployed API
 @FullText nvarchar(4000) = '', -- Text to be scored
 @OperationID nvarchar(1) = 'S' -- P = Detect Key Phrases / S = Detect Sentiment
)
/*
Name: dbo.TextAnalyticsAPI
Desc: Execute an API call against the Azure Cognitive Services Text Analytics API
Author: Rolf Tesmer (Mr. Fox SQL) - https://mrfoxsql.wordpress.com/
Date: 03 Sep 2016
*/
AS EXTERNAL NAME TextAnalyticsAPIAssembly.TextAnalyticsAPIClass.TextAnalyticsAPI
GO

If interested – this is the method to create a SQL CLR Procedure within SQL – https://msdn.microsoft.com/en-us/library/ms131052.aspx

 

Run the CLR Procedure and Parse the JSON Response

Now we have it all setup – this is the fun part to call the SQL Procedure and have it call out via the DLL to the API, and return back the API response!

NOTE that you need to replace the “x…x” with your own API key created earlier and the @OperationID with “S” or “P” for whatever API function you want.

EXECUTE dbo.p_TextAnalyticsAPI
 @APIBaseURL = DEFAULT,
 @APIVersionURL = DEFAULT,
 @APIKey = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
 @FullText = 'The hotel was fantastic, and the staff were every nice. We would definitely stay again!',
 @OperationID = 'S'
GO

 

EXECUTE dbo.p_TextAnalyticsAPI
 @APIBaseURL = DEFAULT,
 @APIVersionURL = DEFAULT,
 @APIKey = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
 @FullText = 'The hotel was fantastic, and the staff were every nice. We would definitely stay again!',
 @OperationID = 'P'
GO

 

 

 

And just as a final step – you can use the SQL 2016 JSON functionality to natively parse the JSON row to get the actual data into a more useable flat format.  See this example below.

declare @JSONDocument nvarchar(4000) = '{"documents":[{"score":0.8699733,"id":"1"}],"errors":[]}',
 @SentimentScore float

select ISJSON(@JSONDocument) as IsValidJSON

SELECT
 @JSONDocument as JSONDocument,
 JSON_VALUE(@JSONDocument, '$.documents[0].score') as SentimentScore,
 JSON_QUERY(@JSONDocument, '$.errors') as Errors

 

 

 

 

Key Summary

So there you have it – and end to end view of how to…

1. Deploy the Azure Text Analytics API
2. Create a C# DLL to manage and control calls to the API
3. Reference the C# DLL within a SQL CLR Procedure
4. Call the SQL CLR procedure to interact with the API
5. Parse the JSON response within SQL Server 2016

The cool thing is this is now a nice template which can be used to call the other Azure Cognitive API’s – so check them out and see which of these you think you can leverage within SQL!

AND of course, as I always say, please test this yourself as your results may vary!

Happy coding!

 

Other Options…?

If desired this could have also been done in sp_OACreate however in my view there are some drawbacks to this method too…

• This is not supported in Azure SQL Database (yet) – so this solution would only work in a full SQL install, such as within an Azure VM (IaaS).  Mind you neither is CLR
• The command needs to call out of SQL Server, so you need to enable the sp_configure option “Ole Automation Procedures” – see here https://msdn.microsoft.com/en-us/library/ms191188.aspx
• And this is just me personally — I am not particularly in love with OLE Automation procedures as I find them a bit fiddly… and unstable… but hey, thats just me!

 

CODE – C# CLR DLL

The code is also here as a Visual Studio solution on Github – https://github.com/rolftesmer/AzureTextAnalyticsAPISQLDLL

//Name: dbo.TextAnalyticsAPI
//Desc: Execute an API call against the Azure Cognitive Services Text Analytics API for usage in a SQL 2016 CLR DLL
//Author: Rolf Tesmer (Mr.Fox SQL) - https://mrfoxsql.wordpress.com/
//Date: 03 Sep 2016

using System;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Net.Http.Headers;
using System.Net.Http;
using System.Threading.Tasks;
using System.Data;

public class TextAnalyticsAPIClass
{
 static async Task<String> CallEndpoint(HttpClient client, string uri, byte[] byteData)
 {
 using (var content = new ByteArrayContent(byteData))
 {
 content.Headers.ContentType = new MediaTypeHeaderValue("application/json");
 //var JSONCallResponse = await client.PostAsync(uri, content); // fails
 var JSONCallResponse = client.PostAsync(uri, content).Result;
 return await JSONCallResponse.Content.ReadAsStringAsync();
 }
 }

 public static async void TextAnalyticsAPI(String APIBaseURL, String APIVersionURL, String APIKey, String FullText, String OperationID)
 {

 HttpClient client;
 byte[] byteData;
 string JSONCallString = "";
 string JSONCallResponse = "";
 string uri = "";

 // Define client http request header
 client = new HttpClient();
 client.BaseAddress = new Uri(APIBaseURL);
 client.DefaultRequestHeaders.Add("Ocp-Apim-Subscription-Key", APIKey);
 client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

 // Define JSON Call Request body
 JSONCallString = "{\"documents\":[{\"id\":\"" + 1 + "\",\"text\":\"" + FullText.Replace("\"", "") + "\"}]}";
 byteData = Encoding.UTF8.GetBytes(JSONCallString);

 // Define JSON Call Request uri
 if (OperationID == "S")
 uri = APIBaseURL + APIVersionURL + "sentiment";
 if (OperationID == "P")
 uri = APIBaseURL + APIVersionURL + "keyPhrases";

 // Call the Text Analytics API
 var content = new ByteArrayContent(byteData);
 content.Headers.ContentType = new MediaTypeHeaderValue("application/json");
 JSONCallResponse = await CallEndpoint(client, uri, byteData);

 // Return the JSON Result
 SqlDataRecord record = new SqlDataRecord(new SqlMetaData("JSONDocument", SqlDbType.NVarChar, 4000));
 record.SetSqlString(0, JSONCallResponse);
 SqlContext.Pipe.Send(record);
 }
}

 

---

Disclaimer: all content on Mr. Fox SQL blog is subject to the disclaimer found here