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


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

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

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

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

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

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

Необходимые компоненты

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

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

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

  1. Откройте Azure Data Studio, подключитесь к своему экземпляру SQL и откройте новое окно запроса.

  2. Подключитесь к базе данных irissql.

    USE irissql
    GO
    
  3. Скопируйте приведенный ниже код, чтобы создать новую хранимую процедуру.

    При выполнении эта процедура вызывает 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
    
  4. Убедитесь, что хранимая процедура существует.

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

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

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

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

  1. Чтобы выполнить процедуру, выполните следующий скрипт. Конкретная инструкция для исполнения хранимой процедуры — 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
    
  2. Убедитесь, что модель вставлена.

    SELECT * FROM dbo.iris_models
    

    Результаты

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

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

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

  1. Выполните следующий код, чтобы создать хранимую процедуру, которая выполняет оценку. При выполнении эта процедура позволяет загрузить двоичную модель, использовать столбцы [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
    
  2. Выполните хранимую процедуру, указав имя модели "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: