[In-Database Python Analysis Tutorial with SQL Server 2017] Step 2: Import data to SQL Server using PowerShell

From the beginning of the tutorial

In-Database Python Analysis for SQL Developers

Previous step

Step 1: Download sample data

Next step

Step 3: Data Search and Visualization

Step 2: Import data into SQL Server using PowerShell

In this step, you will run the downloaded script RunSQL_SQL_Walkthrough.ps1 to create the database objects needed for the tutorial and import the sample data.

Object creation and data loading

Execute the PowerShell script RunSQL_SQL_Walkthrough.ps1 in the downloaded files to prepare the tutorial environment. This script performs the following actions:

--Install the SQL Native Client and SQL command line utilities if they are not already installed. These are required for bulk loading data using bcp.

--Create a database and tables on your SQL Server instance and bulk load the data into them.

--Create more functions and stored procedures.

Script execution

  1. Open a PowerShell command prompt as an administrator and run the following command:

    .\RunSQL_SQL_Walkthrough.ps1
    

You will be prompted to enter the following information: --The name or address of the server where Machine Learning Services (Python) is installed. --The name of the database to create --The target SQL Server user name and password. This user must have permission to create databases, tables, stored procedures, functions, and load data into tables. If you omit the user name and password, you will be logged in as the current Windows user. --The path of the sample data file nyctaxi1pct.csv in the downloaded files. For example, C: \ tempPythonSQL \ nyctaxi1pct.csv.

