[In-Database Python Analysis Tutorial with SQL Server 2017]

In-Database Python Analysis for SQL Developers

The purpose of this tutorial is to provide SQL programmers with a hands-on experience of building machine learning solutions in SQL Server. In this tutorial, you will learn how to incorporate Python into your application by adding Python code to your stored procedure.

[!NOTE] Click here for the R version of a similar tutorial (http://qiita.com/qio9o9/items/4f020bb93dc07567e556). The R version works on both SQL Server 2017 and SQL Server 2016.

Overview

The life cycle of machine learning development generally consists of data acquisition and cleansing, data exploration and feature engineering, model training and tuning, and finally model deployment to production. For actual coding, debugging, and testing, it is best to use an integrated development environment for Python (Python Tools for Visual Studio, PyCharm, Spyder, etc.) such as:

After creating and testing the solution in the Python IDE, deploy the Python code to SQL Server as a Transact-SQL stored procedure. This tutorial will provide you with all the Python code you need.

-Step 1: Download sample data

Download the sample dataset and all script files to your local computer.

-Step 2: Import data into SQL Server using PowerShell

Run a PowerShell script that creates a database and table on the specified instance and loads the sample data into the table.

-Step 3: Data Search and Visualization

Run Python from a Transact-SQL stored procedure to perform basic data exploration and visualization.

-Step 4: Feature extraction of data using T-SQL

Data feature extraction is performed using a user-defined function.

-Step 5: Training and saving the model using T-SQL

Build and save a machine learning model with stored procedural Python code.

-Step 6: Manipulate the model

After saving the model in the database, use Transact-SQL to call the model for prediction.

[!NOTE] If there is a problem with the code embedded in the stored procedure, the information returned by the stored procedure is usually not enough to understand the cause of the error, so testing your Python code is an integrated development environment for Python (IDE). ) Is recommended.

scenario

This tutorial uses the well-known NYC Taxi dataset. Data will be sampled and used to make this tutorial quick and easy. Based on columns in this dataset, such as time, distance, and pick-up location, we will create a binary classification model that predicts whether a tip will be available for a particular ride.

Requirements

Before you start the tutorial, you need to complete the following preparations: ::

--Install Database Engine Services and Machine Learning Services (In-Database) for SQL Server 2017.

--To run Python (and R) in SQL Server 2017, you need to change the setting of external scripts enabled in sp_configure. Also, the external scripts enabled parameter requires a restart of SQL Server 2017 for the setting changes to take effect.

―― 1. Enabling the external script execution function

    ```SQL:T-SQL
    EXEC sp_configure 'external scripts enabled', 1;
    ```

―― 2. Restart SQL Server 2017

    ```cmd:cmd
    net stop "SQL Server Launchpad (MSSQLSERVER)"
    net stop "SQL Server (MSSQLSERVER)"
    net start "SQL Server (MSSQLSERVER)"
    net start "SQL Server Launchpad (MSSQLSERVER)"
    ```

Change the instance name passed to the net command according to your environment. Also, if there is a service that depends on the SQL Server service, such as the SQL Server Agent service, restart it explicitly.

--The SQL Server login used in this tutorial requires permissions to create databases and other objects, update data, browse data, and execute stored procedures.

Link

Next step

Step 1: Download sample data

Source

In-Database Python Analytics for SQL Developers

Related item

Machine Learning Services with Python

Recommended Posts

[In-Database Python Analysis Tutorial with SQL Server 2017]
[In-Database Python Analysis Tutorial with SQL Server 2017] Step 6: Using the model
[In-Database Python Analysis Tutorial with SQL Server 2017] Step 1: Download sample data
[In-Database Python Analysis Tutorial with SQL Server 2017] Step 3: Data Exploration and Visualization
[In-Database Python Analysis Tutorial with SQL Server 2017] Step 2: Import data to SQL Server using PowerShell
[In-Database Python Analysis Tutorial with SQL Server 2017] Step 4: Feature extraction of data using T-SQL
[In-Database Python Analysis Tutorial with SQL Server 2017] Step 5: Training and saving a model using T-SQL
Data analysis with python 2
Voice analysis with python
Voice analysis with python
Data analysis with Python
Local server with python
[Python] Morphological analysis with MeCab
[Co-occurrence analysis] Easy co-occurrence analysis with Python! [Python]
Sentiment analysis with Python (word2vec)
Planar skeleton analysis with Python
Japanese morphological analysis with Python
Easy HTTP server with Python
Muscle jerk analysis with Python
ODBC access to SQL Server from Linux with Python
Access the host SQL Server with python27 / pyodbc on the container
EEG analysis in Python: Python MNE tutorial
3D skeleton structure analysis with Python
Impedance analysis (EIS) with python [impedance.py]
Text mining with Python ① Morphological analysis
Data analysis starting with python (data visualization 1)
Logistic regression analysis Self-made with python
Data analysis starting with python (data visualization 2)
Python tutorial
Marketing analysis with Python ① Customer analysis (decyl analysis, RFM analysis)
Two-dimensional saturated-unsaturated osmotic flow analysis with Python
Machine learning with python (2) Simple regression analysis
2D FEM stress analysis program with Python
Recommendation tutorial using association analysis (python implementation)
Tweet analysis with Python, Mecab and CaboCha
Principal component analysis with Power BI + Python
Data analysis starting with python (data preprocessing-machine learning)
Two-dimensional unsteady heat conduction analysis with Python
Python: Simplified morphological analysis with regular expressions
FizzBuzz with Python3
Scraping with Python
Statistics with python
ODBC connection to FileMaker 11 Server Advanced with Python 3
Python Django Tutorial (5)
Python Django Tutorial (2)
Scraping with Python
Python tutorial summary
Python with Go
Data analysis python
Python Web Content made with Lolipop cheap server
[Various image analysis with plotly] Dynamic visualization with plotly [python, image]
Twilio with Python
Medical image analysis with Python 1 (Read MRI image with SimpleITK)
Integrate with Python
Start a simple Python web server with Docker
Play with 2016-Python
sql from python
AES256 with python
Tested with Python
Python Django Tutorial (8)
python starts with ()