Краткое руководство. Создание и оценка прогнозной модели в Python с помощью машинного обучения SQLQuickstart: Create and score a predictive model in Python with SQL machine learning

Применимо к: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.In this quickstart, you'll create and train a predictive model using Python. Затем вы сохраните модель в таблицу в экземпляре SQL Server и примените эту модель для прогнозирования значений на основе новых данных с помощью Служб машинного обучения SQL Server, Служб машинного обучения управляемого экземпляра SQL Azure или Кластеров больших данных SQL Server.You'll save the model to a table in your SQL Server instance, and then use the model to predict values from new data using SQL Server Machine Learning Services, Azure SQL Managed Instance Machine Learning Services, or SQL Server Big Data Clusters.

Вы создадите и запустите две хранимые процедуры, выполняемые в SQL.You'll create and execute two stored procedures running in SQL. В первой из них используется классический набор данных Iris и создается модель упрощенного алгоритма Байеса для прогнозирования вида ирисов на основе характеристик цветка.The first one uses the classic Iris flower data set and generates a Naïve Bayes model to predict an Iris species based on flower characteristics. Вторая процедура предназначена для оценки — она вызывает модель, созданную в первой процедуре, для вывода набора прогнозов на основе новых данных.The second procedure is for scoring - it calls the model generated in the first procedure to output a set of predictions based on new data. Поместив код Python в хранимую процедуру SQL, вы переносите операции в SQL, благодаря чему они могут многократно использоваться и вызываться другими хранимыми процедурами и клиентскими приложениями.By placing Python code in a SQL stored procedure, operations are contained in SQL, are reusable, and can be called by other stored procedures and client applications.

Выполнив это краткое руководство, вы узнаете, как делать следующее.By completing this quickstart, you'll learn:

  • Внедрение кода Python в хранимую процедуруHow to embed Python code in a stored procedure
  • Передача входных данных в код с помощью входных данных хранимой процедурыHow to pass inputs to your code through inputs on the stored procedure
  • Использование хранимых процедур для эксплуатации моделейHow stored procedures are used to operationalize models

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

Для работы с этим кратким руководством необходимо следующее.You need the following prerequisites to run this quickstart.

Создание хранимой процедуры, которая порождает моделиCreate a stored procedure that generates models

На этом шаге вы создадите хранимую процедуру, которая порождает модель для прогнозирования результатов.In this step, you'll create a stored procedure that generates a model for predicting outcomes.

  1. Откройте Azure Data Studio, подключитесь к своему экземпляру SQL и откройте новое окно запроса.Open Azure Data Studio, connect to your SQL instance, and open a new query window.

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

    USE irissql
    GO
    
  3. Скопируйте приведенный ниже код, чтобы создать новую хранимую процедуру.Copy in the following code to create a new stored procedure.

    При выполнении эта процедура вызывает sp_execute_external_script для запуска сеанса Python.When executed, this procedure calls sp_execute_external_script to start a Python session.

    Входные данные, необходимые для кода Python, передаются в качестве входных параметров для этой хранимой процедуры.Inputs needed by your Python code are passed as input parameters on this stored procedure. Выходные данные будут обучены на основе библиотеки Python scikit-learn, содержащей алгоритмы машинного обучения.Output will be a trained model, based on the Python scikit-learn library for the machine learning algorithm.

    В этом коде для сериализации модели используется pickle.This code uses pickle to serialize the model. Модель будет обучена с использованием данных из столбцов 0–4 в таблице iris_data.The model will be trained using data from columns 0 through 4 from the iris_data table.

    Параметры во второй части процедуры относятся к вводу данных и выходным данным модели.The parameters you see in the second part of the procedure articulate data inputs and model outputs. Рекомендуется добиваться, чтобы код Python, выполняемый в хранимой процедуре, имел четко определенные входные и выходные данные, сопоставленные с входными и выходными данными хранимой процедуры, передаваемыми во время выполнения.As much as possible, you want the Python code running in a stored procedure to have clearly defined inputs and outputs that map to stored procedure inputs and outputs passed in at run time.

    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. Убедитесь, что хранимая процедура существует.Verify the stored procedure exists.

    Если скрипт T-SQL из предыдущего шага выполнился без ошибок, создается новая хранимая процедура с именем generate_iris_model, которая добавляется в базу данных irissql.If the T-SQL script from the previous step ran without error, a new stored procedure called generate_iris_model is created and added to the irissql database. Хранимые процедуры можно найти в обозревателе объектов Azure Data Studio в разделе Программирование.You can find stored procedures in the Azure Data Studio Object Explorer, under Programmability.

