SQL Server 2017 Machine Learning Services with R

SQL Server 2017 Machine Learning Services with R

by Tomaz Kastrun, Julie Koesmarno
Released February 2018
Publisher(s): Packt Publishing
ISBN: 9781787283572

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Develop and run efficient R scripts and predictive models for SQL Server 2017

About This Book

  • Learn how you can combine the power of R and SQL Server 2017 to build efficient, cost-effective data science solutions
  • Leverage the capabilities of R Services to perform advanced analytics - from data exploration to predictive modeling
  • A quick primer with practical examples to help you get up- and- running with SQL Server 2017 Machine Learning Services with R, as part of database solutions with continuous integration / continuous delivery.

Who This Book Is For

This book is for data analysts, data scientists, and database administrators with some or no experience in R but who are eager to easily deliver practical data science solutions in their day-to-day work (or future projects) using SQL Server.

What You Will Learn

  • Get an overview of SQL Server 2017 Machine Learning Services with R
  • Manage SQL Server Machine Learning Services from installation to configuration and maintenance
  • Handle and operationalize R code
  • Explore RevoScaleR R algorithms and create predictive models
  • Deploy, manage, and monitor database solutions with R
  • Extend R with SQL Server 2017 features
  • Explore the power of R for database administrators

In Detail

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.

Style and approach

This fast-paced guide will help data scientists and DBAs implement all new data science projects using SQL Server 2017 Machine Learning Services.

