Boost Your Analytics With ML For SQL Nerds: SQL Saturday Spokane - Mar 10, 2018

Boost your Analytics with ML
for SQL Nerds
SQL Saturday Spokane – Mar 10, 2018

Julie Koesmarno | @MsSQLGirl | mssqlgirl.com | jukoesma@microsoft.com
Principal Program Manager in Business Analytics for SQL Products
Business Analytics Lead
Drive business analytics strategy and vision
for Business Growth and Customer Success of
Azure Data products.
Author
SQL Server 2017 Machine Learning Services
Julie Koesmarno with R
Blog: MsSQLGirl.com
Principal Program Manager,

Speaker
Microsoft PASS Summit, SQL Saturdays, User Groups -
Data Warehouse / BI, Data Visualization, SQL
/juliekoesmarno @MsSQLGirl jukoesma@microsoft.com Server + R, ADLA
SQL Server 2017 Machine Learning Services with R
e-book $10 (save 68%)

http://bit.ly/2DdfPLs
Agenda
1. Y ML
2. Operationalizing ML
3. Tips & Tricks
4. Resources
1. Y ML?
automation
delighting customers
Intelligent solutions enable differentiation
Deepen Predict Automate

Engagement Outcomes Actions
2. Operationalizing ML
Language Platform:
Statistics programming language
Data visualization tool
Open source
Ecosystem:
10,000+ packages in CRAN
Community: Scalable to big data
2.5+M users Rich application and platform integration
Taught in most universities
Popular with new and recent grads
Thriving user groups worldwide
Typical Predictive Modelling Steps
Challenges of using R
Data Movement Deployment Scale and Performance
• Moving data from the DB to R • How do I call the R script from my • Most R functions are single threaded
production application? and only accommodate datasets that
• Runtime becomes painful as data fit into available memory
volumes grow
• Movement carries security risks
ML Operationalization pre SQL 2016
Separate Service or Embedded Logic
Analytic Server
Before SQL 2016: Messy!
.. and Bandwidth issue …
In-database advanced analytics
Pushing intelligence to where data lives
Application Application
Intelligence
Intelligence
Database Database
Before Intelligence built in to the DB

SQL Server R Services solves problems
Reduce or eliminate Deploy R scripts Achieve enterprise scale
data movement with and models and performance
in-database analytics
• Leverage built-in extensibility • Use familiar T-SQL stored procedures • Use parallelism query capabilities of
mechanisms to allow secure execution to invoke R scripts from your app in-memory and ColumnStore indexes
of R scripts
• Embed the returned predictions • Leverage RevoScaleR support for
and plots large datasets and parallel algorithms
Deploy predictive analytics
Make your apps
intelligent by consuming
Use PREDICT
Deploy with to natively predictions
sp_execute_external_
predict with the model
Train a model with script and R code to
sp_execute_external_ predict with the model
Develop, explore and script and save in DB
experiment in your
favorite R IDE
Develop Train Deploy Consume

SQL Server 2016 = clean
ML Operationalization with SQL 2016
*Note:
SQL 2016: use sp_execute_external_script
SQL 2017: use PREDICT
sp_execute_external_script Patterns
Demo: ML in SQL Server
Toppings on your Frozen Yoghurt?
Like Toppings on your Frozen Yoghurt?
For demo ☺
jukoesma@Microsoft.com
#SQLFroyo
Attach Picture
Architecture
Microsoft
Flow
PowerApps
3. Tips & Tricks
SQL Server ML Features
Custom Reports for SSMS
Use SSMS custom reports from SQL Server Samples
github
SQL Server Extensibility Framework
Host external runtimes securely on SQL Server machine
Resource governance on external processes
EXTERNAL RESOURCE POOL to control CPU, Memory, CPU Affinity
Integrate with SQL query execution
New external script operator to exchange data / parameters
Parallel query pushing data to multiple external processes / threads
Streaming mode execution
Batch mode execution (in SQL Server 2017)
Implied Authentication
Impersonation for loopback connections from external scripts
Just use trusted connection in connection string
Native Scoring – New in SQL Server 2017
Native Predict function in-DB to achieve

better performance. No need to have ML Syntax sample:
Services installed! SELECT t.*, p.Label_prob as Score
FROM PREDICT(MODEL = @model,
DATA = t) WITH(Label_prob float) as p
WHERE p.Label_prob > .8
2017
*NEW*
Native Scoring for Microsoft R Server Models
T-SQL PREDICT function
In-database Package Management
CREATE EXTERNAL LIBRARY DDL
R Services in Azure SQLDB
Native Scoring using PREDICT function
Requirements:
rx* models only
Serialized model from rxSerializeModel (R)
Serialized model from rx_serialize_model (Python)
Key Benefits:
Runs natively in SQL Server (No R / Python dependency)
Low latency for execution
Ideal for highly concurrent scoring of few rows
Can be used in INSERT/UPDATE/MERGE statement directly
Don’t Do
Run R / Python script as-is Develop/Test from RTVS, PTVS,
Embed secrets in scripts RStudio or other IDE
Do data transformations that can SQL Compute Context from
be achieved in SQL client
Access network resources Data processing &
Process/transform files as part of transformations in SQL Server
the stored procedure call Data integration using SQL
Embed the R/Python code Server features
directly in applications Model management in database
4. Resources
Call to action
Resources
Getting started SQL + ML tutorials: http://aka.ms/sqldev
SSMS Reports for R Services: http://bit.ly/2r525gu
Build 2017: Serving AI with Data (SQL Server 2017):
http://bit.ly/2rbEDlZ
SQL Server Samples on GitHub:
R Services http://bit.ly/2s7tBNV
ML Services http://bit.ly/2rbLNGz
Using RTVS: http://bit.ly/2rbrXLU
Build 2017 session recordings on Channel 9: http://bit.ly/2puyVun
Microsoft Virtual Academy online: http://aka.ms/mva
Questions?
jukoesma@Microsoft.com
@MsSQLGirl
Spokane
SQL User Group
http://spokanewa.pass.org/

Boost Your Analytics With ML For SQL Nerds: SQL Saturday Spokane - Mar 10, 2018

Uploaded by

Copyright:

Available Formats

Boost Your Analytics With ML For SQL Nerds: SQL Saturday Spokane - Mar 10, 2018

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Boost Your Analytics With ML For SQL Nerds: SQL Saturday Spokane - Mar 10, 2018

Uploaded by

Copyright:

Available Formats

Boost your Analytics with ML

for SQL Nerds

SQL Saturday Spokane – Mar 10, 2018

Julie Koesmarno with R

Principal Program Manager,

e-book $10 (save 68%)

Deepen Predict Automate

Before Intelligence built in to the DB

Develop Train Deploy Consume

Native Predict function in-DB to achieve

You might also like