Учебник по Python. Обучение и сохранение модели Python с помощью T-SQLPython tutorial: Train and save a Python model using T-SQL

Применимо к:Applies to: даSQL Server 2017 (14.x);SQL Server 2017 (14.x)yesSQL Server 2017 (14.x);SQL Server 2017 (14.x) и более поздние версии ДаУправляемый экземпляр SQL AzureAzure SQL Managed InstanceYesУправляемый экземпляр SQL AzureAzure SQL Managed InstanceПрименимо к:Applies to: даSQL Server 2017 (14.x);SQL Server 2017 (14.x)yesSQL Server 2017 (14.x);SQL Server 2017 (14.x) and later ДаУправляемый экземпляр SQL AzureAzure SQL Managed InstanceYesУправляемый экземпляр SQL AzureAzure SQL Managed Instance

В четвертой части этой серии руководств вы узнаете, как обучить модель машинного обучения с помощью пакетов Python scikit-learn и revoscalepy.In part four of this five-part tutorial series, you'll learn how to train a machine learning model using the Python packages scikit-learn and revoscalepy. Эти библиотеки Python устанавливаются в составе машинного обучения SQL Server.These Python libraries are already installed with SQL Server machine learning.

Вы загрузите модули и вызовете необходимые функции для создания и обучения модели с помощью хранимой процедуры SQL Server.You'll load the modules and call the necessary functions to create and train the model using a SQL Server stored procedure. Для модели требуются функции данных, разработанные в предыдущих частях этой серии руководств.The model requires the data features you engineered in earlier parts of this tutorial series. Наконец, вы сохраните обученную модель в таблице SQL ServerSQL Server.Finally, you'll save the trained model to a SQL ServerSQL Server table.

Работая с этой статьей, вы узнаете о следующем.In this article, you'll:

  • Создание и обучение модели с помощью хранимой процедуры SQLCreate and train a model using a SQL stored procedure
  • Сохранение обученной модели в таблице SQLSave the trained model to a SQL table

В первой части были установлены необходимые компоненты и восстановлена демонстрационная база данных.In part one, you installed the prerequisites and restored the sample database.

Во второй части вы изучили образец данных и создали несколько графиков.In part two, you explored the sample data and generated some plots.

В третьей части вы узнали, как создавать функции из необработанных данных с помощью функции Transact-SQL.In part three, you learned how to create features from raw data by using a Transact-SQL function. Затем вы вызвали эту функцию из хранимой процедуры, чтобы создать таблицу, содержащую значения характеристик.You then called that function from a stored procedure to create a table that contains the feature values.

Из пятой части вы узнаете, как ввести в эксплуатацию модели, которые были обучены и сохранены в соответствии с инструкциями в четвертой части.In part five, you'll learn how to operationalize the models that you trained and saved in part four.

Разделение примера данных на обучающий и проверочный наборыSplit the sample data into training and testing sets

  1. Создайте хранимую процедуру с именем PyTrainTestSplit, чтобы разделить данные в таблице nyctaxi_sample на две части: nyctaxi_sample_training и nyctaxi_sample_testing.Create a stored procedure called PyTrainTestSplit to divide the data in the nyctaxi_sample table into two parts: nyctaxi_sample_training and nyctaxi_sample_testing.

    Эта хранимая процедура уже должна быть создана. Но если это не так, ее можно создать, выполнив следующий код:This stored procedure should already be created for you, but you can run the following code to create it:

    DROP PROCEDURE IF EXISTS PyTrainTestSplit;
    GO
    
    CREATE PROCEDURE [dbo].[PyTrainTestSplit] (@pct int)
    AS
    
    DROP TABLE IF EXISTS dbo.nyctaxi_sample_training
    SELECT * into nyctaxi_sample_training FROM nyctaxi_sample WHERE (ABS(CAST(BINARY_CHECKSUM(medallion,hack_license)  as int)) % 100) < @pct
    
    DROP TABLE IF EXISTS dbo.nyctaxi_sample_testing
    SELECT * into nyctaxi_sample_testing FROM nyctaxi_sample
    WHERE (ABS(CAST(BINARY_CHECKSUM(medallion,hack_license)  as int)) % 100) > @pct
    GO
    
  2. Чтобы разделить данные с помощью пользовательского разбиения, выполните хранимую процедуру и введите целое число, представляющее процент данных, выделенных для обучающего набора.To divide your data using a custom split, run the stored procedure, and type an integer that represents the percentage of data allocated to the training set. Например, следующая инструкция выделит в обучающий набор 60 % данных.For example, the following statement would allocate 60% of data to the training set.

    EXEC PyTrainTestSplit 60
    GO
    

Создание модели логистической регрессииBuild a logistic regression model

