Tutorial: Prepare data to train a predictive model in R with SQL machine learning
Applies to: 
SQL Server 2016 (13.x) and later
Azure SQL Managed Instance
In part two of this four-part tutorial series, you'll prepare data from a database using R. Later in this series, you'll use this data to train and deploy a predictive model in R with SQL Server Machine Learning Services or on Big Data Clusters.
In part two of this four-part tutorial series, you'll prepare data from a database using R. Later in this series, you'll use this data to train and deploy a predictive model in R with SQL Server Machine Learning Services.
In part two of this four-part tutorial series, you'll prepare data from a database using R. Later in this series, you'll use this data to train and deploy a predictive model in R with SQL Server R Services.
In part two of this four-part tutorial series, you'll prepare data from a database using R. Later in this series, you'll use this data to train and deploy a predictive model in R with Azure SQL Managed Instance Machine Learning Services.
In this article, you'll learn how to:
- Restore a sample database into a database
- Load the data from the database into an R data frame
- Prepare the data in R by identifying some columns as categorical
In part one, you learned how to restore the sample database.
In part three, you'll learn how to train a machine learning 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
Part two of this tutorial assumes you have completed part one and its prerequisites.
Load the data into a data frame
To use the data in R, you'll load the data from the database into a data frame (rentaldata).
Create a new RScript file in RStudio and run the following script. Replace ServerName with your own connection information.
#Define the connection string to connect to the TutorialDB database
connStr <- "Driver=SQL Server;Server=ServerName;Database=TutorialDB;uid=Username;pwd=Password"
#Get the data from the table
library(RODBC)
ch <- odbcDriverConnect(connStr)
#Import the data from the table
rentaldata <- sqlFetch(ch, "dbo.rental_data")
#Take a look at the structure of the data and the top rows
head(rentaldata)
str(rentaldata)
You should see results similar to the following.
Year Month Day RentalCount WeekDay Holiday Snow
1 2014 1 20 445 2 1 0
2 2014 2 13 40 5 0 0
3 2013 3 10 456 1 0 0
4 2014 3 31 38 2 0 0
5 2014 4 24 23 5 0 0
6 2015 2 11 42 4 0 0
'data.frame': 453 obs. of 7 variables:
$ Year : int 2014 2014 2013 2014 2014 2015 2013 2014 2013 2015 ...
$ Month : num 1 2 3 3 4 2 4 3 4 3 ...
$ Day : num 20 13 10 31 24 11 28 8 5 29 ...
$ RentalCount: num 445 40 456 38 23 42 310 240 22 360 ...
$ WeekDay : num 2 5 1 2 5 4 1 7 6 1 ...
$ Holiday : int 1 0 0 0 0 0 0 0 0 0 ...
$ Snow : num 0 0 0 0 0 0 0 0 0 0 ...
Prepare the data
In this sample database, most of the preparation has already been done, but you'll do one more preparation here. Use the following R script to identify three columns as categories by changing the data types to factor.
#Changing the three factor columns to factor types
rentaldata$Holiday <- factor(rentaldata$Holiday);
rentaldata$Snow <- factor(rentaldata$Snow);
rentaldata$WeekDay <- factor(rentaldata$WeekDay);
#Visualize the dataset after the change
str(rentaldata);
You should see results similar to the following.
data.frame': 453 obs. of 7 variables:
$ Year : int 2014 2014 2013 2014 2014 2015 2013 2014 2013 2015 ...
$ Month : num 1 2 3 3 4 2 4 3 4 3 ...
$ Day : num 20 13 10 31 24 11 28 8 5 29 ...
$ RentalCount: num 445 40 456 38 23 42 310 240 22 360 ...
$ WeekDay : Factor w/ 7 levels "1","2","3","4",..: 2 5 1 2 5 4 1 7 6 1 ...
$ Holiday : Factor w/ 2 levels "0","1": 2 1 1 1 1 1 1 1 1 1 ...
$ Snow : Factor w/ 2 levels "0","1": 1 1 1 1 1 1 1 1 1 1 ...
The data is now prepared for training.
Clean up resources
If you're not going to continue with this tutorial, delete the TutorialDB database.
Next steps
In part two of this tutorial series, you learned how to:
- Load the sample data into an R data frame
- Prepare the data in R by identifying some columns as categorical
To create a machine learning model that uses data from the TutorialDB database, follow part three of this tutorial series: