Учебник. Создание моделей на основе секций в R на SQL ServerTutorial: Create partition-based models in R on SQL Server

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server нетБаза данных SQL AzureнетХранилище данных SQL AzureнетParallel Data WarehouseAPPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

В SQL Server 2019 моделирование на основе секций — это возможность создания и обучения моделей по секционированным данным.In SQL Server 2019, partition-based modeling is the ability to create and train models over partitioned data. Для данных стратифицированной, которые естественным образом направлены в определенную схему классификации, такие как географические регионы, Дата и время, возраст или пол, можно выполнить сценарий для всего набора данных, с возможностью моделировать, обучать и оценивать секции, которые остаются без изменений. над всеми этими операциями.For stratified data that naturally segments into a given classification scheme - such as geographic regions, date and time, age or gender - you can execute script over the entire data set, with the ability to model, train, and score over partitions that remain intact over all these operations.

Моделирование на основе секций включено с помощью двух новых параметров в sp_execute_external_script:Partition-based modeling is enabled through two new parameters on sp_execute_external_script:

  • input_data_1_partition_by_columns, который указывает столбец для секционирования.input_data_1_partition_by_columns, which specifies a column to partition by.
  • input_data_1_order_by_columns указывает, какие столбцы нужно упорядочить.input_data_1_order_by_columns specifies which columns to order by.

В этом руководстве рассматривается моделирование на основе секций с помощью классической модели НЬЮного такси и скрипта R.In this tutorial, learn partition-based modeling using the classic NYC taxi sample data and R script. Столбец секционирования — это метод оплаты.The partition column is the payment method.

  • Секции основываются на типах платежей (5).Partitions are based on payment types (5).
  • Создание и обучение моделей в каждой секции и сохранение объектов в базе данных.Create and train models on each partition and store the objects in the database.
  • Прогноз вероятности результатов TIP для каждой модели секционирования с использованием образцов данных, зарезервированных для этой цели.Predict the probability of tip outcomes over each partition model, using sample data reserved for that purpose.

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

Для работы с этим руководством необходимо следующее:To complete this tutorial, you must have the following:

  • Достаточно системных ресурсов.Sufficient system resources. Набор данных является большим, и операции обучения требуют больших ресурсов.The data set is large and training operations are resource-intensive. Если это возможно, используйте систему, имеющую не менее 8 ГБ ОЗУ.If possible, use a system having at least 8 GB RAM. Кроме того, можно использовать небольшие наборы данных, чтобы обойти ограничения ресурсов.Alternatively, you can use smaller data sets to work around resource constraints. Инструкции по сокращению набора данных являются встроенными.Instructions for reducing the data set are inline.

  • Средство для выполнения запросов T-SQL, например SQL Server Management Studio.A tool for T-SQL query execution, such as SQL Server Management Studio.

  • NYCTaxi_Sample. bak, который можно скачать и восстановить в локальном экземпляре ядра СУБД.NYCTaxi_Sample.bak, which you can download and restore to your local database engine instance. Размер файла составляет примерно 90 МБ.File size is approximately 90 MB.

  • SQL Server 2019 предварительный просмотр экземпляра ядра СУБД с интеграцией Службы машинного обучения и R.SQL Server 2019 preview database engine instance, with Machine Learning Services and R integration.

Проверьте версию, выполнив SELECT @@Version как запрос T-SQL в средстве запроса.Check version by executing SELECT @@Version as a T-SQL query in a query tool. Выходные данные должны иметь значение "Microsoft SQL Server 2019 (CTP 2,4)-15,0. x".Output should be "Microsoft SQL Server 2019 (CTP 2.4) - 15.0.x".

Проверьте доступность пакетов R, выполнив правильный отформатированный список всех пакетов R, установленных в данный момент с экземпляром ядра СУБД.Check availability of R packages by returning a well-formatted list of all R packages currently installed with your database engine instance:

EXECUTE sp_execute_external_script
  @language=N'R',
  @script = N'str(OutputDataSet);
  packagematrix <- installed.packages();
  Name <- packagematrix[,1];
  Version <- packagematrix[,3];
  OutputDataSet <- data.frame(Name, Version);',
  @input_data_1 = N''
WITH RESULT SETS ((PackageName nvarchar(250), PackageVersion nvarchar(max) ))

Подключение к базе данныхConnect to the database

Запустите Management Studio и подключитесь к экземпляру ядра СУБД.Start Management Studio and connect to the database engine instance. В обозревателе объектов убедитесь, что база данных NYCTaxi_Sample существует.In Object Explorer, verify the NYCTaxi_Sample database exists.

Создание КалкулатедистанцеCreate CalculateDistance

