Megosztás a következőn keresztül:

Oktatóanyag: Partícióalapú modellek létrehozása az R-ben az SQL Serveren

A következőkre vonatkozik: SQL Server 2016 (13.x) és újabb verziók

Az SQL Server 2019-ben a partícióalapú modellezés lehetővé teszi modellek létrehozását és betanítását particionált adatokon keresztül. Az olyan rétegzett adatok esetében, amelyek természetesen egy adott besorolási sémába vannak szegmentálva – például földrajzi régiók, dátum és idő, életkor vagy nem –, szkriptet futtathat a teljes adatkészleten, és modellezheti, betaníthatja és pontszámot állíthat be az összes művelet során érintetlen partíciókon.

A partícióalapú modellezés két új paraméterrel engedélyezve van a sp_execute_external_script:

  • input_data_1_partition_by_columns, amely megadja a particionálásra szolgáló oszlopot.
  • input_data_1_order_by_columns megadja, hogy mely oszlopok szerint kell sorrendbe rendezni.

Ebben az oktatóanyagban megismerheti a partícióalapú modellezést a klasszikus NYC taxi mintaadatok és R-szkript használatával. A partíciós oszlop a fizetési mód.

  • A partíciók fizetési módokon alapulnak (5).
  • Hozzon létre és taníts be modelleket az egyes partíciókon, és tárolja az objektumokat az adatbázisban.
  • Előrejelzi a tipperedmények valószínűségét az egyes partíciómodelleken az erre a célra fenntartott mintaadatok használatával.

Előfeltételek

Az oktatóanyag elvégzéséhez a következőkre van szüksége:

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

Csatlakozás az adatbázishoz

Indítsa el az SSMS-t, és csatlakozzon az adatbázismotor-példányhoz. Az Object Explorerben ellenőrizze, hogy létezik-e NYCTaxi_Sample adatbázis .

Készítsd el a CalculateDistance-t

A demóadatbázis skaláris függvénnyel rendelkezik a távolság kiszámításához, de a tárolt eljárás jobban működik egy táblaértékű függvénnyel. Futtassa a következő szkriptet a CalculateDistancebetanítási lépésben használt függvény későbbi létrehozásához.

A függvény létrehozásának megerősítéséhez az Object Explorerben ellenőrizze az \Programmability\Functions\Table-valued FunctionsNYCTaxi_Sample adatbázis alatti elemet.

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

Partíciónkénti modellek létrehozásának és betanításának eljárásának meghatározása

Ez az oktatóanyag egy tárolt eljárásba csomagolja az R-szkriptet. Ebben a lépésben létrehoz egy tárolt eljárást, amely R használatával hoz létre egy bemeneti adatkészletet, létrehoz egy besorolási modellt a tipperedmények előrejelzéséhez, majd tárolja a modellt az adatbázisban.

A szkript által használt paraméterbemenetek között azokat látni fogja, mint input_data_1_partition_by_columns és input_data_1_order_by_columns. Ne feledje, hogy ezek a paraméterek az a mechanizmus, amellyel particionált modellezés történik. A paraméterek bemenetként lesznek átadva sp_execute_external_script a partíciók feldolgozásához a külső szkripttel, amely minden partícióhoz egyszer fut.

Ebben a tárolt eljárásban használja a párhuzamosságot a gyorsabb befejezési idő érdekében.

A szkript futtatása után az Object Explorerben meg kell hogy jelenjen \Programmability\Stored Procedures az NYCTaxi_Sample adatbázis alatt. A modellek tárolására használt új táblázatot is látnia kell: 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

Párhuzamos végrehajtás

Figyelje meg, hogy a sp_execute_external_script bemenetek közé tartoznak @parallel=1a párhuzamos feldolgozás engedélyezéséhez használt bemenetek. A korábbi kiadásokkal ellentétben az SQL Server 2019-től kezdve a beállítás @parallel=1 erősebb tippet ad a lekérdezésoptimalizálónak, így a párhuzamos végrehajtás sokkal valószínűbb lesz.

Alapértelmezés szerint a lekérdezésoptimalizáló 256-nál több sorból álló táblákon @parallel=1 működik, de ezt explicit módon kezelheti, ahogyan az ebben a szkriptben látható @parallel=1.

Jótanács

A számítási feladatok betanításához @parallel bármilyen tetszőleges betanítási szkripttel használhatja, még a nem Microsoft-rx algoritmusokat használók is. Az SQL Server betanítási forgatókönyveiben általában csak a RevoScaleR algoritmusok (az rx előtaggal) kínálnak párhuzamosságot. Az új paraméterrel azonban párhuzamossá teheti a függvényeket hívó szkripteket, beleértve a nyílt forráskódú R-függvényeket is, amelyek nem kifejezetten ezzel a képességgel lesznek megtervezve. Ez azért működik, mert a partíciók affinitással rendelkeznek az adott szálakhoz, ezért a szkriptben meghívott összes művelet partíciónként fut az adott szálon.

Az eljárás futtatása és a modell betanítása

Ebben a szakaszban a szkript betanítja az előző lépésben létrehozott és mentett modellt. Az alábbi példák két módszert mutatnak be a modell betanítására: egy teljes adatkészlet vagy egy részleges adatkészlet használatát.

Várjuk, hogy ez a lépés eltarthat egy ideig. A betanítás számításigényes, sok percet vesz igénybe. Ha a rendszererőforrások, különösen a memória nem elegendőek a terheléshez, használja az adatok egy részhalmazát. A második példa a szintaxist adja meg.

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

Megjegyzés:

Ha más számítási feladatokat is futtat, hozzáfűzheti OPTION(MAXDOP 2) a SELECT utasításhoz, ha csak 2 magra szeretné korlátozni a lekérdezésfeldolgozást.

Eredmények ellenőrzése

A modelltáblában szereplő eredménynek öt különböző modellnek kell lennie az öt fizetési típus szerint szegmentált öt partíció alapján. A modellek az ml_models adatforrásban találhatók.

SELECT *
FROM ml_models

Az eredmények előrejelzésére szolgáló eljárás meghatározása

A pontozáshoz ugyanazokat a paramétereket használhatja. Az alábbi minta egy R-szkriptet tartalmaz, amely a jelenleg feldolgozás alatt álló partícióhoz tartozó megfelelő modell használatával fog pontszámot kiosztani.

A korábbiakhoz hasonlóan hozzon létre egy tárolt eljárást az R-kód körbefuttatásához.

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

Táblázat létrehozása az előrejelzések tárolásához

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

Az eljárás futtatása és az előrejelzések mentése

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

Előrejelzések megtekintése

Mivel az előrejelzések tárolása történik, futtathat egy egyszerű lekérdezést egy eredményhalmaz visszaadásához.

SELECT *
FROM prediction_results;

Következő lépések

  • Ebben az oktatóanyagban a sp_execute_external_script-t használtad a particionált adatokon végzett műveletek iterálására. A külső szkriptek tárolt eljárásokban való meghívásának és a RevoScaleR-függvények használatának részletesebb ismertetéséhez folytassa az alábbi oktatóanyaggal.

Útmutató az R és az SQL Server használatához

  • Last updated on