Table of contents

  1. Title Page
  2. Copyright and Credits
    1. SQL Server 2017 Machine Learning Services with R
  3. www.PacktPub.com
    1. Why subscribe?
    2. PacktPub.com
  4. Contributors
    1. About the authors
    2. About the reviewers
    3. Packt is searching for authors like you
  5. Preface
    1. Who this book is for
    2. What this book covers
    3. To get the most out of this book
      1. Download the example code files
      2. Download the color images
      3. Conventions used
    4. Get in touch
      1. Reviews
  6. Introduction to R and SQL Server
    1. Using R prior to SQL Server 2016
    2. Microsoft's commitment to the open source R language
    3. Boosting analytics with SQL Server R integration
    4. Summary 
  7. Overview of Microsoft Machine Learning Server and SQL Server
    1. Analytical barriers
    2. The Microsoft Machine learning R Server platform
      1. Microsoft R Open (MRO)
      2. Microsoft Machine Learning R Server
      3. Microsoft SQL Server Machine Learning R Services
      4. R Tools for Visual Studio (RTVS)
    3. The Microsoft Machine Learning R Services architecture
      1. R Limitations
        1. Performance issues
        2. Memory limitations
        3. Security aspects
        4. Language syntax
    4. Summary
  8. Managing Machine Learning Services for SQL Server 2017 and R
    1. Minimum requirements
    2. Choosing the edition
      1. Configuring the database
    3. Configuring the environment and installing R Tools for Visual Studio (RTVS)
    4. Security
      1. Resource Governor
      2. Installing new R packages
    5. Package information
      1. Using R Tools for Visual Studio (RTVS) 2015 or higher
      2. Using R.exe in CMD
      3. Using XP_CMDSHELL
      4. Copying files
      5. Using the rxInstallPackages function
      6. Managing SQL Server R Services with PowerShell
      7. Getting to know the sp_execute_external_script external procedure
      8. Arguments
    6. Summary
  9. Data Exploration and Data Visualization
    1. Understanding SQL and R data types
      1. Data frames in R
    2. Data exploration and data munging
      1. Importing SQL Server data into R
      2. Exploring data in R
      3. Data munging in R
        1. Adding/removing rows/columns in data frames
        2. More data munging with dplyr
        3. Finding missing values
        4. Transpose data
        5. Pivot / Unpivot data
      4. Example - data exploration and munging using R in T-SQL
    3. Data visualization in R
      1. Plot
      2. Histogram
      3. Boxplot
      4. Scatter plot
      5. Tree diagram
      6. Example – R data visualization in T-SQL
    4. Integrating R code in reports and visualizations
      1. Integrating R in SSRS reports
      2. Integrating R in Power BI
    5. Summary
  10. RevoScaleR Package
    1. Overcomming R language limitations
    2. Scalable and distributive computational environments
    3. Functions for data preparation
      1. Data import from SAS, SPSS, and ODBC
      2. Importing SAS data
      3. Importing SPSS data
      4. Importing data using ODBC
    4. Variable creation and data transformation
    5. Variable creation and recoding
    6. Dataset subsetting
    7. Dataset merging
    8. Functions for descriptive statistics
    9. Functions for statistical tests and sampling
    10. Summary
  11. Predictive Modeling
    1. Data modeling
    2. Advanced predictive algorithms and analytics
    3. Deploying and using predictive solutions
    4. Performing predictions with R Services in the SQL Server database
    5. Summary
  12. Operationalizing R Code
    1. Integrating an existing R model
      1. Prerequisite – prepare the data
      2. Step 1 – Train and save a model using T-SQL
      3. Step 2 – Operationalize the model
    2. Fast batch prediction
      1. Prerequisites
      2. Real-time scoring
      3. Native scoring
    3. Integrating the R model for fast batch prediction
      1. Step 1 – Train and save a real-time scoring model using T-SQL
      2. Step 2a – Operationalize the model using real-time scoring
      3. Step 2b – Operationalize the model using native scoring
    4. Managing roles and permissions for workloads
      1. Extensibility framework workloads
      2. Fast batch prediction workloads
      3. External packages
    5. Tools
      1. Using SSMS as part of operationalizing R script
        1. Using custom reports for SQL Server R Services
          1. Adding the custom reports for the first time
          2. Viewing an R Services custom report
        2. Managing SQL Server Machine Learning Services with DMVs
          1. System configuration and system resources
          2. Resource governor
      2. Operationalizing R code with Visual Studio
    6. Integrating R workloads and prediction operations beyond SQL Server
      1. Executing SQL Server prediction operations via PowerShell
      2. Scheduling training and prediction operations
      3. Operationalizing R script as part of SSIS
    7. Summary
  13. Deploying, Managing, and Monitoring Database Solutions containing R Code
    1. Integrating R into the SQL Server Database lifecycle workflow
      1. Preparing your environment for the database lifecycle workflow
    2. Prerequisites for this chapter
      1. Creating the SQL Server database project
      2. Importing an existing database into the project
      3. Adding a new stored procedure object
      4. Publishing schema changes
      5. Adding a unit test against a stored procedure
    3. Using version control
    4. Setting up continuous integration
      1. Creating a build definition in VSTS
      2. Deploying the build to a local SQL Server instance
      3. Adding the test phase to the build definition
      4. Automating the build for CI
    5. Setting up continuous delivery
    6. Monitoring the accuracy of the productionized model
      1. Useful references
    7. Summary
  14. Machine Learning Services with R for DBAs
    1. Gathering relevant data
    2. Exploring and analyzing data
    3. Creating a baseline and workloads, and replaying
    4. Creating predictions with R - disk usage
    5. Summary
  15. R and SQL Server 2016/2017 Features Extended
    1. Built-in JSON capabilities
    2. Accessing external data sources using PolyBase
    3. High performance using ColumnStore and in memory OLTP
      1. Testing rxLinMod performance on a table with a primary key
      2. Testing rxLinMod performance on a table with a clustered ColumnStore index
      3. Testing rxLinMod performance on a memory-optimized table with a primary key
      4. Testing rxLinMod performance on a memory-optimized table with a clustered ColumnStore index
      5. Comparing results
    4. Summary
  16. Other Books You May Enjoy
    1. Leave a review - let other readers know what you think

Product information

  • Title: SQL Server 2017 Machine Learning Services with R
  • Author(s): Tomaz Kastrun, Julie Koesmarno
  • Release date: February 2018
  • Publisher(s): Packt Publishing
  • ISBN: 9781787283572