Tutorial: Erstellen von partitionsbasierten Modellen in SQL Server mit RTutorial: Create partition-based models in R on SQL Server

Anwendungsbereich:Applies to: JaSQL ServerSQL Server (alle unterstützten Versionen) yesSQL ServerSQL Server (all supported versions) Anwendungsbereich:Applies to: JaSQL ServerSQL Server (alle unterstützten Versionen) yesSQL ServerSQL Server (all supported versions)

Die partitionsbasierte Modellierung in SQL Server 2019 beschreibt die Möglichkeit, Modelle über partitionierte Daten zu erstellen und zu trainieren.In SQL Server 2019, partition-based modeling is the ability to create and train models over partitioned data. Bei geschichteten Daten, die auf natürliche Weise in ein jeweiliges Klassifizierungsschema segmentiert werden, z. B. geografische Regionen, Datum und Uhrzeit, Alter oder Geschlecht, können Sie ein Skript für das gesamte Dataset ausführen, das Partitionen, die nach all diesen Vorgängen intakt bleiben, modellieren, trainieren und bewerten kann.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.

Die partitionsbasierte Modellierung wird mithilfe zwei neuer Parameter in sp_execute_external_script aktiviert:Partition-based modeling is enabled through two new parameters on sp_execute_external_script:

  • Der Parameter input_data_1_partition_by_columns, der eine Spalte angibt, nach der partitioniert werden soll.input_data_1_partition_by_columns, which specifies a column to partition by.
  • Der Parameter input_data_1_order_by_columns, der angibt, nach welchen Spalten sortiert werden soll.input_data_1_order_by_columns specifies which columns to order by.

In diesem Tutorial erlernen Sie die partitionsbasierte Modellierung anhand der bekannten NYC-Taxibeispieldaten und mithilfe eines R-Skripts.In this tutorial, learn partition-based modeling using the classic NYC taxi sample data and R script. Die Partitionsspalte für dieses Beispiel ist die Zahlungsmethode.The partition column is the payment method.

  • Die Partitionen basieren auf Zahlungsarten (5).Partitions are based on payment types (5).
  • Erstellen und trainieren Sie Modelle für jede Partition, und speichern Sie die Objekte in der Datenbank.Create and train models on each partition and store the objects in the database.
  • Sagen Sie mithilfe von Beispieldaten, die für diesen Zweck vorgesehen sind, die Wahrscheinlichkeit von Trinkgeldern für jedes Partitionsmodell hervor.Predict the probability of tip outcomes over each partition model, using sample data reserved for that purpose.

VoraussetzungenPrerequisites

Für dieses Tutorial benötigen Sie Folgendes:To complete this tutorial, you must have the following:

Überprüfen Sie Ihre Version, indem Sie SELECT @@Version in einem Abfragetool als T-SQL-Abfrage ausführen.Check version by executing SELECT @@Version as a T-SQL query in a query tool.

Überprüfen Sie die Verfügbarkeit von R-Paketen, indem Sie wie folgt eine gut formatierte Liste aller derzeit in Ihrer Datenbank-Engine-Instanz installierten R-Pakete zurückgeben: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) ))

Herstellen der Verbindung mit der DatenbankConnect to the database

Starten Sie Management Studio, und stellen Sie eine Verbindung mit der Datenbank-Engine-Instanz her.Start Management Studio and connect to the database engine instance. Vergewissern Sie sich im Objekt-Explorer, dass die NYCTaxi_Sample-Datenbank vorhanden ist.In Object Explorer, verify the NYCTaxi_Sample database exists.

Erstellen der CalculateDistance-FunktionCreate CalculateDistance

Die Beispieldatenbank enthält eine Skalarfunktion zum Berechnen von Entfernungen, jedoch funktioniert die gespeicherte Prozedur mit einer Tabellenwertfunktion besser.The demo database comes with a scalar function for calculating distance, but our stored procedure works better with a table-valued function. Führen Sie das folgende Skript aus, um die CalculateDistance-Funktion zu erstellen, die später im Trainingsschritt verwendet wird.Run the following script to create the CalculateDistance function used in the training step later on.

Überprüfen Sie unter „\Programmability\Functions\Table-valued Functions“ in der Datenbank NYCTaxi_Sample im Objekt-Explorer, ob die Funktion erstellt wurde.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

Definieren einer Prozedur zum Erstellen und Trainieren von Modellen pro PartitionDefine a procedure for creating and training per-partition models

In diesem Tutorial umschließen Sie ein R-Skript in einer gespeicherten Prozedur.This tutorial wraps R script in a stored procedure. In diesem Schritt erstellen Sie eine gespeicherte Prozedur, die R verwendet, um ein Eingabedataset zu erstellen, ein Klassifizierungsmodell zum Vorhersagen von Trinkgeldern zu erstellen und das Modell anschließend in der Datenbank zu speichern.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.

Unter den von diesem Skript verwendeten Parametereingaben finden Sie die Parameter input_data_1_partition_by_columns und 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. Denken Sie daran, dass diese Parameter den Mechanismus darstellen, nach dem die partitionierte Modellierung erfolgt.Recall that these parameters are the mechanism by which partitioned modeling occurs. Die Parameter werden als Eingaben an sp_execute_external_script übergeben, um Partitionen mit dem externen Skript zu verarbeiten, das für jede Partition einmal ausgeführt wird.The parameters are passed as inputs to sp_execute_external_script to process partitions with the external script executing once for every partition.

Verwenden Sie die Parallelität für diese gespeicherte Prozedur, um eine schnellere Ausführung zu erzielen.For this stored procedure, use parallelism for faster time to completion.

