Zelfstudie: Op partities gebaseerde modellen maken in R op SQL Server

Artikel
01/02/2025

Van toepassing op: SQL Server 2016 (13.x) en latere versies

In SQL Server 2019 is modellering op basis van partities de mogelijkheid om modellen te maken en te trainen over gepartitioneerde gegevens. Voor gelaagde gegevens die natuurlijk segmenteren in een bepaald classificatieschema, zoals geografische regio's, datum en tijd, leeftijd of geslacht, kunt u een script uitvoeren voor de hele gegevensset, met de mogelijkheid om partities te modelleren, trainen en scoren die intact blijven gedurende al deze bewerkingen.

Modellering op basis van partities wordt ingeschakeld via twee nieuwe parameters op sp_execute_external_script:

input_data_1_partition_by_columns, waarmee een kolom wordt opgegeven waarop moet worden gepartitioneerd.
input_data_1_order_by_columns geeft aan op welke kolommen moeten worden gesorteerd.

In deze zelfstudie leert u modellering op basis van partities met behulp van de klassieke voorbeeldgegevens van nyc-taxi's en R-script. De partitiekolom is de betalingswijze.

Partities zijn gebaseerd op betalingstypen (5).
Maak en train modellen op elke partitie en sla de objecten op in de database.
Voorspel de kans op tipresultaten voor elk partitiemodel met behulp van voorbeeldgegevens die voor dat doel zijn gereserveerd.

Voorwaarden

Als u deze zelfstudie wilt voltooien, moet u het volgende hebben:

Voldoende systeembronnen. De gegevensset is groot en trainingsbewerkingen zijn resource-intensief. Gebruik indien mogelijk een systeem met ten minste 8 GB RAM. U kunt ook kleinere gegevenssets gebruiken om resourcebeperkingen te omzeilen. Instructies voor het reduceren van de gegevensset zijn opgenomen.
Een hulpprogramma voor het uitvoeren van T-SQL-query's, zoals SSMS -(SQL Server Management Studio).
NYCTaxi_Sample.bakkunt u downloaden en herstellen naar uw lokale SQL Server-exemplaar. De bestandsgrootte is ongeveer 90 MB.
Exemplaar van SQL Server 2019-database-engine, met Machine Learning Services en R-integratie.
De zelfstudie maakt gebruik van een loopback-verbinding met SQL Server vanuit een R-script via ODBC. Daarom moet u een aanmelding maken voor SQLRUserGroup.
Controleer de beschikbaarheid van R-pakketten door een goed opgemaakte lijst te retourneren van alle R-pakketten die momenteel zijn geïnstalleerd met uw database-engine-exemplaar:

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) ))

Verbinding maken met de database

Start SSMS en maak verbinding met de instantie van de database-engine. Controleer in de Objectverkennerof de NYCTaxi_Sample-database bestaat.

CalculateDistance maken

De demodatabase wordt geleverd met een scalaire functie voor het berekenen van afstand, maar onze opgeslagen procedure werkt beter met een tabelwaardefunctie. Voer het volgende script uit om de CalculateDistance functie te maken die wordt gebruikt in de trainingsstap later.

Als u wilt controleren of de functie is gemaakt, controleert u in Objectverkennerde \Programmability\Functions\Table-valued Functions onder de NYCTaxi_Sample-database.

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

Een procedure definiëren voor het maken en trainen van modellen per partitie

In deze tutorial wordt een R-script ingebed in een opgeslagen procedure. In deze stap maakt u een opgeslagen procedure die gebruikmaakt van R om een invoergegevensset te maken, een classificatiemodel te maken voor het voorspellen van tipresultaten en slaat u het model vervolgens op in de database.

Onder de parameterinvoer die door dit script wordt gebruikt, ziet u input_data_1_partition_by_columns en input_data_1_order_by_columns. Zoals u weet, zijn deze parameters het mechanisme waarmee gepartitioneerde modellering plaatsvindt. De parameters worden doorgegeven als invoer aan sp_execute_external_script om partities te verwerken met het externe script dat eenmaal voor elke partitie wordt uitgevoerd.

