Machine Learning Services Indatabase and Standalone Andrew Loree
Machine Learning Services In-database and Stand-alone Andrew Loree
Got a question? Andrew Loree www. andyloree. com andy@andyloree. com @Low. On. Disk. Space
Expectations & Assumptions What is your primary role? • SQL Developer • App Developer • SQL sysadmin/Platform DBA • Not a Python/R language tutorial • Not a Machine Learning (ML) how-to
Goals – To Understand… • What is Machine Learning Server? • Different deployment designs • In-database - SQL Server Machine Learning Services • Stand-alone • Administrative features • Language features • Simple use-cases
Does the Machine really Learn? • Pattern recognition is from learning and past experiences, and we use it every day • Which of these charges were fraudulent for my credit card? Ship to Address Type Merchant Date Amount Mine Computers & Accessories AMAZON 04 -09 $734. 95 Mine Computers & Accessories AMAZON 04 -09 $12. 98 Not Mine Books AMAZON 04 -10 $468. 03 Mine Electronics NEWEGG 04 -12 $198. 32 When is there enough data and when do you have too much? Enter ML
Types of Questions (about Data) • Descriptive - how many of X did I sell? • Associative - is there an association between temperature and sales? (hypothesis) • Comparative - how many X sells versus Y? • Predicative - using associations and comparatives to predicate sales of X? Machine Learning can answer predictive questions
Current Microsoft ML Landscape • Machine Learning Server – operationalized ML services, parallel and distributed processing • SQL Server Machine Learning Services – functionally similar to ML Server, in-database • Data Science Virtual Machine (DSVM) – preconfigured, multiscenario, primarily desktop focused • Spark MLLib – ML for HDInsight • Azure Machine Learning Studio – Drag-and-drop web environment to build, test and publish predictive analytics • Cognitive Toolkit – Deep-learning, useable by all of the above • Cognitive Services – APIs to pre-trained models covering sentiment detection, vision, speech, language and knowledge search
Why Machine Learning Server? How do we consume our model?
Why Machine Learning Server? Operationalized Model Management
What is Machine Learning Server? • Operationalized model management for Machine Learning in the enterprise Answers to how to deploy and consume ML models • Scalable language support Microsoft. ML and revoscalepy • Two different deployment methods (as well as how they are consumed and managed) • In-database – data proximity to SQL Server • Stand-alone – portable, remote compute-context
revoscalepy Package Scalable parallel predictive modeling algorithms • Linear regression • Logistic regression • Decision tree • Boosted tree • Random forest • Predictions Also contains functions: • Compute context • Data sources • Data manipulation • Analytic functions • Job functions (for Spark) • Serialization • Utilities
Microsoft. ML Package • Optimized ML algorithms and pre-trained models • Multi-threaded, scalable beyond memory
Demo: In-Database • Scenario: Data cleansing street names from addresses • sp_execute_external_scripts
Maintenance: In-database • Two paths for component version upgrades: • Default – SQL Server SP and update cadence • Binding – upgrades using Machine Learning Server release cadence • Sql. Bind. R. exe (part of ML Server installation) to control binding/unbinding • Adding packages • Run python C: Program FilesMicrosoft SQL ServerMSSQL 14. MSSQLSERVERPYTHON_SERVICES • PIP - make sure your path has -PYTHON_SERVICESScripts] • R - use CREATE EXTERNAL LIBRARY, not supported with Python yet
Components: In-database • Launchpad – starts satellite processes • Bxl. Server – binary exchange to lanagauge • Sql. Satellite – handles data communication https: //docs. microsoft. com/en-us/sql/advanced-analytics/python/new-components-in-sql-server-to-support-python-integration
Security & Performance: In-database • Security • Script execution via Launchpad uses local accounts • Configuration Manager -> Launchpad Service • EXECUTE ANY EXTERNAL SCRIPT granted per db • Performance • Manage resources with EXTERNAL resource governor pool • Balance CPU/memory usage with default pool • Increase number of Launchpad pool users (default 20) • Some tasks better are better left to an application tier
Demo: Pre-trained Model • Scenario: Generate positive/negative summary of reviews for a product • Pre-trained sentiment analysis from microsoftml sentiment – general feeling or opinion
Demo: Models and PREDICT • Scenario: Using bike rental data, dates and weather to build a model and predict future rentals • Revoscalepy rx_lin_mod() • Serialize trained model • T-SQL PREDICT
Deployment: In-database • Models are data (varbinary) • R/Python scripts are data (nvarchar) • Many ways to version and control, e. g. • Restrict arbitrary scripts via EXECUTE AS within sprocs • Store scripts and models in tables control with row-level security • In-database available only on Windows SQL Server installation today • PREDICT is available on all SQL platforms and versions • Monitored with extended events
Administration: In-Database • DMV’s – current workers sys. dm_external_script_execution_stats – execution counters Process execution sys. dm_external_script_requests SELECT * FROM sys. dm_os_performance_counters WHERE object_name LIKE '%External Scripts%’ Resource pools – Enterprise Edition sys. resource_governor_external_resource_pools sys. dm_resource_governor_external_resource_pool_affinity
Compute Context: In-database • Allows users to run code within SQL Server as a remote compute context • Rx. In. Sql. Server • Use-cases: • Avoid data movement • More powerful calculation • Maintain data security https: //blogs. technet. microsoft. com/dataplatforminsider/2017/09/26/in-database-machine-learning-in-sql-server-2017/
Stand-alone • Machine Learning Server • Setup one-box configuration > az ml admin boostrap • Jupyter notebook to create and deploy, and consume
Multi-server Configuration • Leverage loadbalancing • Web and compute nodes separate • Typical web scaleout
Administration: Stand-alone • az ml admin is primary source of configuration/control • Monitoring and configuration tuning Kestrl web service https: //docs. microsoft. com/en-us/aspnet/core/fundamentals/servers/kestrel? tabs=aspnetcore 2 x
Got a question? Andrew Loree www. andyloree. com andy@andyloree. com @Low. On. Disk. Space
- Slides: 25