Поделиться через

Руководство по Python. Обучение и сохранение модели Python с помощью T-SQL

  • Статья

Область применения: SQL Server 2017 (14.x) и более поздних версий Управляемый экземпляр SQL Azure

В четвертой части этой серии руководств вы узнаете, как обучить модель машинного обучения с помощью пакетов Python scikit-learn и revoscalepy. Эти библиотеки Python устанавливаются в составе машинного обучения SQL Server.

Вы загрузите модули и вызовете необходимые функции для создания и обучения модели с помощью хранимой процедуры SQL Server. Для модели требуются функции данных, разработанные в предыдущих частях этой серии руководств. Наконец, вы сохраните обученную модель в таблице SQL Server.

Работая с этой статьей, вы узнаете о следующем.

  • Создание и обучение модели с помощью хранимой процедуры SQL
  • Сохранение обученной модели в таблице SQL

В первой части были установлены необходимые компоненты и восстановлена демонстрационная база данных.

Во второй части вы изучили образец данных и создали несколько графиков.

В третьей части вы узнали, как создавать функции из необработанных данных с помощью функции Transact-SQL. Затем вы вызвали эту функцию из хранимой процедуры, чтобы создать таблицу, содержащую значения характеристик.

Из пятой части вы узнаете, как ввести в эксплуатацию модели, которые были обучены и сохранены в соответствии с инструкциями в четвертой части.

Разделение примера данных на обучающий и проверочный наборы

  1. Создайте хранимую процедуру с именем PyTrainTestSplit, чтобы разделить данные в таблице nyctaxi_sample на две части: nyctaxi_sample_training и nyctaxi_sample_testing.

    Чтобы создать ее, выполните следующий код:

    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. Чтобы разделить данные с помощью пользовательского разбиения, выполните хранимую процедуру и предоставьте целочисленный параметр, который представляет процент данных, выделяемых для обучающего набора. Например, следующая инструкция выделит в обучающий набор 60 % данных.

    EXEC PyTrainTestSplit 60
GO

Создание модели логистической регрессии

После подготовки данных их можно использовать для обучения модели. Для этого вызывается хранимая процедура, которая выполняет некоторый код Python, принимая таблицу обучающих данных в качестве входных данных. В этом руководстве вы создадите две модели. Обе модели будут использовать двоичную классификацию.

  • Хранимая процедура PyTrainScikit создает модель прогнозирования чаевых с помощью пакета scikit-learn.
  • Хранимая процедура TrainTipPredictionModelRxPy создает модель прогнозирования чаевых с помощью пакета revoscalepy.

Эта хранимая процедура использует указанные входные данные для создания и обучения модели логистической регрессии. Весь код Python упаковывается в системную хранимую процедуру sp_execute_external_script.

Чтобы упростить повторное обучение модели на основе новых данных, можно поместить вызов sp_execute_external_script в другую хранимую процедуру и передать ей новые обучающие данные в качестве параметра. В этом разделе описаны этапы этого действия.

PyTrainScikit

  1. В Management Studio откройте новое окно запроса и выполните следующую инструкцию, чтобы создать хранимую процедуру PyTrainScikit. Поскольку хранимая процедура уже включает в себя определение входных данных, указывать входной запрос не требуется.

    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.

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

    Обработка данных и компоновка модели может занять несколько минут. Сообщения, которые будут переданы в поток stdout Python, отображаются в окне "Сообщения" в Среде Management Studio. Например:

    STDOUT message(s) from external script:
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy

  3. Откройте таблицу nyc_taxi_models. Вы увидите, что была добавлена одна новая строка, которая содержит сериализованную модель в столбце model.

    SciKit_model
0x800363736B6C6561726E2E6C696E6561....

TrainTipPredictionModelRxPy

Эта хранимая процедура использует пакет Python revoscalepy. Он содержит объекты, преобразования и алгоритмы, аналогичные тем, которые содержатся в пакете RevoScaleR для языка R.

С помощью revoscalepy можно создавать удаленные контексты вычислений, перемещать данные между контекстами вычислений, преобразовывать данные и обучать прогнозные модели с помощью популярных алгоритмов, таких как логистическая и линейная регрессия, деревья принятия решений и др. Дополнительные сведения см. в статьях Модуль revoscalepy в SQL Server и Справочник по функции revoscalepy.

  1. В Management Studio откройте новое окно запроса и выполните следующую инструкцию, чтобы создать хранимую процедуру TrainTipPredictionModelRxPy. Поскольку хранимая процедура уже включает в себя определение входных данных, указывать входной запрос не требуется.

    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

    В рамках обучения модели эта хранимая процедура выполняет следующие действия:

    • Запрос SELECT применяет пользовательскую скалярную функцию fnCalculateDistance для вычисления прямого расстояния между местами посадки и высадки. Результаты выполнения запроса сохраняются во входной переменной Python по умолчанию InputDataset.
    • Двоичная переменная tipped применяется в качестве столбца меток или результатов, и модель компонуется с использованием следующих столбцов характеристик: passenger_count, trip_distance, trip_time_in_secs и direct_distance.
    • Обученная модель сериализуется и сохраняется в переменной Python logitObj. С помощью ключевого слова OUTPUT T-SQL можно добавить переменную в качестве выходных данных хранимой процедуры. На следующем шаге эта переменная используется для вставки двоичного кода модели в таблицу базы данных nyc_taxi_models. Этот механизм упрощает хранение и повторное использование моделей.

  2. Выполните хранимую процедуру следующим образом, чтобы вставить обученную модель revoscalepy в таблицу nyc_taxi_models.

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

    Обработка данных и компоновка модели может занять некоторое время. Сообщения, которые будут переданы в поток stdout Python, отображаются в окне "Сообщения" в Среде Management Studio. Например:

    STDOUT message(s) from external script:
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy

  3. Откройте таблицу nyc_taxi_models. Вы увидите, что была добавлена одна новая строка, которая содержит сериализованную модель в столбце model.

    revoscalepy_model
0x8003637265766F7363616c....

В следующей части этого учебника обученная модель будет использоваться для создания прогнозов.

Следующие шаги

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

  • Создание и обучение модели с помощью хранимой процедуры SQL
  • Обученная модель сохранена в таблице SQL

Руководство по Python. Выполнение прогнозов с помощью Python, внедренного в хранимую процедуру