Skip to content
Python and T-SQL solution for Sentiment analysis and Real-time predictions in SQL Server 2017+
Jupyter Notebook PowerShell TSQL Python Batchfile
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
PASSInsights201908-Dev
PS-CMD
Python
Raffle
SQL
.gitattributes
.gitignore
AIForBusiness.xcf
CognitiveAPI.ipynb
Cover.png
Eval-SentimentPrediction-HowSatisfied.jpg
IMG_3248.JPG
IMG_3249.JPG
PASSOffers.png
README.md
SentimentML.sln
SentimentPrediction-Resources.png
Sponsor.png
[AppDBDev]_HiramFleitas_Realtime_Sentiment_Prediction_SQLServer.pdf
[AppDBDev]_HiramFleitas_Realtime_Sentiment_Prediction_SQLServer.pptx
microsoft-machine-learning-algorithm-cheat-sheet-v6.pdf
quotes.txt

README.md

cover

Resources 🐱‍🚀

  1. fleitasarts.com
  2. ailab.microsoft.com
  3. SQL Server R Services Samples: Microsoft Repo
  4. Pre-Trained ML Models: Install in SQL Server
  5. SQL Server Machine Learning Services: Tutorials
  6. SQL Server Components to Support Python: Interaction of Components
  7. hreading ML: Logistic Regression
  8. Resource Governor: Alter External Resource Pool
  9. Interactive deep learning: Learn alert
  10. aka.ms/sqlworkshops
  11. aka.ms/ss19

Sentiment Prediction

Real-time in SQL Server

Python and T-SQL solution for Sentiment analysis and Real-time predictions in SQL Server 2017+

Getting Started

This example should work on any machine running SQL Server 2017.

It assumes you have restored the tpcxbb_1gb sample database on a SQL Server 2017 instance. If not, just follow the instructions.

Get Sentiment Example: (requirements: SQL Server 2017+, Machine Learning Services (In-Database) R & Python, tpcxbb_1gb)

tpcxbb_1gb db (see latest release tpcxbb_1gb.bak [234 MB]): https://sqlchoice.blob.core.windows.net/sqlchoice/static/tpcxbb_1gb.bak

Prerequisites

Powershell/Cmd, SQL Server 2017, Machine Learning Services (In-Database) R & Python, tpcxbb_1gb database.

Installing

Install SQL Server 2017+ on your local machine. I recommend choosing Developer Edition for the purpose of this example: https://www.microsoft.com/en-us/sql-server/sql-server-downloads

Select to Add Feature Machine Learning Services (In-Database), R and Python.

Download latest release tpcxbb_1gb.bak [234 MB] and restore it. (If you already have you may skip this step)

To restore see file: SQLServerScripts.sql

--verify
restore filelistonly from disk = 'c:\users\hfleitas\downloads\tpcxbb_1gb.bak'
go 
--set your own paths
restore database [tpcxbb_1gb] from disk = 'c:\users\hfleitas\downloads\tpcxbb_1gb.bak' with replace,
move 'tpcxbb_1gb' to 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\tpcxbb_1gb.mdf', 
move 'tpcxbb_1gb_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\tpcxbb_1gb.ldf'
go
--rollup compat to 140 for 2017 or 150 for 2019
alter database [tpcxbb_1gb] set COMPATIBILITY_LEVEL = 150 --2019
go
--update stats (you may skip this step)
use [tpcxbb_1gb]
go
EXEC sp_updatestats
  1. Enable external scripts by running SQLServerScripts.sql on your SQL Server instance. Be aware Reconfigure with Override will immediatly apply any modified config values to the running config.
EXEC sp_configure 'external scripts enabled', 1
RECONFIGURE WITH OVERRIDE
go
  1. Walk through the steps in SQLServerScripts.sql to see how Sentiment Analysis works inside the datase.

  2. You may Start without Debugging GetSentimentExample.py, in Visual Studio 2017 Community Edition, get the Tools/Features for Python, then set the Python Envirments to SQLServer2019ctp2 or SQLServer2017PythonSvcs, whichever version of SQL Server you have installed. The prefix path for the Python Enviroment will be like so [C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES], click Auto Detect, Apply and set this as the default enviroment.

End with sp_rxPredict:

exec sp_rxPredict @model = @model_bin, @inputData = N'SELECT pr_review_content, cast(tag as varchar(1)) as tag FROM product_reviews_test_data' end;

Purpose

This example will illustrate how to:

  • Add ML Features
  • Grant Access
  • Config
  • Install Pre-Trained & Open Source ML Models (Deep Neural Networks)
  • Code in Python and T-SQL (sp_execute_external_script)
  • Python Profiling
  • Real-time scoring
  • Azure Data Studio Notebooks
  • Cognitive API (Text Analytics)

The intention is to process large amounts of inputs and predict quality scores fast enough for real-time operations. It's advisable to monitor the system task manager cpu/ram during the train model step(s).

Deployment

You may clone this repo and open it in Azure Data Studio or open the solution file [SentimentML.sln] in Visual Studio.

Follow instructions in SQLServerScripts.sql.

Built With

Contributing

Please read CONTRIBUTING.md for code of conduct, and the process for submitting pull requests.

Versioning

I use Github for versioning. For the versions available, see the tags on this repository.

Authors

  • Hiram Fleitas - This Repo - SentimentPrediction - Hiram Fleitas

See also the list of contributors who participated in this project.

License

This project is licensed under the MIT License - see the LICENSE.md file for details

Acknowledgments

  • Nellie Gustafsson - Senior Program Manager (Microsoft)
  • Sumit Kumar - Principal Product Manager (Microsoft)
  • Ryan Donaghy - Senior Software Developer (Universal Property)
  • MS Research and the entire SQL Server ML team.
You can’t perform that action at this time.