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

Применимо к:Applies to: даSQL Server 2016 (13.x);SQL Server 2016 (13.x)yesSQL Server 2016 (13.x);SQL Server 2016 (13.x) и более поздние версии ДаУправляемый экземпляр SQL AzureAzure SQL Managed InstanceYesУправляемый экземпляр SQL AzureAzure SQL Managed InstanceПрименимо к:Applies to: даSQL Server 2016 (13.x);SQL Server 2016 (13.x)yesSQL Server 2016 (13.x);SQL Server 2016 (13.x) and later ДаУправляемый экземпляр SQL AzureAzure SQL Managed InstanceYesУправляемый экземпляр SQL AzureAzure SQL Managed Instance

В этом кратком руководстве вы создадите и обучите прогнозную модель с помощью T. Затем вы сохраните ее в таблицу в экземпляре SQL Server и примените эту модель для прогнозирования значений на основе новых данных с помощью Служб машинного обучения SQL Server в кластерах больших данных.In this quickstart, you'll create and train a predictive model using T. 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 or on Big Data Clusters.

В этом кратком руководстве вы создадите и обучите прогнозную модель с помощью T. Затем вы сохраните модель в таблицу в экземпляре SQL Server и примените эту модель для прогнозирования значений на основе новых данных с помощью Служб машинного обучения SQL Server.In this quickstart, you'll create and train a predictive model using T. 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.

В этом кратком руководстве вы создадите и обучите прогнозную модель с помощью T. Затем вы сохраните ее в таблицу в экземпляре SQL Server и примените эту модель для прогнозирования значений на основе новых данных с помощью служб SQL Server R Services.In this quickstart, you'll create and train a predictive model using T. 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 R Services.

В этом кратком руководстве вы создадите и обучите прогнозную модель с помощью T. Затем вы сохраните модель в таблице в экземпляре SQL Server и примените эту модель для прогнозирования значений на основе новых данных с помощью Служб машинного обучения в Управляемом экземпляре SQL Azure.In this quickstart, you'll create and train a predictive model using T. 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 Azure SQL Managed Instance Machine Learning Services.

Вы создадите и запустите две хранимые процедуры, выполняемые в SQL.You'll create and execute two stored procedures running in SQL. В первой из них используется набор данных mtcars, входящий в состав R, на основе которого создается простая обобщенная линейная модель (ОЛМ), которая прогнозирует вероятность оснащения автомобиля механической КПП.The first one uses the mtcars dataset included with R and generates a simple generalized linear model (GLM) that predicts the probability that a vehicle has been fitted with a manual transmission. Вторая процедура предназначена для оценки — она вызывает модель, созданную в первой процедуре, для вывода набора прогнозов на основе новых данных.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. Поместив код R в хранимую процедуру SQL, вы переносите операции в SQL, благодаря чему они могут многократно использоваться и вызываться другими хранимыми процедурами и клиентскими приложениями.By placing R code in a SQL stored procedure, operations are contained in SQL, are reusable, and can be called by other stored procedures and client applications.

Совет

Если вам требуется средство обновления линейных моделей, ознакомьтесь со следующим руководством, в котором описывается процесс компоновки моделей с помощью функции rxLinMod: Fitting Linear Models using RevoScaleR (Подготовка линейных моделей с помощью RevoScaleR)If you need a refresher on linear models, try this tutorial which describes the process of fitting a model using rxLinMod: Fitting Linear Models

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

  • Внедрение кода R в хранимую процедуруHow to embed R 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.

  • Инструмент для выполнения SQL-запросов, содержащих сценарии R.A tool for running SQL queries that contain R scripts. В этом кратком руководстве используется Azure Data Studio.This quickstart uses Azure Data Studio.

Создание моделиCreate the model

Чтобы создать модель, вам необходимо сформировать исходные данные для обучения, создать модель и обучить ее с использованием этих данных, а затем сохранить эту модель в базе данных, где она будет использоваться для создания прогнозов на основе новых данных.To create the model, you'll create source data for training, create the model and train it using the data, then store the model in a database where it can be used to generate predictions with new data.

