Учебник по 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 в хранимой процедуре