После подготовки данных их можно использовать для обучения модели.After the data has been prepared, you can use it to train a model. Для этого вызывается хранимая процедура, которая выполняет некоторый код Python, принимая таблицу обучающих данных в качестве входных данных.You do this by calling a stored procedure that runs some Python code, taking as input the training data table. В этом руководстве вы создадите две модели. Обе модели будут использовать двоичную классификацию.For this tutorial, you create two models, both binary classification models:

  • Хранимая процедура PyTrainScikit создает модель прогнозирования чаевых с помощью пакета scikit-learn.The stored procedure PyTrainScikit creates a tip prediction model using the scikit-learn package.
  • Хранимая процедура TrainTipPredictionModelRxPy создает модель прогнозирования чаевых с помощью пакета revoscalepy.The stored procedure TrainTipPredictionModelRxPy creates a tip prediction model using the revoscalepy package.

Эта хранимая процедура использует указанные входные данные для создания и обучения модели логистической регрессии.Each stored procedure uses the input data you provide to create and train a logistic regression model. Весь код Python находится в системной хранимой процедуре sp_execute_external_script.All Python code is wrapped in the system stored procedure, sp_execute_external_script.

Чтобы упростить повторное обучение модели на основе новых данных, можно поместить вызов sp_execute_external_script в другую хранимую процедуру и передать новые обучающие данные в качестве параметра.To make it easier to retrain the model on new data, you wrap the call to sp_execute_external_script in another stored procedure, and pass in the new training data as a parameter. В этом разделе описаны этапы этого действия.This section will walk you through that process.

