Semantic Analysis – SQL Machine Learning

In this article, you will see how to perform semantic analysis of textual data using the MS SQL Server machine learning services.

By default the SQL Server does not contain any semantic analysis model. It makes use of pre-trained machine learning models, provided by Microsoft for tasks such as semantic analysis, image classification, etc. You can call the pre-trained models using SQL Server machine learning services via Python or R Scripts.

You will be executing the Python script inside your SQL Server Instance to make calls to semantic analysis models for predicted sentiments of text reviews.

Our SQL training courses teach you these techniques and much more, and you can count on the personalized help of our expert trainers.

Prerequisites

1. An SQL Server instance with machine learning services installed and enabled. Look at the official documentation to see how you can install and enable machine learning services on your SQL Server instance.
2. A machine learning model for semantic analysis developed using Microsoft Cognitive Toolkit (CNTK). You will be using a pre-trained sentimental analysis model from Microsoft.
3. You must have administrator rights on the computer and SQL Server to add pre-trained models.

If you heard about Union queries in SQL but are not sure how to use them, we’ve prepared a guide for you.

Installing The Pre-Trained Semantic Analysis Model On SQL Server

The pre-trained model for semantic analysis can be downloaded from the following link: https://aka.ms/mlm4sql.

Once you have downloaded the model, you need to install it in your SQL Server instances so that you can call the model for semantic analysis of text.

Open your Windows PowerShell and execute the following command. Change the path to the semantic model that you just downloaded, if necessary.

PS C:\windows\System32> C:\Users\user_name\Downloads\Install-MLModels.ps1 Your_SQLServer_InstanceName

You might see the following error. This error occurs due to the execution policy for your SQL Server instance.

Execute the following command to fix the policy restriction issue.

PS C:\windows\System32> Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy Unrestricted

You will see the following screen. Enter the ‘Y’ key to accept the policy change.

Now again execute the following command to install the models that you downloaded.

PS C:\windows\System32> C:\Users\user_name\Downloads\Install-MLModels.ps1 Your_SQLServer_InstanceName

You will see the following security warning before installation. Enter the ‘R’ key to run the command.

Once the model is installed, you will see the following messages.

Go to this path in your system.

C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\PYTHON_SERVICES\Lib\site-packages\microsoftml\mxLibs

If you see the following models at the above link, it means that the models are successfully installed.

● AlexNet_Updated.model
● ImageNet1K_mean.xml
● pretrained.model
● ResNet_101_Updated.model
● ResNet_18_Updated.model
● ResNet_50_Updated.model

From the list of the above models, the “pretrained.model” is used for semantic analysis.

Whenever you’re working with SQL databases, don’t forget how important it is to plan your SQL database backups.

Running Semantic Analysis Script In SQL Server

You are now ready to run the semantic analysis scripts on your system. But before that let’s run a test script to see if your SQL Server can run an external Python script. Run the following script on your SQL Server Instance (using command prompt or the Microsoft SQL Server Management Studio).

EXEC sp_execute_external_script @language =N’Python’, @script=N’ print(“Hello World”) ‘

The output below shows that the hello world is successfully printed.

Output:

STDOUT message(s) from external script:   Hello World     Completion time: 2022-01-25T18:04:04.0720768+01:00

Let’s now run a script that predicts sentiments of three dummy movie reviews.

The SQL Server script below calls the Python script that imports the Python NumPy and Pandas libraries. Next, the microsoftml module is imported which contains various machine learning functions to perform semantic analysis including the get_sentiment() method which finds the sentiment of all the strings passed to a Python dictionary.

Check the official documentation for the microsoftml module to see the detail of all the available functionalities.

The sentiment values returned by the get_sentiment() method are transformed in the form of a dictionary containing the text and the sentiment score. The sentiment score lies between 0 and 1 where the negative reviews have a lower score while positive reviews have a higher score.

In the script below, we set a threshold that if a review sentiment score is greater than or equal to 0.6, we consider it positive. Else, the review is considered negative. The resultant value is stored in a new column called Sentiment_Category.

Finally, the review text, scores, and the Sentiment_Category is printed on the console.

EXEC sp_execute_external_script @language =N’Python’, @script=N’ import numpy import pandas from microsoftml import rx_logistic_regression, rx_featurize, rx_predict, get_sentiment# Create the data reviews = pandas.DataFrame(data=dict(text=[ “The movie was really good. I like it”, “It was not a bad film to watch”, “I loved the movie. Will recommend it”]))# Get the sentiment scores sentiment_scores = rx_featurize( data=reviews, ml_transforms=[get_sentiment(cols=dict(scores=”text”))])# Let translate the score to something more meaningful sentiment_scores[“Sentiment_Category”] = sentiment_scores.scores.apply( lambda score: “Positive” if score >= 0.6 else “Negative”) print(sentiment_scores)

Output:

The output below shows the output sentiment scores. You can see that the semantic analysis model is pretty accurate at predicting the sentiment of the sample text reviews. For instance, the sentiment score for the first sentence is 0.88 which is highly evident from the text of the first review.

STDOUT message(s) from external script:   text    scores Sentiment_Category 0  The movie was really good. I like it  0.882751           Positive 1        It was not a bad film to watch  0.444537           Negative 2  I loved the movie. Will recommend it  0.886172           Positive     Completion time: 2022-01-25T18:21:53.2946719+01:00

If you would like to learn about SQL if statements check out our article here!

Final Thoughts

By default, the SQL Server is not capable of text semantic analysis. However, with the help of SQL Server machine learning services, you can call pre-trained semantic analysis models for sentiment analysis in SQL server. Though pre-trained models work well for semantic analysis, you can also train your own machine learning models in SQL Server and perform semantic analysis with those models.

Want to learn more about SQL and its tools? Read our guide to the SQL Update Statement here or our article about SQL Rank Functions.