| title | SQL Server Machine Learning Tutorials | Microsoft Docs | |
|---|---|---|
| ms.custom |
|
|
| ms.date | 04/20/2017 | |
| ms.prod | sql-server-2016 | |
| ms.reviewer | ||
| ms.suite | ||
| ms.technology |
|
|
| ms.tgt_pltfrm | ||
| ms.topic | article | |
| applies_to |
|
|
| dev_langs |
|
|
| ms.assetid | 5ccc75f6-6703-47d9-b879-9a740569b45e | |
| caps.latest.revision | 31 | |
| author | jeannt | |
| ms.author | jeannt | |
| manager | jhubbard |
Use these tutorials to learn about machine learning in SQL Server 2016 and SQL Server 2017. Topics covered include:
- How to run R or Python from T-SQL
- What are remote and local compute contexts? How to execute R or Python in a SQL Server compute context
- Developing models in R and saving trained models in a SQL Server table
- How to wrap R or code in a stored procedure
- Methods for optimizing R and SQL code for a production environment
- Real-world scenarios for embedding machine learning in applications
To use these tutorials, you must have installed one of the following server products:
-
SQL Server 2016 R Services (In-Database)
Supports R. Be sure to install the machine learning features, and then enable external scripting.
-
SQL Server 2017 Machine Learning Services (In-Database)
Supports either R or Python. You must select the machine learning feature and the language to install, and then enable external scripting.
For more information about setup, see Prerequisites.
Note
Support for Python is a new feature in SQL Server 2017 (CTP 2.0). Although the feature is in pre-release and not supported for production environments, we invite you to try it out and send feedback.
Learn how to call Python in T-SQL, using the extensibility mechanism pioneered in SQL Server 2016. Now supports Python!
Create a model using rxLinMod, and run it in SQL Server, using the new revoscalepy lilbrary from a remote Python terminal.
Create a machine learning model to predict demand for a ski rental business, and operationalize that model for day-to-day demand prediction using stored procedures. All code and data provided!
Learn how to deploy a Python model using the latest version of Microsoft Machine Learning Server.
This section lists tutorials that were developed for SQL Server 2016 R Services. All require Microsoft R, and make extensive use of features in the RevoScaleR package for SQL Server compute contexts.
Note
Unless otherwise indicated, these tutorials are expected to work without modification in SQL Server 2017.
Getting Started with RevoScaleR and SQL Server
What's in it: You'll learn how to use the functions in the RevoScaleR 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: 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. For setup help, see Prerequisites.
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: This tutorial is intended for SQL developers, application developers, or SQL professionals who will be supporting R solutions and want to learn how to deploy R models to SQL Server.
Requirements: 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. However, some basic knowledge of R is helpful.
You must have access to a SQL Server with the R language installed and enabled. For setup help, see Prerequisites.
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: This tutorial is for people who are new to the feature, and want to learn the basics of calling R from a stored procedure.
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.
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.
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.
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)
Explore R and Scale R in 25 Short Functions
New to R? Wondering what RevoScaleR offers that R doesn't? See this R Server quick-start.
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.
-
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.
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
In SQL Server vNext CTP 2.0, R Services has been renamed Machine Learning Services (in-Database). With this latest release, 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 important 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.