Getting Started with Python on SQL Server

Python has recently become a favorite of data scientists because of its gentle learning curve and extensive data analysis and machine learning libraries. This course covers how to install, manage, and use Python on SQL Server for machine learning.
Course info
Level
Intermediate
Updated
Apr 18, 2018
Duration
1h 25m
Table of contents
Course Overview
Getting Started with SQL Server for Machine Learning
Running Python Programs in SQL Server
Performing Data Analytics with the revoscalepy Package
Using the microsoftml Library
Managing and Monitoring Python on SQL Server
Course Summary
Description
Course info
Level
Intermediate
Updated
Apr 18, 2018
Duration
1h 25m
Description

At the core of using Python for data analysis and machine learning in SQL Server is a thorough knowledge of how SQL Server implements external scripting, which includes Python programs. In this course, Getting Started with Python on SQL Server, you'll learn the fundamentals of SQL Server machine learning with a focus on Python. First, you’ll learn how to install SQL Server with support for machine learning in Python. Next, you’ll explore the three principle methods of harnessing this new power and how to run Python programs both in a database and on the desktop. Finally, you’ll discover how to secure, monitor, and manage the usage of Python on SQL Server. When you’re finished with this course, you’ll have a foundational knowledge that will help you as you move forward to use Python with SQL Server as a data scientist or monitor and manage it as a DBA.

About the author
About the author

Gerald is a multiple-year of the Microsoft MVP award, Gerald has led introductory classes in Python and SQL for industry-sponsored events at Ryerson University, Toronto and the University of Toronto (his alma mater). 

More from the author
Functional Programming with Python
Advanced
1h 49m
2 Aug 2017
Building More Python Design Patterns
Intermediate
2h 7m
10 Apr 2017
Design Patterns with Python
Intermediate
1h 57m
13 Oct 2016
Section Introduction Transcripts
Section Introduction Transcripts

Course Overview
Hi. I'm Gerald Britton, a Pluralsight author, SQL Server MVP, and longtime Pythonista. If you're interested in using Python with SQL Server, especially for data science and machine learning, this course is for you. If you're a DVA who needs to manage SQL Server instances that has Python installed, this course will get you up to speed. Python is consistently ranked among the top 5 programming languages in the world today. For data science and machine learning, it frequently holds the top spot. Recognizing its importance in this domain, Microsoft now includes Python in its SQL Server distribution, Understanding the implementation of Python support is key to using it properly and efficiently. Getting started with Python on SQL Server digs into how SQL Server implements Python support, while isolating it from other typical database activities. You will start off learning how to install this new functionality, and then move on to using Python, along with the included libraries, to learn how to analyze data sets, train and test data models, and produce charts and graphs. You will also see how to secure, monitor, and manage the activities on the server that are using Python and other external scripting languages. Finally, you'll discover great resources for further study so that you can get the most out of this new capability.

Getting Started with SQL Server for Machine Learning
Hello. Welcome to the course, Getting Started with Python on SQL Server. My name is Gerald Britton. I'm a senior IT solution designer, a SQL Server MVP, and Pluralsight author. I hope you find this course intriguing and that it inspires you to go deeper. In the last few years, Microsoft has been aggressively adding new capabilities to SQL Server. Part of this is attributable to its investment in the Azure cloud computing environment where new features to software can be prototyped, developed, tested, and then released to millions of active users. One of these new features is known as Machine Learning, which is a branch of the discipline known generally as Data Science. Today, many data scientists use Python as the preferred language to build machine learning solutions. Starting with SQL Server 2017, Microsoft has enabled the use of Python directly inside the server. This course explores this exciting new functionality and how to use it effectively.

Running Python Programs in SQL Server
In the previous module you saw how to install SQL Server with Python support. If you've been performing the installation on your own as you watch the walkthrough, then you now have SQL Server running on your target machine and have been able to run a simple Python script. In this module, I want to take you behind the scenes to give you a good overview of how the pieces of the puzzle fit together. This should help you better understand how this all works, which in turn will help you make the best use of the new capabilities and help others to do the same. First, I'll give you a high-level overview of how Python programs are run on SQL Server. That will include the components supplies to support Python execution, both for running Python programs in database using sp_execute_external_script, and also for running Python programs remotely using the revoscalepy Package. I'll introduce you to the revoscalepy package that was installed as part of the previous module. This is the package that most data scientists will use to interact with SQL Server from their workstations. Then, we'll take a deeper dive into sp_execute_external_script, looking at its options and how to use it effectively. We'll also peel back the curtain to see how these components look in action.

Performing Data Analytics with the revoscalepy Package
Hi. Welcome back to the course, Getting Started with Python on SQL Server. I hope that by this point you're starting to get comfortable with running Python programs in SQL Server. So far though, we've only looked at running such programs in database, that is, using the sp_execute_external_script store procedure. Now, that's not the only way to leverage Python with SQL Server. In fact, many, if not most, data scientists will start from their workstations. To support those users, the revoscalepy library is the starting point. This module digs deeply into revoscalepy, discussing its history, architecture, and rich functionality. Then we'll work through demos showing how to use it to solve problems using different modes of operation. Here's an overview of the topics that I'll cover in this module. I'll start off by looking at the origins of revoscalepy, which will help provide context for its use. I'll talk about the two operating modes available, local and remote, and briefly introduce the major functionality included. Next, I'll get the shared environment installed so I can use it, and you'll see some of the data sources that can be used, and the most important functions available. A simple help world program will test out the new environment, and then, I'll review the architecture of a program using revoscalepy, and how the process flows from client to server and back. Next, I'll use one of the sample data sources provided with the installation, and use revoscalepy to summarize some of its key aspects. The last demo will take that data and plot it to produce a chart. Let's get started.

Using the microsoftml Library
Hi. Welcome back to the course, Getting Started with Python on SQL Server. I'm Gerald Britton with Pluralsight. In this module, we'll take a look at the Python package Microsoft ML that comes with SQL Server, starting with version 2017. This package adds state of the art, high-performance machine learning algorithms and data transforms to the functionality that comes with revoscalepy. Interestingly, these algorithms and transforms were fist developed for in-house use by Microsoft. Now they are used by many teams throughout the company for daily data analysis and machine learning. Microsoft ML was first developed for use with the R language, and the Python implementation extends it's use to the large Python community.

Managing and Monitoring Python on SQL Server
Hi. Welcome back to the course, Getting Started with Python on SQL Server. I'm Gerald Britton with Pluralsight. In this module, I want to cover some key topics around managing and monitoring Python usage with SQL Server. As you deploy and use Python on SQL Server, this will become increasingly important. Here's an overview of the key areas to consider. I want to start by discussing security. Adding a major, general purpose programming language such as Python, to a complicated application like SQL Server is not a trivial task. Securing all that power has to be planned and executed carefully to build a useful system without exposing the environment to unnecessary risk. Fortunately, the implementation in SQL Server is designed with security in mind. Once secure, I'll move on to the tools available to monitor Python activity, including new dynamic management views, or DMVs, and new extended events you can use. Finally, I'll show you how you can manage the resources consumed by running machine learning in database to ensure a proper balance between differing workloads.