Демонстрационная база данных поставляется с скалярной функцией для вычисления расстояния, но наша хранимая процедура лучше работает с возвращающей табличное значение функцией.The demo database comes with a scalar function for calculating distance, but our stored procedure works better with a table-valued function. Выполните следующий скрипт, чтобы создать функцию калкулатедистанце , которая будет использоваться на шаге обучения позже.Run the following script to create the CalculateDistance function used in the training step later on.

Чтобы убедиться, что функция создана, проверьте функции \Программабилити\функтионс\табле-валуед в базе данных NYCTaxi_Sample в обозревателе объектов.To confirm the function was created, check the \Programmability\Functions\Table-valued Functions under the NYCTaxi_Sample database in Object Explorer.

USE NYCTaxi_sample
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[CalculateDistance] (
    @Lat1 FLOAT
    ,@Long1 FLOAT
    ,@Lat2 FLOAT
    ,@Long2 FLOAT
    )
    -- User-defined function calculates the direct distance between two geographical coordinates.
RETURNS TABLE
AS
RETURN

SELECT COALESCE(3958.75 * ATAN(SQRT(1 - POWER(t.distance, 2)) / nullif(t.distance, 0)), 0) AS direct_distance
FROM (
    VALUES (CAST((SIN(@Lat1 / 57.2958) * SIN(@Lat2 / 57.2958)) + (COS(@Lat1 / 57.2958) * COS(@Lat2 / 57.2958) * COS((@Long2 / 57.2958) - (@Long1 / 57.2958))) AS DECIMAL(28, 10)))
    ) AS t(distance)
GO

Определение процедуры создания и обучения моделей отдельных секцийDefine a procedure for creating and training per-partition models

В этом руководстве сценарий R создается в виде оболочки в хранимой процедуре.This tutorial wraps R script in a stored procedure. На этом шаге создается хранимая процедура, которая использует R для создания входного набора данных, построения модели классификации для прогнозирования результатов TIP, а затем сохраняет модель в базе данных.In this step, you create a stored procedure that uses R to create an input dataset, build a classification model for predicting tip outcomes, and then stores the model in the database.

Между входными параметрами, используемыми этим скриптом, вы увидите input_data_1_partition_by_columns и input_data_1_order_by_columns.Among the parameter inputs used by this script, you'll see input_data_1_partition_by_columns and input_data_1_order_by_columns. Помните, что эти параметры являются механизмом, с помощью которого создается секционированное моделирование.Recall that these parameters are the mechanism by which partitioned modeling occurs. Параметры передаются в качестве входных данных sp_execute_external_script для обработки секций с помощью внешнего скрипта, который выполняется один раз для каждой секции.The parameters are passed as inputs to sp_execute_external_script to process partitions with the external script executing once for every partition.

Для этой хранимой процедуры Используйте Parallel для ускорения выполнения.For this stored procedure, use parallelism for faster time to completion.

После выполнения этого скрипта вы увидите train_rxLogIt_per_partition в процедурах \программабилити\сторед в базе данных NYCTaxi_Sample в обозревателе объектов.After you run this script, you should see train_rxLogIt_per_partition in \Programmability\Stored Procedures under the NYCTaxi_Sample database in Object Explorer. Также должна отобразиться новая таблица, используемая для хранения моделей: dbo. nyctaxi_models.You should also see a new table used for storing models: dbo.nyctaxi_models.

USE NYCTaxi_Sample
GO

CREATE
    OR

ALTER PROCEDURE [dbo].[train_rxLogIt_per_partition] (@input_query NVARCHAR(max))
AS
BEGIN
    DECLARE @start DATETIME2 = SYSDATETIME()
        ,@model_generation_duration FLOAT
        ,@model VARBINARY(max)
        ,@instance_name NVARCHAR(100) = @@SERVERNAME
        ,@database_name NVARCHAR(128) = db_name();

    EXEC sp_execute_external_script @language = N'R'
        ,@script = 
        N'
    
    # Make sure InputDataSet is not empty. In parallel mode, if one thread gets zero data, an error occurs
    if (nrow(InputDataSet) > 0) {
    # Define the connection string
    connStr <- paste("Driver=SQL Server;Server=", instance_name, ";Database=", database_name, ";Trusted_Connection=true;", sep="");
    
    # build classification model to predict a tip outcome
    duration <- system.time(logitObj <- rxLogit(tipped ~ passenger_count + trip_distance + trip_time_in_secs + direct_distance, data = InputDataSet))[3];

    # First, serialize a model to and put it into a database table
    modelbin <- as.raw(serialize(logitObj, NULL));

    # Create the data source. To reduce data size, add rowsPerRead=500000 to cut the dataset by half.
    ds <- RxOdbcData(table="ml_models", connectionString=connStr);

    # Store the model in the database
    model_name <- paste0("nyctaxi.", InputDataSet[1,]$payment_type);
    
    rxWriteObject(ds, model_name, modelbin, version = "v1",
    keyName = "model_name", valueName = "model_object", versionName = "model_version", overwrite = TRUE, serialize = FALSE);
    } 
    
    '
        ,@input_data_1 = @input_query
        ,@input_data_1_partition_by_columns = N'payment_type'
        ,@input_data_1_order_by_columns = N'passenger_count'
        ,@parallel = 1
        ,@params = N'@instance_name nvarchar(100), @database_name nvarchar(128)'
        ,@instance_name = @instance_name
        ,@database_name = @database_name
    WITH RESULT SETS NONE