![sqldev-python-ps-1-gho9o9.png](https://qiita-image-store.s3.amazonaws.com/0/195839/db34c97d-167e-b897-1bdc-299d6feeed71.png)

![sqldev-python-ps-2-gho9o9.png](https://qiita-image-store.s3.amazonaws.com/0/195839/92d4987c-d3f6-ea5f-db2d-6e5c63b64346.png)
  1. All T-SQL scripts have been modified to replace the database and user names specified in the steps above with placeholders.

  2. Make sure that the stored procedures and functions created by the T-SQL script are created in the database.

    T-SQL script file Stored procedure / function
    create-db-tb-upload-data.sql Create a database and four tables.

    tablenyctaxi_sample:The main NYC Taxi dataset is created. The data loaded is a 1% sample of the NYC Taxi dataset. The definition of the clustered column store index improves storage efficiency and query performance.

    tablenyc_taxi_models:A trained advanced analytical model is registered.

    tablenyctaxi_sample_training:The dataset used to train the model is created.

    tablenyctaxi_sample_testing:The dataset used to test the model is registered.
    fnCalculateDistance.sql Scalar value function that calculates the direct distance between the boarding position and the disembarking positionfnCalculateDistanceCreate a.
    fnEngineerFeatures.sql A table-valued function that returns a feature-value set for model trainingfnEngineerFeaturesCreate a.
    TrainingTestingSplit.sql nyctaxi_Data in the sample table, nyctaxi_sample_training and nyctaxi_sample_Procedure to divide into two of testingTrainingTestingSplitCreate a.
    PredictTipSciKitPy.sql For prediction using the model, scikit-Procedure to call the trained model created by learnPredictTipSciKitPyCreate a. The procedure accepts a query as an input parameter and returns a column of numbers containing the score for the input row.
    PredictTipRxPy.sql A procedure that calls a trained model created with RevoScalePy for prediction using the modelPredictTipRxPyCreate a. The procedure accepts a query as an input parameter and returns a column of numbers containing the score for the input row.
    PredictTipSingleModeSciKitPy.sql For prediction using the model, scikit-Procedure to call the trained model created by learnPredictTipSingleModeSciKitPyCreate a. This stored procedure takes new observations as input, accepts individual feature values as inline parameters, and returns predictions for the new observations.
    PredictTipSingleModeRxPy.sql For prediction using the model, scikit-Procedure to call the trained model created by learnPredictTipSingleModeRxPyCreate a. This stored procedure takes new observations as input, accepts individual feature values as inline parameters, and returns predictions for the new observations.
    SerializePlots.sql Procedure for data searchSerializePlotsCreate a. This stored procedure uses Python to create graphics and serialize graph objects.
    TrainTipPredictionModelSciKitPy.sql scikit-Procedure for training logistic regression model by learnTrainTipPredictionModelSciKitPyCreate a. This model is trained using 60% of randomly selected data to predict the tipped value (whether or not to tip). The output of the stored procedure is a trained model, a tablenyc_taxi_modelsWill be registered in.
    TrainTipPredictionModelRxPy.sql Procedure for training logistic regression model with RevoScalePyTrainTipPredictionModelRxPyCreate a. This model is trained using 60% of randomly selected data to predict the tipped value (whether or not to tip). The output of the stored procedure is a trained model, a tablenyc_taxi_modelsWill be registered in.

    sqldev-python-browsetables1-gho9o9.png

    [!NOTE]

The T-SQL script does not recreate the database object, so if it already exists, the data will be duplicated. Therefore, delete the existing object before executing the script again.

** Important **: Redefining some objects

After performing the above steps, execute the following SQL to redefine some objects. ** The specifications of RevoScalePy changed when upgrading from SQL Server 2017 CTP to SQL Server 2017 RC, and the main reason for this redefinition is to accommodate the changes. ** **

Link

Next step

Step 3: Data exploration and visualization

Previous step

Step 1: Download sample data

From the beginning of the tutorial

In-Database Python Analysis for SQL Developers

Source

Step 2: Import Data to SQL Server using PowerShell

Related item

Machine Learning Services with Python

Recommended Posts

[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 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]
[In-Database Python Analysis Tutorial with SQL Server 2017] Step 5: Training and saving a model using T-SQL
Data analysis with python 2
Data analysis using Python 0
Data analysis with Python
Reading Note: An Introduction to Data Analysis with Python
[Technical book] Introduction to data analysis using Python -1 Chapter Introduction-
ODBC access to SQL Server from Linux with Python
Data analysis using python pandas
20200329_Introduction to Data Analysis with Python Second Edition Personal Summary
I know? Data analysis using Python or things you want to use when you want with numpy
Data analysis starting with python (data visualization 1)
Data analysis starting with python (data visualization 2)
Introduction to Data Analysis with Python P32-P43 [ch02 3.US Baby Names 1880-2010]
Introduction to Data Analysis with Python P17-P26 [ch02 1.usa.gov data from bit.ly]
Links to people who are just starting data analysis with python
From preparation for morphological analysis with python using polyglot to part-of-speech tagging
Convert Excel data to JSON with python
Recommendation tutorial using association analysis (python implementation)
Convert FX 1-minute data to 5-minute data with Python
Connect your SQL Server database to Alibaba Cloud Function Compute using Python
Data analysis starting with python (data preprocessing-machine learning)
I tried fMRI data analysis with python (Introduction to brain information decoding)
Convert csv, tsv data to matrix with python --using MovieLens as an example
When using PyQtGraph with Python Pyside, pay attention to the order of import
ODBC connection to FileMaker 11 Server Advanced with Python 3
I tried to get CloudWatch data with Python
[Python] Flow from web scraping to data analysis
Write CSV data to AWS-S3 with AWS-Lambda + Python
I tried to make a function to retrieve data from database column by column using sql with sqlite3 of python [sqlite3, sql, pandas]
Data analysis python
Quickly create a Python data analysis dashboard with Streamlit and deploy it to AWS
Data analysis for improving POG 1 ~ Web scraping with Python ~
Get Amazon RDS (PostgreSQL) data using SQL with pandas
[For beginners] How to study Python3 data analysis exam
How to scrape image data from flickr with python
Easy way to scrape with python using Google Colab
Data analysis environment construction with Python (IPython notebook + Pandas)
Challenge principal component analysis of text data with Python
Write data to KINTONE using the Python requests module
Process csv data with python (count processing using pandas)
Principal component analysis using python from nim with nimpy
I tried to analyze J League data with Python
Model.objects.extra to consider before using raw SQL with Django
[Python] Try to create ring fit data using Amazon Textract [OCR] (Try code review with Code Guru)
Data analysis using xarray
Data analysis overview python
Voice analysis with python
Python data analysis template
[Python tutorial] Data structure
Import tsv with Python
Voice analysis with python
Local server with python
I want to be able to analyze data with Python (Part 3)
screen and split screen with python and ssh login to remote server
I tried to make various "dummy data" with Python faker
Getting Started with python3 # 3 Try Advanced Computations Using Import Statements
I want to be able to analyze data with Python (Part 1)