In-Database Python analytics for SQL developers

The goal of this walkthrough is to provide SQL programmers with hands-on experience building a machine learning solution using Python that runs in SQL Server. In this walkthrough, you'll learn how to add Python code to stored procedures and run stored procedures to build and predict from models.

Note

Prefer R? See this tutorial, which provides a similar solution but uses R, and can be run in either SQL Server 2016 or SQL Server 2017.

Overview

The process of building a machine learning solution is a complex one that can involve multiple tools, and the coordination of subject matter experts across several phases:

  • obtaining and cleaning data
  • exploring the data and building features useful for modeling
  • training and tuning the model
  • deployment to production

The focus of this walkthrough is on building and deploying a solution using SQL Server.

The data is from the well-known NYC Taxi data set. To make this walkthrough quick and easy, the data is sampled. You'll create a binary classification model that predicts whether a particular trip is likely to get a tip or not, based on columns such as the time of day, distance, and pick-up location.

All tasks can be done using Transact-SQL stored procedures in the familiar environment of Management Studio

Requirements

Prerequisites

  • Install an instance of SQL Server 2017 with Machine Learning Services and Python enabled. For more information, see Set up SQL Server Machine Learning Services with Python.
  • The login that you use for this walkthrough must have permissions to create databases and other objects, to upload data, select data, and run stored procedures.

Experience level

You should be familiar with fundamental database operations, such as creating databases and tables, importing data into tables, and creating SQL queries.

An experienced SQL programmer should be able to complete this walkthrough by using Transact-SQL in SQL Server Management Studio or by running the provided PowerShell scripts.

Python: Basic knowledge is useful but not required. All Python code is provided.

Some knowledge of PowerShell is helpful.

Tools

For this tutorial, we're assuming that you've reached the deployment phase. You have been given clean data, complete T-SQL code for feature engineering, and working Python code. Therefore, you can complete this tutorial using SQL Server Management Studio, or any other tool that supports running SQL statements.

If you want to develop and test your own Python code, or debug a Python solution, we recommend using a dedicated development environment:

  • Visual Studio 2017 supports both R and Python. We recommend the Data Science workload, which also supports R and F#.
  • If you have an earlier version of Visual Studio, Python Extensions for Visual Studio makes it easy to manage multiple Python environments.
  • PyCharm is a popular IDE among Python developers.

    Note

    In general, avoid writing or testing new Python code in SQL Server Management Studio. If the code that you embed in a stored procedure has any problems, the information that is returned from the stored procedure is usually inadequate to understand the cause of the error.

Use the following resources to help you plan and execute a successful machine learning project:

Estimated time required

Step Time (hrs)
Download the sample data 0:15
Import data to SQL Server using PowerShell 0:15
Explore and visualize the data 0:20
Create data features using T-SQL 0:30
Train and save a Model using T-SQL 0:15
Operationalize the model 0:40

Get started

Step 1: Download the sample data