Выполнение процедуры для создания и обучения моделейExecute the procedure to create and train models

На этом шаге выполняется процедура запуска внедренного кода с созданием обученной и сериализованной модели в качестве выходных данных.In this step, you execute the procedure to run the embedded code, creating a trained and serialized model as an output.

Модели, хранимые для повторного использования в базе данных, сериализуются как поток байтов и хранятся в столбце VARBINARY(MAX) в таблице базы данных.Models that are stored for reuse in your database are serialized as a byte stream and stored in a VARBINARY(MAX) column in a database table. После того как модель создана, обучена, сериализована и сохранена в базе данных, она может быть вызвана другими процедурами или функцией PREDICT T-SQL для оценки рабочих нагрузок.Once the model is created, trained, serialized, and saved to a database, it can be called by other procedures or by the PREDICT T-SQL function in scoring workloads.

  1. Чтобы выполнить процедуру, выполните следующий скрипт.Run the following script to execute the procedure. Конкретная инструкция для исполнения хранимой процедуры — EXECUTE в четвертой строке.The specific statement for executing a stored procedure is EXECUTE on the fourth line.

    Этот скрипт удаляет существующую модель с тем же именем ("Naive Bayes"), чтобы освободить место для новой, созданной путем выполнения той же процедуры.This particular script deletes an existing model of the same name ("Naive Bayes") to make room for new ones created by rerunning the same procedure. Без удаления модели возникает ошибка, сообщающая, что объект уже существует.Without model deletion, an error occurs stating the object already exists. Модель хранится в таблице с именем iris_models, которая будет подготовлена при создании базы данных irissql.The model is stored in a table called iris_models, provisioned when you created the irissql database.

    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. Убедитесь, что модель вставлена.Verify that the model was inserted.

    SELECT * FROM dbo.iris_models
    

    РезультатыResults

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

Создание и выполнение хранимой процедуры для создания прогнозовCreate and execute a stored procedure for generating predictions

Теперь, когда модель создана, обучена и сохранена, переходите к следующему шагу: создание хранимой процедуры, дающей прогнозы.Now that you have created, trained, and saved a model, move on to the next step: creating a stored procedure that generates predictions. Это делается путем вызова sp_execute_external_script для запуска скрипта Python, который загружает сериализованную модель и передает ей новые входные данные для оценки.You'll do this by calling sp_execute_external_script to run a Python script that loads the serialized model and gives it new data inputs to score.

  1. Выполните следующий код, чтобы создать хранимую процедуру, которая выполняет оценку.Run the following code to create the stored procedure that performs scoring. При выполнении эта процедура позволяет загрузить двоичную модель, использовать столбцы [1,2,3,4] в качестве входных данных и указать столбцы [0,5,6] в качестве выходных данных.At run time, this procedure will load a binary model, use columns [1,2,3,4] as inputs, and specify columns [0,5,6] as output.

    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 the stored procedure, giving the model name "Naive Bayes" so that the procedure knows which model to use.

    EXECUTE predict_species 'Naive Bayes';
    GO
    

    При выполнении хранимой процедуры она возвращает data.frame из Python.When you run the stored procedure, it returns a Python data.frame. Эта строка T-SQL указывает схему возвращаемых результатов: WITH RESULT SETS ( ("id" int, "SpeciesId" int, "SpeciesId.Predicted" int));.This line of T-SQL specifies the schema for the returned results: WITH RESULT SETS ( ("id" int, "SpeciesId" int, "SpeciesId.Predicted" int));. Результаты можно вставить в новую таблицу или вернуть в приложение.You can insert the results into a new table, or return them to an application.

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

    Результаты представляют собой 150 прогнозов о видах цветов, основанных на характеристиках цветка, поданных в качестве входных данных.The results are 150 predictions about species using floral characteristics as inputs. Для большинства наблюдений прогнозируемый вид цветов соответствует реальному.For the majority of the observations, the predicted species matches the actual species.

    Этот пример был упрощен благодаря применению набору данных Iris в Python для обучения и оценки.This example has been made simple by using the Python iris dataset for both training and scoring. Более распространенный подход заключается в выполнении SQL-запроса для получения новых данных и их передачи в Python как InputDataSet.A more typical approach would involve running a SQL query to get the new data, and passing that into Python as InputDataSet.