PyTrainScikitPyTrainScikit

  1. В среде Среда Management StudioManagement Studio откройте новое окно Запрос и выполните приведенную ниже инструкцию, чтобы создать хранимую процедуру PyTrainScikit.In Среда Management StudioManagement Studio, open a new Query window and run the following statement to create the stored procedure PyTrainScikit. Поскольку хранимая процедура уже включает в себя определение входных данных, указывать входной запрос не требуется.The stored procedure contains a definition of the input data, so you don't need to provide an input query.

    DROP PROCEDURE IF EXISTS PyTrainScikit;
    GO
    
    CREATE PROCEDURE [dbo].[PyTrainScikit] (@trained_model varbinary(max) OUTPUT)
    AS
    BEGIN
    EXEC sp_execute_external_script
      @language = N'Python',
      @script = N'
    import numpy
    import pickle
    from sklearn.linear_model import LogisticRegression
    
    ##Create SciKit-Learn logistic regression model
    X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]
    y = numpy.ravel(InputDataSet[["tipped"]])
    
    SKLalgo = LogisticRegression()
    logitObj = SKLalgo.fit(X, y)
    
    ##Serialize model
    trained_model = pickle.dumps(logitObj)
    ',
    @input_data_1 = N'
    select tipped, fare_amount, passenger_count, trip_time_in_secs, trip_distance, 
    dbo.fnCalculateDistance(pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as direct_distance
    from nyctaxi_sample_training
    ',
    @input_data_1_name = N'InputDataSet',
    @params = N'@trained_model varbinary(max) OUTPUT',
    @trained_model = @trained_model OUTPUT;
    ;
    END;
    GO
    
  2. Выполните следующие инструкции SQL, чтобы вставить обученную модель в таблицу nyc_taxi_models.Run the following SQL statements to insert the trained model into table nyc_taxi_models.

    DECLARE @model VARBINARY(MAX);
    EXEC PyTrainScikit @model OUTPUT;
    INSERT INTO nyc_taxi_models (name, model) VALUES('SciKit_model', @model);
    

    Обработка данных и компоновка модели может занять несколько минут.Processing of the data and fitting the model might take a couple of mins. Сообщения, которые должны передаваться в поток stdout Python, отображаются в окне Сообщения среды Среда Management StudioManagement Studio.Messages that would be piped to Python's stdout stream are displayed in the Messages window of Среда Management StudioManagement Studio. Пример:For example:

    STDOUT message(s) from external script:
    C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy
    
  3. Откройте таблицу nyc_taxi_models.Open the table nyc_taxi_models. Вы увидите, что была добавлена одна новая строка, которая содержит сериализованную модель в столбце model.You can see that one new row has been added, which contains the serialized model in the column model.

    SciKit_model
    0x800363736B6C6561726E2E6C696E6561....
    

TrainTipPredictionModelRxPyTrainTipPredictionModelRxPy

В этой хранимой процедуре используется пакет revoscalepy, который является новым пакетом для Python.This stored procedure uses the new revoscalepy package, which is a new package for Python. Он содержит объекты, преобразования и алгоритмы, аналогичные тем, которые содержатся в пакете RevoScaleR для языка R.It contains objects, transformation, and algorithms similar to those provided for the R language's RevoScaleR package.

С помощью revoscalepy можно создавать удаленные контексты вычислений, перемещать данные между контекстами вычислений, преобразовывать данные и обучать прогнозные модели с помощью популярных алгоритмов, таких как логистическая и линейная регрессия, деревья принятия решений и др.By using revoscalepy, you can create remote compute contexts, move data between compute contexts, transform data, and train predictive models using popular algorithms such as logistic and linear regression, decision trees, and more. Дополнительные сведения см. в статьях Модуль revoscalepy в SQL Server и Справочник по функции revoscalepy.For more information, see revoscalepy module in SQL Server and revoscalepy function reference.

  1. В среде Среда Management StudioManagement Studio откройте новое окно Запрос и выполните приведенную ниже инструкцию, чтобы создать хранимую процедуру TrainTipPredictionModelRxPy.In Среда Management StudioManagement Studio, open a new Query window and run the following statement to create the stored procedure TrainTipPredictionModelRxPy. Поскольку хранимая процедура уже включает в себя определение входных данных, указывать входной запрос не требуется.Because the stored procedure already includes a definition of the input data, you don't need to provide an input query.

    DROP PROCEDURE IF EXISTS TrainTipPredictionModelRxPy;
    GO
    
    CREATE PROCEDURE [dbo].[TrainTipPredictionModelRxPy] (@trained_model varbinary(max) OUTPUT)
    AS
    BEGIN
    EXEC sp_execute_external_script 
      @language = N'Python',
      @script = N'
    import numpy
    import pickle
    from revoscalepy.functions.RxLogit import rx_logit
    
    ## Create a logistic regression model using rx_logit function from revoscalepy package
    logitObj = rx_logit("tipped ~ passenger_count + trip_distance + trip_time_in_secs + direct_distance", data = InputDataSet);
    
    ## Serialize model
    trained_model = pickle.dumps(logitObj)
    ',
    @input_data_1 = N'
    select tipped, fare_amount, passenger_count, trip_time_in_secs, trip_distance, 
    dbo.fnCalculateDistance(pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as direct_distance
    from nyctaxi_sample_training
    ',
    @input_data_1_name = N'InputDataSet',
    @params = N'@trained_model varbinary(max) OUTPUT',
    @trained_model = @trained_model OUTPUT;
    ;
    END;
    GO
    

    В рамках обучения модели эта хранимая процедура выполняет следующие действия:This stored procedure performs the following steps as part of model training:

    • Запрос SELECT применяет пользовательскую скалярную функцию fnCalculateDistance для вычисления прямого расстояния между местами посадки и высадки.The SELECT query applies the custom scalar function fnCalculateDistance to calculate the direct distance between the pick-up and drop-off locations. Результаты выполнения запроса сохраняются во входной переменной Python по умолчанию InputDataset.The results of the query are stored in the default Python input variable, InputDataset.
    • Двоичная переменная tipped применяется в качестве столбца меток или результатов, и модель компонуется с использованием следующих столбцов характеристик: passenger_count, trip_distance, _trip_time_in_secs_и direct_distance.The binary variable tipped is used as the label or outcome column, and the model is fit using these feature columns: passenger_count, trip_distance, trip_time_in_secs, and direct_distance.
    • Обученная модель сериализуется и сохраняется в переменной Python logitObj.The trained model is serialized and stored in the Python variable logitObj. С помощью ключевого слова OUTPUT T-SQL можно добавить переменную в качестве выходных данных хранимой процедуры.By adding the T-SQL keyword OUTPUT, you can add the variable as an output of the stored procedure. На следующем шаге эта переменная используется для вставки двоичного кода модели в таблицу базы данных nyc_taxi_models.In the next step, that variable is used to insert the binary code of the model into a database table nyc_taxi_models. Этот механизм упрощает хранение и повторное использование моделей.This mechanism makes it easy to store and re-use models.
  2. Выполните хранимую процедуру следующим образом, чтобы вставить обученную модель revoscalepy в таблицу nyc_taxi_models.Run the stored procedure as follows to insert the trained revoscalepy model into the table nyc_taxi_models.

    DECLARE @model VARBINARY(MAX);
    EXEC TrainTipPredictionModelRxPy @model OUTPUT;
    INSERT INTO nyc_taxi_models (name, model) VALUES('revoscalepy_model', @model);
    

    Обработка данных и компоновка модели может занять некоторое время.Processing of the data and fitting the model might take a while. Сообщения, которые должны передаваться в поток stdout Python, отображаются в окне Сообщения среды Среда Management StudioManagement Studio.Messages that would be piped to Python's stdout stream are displayed in the Messages window of Среда Management StudioManagement Studio. Пример:For example:

    STDOUT message(s) from external script:
    C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy
    
  3. Откройте таблицу nyc_taxi_models.Open the table nyc_taxi_models. Вы увидите, что была добавлена одна новая строка, которая содержит сериализованную модель в столбце model.You can see that one new row has been added, which contains the serialized model in the column model.

    revoscalepy_model
    0x8003637265766F7363616c....
    

В следующей части этого учебника обученная модель будет использоваться для создания прогнозов.In the next part of this tutorial, you'll use the trained models to create predictions.

Дальнейшие шагиNext steps

Работая с этой статьей, вы выполните следующие задачи:In this article, you:

  • Создание и обучение модели с помощью хранимой процедуры SQLCreated and trained a model using a SQL stored procedure
  • Обученная модель сохранена в таблице SQLSaved the trained model to a SQL table