Voor deze opgeslagen procedure parallelisme gebruiken voor een snellere voltooiing.

Nadat u dit script hebt uitgevoerd, ziet u in Objectverkennertrain_rxLogIt_per_partition in \Programmability\Stored Procedures onder de NYCTaxi_Sample-database. U ziet ook een nieuwe tabel die wordt gebruikt voor het opslaan van modellen: 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

Parallelle uitvoering

U ziet dat de sp_execute_external_script invoer @parallel=1bevat, die wordt gebruikt om parallelle verwerking mogelijk te maken. In tegenstelling tot eerdere releases, vanaf SQL Server 2019, levert het instellen van @parallel=1 een sterkere hint aan de queryoptimalisatie, waardoor parallelle uitvoering een veel waarschijnlijker resultaat oplevert.

De queryoptimalisatie werkt standaard in modus @parallel=1 op tabellen met meer dan 256 rijen, maar u kunt dit expliciet instellen door @parallel=1 in te stellen, zoals in dit script wordt weergegeven.

Tip

Voor trainingsworkloads kunt u @parallel gebruiken met elk willekeurig trainingsscript, zelfs degenen die gebruikmaken van niet-Microsoft-rx-algoritmen. Normaal gesproken bieden alleen RevoScaleR-algoritmen (met het rx-voorvoegsel) parallelle uitvoering in trainingsscenario's in SQL Server. Maar met de nieuwe parameter kunt u een script parallelliseren dat functies aanroept, inclusief opensource R-functies, die niet specifiek zijn ontworpen met die mogelijkheid. Dit werkt omdat partities affiniteit hebben met specifieke threads, dus alle bewerkingen die in een script worden aangeroepen, worden uitgevoerd per partitie, op de opgegeven thread.

Voer de procedure uit en train het model

In deze sectie traint het script het model dat u in de vorige stap hebt gemaakt en opgeslagen. In de onderstaande voorbeelden ziet u twee benaderingen voor het trainen van uw model: het gebruik van een volledige gegevensset of gedeeltelijke gegevens.

Verwacht dat deze stap enige tijd duurt. Training is rekenintensief, het duurt vele minuten om te voltooien. Als systeembronnen, met name geheugen, onvoldoende zijn voor de belasting, gebruikt u een subset van de gegevens. Het tweede voorbeeld bevat de syntaxis.

--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

Notitie

Als u andere workloads uitvoert, kunt u OPTION(MAXDOP 2) toevoegen aan de SELECT-instructie als u de verwerking van query's wilt beperken tot slechts 2 kernen.

Resultaten controleren

Het resultaat in de tabel modellen moet vijf verschillende modellen zijn, op basis van vijf partities die zijn gesegmenteerd door de vijf betalingstypen. Modellen bevinden zich in de ml_models gegevensbron.

SELECT *
FROM ml_models

Een procedure definiëren voor het voorspellen van resultaten

U kunt dezelfde parameters gebruiken voor scoren. Het volgende voorbeeld bevat een R-script dat het berekenen van scores uitvoert middels het juiste model voor de partitie die momenteel wordt verwerkt.

Net als voorheen maakt u een opgeslagen procedure om uw R-code te verpakken.

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

Een tabel maken om voorspellingen op te slaan

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

Voer de procedure uit en sla voorspellingen op

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

Voorspellingen weergeven

Omdat de voorspellingen zijn opgeslagen, kunt u een eenvoudige query uitvoeren om een resultatenset te retourneren.

SELECT *
FROM prediction_results;

Volgende stappen

In deze zelfstudie hebt u sp_execute_external_script gebruikt om bewerkingen over gepartitioneerde gegevens te herhalen. Ga verder met de volgende zelfstudie voor meer informatie over het aanroepen van externe scripts in opgeslagen procedures en het gebruik van RevoScaleR-functies.

handleiding voor R en SQL Server

Delen via