Predictive Models with SQL Server Machine Learning Services

Predictive Models with SQL Server Machine Learning Services Bob Rubocki – Practice Manager, BI Architect January 22, 2019

2 Bob Rubocki Practice Manager & BI Architect, Pragmatic Works [email protected] linkedin.com/in/robertrubocki @BobRubocki bobrubocki.wordpress.com

Agenda R, Python Overview SQL Server Machine Learning Services Development Experience Executing R, Python from SQL Server Demo (R only) Patterns apply to Python Not an R or data science deep-dive Very cool SQL Server integration!

R Overview S – built at Bell Labs “S” for stats Naming style like “C” R Built by Ross Ihaka and Robert Gentleman at U of Auckland (NZ) Name close to S Built for statistical analysis Managed by Comprehensive R Archive Network (CRAN) cran.r-project.org Open source – continuously new and updated packages

Python Overview General purpose language Managed by Python Software Foundation www.python.org Open source - continuously new and updated packages Machine learning via packages numpy scikit-learn Many others

SQL Server Machine Learning History SQL Server R Services SQL Server 2016 R SQL Server Machine Learning Services SQL Server 2017 R and Python Azure SQL DB Machine Learning Services Azure SQL DB (preview) R Python?

Should I use R or Python?

Answer Quartz - If you want to upgrade your data analysis skills, which programming language should you learn? https://qz.com/1063071/the-great-r-versus-python-for-data-science-debate/

Why Use SQL Server Machine Learning Services? Our app data is in SQL Server Integrate advanced analytics into apps R version provides parallelism, performance for large data sets Keep data close to R/Python process – reduce latency R/Python version management

SQL Server Machine Learning Services Not installed by default R/Python run outside SQL Server SQL Server Launchpad to call R/Python

Installation Enable external scripts Restart SQL Server instance for configuration to take effect

Hello World! R Python

R/Python Versions included with ML Services (As of 2019-01-21) R Python R Open (MS), R 3.5.1 RevoScaleR MicrosoftML Python 3.5.2, Anaconda 4.2 revoscalepy microsoftml R Open Microsoft’s enhanced R distribution Multithreading Release Stability RevoScale Name from Revolution Analytics Acquired by Microsoft High performance, parallelism MicrosoftML Additional ML functionality Pretrained models for image classification,

Updating R and Python SQL Server Cumulative updates – service packs Bind to Machine Learning Server Run Machine Learning Server Installer R/Python components now registered as Machine Learning Server Components More frequent updates than SQL Server Manual – not recommended

Tools and Developer Experience R R Studio Visual Studio Others Python Visual Studio Code Others

Tools and Developer Experience R/Python IDE SSMS Data Exploration Feature Engineering Experimenting, model selection Operations App integration

Executing R, Python from SQL Server sp execute external script R/Python script as input parameter Executes R/Python external process R/Python code to create and train a model Execute Prediction functions in R/Python using trained model R – rxPredict Python – rx predict

PREDICT T-SQL Function SQL Server 2017 and later, including Azure SQL DB Runs within SQL Server process, NOT external process Requires trained model binary in native format (perhaps stored in a table) Does NOT require Machine Learning Services (R/Python) to execute

Development and Deployment Pattern Use R Studio for experimentation, determine best model(s) Use SQL Server for 1 Create Model Stored procedure Create trained model with R Output trained model binary 2 INSERT trained model object to a table Table keeps model versions Retrain periodically Store models with different algorithms 3 Prediction Stored Procedure Trained model as input SQL data set as input Procedure returns predictions

Demo Predict number of ski rentals based on historical data Based on demo from Microsoft https://microsoft.github.io/sql-ml-tutorials/R/rentalprediction/ https://microsoft.github.io/sql-ml-tutorials/python/rentalprediction/ 1. R Studio – use R, build two regression models, choose best 2. SQL Server – use R from step 1, build trained model in SQL, execute predictions

Conclusion R, Python Overview SQL Server Machine Learning Services Development Experience Executing R, Python from SQL Server

[email protected] @bobrubocki linkedin.com/in/robertrubocki/