Краткое руководство. Создание и оценка прогнозной модели в Python с помощью машинного обучения SQL

Статья
05/30/2023

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

В этом кратком руководстве вы создадите и обучите прогнозную модель с помощью Python. Затем вы сохраните модель в таблицу в экземпляре SQL Server и примените эту модель для прогнозирования значений на основе новых данных с помощью Служб машинного обучения SQL Server, Служб машинного обучения управляемого экземпляра SQL Azure или Кластеров больших данных SQL Server.

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

Выполнив это краткое руководство, вы узнаете, как делать следующее.

Внедрение кода Python в хранимую процедуру
Передача входных данных в код с помощью входных данных хранимой процедуры
Использование хранимых процедур для эксплуатации моделей

Предварительные требования

Для работы с этим кратким руководством необходимо следующее.

База данных SQL на одной из следующих платформ:
- Службы машинного обучения SQL Server. Сведения об установке см. в руководстве по установке для Windows или руководстве по установке для Linux.
- Кластеры больших данных SQL Server. Применение служб машинного обучения в кластерах больших данных SQL Server.
- Службы машинного обучения в управляемом экземпляре SQL Azure. Дополнительные сведения см. в статье Общие сведения о службах машинного обучения в управляемом экземпляре SQL Azure.
Средство для выполнения SQL-запросов, содержащих сценарии Python. В этом кратком руководстве используется Azure Data Studio.
В качестве примера данных в этом упражнении используется образец базы данных Iris. Следуйте инструкциям в разделе Демонстрационные данные Iris, чтобы создать образец базы данных irissql.

Создание хранимой процедуры, которая порождает модели

На этом шаге вы создадите хранимую процедуру, которая порождает модель для прогнозирования результатов.

Откройте Azure Data Studio, подключитесь к своему экземпляру SQL и откройте новое окно запроса.
Подключитесь к базе данных irissql.
```
USE irissql
GO
```
Скопируйте приведенный ниже код, чтобы создать новую хранимую процедуру.

При выполнении эта процедура вызывает sp_execute_external_script для запуска сеанса Python.

Входные данные, необходимые для кода Python, передаются в качестве входных параметров для этой хранимой процедуры. Выходные данные будут обучены на основе библиотеки Python scikit-learn, содержащей алгоритмы машинного обучения.

В этом коде для сериализации модели используется pickle. Модель будет обучена с использованием данных из столбцов 0–4 в таблице iris_data.

Параметры во второй части процедуры относятся к вводу данных и выходным данным модели. Рекомендуется добиваться, чтобы код Python, выполняемый в хранимой процедуре, имел четко определенные входные и выходные данные, сопоставленные с входными и выходными данными хранимой процедуры, передаваемыми во время выполнения.
```
CREATE PROCEDURE generate_iris_model (@trained_model VARBINARY(max) OUTPUT)
AS
BEGIN
    EXECUTE sp_execute_external_script @language = N'Python'
        , @script = N'
import pickle
from sklearn.naive_bayes import GaussianNB
GNB = GaussianNB()
trained_model = pickle.dumps(GNB.fit(iris_data[["Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width"]], iris_data[["SpeciesId"]].values.ravel()))
'
        , @input_data_1 = N'select "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "SpeciesId" from iris_data'
        , @input_data_1_name = N'iris_data'
        , @params = N'@trained_model varbinary(max) OUTPUT'
        , @trained_model = @trained_model OUTPUT;
END;
GO
```
Убедитесь, что хранимая процедура существует.

Если скрипт T-SQL из предыдущего шага выполнился без ошибок, создается новая хранимая процедура с именем generate_iris_model, которая добавляется в базу данных irissql. Хранимые процедуры можно найти в обозревателе объектов Azure Data Studio в разделе Программирование.

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

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

Модели, хранимые для повторного использования в базе данных, сериализуются как поток байтов и хранятся в столбце VARBINARY(MAX) в таблице базы данных. После того как модель создана, обучена, сериализована и сохранена в базе данных, она может быть вызвана другими процедурами или функцией PREDICT T-SQL для оценки рабочих нагрузок.

Чтобы выполнить процедуру, выполните следующий скрипт. Конкретная инструкция для исполнения хранимой процедуры — EXECUTE в четвертой строке.

