Självstudie: Skapa partitionsbaserade modeller i R på SQL Server

Artikel
01/02/2025

gäller för: SQL Server 2016 (13.x) och senare versioner

I SQL Server 2019 är partitionsbaserad modellering möjligheten att skapa och träna modeller över partitionerade data. För stratifierade data som naturligt segmenteras i ett visst klassificeringsschema – till exempel geografiska regioner, datum och tid, ålder eller kön – kan du köra skript över hela datauppsättningen, med möjlighet att modellera, träna och poängsätta partitioner som förblir intakta över alla dessa åtgärder.

Partitionsbaserad modellering aktiveras via två nya parametrar på sp_execute_external_script:

input_data_1_partition_by_columns, som anger en kolumn som ska partitioneras av.
input_data_1_order_by_columns anger vilka kolumner som ska sorteras efter.

I den här självstudien lär du dig partitionsbaserad modellering med hjälp av klassiska NYC taxi-exempeldata och R-skript. Partitionskolumnen är betalningsmetoden.

Partitioner baseras på betalningstyper (5).
Skapa och träna modeller på varje partition och lagra objekten i databasen.
Förutse sannolikheten för tipsresultat för varje partitionsmodell med hjälp av exempeldata som är reserverade för det ändamålet.

Förutsättningar

För att slutföra den här självstudien måste du ha följande:

Tillräckligt med systemresurser. Datamängden är stor och träningsåtgärderna är resursintensiva. Använd om möjligt ett system med minst 8 GB RAM-minne. Du kan också använda mindre datauppsättningar för att kringgå resursbegränsningar. Instruktioner för att minska datamängden är infogade.
Ett verktyg för T-SQL-frågekörning, till exempel SQL Server Management Studio (SSMS).
NYCTaxi_Sample.bak, som du kan ladda ned och återställa till din lokala SQL Server-instans. Filstorleken är cirka 90 MB.
SQL Server 2019-databasmotorinstans med Machine Learning Services och R-integrering.
Självstudien använder loopbackanslutningen till SQL Server från ett R-skript via ODBC. Därför måste du skapa en inloggning för SQLRUserGroup.
Kontrollera tillgängligheten för R-paket genom att returnera en välformaterad lista över alla R-paket som för närvarande är installerade med din databasmotorinstans:

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

Ansluta till databasen

Starta SSMS och anslut till databasmotorinstansen. I Object Explorerkontrollerar du att NYCTaxi_Sample databas finns.

Skapa CalculateDistance

Demodatabasen levereras med en skalär funktion för att beräkna avstånd, men vår lagrade procedur fungerar bättre med en tabellvärdesfunktion. Kör följande skript för att skapa funktionen CalculateDistance som används i träningssteget senare.

Kontrollera \Programmability\Functions\Table-valued Functions under NYCTaxi_Sample-databasen i Object Explorerför att bekräfta att funktionen har skapats.

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

Definiera en procedur för att skapa och träna modeller per partition

Den här självstudien inkapslar R-skript i en lagrad procedur. I det här steget skapar du en lagrad procedur som använder R för att skapa en indatauppsättning, skapa en klassificeringsmodell för att förutsäga tipsresultat och sedan lagra modellen i databasen.

Bland de parameterindata som används av det här skriptet visas input_data_1_partition_by_columns och input_data_1_order_by_columns. Kom ihåg att dessa parametrar är den mekanism som partitionerad modellering sker med. Parametrarna skickas som indata till sp_execute_external_script för att bearbeta partitioner med det externa skriptet som körs en gång för varje partition.

För den här lagrade proceduren använder parallellitet för snabbare tid till slutförande.

När du har kört det här skriptet i Object Explorerbör du se train_rxLogIt_per_partition i \Programmability\Stored Procedures under NYCTaxi_Sample-databasen. Du bör också se en ny tabell som används för att lagra modeller: 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

Parallell körning

Observera att sp_execute_external_script indata inkluderar @parallel=1, som används för att aktivera parallell bearbetning. Till skillnad från tidigare versioner, med början i SQL Server 2019, ger inställningen @parallel=1 ett starkare tips till frågeoptimeraren, vilket gör parallell körning till ett mycket mer sannolikt resultat.

Som standard tenderar frågeoptimeraren att fungera under @parallel=1 på tabeller som har fler än 256 rader, men om du kan hantera detta explicit genom att ange @parallel=1 som visas i det här skriptet.

Tips

För träningsuppgifter kan du använda @parallel med vilket träningsskript som helst, även de som använder algoritmer som inte är från Microsoft-rx. Vanligtvis erbjuder endast RevoScaleR-algoritmer (med rx-prefixet) parallellitet i träningsscenarier i SQL Server. Men med den nya parametern kan du parallellisera ett skript som anropar funktioner, inklusive R-funktioner med öppen källkod, som inte är specifikt utformade med den funktionen. Detta fungerar eftersom partitioner har tillhörighet till specifika trådar, så alla åtgärder som anropas i ett skript körs per partition på den angivna tråden.

Kör proceduren och träna modellen

I det här avsnittet tränar skriptet den modell som du skapade och sparade i föregående steg. Exemplen nedan visar två metoder för att träna din modell: att använda en hel datauppsättning eller en partiell datamängd.

Förvänta dig att det här steget tar en stund. Träningen är beräkningsintensiv, vilket tar många minuter att slutföra. Om systemresurser, särskilt minne, inte är tillräckliga för belastningen använder du en delmängd av data. Det andra exemplet innehåller syntaxen.

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

Not

Om du kör andra arbetsbelastningar kan du lägga till OPTION(MAXDOP 2) till SELECT-instruktionen om du vill begränsa frågebearbetningen till bara 2 kärnor.

Kontrollera resultat

Resultatet i modelltabellen ska vara fem olika modeller, baserat på fem partitioner segmenterade efter de fem betalningstyperna. Modellerna finns i datakällan ml_models.

SELECT *
FROM ml_models

Definiera en procedur för att förutsäga resultat

Du kan använda samma parametrar för bedömning. Följande exempel innehåller ett R-skript som ska poängsätta med hjälp av rätt modell för den partition som för närvarande bearbetas.

Precis som tidigare skapar du en lagrad procedur för att omsluta R-koden.

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

Skapa en tabell för att lagra förutsägelser

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

Kör proceduren och spara förutsägelser

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

Visa förutsägelser

Eftersom förutsägelserna lagras kan du köra en enkel fråga för att returnera en resultatuppsättning.

SELECT *
FROM prediction_results;

Nästa steg

I den här handledningen använde du sp_execute_external_script för att iterera åtgärder över partitionerade data. Om du vill titta närmare på hur du anropar externa skript i lagrade procedurer och använder RevoScaleR-funktioner fortsätter du med följande självstudie.

genomgång för R och SQL Server

Dela via