Создание исходных данныхCreate the source data

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

  2. Создайте таблицу, чтобы сохранить данные для обучения.Create a table to save the training data.

    CREATE TABLE dbo.MTCars(
        mpg decimal(10, 1) NOT NULL,
        cyl int NOT NULL,
        disp decimal(10, 1) NOT NULL,
        hp int NOT NULL,
        drat decimal(10, 2) NOT NULL,
        wt decimal(10, 3) NOT NULL,
        qsec decimal(10, 2) NOT NULL,
        vs int NOT NULL,
        am int NOT NULL,
        gear int NOT NULL,
        carb int NOT NULL
    );
    
  3. Вставьте данные из встроенного набора данных mtcars.Insert the data from the built-in dataset mtcars.

    INSERT INTO dbo.MTCars
    EXEC sp_execute_external_script @language = N'R'
        , @script = N'MTCars <- mtcars;'
        , @input_data_1 = N''
        , @output_data_1_name = N'MTCars';
    

    Совет

    Многие наборы данных, малые и большие, включаются в среду выполнения R.Many datasets, small and large, are included with the R runtime. Чтобы получить список наборов данных, установленных с R, введите library(help="datasets") в командной строке R.To get a list of datasets installed with R, type library(help="datasets") from an R command prompt.

Создание и обучение моделиCreate and train the model

Данные о скорости автомобиля содержат два численных столбца — мощность в лошадиных силах (hp) и масса (wt).The car speed data contains two columns, both numeric: horsepower (hp) and weight (wt). На основе этих данных вы создадите обобщенную линейную модель (ОЛМ), которая оценивает вероятность того, что автомобиль оснащен механической КПП.From this data, you'll create a generalized linear model (GLM) that estimates the probability that a vehicle has been fitted with a manual transmission.

Чтобы создать модель, необходимо определить формулу внутри кода R, а затем передать данные в качестве входных параметров.To build the model, you define the formula inside your R code, and pass the data as an input parameter.

DROP PROCEDURE IF EXISTS generate_GLM;
GO
CREATE PROCEDURE generate_GLM
AS
BEGIN
    EXEC sp_execute_external_script
    @language = N'R'
    , @script = N'carsModel <- glm(formula = am ~ hp + wt, data = MTCarsData, family = binomial);
        trained_model <- data.frame(payload = as.raw(serialize(carsModel, connection=NULL)));'
    , @input_data_1 = N'SELECT hp, wt, am FROM MTCars'
    , @input_data_1_name = N'MTCarsData'
    , @output_data_1_name = N'trained_model'
    WITH RESULT SETS ((model VARBINARY(max)));
END;
GO
  • Первый аргумент для функции glm — это параметр formula, где дистанция (am) является значением, зависимым от скорости (hp + wt).The first argument to glm is the formula parameter, which defines am as dependent on hp + wt.
  • Входные данные хранятся в переменной MTCarsData, которая заполняется с помощью SQL-запроса.The input data is stored in the variable MTCarsData, which is populated by the SQL query. Если для входных данных не назначено определенное имя, имя переменной по умолчанию будет InputDataSet.If you don't assign a specific name to your input data, the default variable name would be InputDataSet.

Сохранение модели в базе данныхStore the model in the database

Далее необходимо сохранить модель в базе данных, где ее можно будет использовать для прогнозирования или повторного обучения.Next, store the model in a database so you can use it for prediction or retrain it.

  1. Создайте таблицу для хранения модели.Create a table to store the model.

    Выходные данные пакета R, с помощью которого создается модель, обычно являются двоичным объектом.The output of an R package that creates a model is usually a binary object. Это означает, что таблица, в которой хранится модель, должна содержать столбец типа varbinary(max) .Therefore, the table where you store the model must provide a column of varbinary(max) type.

    CREATE TABLE GLM_models (
        model_name varchar(30) not null default('default model') primary key,
        model varbinary(max) not null
    );
    
  2. Выполните приведенную ниже инструкцию Transact-SQL, чтобы вызвать хранимую процедуру, создать модель и сохранить ее в созданной таблице.Run the following Transact-SQL statement to call the stored procedure, generate the model, and save it to the table you created.

    INSERT INTO GLM_models(model)
    EXEC generate_GLM;
    

    Совет

    Если вы выполните этот код во второй раз, появится следующая ошибка: "Нарушение ограничения первичного ключа... Невозможно вставить повторяющийся ключ в объект dbo.stopping_distance_models".If you run this code a second time, you get this error: "Violation of PRIMARY KEY constraint...Cannot insert duplicate key in object dbo.stopping_distance_models". Один из вариантов, который поможет избежать этой ошибки, — обновить имя для каждой новой модели.One option for avoiding this error is to update the name for each new model. Например, можно изменить имя на нечто более описательное и включить тип модели, день создания и т. д.For example, you could change the name to something more descriptive, and include the model type, the day you created it, and so forth.

    UPDATE GLM_models
    SET model_name = 'GLM_' + format(getdate(), 'yyyy.MM.HH.mm', 'en-gb')
    WHERE model_name = 'default model'
    

