Skip to content

machine-learning-services-tutorials.md

Latest commit

 

History

History
179 lines (107 loc) · 11.1 KB

machine-learning-services-tutorials.md

File metadata and controls

179 lines (107 loc) · 11.1 KB
title SQL Server R Services Tutorials | Microsoft Docs
ms.custom
SQL2016_New_Updated
ms.date 04/07/2017
ms.prod sql-server-2016
ms.reviewer
ms.suite
ms.technology
r-services
ms.tgt_pltfrm
ms.topic article
applies_to
SQL Server 2016
dev_langs
R
ms.assetid 5ccc75f6-6703-47d9-b879-9a740569b45e
caps.latest.revision 31
author jeannt
ms.author jeannt
manager jhubbard

SQL Server R Services Tutorials

Use these tutorials to learn how to do machine learning in SQL Server 2016 and SQL Server vNext:

SQL Server 2016

  • Learn to run R using T-SQL
  • Learn how to create remote and local compute contexts, then switch local and SQL Server compute contexts with a single function
  • Develop models in R and saved trained models in a SQL Server table
  • Wrap R code in stored procedures, to build a complete analytic workflow in a production environment

SQL Server vNext

  • New samples coming for these features: real-time scoring, parallel small models using rxExecBy
  • Look for Python samples soon!

Be sure to complete any prerequisites, such as setup, before starting.

SQL Server Tutorials

Data Science Deep Dive

Getting Started with RevoScaleR and SQL Server

What's in it: You'll learn how to use the functions in the ScaleR packages to move data between R and SQL, and to switch compute contexts to suit a particular task. You will create some models and plots and move them between your development environment and SQL Server.

This walkthrough is a good place to start for data scientists or developers who are already familiar with the R language, and who want to learn about the enhanced R packages and functions in Microsoft R by Revolution Analytics.

Audience: This tutorial is intended for people who have been using R and want to learn more about how RevoScaleR and SQL Server can improve the R experience.

Requirements: Some basic R knowledge. Access to a server with SQL Server R Services or Machine Learning Services with R already enabled. For setup help, see Prerequisites.

Applies to: SQL Server 2016 R Services. Not tested in SQL Server vNext.

In-Database Advanced Analytics for the SQL Developer

In-Database Advanced Analytics for SQL Developers (Tutorial)

What's in it: Learn to build and deploy a complete advanced analytics solution using [!INCLUDEtsql].

This example focuses on moving a solution into production. You'll learn how to wrap R code in a stored procedure, save an R model to a [!INCLUDEssNoVersion] database, and make parameterized calls to the R model for prediction.

Audience: TThis tutorial is intended for SQL developers, application developers, or SQL DBAs who will be supporting R solutions and want to learn how to deploy R models to SQL Server.

Requirements: Some basic R knowledge. No R environment is needed; all R code is provided and you can build the complete solution using only [!INCLUDEssManStudioFull] and familiar business intelligence and SQL development tools.

Access to a server with SQL Server R Services or Machine Learning Services with R already enabled. For setup help, see Prerequisites.

Applies to: SQL Server 2016 R Services. Not tested in SQL Server vNext.

Using R Code in T-SQL

Using R Code in Transact-SQL (SQL Server R Services)

What's in it: This quick-start covers the basic syntax for using R in [!INCLUDEtsql].

You'll learn how to call the R run-time from T-SQL, wrap R functions in SQL code, and run a stored procedure that saves R output and R models to a SQL table.

Audience: TThis tutorial is for people who are new to the feature, and want to learn the basics. No R or SQL experience is required.

Requirements: No knowledge of R or SQL required.

To run the code, you'll need either SQL Server Management Studio or another client that can connect to a database and run T-SQL. We recommend the free MSSQL extension for Visual Studio Code if you are new to T-SQL queries.

You must have access to a server with SQL Server R Services or Machine Learning Services with R already enabled. For setup help, see Prerequisites.

Applies to: SQL Server 2016 R Services and SQL Server vNext.

Developing an End-to-End Advanced Analytics Solution

Data Science End-to-End Walkthrough

Demonstrates the data science process from beginning to end, as you acquire data and save it to SQL Server, analyze the data with R and build graphs.

You'll learn how to move graphics between [!INCLUDEssNoVersion] and R, and compare feature engineering in T-SQL with R functions.

Finally, you'll learn how to use the predictive model in [!INCLUDEssNoVersion] for both batch scoring single-row scoring.

Audience: This tutorial is intended for people who have some familiarity with R as well as developer tools such as SQL Server Management Studio.

Requirements: You should have access to an R development environment and be familiar with R commands. You'll need to download the New York City taxi dataset using PowerShell.

Access to a server with SQL Server R Services or Machine Learning Services with R already enabled. For setup help, see Prerequisites.

Applies to: SQL Server 2016 R Services. Not tested in SQL Server vNext.

More Resources

These samples are published on other Microsoft sites, but illustrate common uses of R with SQL Server. Most include sample code.

Retail business optimization sample

Build an intelligent app with SQL Server and R

In this tutorial from the SQL Server development team, you'll learn how a ski rental business might use machine learning to predict future rentals, which helps the business plan and staff to meet future demand.

Applies to: SQL Server 2016 R Services. English only.

Customer segmentation using K-Means Clustering

Clustering in SQL Server R Services

This example demonstrates how to use unsupervised learning, using the rxKmeans library on SQL Server, to segment customers based on sales data. (English only)

Applies to: SQL Server 2016 R Services. English only.

End-to-End Solution Templates

The Microsoft Data Science Team has provided a number of solution templates that can be used for copy-paste creation of solutions for common scenarios. All T-SQL and R code is provided, along with instructions on how to train and deploy a model for scoring using SQL Server stored procedures.

For more information, see Machine Learning Templates with SQL Server 2016 R Services.

Learn RevoScaleR in 25 Functions

Blogs

  • Want to know the real story behind R Services? Read this article from the development and PM team: Why did we build it?

  • The original SQL Server 2016 Product Samples, available on Github and on the Microsoft Download Center, contains some datasets and code samples for R Services, including a demo of insurance fraud detection based on Benford's law. To get only the samples for [!INCLUDErsql_productname], select the zip file, and open the folder Advanced Analytics. The setup instructions are for earlier releases and should be disregarded.

Prerequisites

SQL Server 2016 To run any of these tutorials, you must download and install R Services (in-Database) as described here: Set up SQL Server R Services

SQL Server vNext R Services has been renamed Machine Learning Services (in-Database). Moreover, now you can install either R or Python, or both. Otherwise the overall setup process, architecture, and requirements are the same.

After running SQL Server setup, don't forget these additional steps:

  • Enable the external script execution feature by running sp_configure 'enable external script', 1
  • Restart the server
  • Ensure that the service that calls the external runtime has necessary permissions
  • Ensure that your SQL login or Windows user account has necessary permissions to connect to the server, to read data, and to create any database objects required by the sample

If you run into trouble, see this article for some common issues: Upgrade and Installation of SQL Server R Services

If you do not already have a preferred R development environment, you can install one of these tools to get started:

Note that standard R libraries are insufficient to use these tutorials; both your R development environment and the SQL Server computer running R must have the R packages provided by Microsoft. For more information about what's in Microsoft R, see this article: Microsoft R Products.

See Also

Getting Started with SQL Server R Services
SQL Server R Services Features and Tasks