ЗаключениеConclusion

В этом упражнении вы узнали, как создавать хранимые процедуры, предназначенные для различных задач, где каждая хранимая процедура использует системную хранимую процедуру sp_execute_external_script для запуска процесса Python.In this exercise, you learned how to create stored procedures dedicated to different tasks, where each stored procedure used the system stored procedure sp_execute_external_script to start a Python process. Входные данные для процесса Python передаются в sp_execute_external в качестве параметров.Inputs to the Python process are passed to sp_execute_external as parameters. Как сам скрипт Python, так и переменные данных в базе данных передаются в качестве входных параметров.Both the Python script itself and data variables in a database are passed as inputs.

Как правило, следует планировать использование Azure Data Studio только с готовым кодом Python или очень простым кодом Python, который возвращает выходные данные на основе строк.Generally, you should only plan on using Azure Data Studio with polished Python code, or simple Python code that returns row-based output. В качестве инструментов Azure Data Studio поддерживает языки запросов, такие как T-SQL, и возвращает плоские наборы строк.As a tool, Azure Data Studio supports query languages like T-SQL and returns flattened rowsets. Если код создает визуальные выходные данные, такие как точечные диаграммы или гистограммы, вам потребуется отдельное средство или приложение для конечных пользователей, способное визуализировать изображение вне хранимой процедуры.If your code generates visual output like a scatterplot or histogram, you need a separate tool or end-user application that can render the image outside of the stored procedure.

Для некоторых разработчиков на Python, которые привыкли писать всеобъемлющие скрипты, выполняющие целый ряд операций, выделение задач в отдельные процедуры может показаться лишним.For some Python developers who are used to writing all-inclusive script handling a range of operations, organizing tasks into separate procedures might seem unnecessary. Но обучение и оценка имеют разные варианты использования.But training and scoring have different use cases. Разделив их, можно запланировать каждую задачу по отдельному расписанию и задать разрешения области для каждой операции отдельно.By separating them, you can put each task on a different schedule and scope permissions to each operation.

Последнее преимущество заключается в том, что процессы можно изменять с помощью параметров.A final benefit is that the processes can be modified using parameters. В этом упражнении код на Python, который создал модель (под названием "Naive Bayes" в этом примере), был передан в качестве входных данных для второй хранимой процедуры, вызывающей модель в процессе оценки.In this exercise, Python code that created the model (named "Naive Bayes" in this example) was passed as an input to a second stored procedure calling the model in a scoring process. В этом упражнении используется только одна модель, но вы можете представить, как параметризация модели в задаче оценки сделает этот скрипт более полезным.This exercise only uses one model, but you can imagine how parameterizing the model in a scoring task would make that script more useful.

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

Дополнительные сведения об учебниках по использованию Python и машинного обучения SQL:For more information on tutorials for Python with SQL machine learning, see: