Book Image

SQL Server 2017 Machine Learning Services with R

By : Julie Koesmarno, Tomaž Kaštrun
Book Image

SQL Server 2017 Machine Learning Services with R

By: Julie Koesmarno, Tomaž Kaštrun

Overview of this book

R Services was one of the most anticipated features in SQL Server 2016, improved significantly and rebranded as SQL Server 2017 Machine Learning Services. Prior to SQL Server 2016, many developers and data scientists were already using R to connect to SQL Server in siloed environments that left a lot to be desired, in order to do additional data analysis, superseding SSAS Data Mining or additional CLR programming functions. With R integrated within SQL Server 2017, these developers and data scientists can now benefit from its integrated, effective, efficient, and more streamlined analytics environment. This book gives you foundational knowledge and insights to help you understand SQL Server 2017 Machine Learning Services with R. First and foremost, the book provides practical examples on how to implement, use, and understand SQL Server and R integration in corporate environments, and also provides explanations and underlying motivations. It covers installing Machine Learning Services;maintaining, deploying, and managing code;and monitoring your services. Delving more deeply into predictive modeling and the RevoScaleR package, this book also provides insights into operationalizing code and exploring and visualizing data. To complete the journey, this book covers the new features in SQL Server 2017 and how they are compatible with R, amplifying their combined power.

Related Content you might be interested in

Current Title:

Small book image
SQL Server 2017 Machine Learning Services with R
Julie Koesmarno | Tomaž Kaštrun
Feb, 2018 | 338 pages
Small book image
Data Science with SQL Server Quick Start Guide
Dejan Sarka
Aug, 2018 | 206 pages
Small book image
Hands-On Data Science with SQL Server 2017
Vladimir Muzny | Marek Chmel
Nov, 2018 | 506 pages
Small book image
Introducing Microsoft SQL Server 2019
Dave Noderer | Gregory Woody | Kellyn Gorman | James RowlandJones | Mitchell Pearson | Dustin Ryan | Allan Hirt | Arun Sirpal
Apr, 2020 | 488 pages
Table of Contents (12 chapters)
Preface
Preface
Who this book is for
What this book covers
To get the most out of this book
Get in touch
Free Chapter
1
Introduction to R and SQL Server
Introduction to R and SQL Server
Using R prior to SQL Server 2016
Microsoft's commitment to the open source R language
Boosting analytics with SQL Server R integration
Summary
2
Overview of Microsoft Machine Learning Server and SQL Server
Overview of Microsoft Machine Learning Server and SQL Server
Analytical barriers
The Microsoft Machine learning R Server platform
The Microsoft Machine Learning R Services architecture
Summary
3
Managing Machine Learning Services for SQL Server 2017 and R
Managing Machine Learning Services for SQL Server 2017 and R
Minimum requirements
Choosing the edition
Configuring the environment and installing R Tools for Visual Studio (RTVS)
Security
Package information
Summary
4
Data Exploration and Data Visualization
Data Exploration and Data Visualization
Understanding SQL and R data types
Data exploration and data munging
Data visualization in R
Integrating R code in reports and visualizations
Summary
5
RevoScaleR Package
RevoScaleR Package
Overcomming R language limitations
Scalable and distributive computational environments
Functions for data preparation
Variable creation and data transformation
Variable creation and recoding
Dataset subsetting
Dataset merging
Functions for descriptive statistics
Functions for statistical tests and sampling
Summary
6
Predictive Modeling
Predictive Modeling
Data modeling
Advanced predictive algorithms and analytics
Deploying and using predictive solutions
Performing predictions with R Services in the SQL Server database
Summary
7
Operationalizing R Code
Operationalizing R Code
Integrating an existing R model
Fast batch prediction
Integrating the R model for fast batch prediction
Managing roles and permissions for workloads
Tools
Integrating R workloads and prediction operations beyond SQL Server
Summary
8
Deploying, Managing, and Monitoring Database Solutions containing R Code
Deploying, Managing, and Monitoring Database Solutions containing R Code
Integrating R into the SQL Server Database lifecycle workflow
Prerequisites for this chapter
Using version control
Setting up continuous integration
Setting up continuous delivery
Monitoring the accuracy of the productionized model
Summary
9
Machine Learning Services with R for DBAs
Machine Learning Services with R for DBAs
Gathering relevant data
Exploring and analyzing data
Creating a baseline and workloads, and replaying
Creating predictions with R - disk usage
Summary
10
R and SQL Server 2016/2017 Features Extended
R and SQL Server 2016/2017 Features Extended
Built-in JSON capabilities
Accessing external data sources using PolyBase
High performance using ColumnStore and in memory OLTP
Summary
11
Other Books You May Enjoy
Other Books You May Enjoy
Leave a review - let other readers know what you think

Built-in JSON capabilities

In this scenario, we will use the EMS incidents by month from the official city of Austin open data portal (https://data.austintexas.gov/Public-Safety/EMS-Incidents-by-Month/gjtj-jt2d). The data essentially contains incident counts, broken down by location and priorities for the city of Austin and Travis County incidents, and the percentage of on-time compliance.

The following are the prerequisites to get started:

  1. Download the data from https://data.austintexas.gov/resource/bpws-iwvb.json to a local path, such as C:\Temp\bpws-iwvb.json.
  2. Grant read access to the directory; for example:
Figure 10.1 Granting access to C:\Temp for MS SQL Server
  1. For ease of R visualization, we will use SQL Operations Studio. You can download SQL Ops Studio from: https://docs.microsoft.com/en-us/sql/sql-operations-studio/download.

The following is an excerpt of a JSON...

Previous Section
End of Section 2
Next Section