END;
GO

Параллельное выполнениеParallel execution

Обратите внимание, что входные данные sp_execute_external_script включают @parallel= 1, используемую для параллельной обработки.Notice that the sp_execute_external_script inputs include **@parallel=1**, used to enable parallel processing. В отличие от предыдущих выпусков, в SQL Server 2019 параметр @parallel= 1 обеспечивает более высокую подсказку оптимизатору запросов, что делает параллельное выполнение гораздо более вероятным результатом.In contrast with previous releases, in SQL Server 2019, setting **@parallel=1** delivers a stronger hint to the query optimizer, making parallel execution a much more likely outcome.

По умолчанию оптимизатор запросов работает @parallelс = 1 в таблицах, имеющих более 256 строк, но если эту операцию можно обработать явно, задав @parallel= 1 , как показано в этом скрипте.By default, the query optimizer tends to operate under **@parallel=1** on tables having more than 256 rows, but if you can handle this explicitly by setting **@parallel=1** as shown in this script.

Совет

Для обучения воркоадс можно использовать **@parallel** с любым произвольным сценарием обучения, даже при использовании алгоритмов, отличных от Microsoft RX.For training workoads, you can use **@parallel** with any arbitrary training script, even those using non-Microsoft-rx algorithms. Как правило, только алгоритмы RevoScaleR (с префиксом RX) предлагают параллелизм в сценариях обучения в SQL Server.Typically, only RevoScaleR algorithms (with the rx prefix) offer parallelism in training scenarios in SQL Server. Но с помощью нового параметра можно параллелизации скрипта, который вызывает функции, включая функции R с открытым исходным кодом, не разработанные специально для этой возможности.But with the new parameter, you can parallelize a script that calls functions, including open-source R functions, not specifically engineered with that capability. Это работает потому, что секции имеют сходство с определенными потоками, поэтому все операции, вызываемые в скрипте, выполняются по отдельности для каждого раздела в данном потоке.This works because partitions have affinity to specific threads, so all operations called in a script execute on a per-partition basis, on the given thread.

Выполнение процедуры и обучение моделиRun the procedure and train the model

В этом разделе Скрипт обучает модель, созданную и сохраненную на предыдущем шаге.In this section, the script trains the model that you created and saved in the previous step. В приведенных ниже примерах демонстрируются два подхода к обучению модели: использование всего набора данных или частичных данных.The examples below demonstrate two approaches for training your model: using an entire data set, or a partial data.

Этот шаг должен занять некоторое время.Expect this step to take awhile. Обучение выполняется очень интенсивно и занимает много минут.Training is computationally intensive, taking many minutes to complete. Если системные ресурсы, в особенности память, недостаточно для загрузки, используйте подмножество данных.If system resources, especially memory, are insufficient for the load, use a subset of the data. Во втором примере показан синтаксис.The second example provides the syntax.