Этот скрипт удаляет существующую модель с тем же именем ("Naive Bayes"), чтобы освободить место для новой, созданной путем выполнения той же процедуры. Без удаления модели возникает ошибка, сообщающая, что объект уже существует. Модель хранится в таблице с именем iris_models, которая будет подготовлена при создании базы данных irissql.
```
DECLARE @model varbinary(max);
DECLARE @new_model_name varchar(50)
SET @new_model_name = 'Naive Bayes'
EXECUTE generate_iris_model @model OUTPUT;
DELETE iris_models WHERE model_name = @new_model_name;
INSERT INTO iris_models (model_name, model) values(@new_model_name, @model);
GO
```
Убедитесь, что модель вставлена.
```
SELECT * FROM dbo.iris_models
```
Результаты

model_name model

упрощенный алгоритм Байеса 0x800363736B6C6561726E2E6E616976655F62617965730A...

model_name	model
упрощенный алгоритм Байеса	0x800363736B6C6561726E2E6E616976655F62617965730A...

Создание и выполнение хранимой процедуры для создания прогнозов

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

Выполните следующий код, чтобы создать хранимую процедуру, которая выполняет оценку. При выполнении эта процедура позволяет загрузить двоичную модель, использовать столбцы [1,2,3,4] в качестве входных данных и указать столбцы [0,5,6] в качестве выходных данных.

CREATE PROCEDURE predict_species (@model VARCHAR(100))
AS
BEGIN
    DECLARE @nb_model VARBINARY(max) = (
            SELECT model
            FROM iris_models
            WHERE model_name = @model
            );

    EXECUTE sp_execute_external_script @language = N'Python'
        , @script = N'
import pickle
irismodel = pickle.loads(nb_model)
species_pred = irismodel.predict(iris_data[["Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width"]])
iris_data["PredictedSpecies"] = species_pred
OutputDataSet = iris_data[["id","SpeciesId","PredictedSpecies"]] 
print(OutputDataSet)
'
        , @input_data_1 = N'select id, "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "SpeciesId" from iris_data'
        , @input_data_1_name = N'iris_data'
        , @params = N'@nb_model varbinary(max)'
        , @nb_model = @nb_model
    WITH RESULT SETS((
                "id" INT
              , "SpeciesId" INT
              , "SpeciesId.Predicted" INT
                ));
END;
GO

Выполните хранимую процедуру, указав имя модели "Naive Bayes" (Упрощенный алгоритм Байеса), чтобы процедура знала, какая модель будет использоваться.
```
EXECUTE predict_species 'Naive Bayes';
GO
```
При выполнении хранимой процедуры она возвращает data.frame из Python. Эта строка T-SQL указывает схему возвращаемых результатов: WITH RESULT SETS ( ("id" int, "SpeciesId" int, "SpeciesId.Predicted" int));. Результаты можно вставить в новую таблицу или вернуть в приложение.

Результаты представляют собой 150 прогнозов о видах цветов, основанных на характеристиках цветка, поданных в качестве входных данных. Для большинства наблюдений прогнозируемый вид цветов соответствует реальному.

Этот пример был упрощен благодаря применению набору данных Iris в Python для обучения и оценки. Более распространенный подход заключается в выполнении SQL-запроса для получения новых данных и их передачи в Python как InputDataSet.

Заключение

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

Как правило, следует планировать использование Azure Data Studio только с готовым кодом Python или очень простым кодом Python, который возвращает выходные данные на основе строк. В качестве инструментов Azure Data Studio поддерживает языки запросов, такие как T-SQL, и возвращает плоские наборы строк. Если код создает визуальные выходные данные, такие как точечные диаграммы или гистограммы, вам потребуется отдельное средство или приложение для конечных пользователей, способное визуализировать изображение вне хранимой процедуры.

Для некоторых разработчиков на Python, которые привыкли писать всеобъемлющие скрипты, выполняющие целый ряд операций, выделение задач в отдельные процедуры может показаться лишним. Но обучение и оценка имеют разные варианты использования. Разделив их, можно запланировать каждую задачу по отдельному расписанию и задать разрешения области для каждой операции отдельно.

Последнее преимущество заключается в том, что процессы можно изменять с помощью параметров. В этом упражнении код на Python, который создал модель (под названием "Naive Bayes" в этом примере), был передан в качестве входных данных для второй хранимой процедуры, вызывающей модель в процессе оценки. В этом упражнении используется только одна модель, но вы можете представить, как параметризация модели в задаче оценки сделает этот скрипт более полезным.

Дальнейшие действия

Дополнительные сведения об учебниках по использованию Python и машинного обучения SQL:

Учебники по Python