Оценка новых данных с использованием обученной моделиScore new data using the trained model

По оценкой в области обработки и анализа данных понимается вычисление прогнозов, вероятностей и других значений на основе данных, подаваемых в обученную модель.Scoring is a term used in data science to mean generating predictions, probabilities, or other values based on new data fed into a trained model. Для оценки прогнозов на основе новых данных вы будете использовать модель, созданную в предыдущем разделе.You'll use the model you created in the previous section to score predictions against new data.

Создание таблицы с новыми даннымиCreate a table of new data

Для начала создайте таблицу с новыми данными.First, create a table with new data.

CREATE TABLE dbo.NewMTCars(
    hp INT NOT NULL
    , wt DECIMAL(10,3) NOT NULL
    , am INT NULL
)
GO

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (110, 2.634)

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (72, 3.435)

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (220, 5.220)

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (120, 2.800)
GO

Прогнозирование вероятности оснащения автомобиля механической КППPredict manual transmission

Чтобы получить прогнозы на основе вашей модели, необходимо написать скрипт SQL, который выполняет следующие функции:To get predictions based on your model, write a SQL script that does the following:

  1. Получение требуемой модели.Gets the model you want
  2. Получение новых входных данных.Gets the new input data
  3. Вызов функции прогнозирования R, совместимой с моделью.Calls an R prediction function that is compatible with that model

Со временем ваша таблица может содержать несколько моделей R, созданных с помощью различных параметров и алгоритмов или обученных на основе разных подмножеств данных.Over time, the table might contain multiple R models, all built using different parameters or algorithms, or trained on different subsets of data. В этом примере мы будем использовать модель default model.In this example, we'll use the model named default model.

DECLARE @glmmodel varbinary(max) = 
    (SELECT model FROM dbo.GLM_models WHERE model_name = 'default model');

EXEC sp_execute_external_script
    @language = N'R'
    , @script = N'
            current_model <- unserialize(as.raw(glmmodel));
            new <- data.frame(NewMTCars);
            predicted.am <- predict(current_model, new, type = "response");
            str(predicted.am);
            OutputDataSet <- cbind(new, predicted.am);
            '
    , @input_data_1 = N'SELECT hp, wt FROM dbo.NewMTCars'
    , @input_data_1_name = N'NewMTCars'
    , @params = N'@glmmodel varbinary(max)'
    , @glmmodel = @glmmodel
WITH RESULT SETS ((new_hp INT, new_wt DECIMAL(10,3), predicted_am DECIMAL(10,3)));

Приведенный выше скрипт выполняет следующие действия:The script above performs the following steps:

  • Используйте инструкцию SELECT для получения одной модели из таблицы и передайте ее в качестве входного параметра.Use a SELECT statement to get a single model from the table, and pass it as an input parameter.

  • После извлечения модели из таблицы вызовите функцию unserialize для модели.After retrieving the model from the table, call the unserialize function on the model.

  • Примените функцию predict с соответствующими аргументами к модели и предоставьте новые входные данные.Apply the predict function with appropriate arguments to the model, and provide the new input data.

Примечание

В этом примере на этапе тестирования мы добавили функцию str для проверки схемы данных, возвращаемой из кода R. В дальнейшем вы можете удалить эту инструкцию.In the example, the str function is added during the testing phase, to check the schema of data being returned from R. You can remove the statement later.

Имена столбцов, используемые в скрипте R, не обязательно передаются в выходные данные хранимой процедуры.The column names used in the R script are not necessarily passed to the stored procedure output. В этом случае предложение WITH RESULTS используется для определения новых имен столбцов.Here the WITH RESULTS clause is used to define some new column names.

РезультатыResults

Результирующий набор для прогнозирования вероятности оснащения автомобиля механической КПП

Кроме того, вы можете использовать инструкцию PREDICT (Transact-SQL) для получения прогнозируемого значения или оценки на основе сохраненной модели.It's also possible to use the PREDICT (Transact-SQL) statement to generate a predicted value or score based on a stored model.

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

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