--Example 1: train on entire dataset
EXEC train_rxLogIt_per_partition N'
SELECT payment_type, tipped, passenger_count, trip_time_in_secs, trip_distance, d.direct_distance
  FROM dbo.nyctaxi_sample CROSS APPLY [CalculateDistance](pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as d
';
GO
--Example 2: Train on 20 percent of the dataset to expedite processing.
EXEC train_rxLogIt_per_partition N'
  SELECT tipped, payment_type, passenger_count, trip_time_in_secs, trip_distance, d.direct_distance
  FROM dbo.nyctaxi_sample TABLESAMPLE (20 PERCENT) REPEATABLE (98074)
  CROSS APPLY [CalculateDistance](pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as d
';
GO

Примечание

Если вы используете другие рабочие нагрузки, можно добавить OPTION(MAXDOP 2) к инструкции SELECT, если требуется ограничить обработку запросов только двумя ядрами.If you are running other workloads, you can append OPTION(MAXDOP 2) to the SELECT statement if you want to limit query processing to just 2 cores.

Результаты проверкиCheck results

Результат в таблице Models должен состоять из пяти разных моделей, основанных на пяти секциях, сегментированных пятью типами платежей.The result in the models table should be five different models, based on five partitions segmented by the five payment types. Модели находятся в источнике данных ml_models .Models are in the ml_models data source.

SELECT *
FROM ml_models

Определение процедуры для прогнозирования результатовDefine a procedure for predicting outcomes

Для оценки можно использовать одни и те же параметры.You can use the same parameters for scoring. Следующий пример содержит скрипт R, который будет оценивать использование правильной модели для секции, которая в данный момент обрабатывается.The following sample contains an R script that will score using the correct model for the partition it is currently processing.

Как и ранее, создайте хранимую процедуру для упаковки кода R.As before, create a stored procedure to wrap your R code.

USE NYCTaxi_Sample
GO

-- Stored procedure that scores per partition. 
-- Depending on the partition being processed, a model specific to that partition will be used
CREATE
    OR

ALTER PROCEDURE [dbo].[predict_per_partition]
AS
BEGIN
    DECLARE @predict_duration FLOAT
        ,@instance_name NVARCHAR(100) = @@SERVERNAME
        ,@database_name NVARCHAR(128) = db_name()
        ,@input_query NVARCHAR(max);

    SET @input_query = 'SELECT tipped, passenger_count, trip_time_in_secs, trip_distance, d.direct_distance, payment_type
                          FROM dbo.nyctaxi_sample TABLESAMPLE (1 PERCENT) REPEATABLE (98074)
                          CROSS APPLY [CalculateDistance](pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as d'

    EXEC sp_execute_external_script @language = N'R'
        ,@script = 
        N'
    
    if (nrow(InputDataSet) > 0) {

    #Get the partition that is currently being processed
    current_partition <- InputDataSet[1,]$payment_type;

    #Create the SQL query to select the right model
    query_getModel <- paste0("select model_object from ml_models where model_name = ", "''", "nyctaxi.",InputDataSet[1,]$payment_type,"''", ";")
    

    # Define the connection string
    connStr <- paste("Driver=SQL Server;Server=", instance_name, ";Database=", database_name, ";Trusted_Connection=true;", sep="");
        
    #Define data source to use for getting the model
    ds <- RxOdbcData(sqlQuery = query_getModel, connectionString = connStr)

    # Load the model
    modelbin <- rxReadObject(ds, deserialize = FALSE)
    # unserialize model
    logitObj <- unserialize(modelbin);

    # predict tipped or not based on model
    predictions <- rxPredict(logitObj, data = InputDataSet, overwrite = TRUE, type = "response", writeModelVars = TRUE
        , extraVarsToWrite = c("payment_type"));        
    OutputDataSet <- predictions
    
    } else {
        OutputDataSet <- data.frame(integer(), InputDataSet[,]);        
    }
    '
        ,@input_data_1 = @input_query
        ,@parallel = 1
        ,@input_data_1_partition_by_columns = N'payment_type'
        ,@params = N'@instance_name nvarchar(100), @database_name nvarchar(128)'
        ,@instance_name = @instance_name
        ,@database_name = @database_name
    WITH RESULT SETS((
                tipped_Pred INT
                ,payment_type VARCHAR(5)
                ,tipped INT
                ,passenger_count INT
                ,trip_distance FLOAT
                ,trip_time_in_secs INT
                ,direct_distance FLOAT
                ));
END;
GO

Создание таблицы для хранения прогнозовCreate a table to store predictions

CREATE TABLE prediction_results (
    tipped_Pred INT
    ,payment_type VARCHAR(5)
    ,tipped INT
    ,passenger_count INT
    ,trip_distance FLOAT
    ,trip_time_in_secs INT
    ,direct_distance FLOAT
    );

TRUNCATE TABLE prediction_results
GO

Выполнение процедуры и сохранение прогнозовRun the procedure and save predictions

INSERT INTO prediction_results (
    tipped_Pred
    ,payment_type
    ,tipped
    ,passenger_count
    ,trip_distance
    ,trip_time_in_secs
    ,direct_distance
    )
EXECUTE [predict_per_partition]
GO

Просмотр прогнозовView predictions

Поскольку прогнозы хранятся, можно выполнить простой запрос для возврата результирующего набора.Because the predictions are stored, you can run a simple query to return a result set.

SELECT *
FROM prediction_results;

Следующие шагиNext steps

В этом руководстве вы использовали sp_execute_external_script для итерации операций по секционированным данным.In this tutorial, you used sp_execute_external_script to iterate operations over partitioned data. Подробнее о вызове внешних скриптов в хранимых процедурах и использовании функций RevoScaleR см. в следующем руководстве.For a closer look at calling external scripts in stored procedures and using RevoScaleR functions, continue with the following tutorial.