Edit

Tutorial: Develop a predictive model in R with SQL machine learning

  • Article

Applies to: SQL Server 2016 (13.x) and later Azure SQL Managed Instance

In this four-part tutorial series, you will use R and a machine learning model in SQL Server Machine Learning Services or on Big Data Clusters to predict the number of ski rentals.

In this four-part tutorial series, you will use R and a machine learning model in SQL Server Machine Learning Services to predict the number of ski rentals.

In this four-part tutorial series, you will use R and a machine learning model in SQL Server R Services to predict the number of ski rentals.

In this four-part tutorial series, you will use R and a machine learning model in Azure SQL Managed Instance Machine Learning Services to predict the number of ski rentals.

Imagine you own a ski rental business and you want to predict the number of rentals that you'll have on a future date. This information will help you get your stock, staff, and facilities ready.

In the first part of this series, you'll get set up with the prerequisites. In parts two and three, you'll develop some R scripts in a notebook to prepare your data and train a machine learning model. Then, in part three, you'll run those R scripts inside a database using T-SQL stored procedures.

In this article, you'll learn how to:

  • Restore a sample database

In part two, you'll learn how to load the data from a database into a Python data frame, and prepare the data in R.

In part three, you'll learn how to train a machine learning model model in R.

In part four, you'll learn how to store the model in a database, and then create stored procedures from the R scripts you developed in parts two and three. The stored procedures will run on the server to make predictions based on new data.

Prerequisites

Restore the sample database

The sample database used in this tutorial has been saved to a .bak database backup file for you to download and use.

Note

If you are using Machine Learning Services on Big Data Clusters, see how to Restore a database into the SQL Server big data cluster master instance.

  1. Download the file TutorialDB.bak.

  2. Follow the directions in Restore a database from a backup file in Azure Data Studio, using these details:

    • Import from the TutorialDB.bak file you downloaded
    • Name the target database "TutorialDB"

  3. You can verify that the restored database exists by querying the dbo.rental_data table:

    USE TutorialDB;
SELECT * FROM [dbo].[rental_data];

  1. Download the file TutorialDB.bak.

  2. Follow the directions in Restore a database to a Managed Instance in SQL Server Management Studio, using these details:

    • Import from the TutorialDB.bak file you downloaded
    • Name the target database "TutorialDB"

  3. You can verify that the restored database exists by querying the dbo.rental_data table:

    USE TutorialDB;
SELECT * FROM [dbo].[rental_data];

Clean up resources

If you're not going to continue with this tutorial, delete the TutorialDB database.

Next steps

In part one of this tutorial series, you completed these steps:

  • Installed the prerequisites
  • Restored a sample database

To prepare the data for the machine learning model, follow part two of this tutorial series:

Prepare data to train a predictive model in R