Nachdem Sie dieses Skript ausgeführt haben, sollten Sie train_rxLogIt_per_partition unter „\Programmability\Stored Procedures“ in der Datenbank NYCTaxi_Sample im Objekt-Explorer finden können.After you run this script, you should see train_rxLogIt_per_partition in \Programmability\Stored Procedures under the NYCTaxi_Sample database in Object Explorer. Außerdem sollte eine neue Tabelle vorliegen, die zum Speichern von Modellen verwendet wird: 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

Parallele AusführungParallel execution

Beachten Sie, dass die sp_execute_external_script-Eingaben @parallel=1 enthalten, um die Parallelverarbeitung zu aktivieren.Notice that the sp_execute_external_script inputs include @parallel=1, used to enable parallel processing. Im Gegensatz zu früheren Releases erhält der Abfrageoptimierer durch Festlegen von @parallel=1 in SQL Server 2019 einen effektiveren Hinweis, wodurch die parallele Ausführung wahrscheinlicher wird.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.

Der Abfrageoptimierer wird bei Tabellen mit mehr als 256 Zeilen standardmäßig mit @parallel=1 betrieben. Sie können dies jedoch explizit behandeln, indem Sie @parallel=1 wie in diesem Skript festlegen.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.

Tipp

Für Trainingsworkloads können Sie @parallel mit einem beliebigen arbiträren Trainingsskript verwenden, sogar solche, die nicht von Microsoft stammende RX-Algorithmen verwendenFor training workoads, you can use @parallel with any arbitrary training script, even those using non-Microsoft-rx algorithms. In der Regel bieten nur RevoScaleR-Algorithmen (mit dem RX-Präfix) Parallelität in Trainingsszenarios in SQL Server.Typically, only RevoScaleR algorithms (with the rx prefix) offer parallelism in training scenarios in SQL Server. Mit dem neuen Parameter können Sie jedoch ein Skript parallelisieren, das Funktionen aufruft, einschließlich quelloffene R-Funktionen, die nicht speziell mit dieser Funktion entwickelt wurden.But with the new parameter, you can parallelize a script that calls functions, including open-source R functions, not specifically engineered with that capability. Das funktioniert, weil Partitionen eine Affinität für bestimmte Threads aufweisen. Alle in einem Skript aufgerufenen Vorgänge werden also je nach Partition auf dem jeweiligen Thread (thread.) ausgeführt.This works because partitions have affinity to specific threads, so all operations called in a script execute on a per-partition basis, on the givethread.

Ausführen der Prozedur und Trainieren des ModellsRun the procedure and train the model

In diesem Abschnitt trainiert das Skript das Modell, dass Sie im vorherigen Schritt erstellt und gespeichert haben.In this section, the script trains the model that you created and saved in the previous step. In den folgenden Beispielen werden zwei Vorgehensweisen zum Trainieren Ihres Modells veranschaulicht: mithilfe des gesamten oder eines Teils des Datasets.The examples below demonstrate two approaches for training your model: using an entire data set, or a partial data.

Dieser Schritt wird eine Weile dauern.Expect this step to take awhile. Das Training ist rechenintensiv und benötigt einige Minuten.Training is computationally intensive, taking many minutes to complete. Wenn die Systemressourcen, insbesondere der Arbeitsspeicher, nicht zum Laden ausreichen, sollten Sie eine Teilmenge der Daten verwenden.If system resources, especially memory, are insufficient for the load, use a subset of the data. Im zweiten Beispiel wird die Syntax bereitstellt.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

Hinweis

Wenn Sie andere Workloads ausführen, können Sie OPTION(MAXDOP 2) an die SELECT-Anweisung anfügen, wenn Sie die Abfrageverarbeitung auf nur 2 Kerne begrenzen möchten.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.

Überprüfen der ErgebnisseCheck results

Das Ergebnis in der Modelltabelle sollte aus fünf verschiedenen Modellen bestehen, die auf den fünf Partitionen basieren, die nach den fünf Zahlungsmethoden segmentiert wurden.The result in the models table should be five different models, based on five partitions segmented by the five payment types. Die Modelle befinden sich in der Datenquelle ml_models.Models are in the ml_models data source.

SELECT *
FROM ml_models

Definieren einer Prozedur zum Vorhersagen von ErgebnissenDefine a procedure for predicting outcomes

Sie können dieselben Parameter für die Bewertung verwenden.You can use the same parameters for scoring. Das folgende Beispiel enthält ein R-Skript, das die Bewertung mit dem richtigen Modell für die Partition durchführt, das aktuell verarbeitet wird.The following sample contains an R script that will score using the correct model for the partition it is currently processing.

Erstellen Sie wie zuvor eine gespeicherte Prozedur, um Ihren R-Code zu umschließen.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

Erstellen einer Tabelle zum Speichern von VorhersagenCreate 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

Ausführen der Prozedur und Speichern von VorhersagenRun 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

Anzeigen von VorhersagenView predictions

Da die Vorhersagen gespeichert werden, können Sie eine einfache Abfrage ausführen, um ein Resultset zurückzugeben.Because the predictions are stored, you can run a simple query to return a result set.

SELECT *
FROM prediction_results;

Nächste SchritteNext steps

In diesem Tutorial haben Sie sp_execute_external_script verwendet, um Vorgänge für partitionierte Daten zu durchlaufen.In this tutorial, you used sp_execute_external_script to iterate operations over partitioned data. Im folgenden Tutorial erhalten Sie ausführlichere Informationen zum Aufrufen externer Skripts in gespeicherten Prozeduren und Verwenden von RevoScaleR-Funktionen.For a closer look at calling external scripts in stored procedures and using RevoScaleR functions, continue with the following tutorial.