Kurz: Vytváření modelů založených na oddílech v R na SQL Serveru

Článek
12/18/2024

platí pro: SQL Server 2016 (13.x) a novější verze

V SQL Serveru 2019 je modelování založené na oddílech funkce umožňující vytvářet a trénovat modely na rozdělených datech. Pro stratifikovaná data, která se přirozeně segmentují do daného klasifikačního schématu jako jsou zeměpisné oblasti, datum a čas, věk nebo pohlaví, můžete spustit skript přes celou datovou sadu s možností modelovat, trénovat a vyhodnocovat oddíly, které zůstávají neporušené ve všech těchto operacích.

Modelování založené na oddílech je povolené prostřednictvím dvou nových parametrů na sp_execute_external_script:

input_data_1_partition_by_columns, který určuje sloupec, podle kterého se má provést rozdělení.
input_data_1_order_by_columns určuje sloupce, podle kterých se mají seřadit.

V tomto kurzu se naučíte modelování založené na dělení pomocí dat z klasické ukázky NYC taxi a R skriptu. Sloupec pro rozdělení je způsob platby.

Oddíly jsou založené na typech plateb (5).
Vytvářejte a trénujte modely v jednotlivých oddílech a ukládejte objekty do databáze.
Předpovězte pravděpodobnost výsledků spropitného pro každý model rozdělení s využitím vzorových dat určených k tomuto účelu.

Požadavky

K dokončení tohoto kurzu musíte mít následující:

Dostatek systémových prostředků. Datová sada je velká a trénovací operace jsou náročné na prostředky. Pokud je to možné, použijte systém s minimálně 8 GB paměti RAM. Alternativně můžete použít menší datové sady pro práci s omezeními prostředků. Pokyny ke snížení datové sady jsou přímo v textu.
Nástroj pro provádění dotazů T-SQL, například SQL Server Management Studio (SSMS).
NYCTaxi_Sample.bak, které můžete stáhnout a obnovit do místní instance SQL Serveru. Velikost souboru je přibližně 90 MB.
Instance databázového stroje SQL Serveru 2019 s integrací služby Machine Learning Services a R
Tento kurz používá připojení zpětné smyčky k SQL Serveru z R skriptu přes rozhraní ODBC. Proto je nutné vytvořit přihlášení pro SQLRUserGroup.
Zkontrolujte dostupnost balíčků R vrácením dobře naformátovaného seznamu všech balíčků R, které jsou aktuálně nainstalované s vaší instancí databázového stroje:

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

Připojení k databázi

Spusťte SSMS a připojte se k instanci databázového stroje. V Průzkumníku objektů ověřte, zda existuje databáze NYCTaxi_Sample.

Vytvořit CalculateDistance

Ukázková databáze obsahuje skalární funkci pro výpočet vzdálenosti, ale naše uložená procedura funguje lépe s tabulkovou funkcí. Spuštěním následujícího skriptu vytvořte funkci CalculateDistance použitou v kroku trénování později.

Pokud chcete ověřit, že byla funkce vytvořená, zkontrolujte v průzkumníku objektů \Programmability\Functions\Table-valued Functions v databázi NYCTaxi_Sample.

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

Definujte postup pro vytváření a učení modelů pro jednotlivé oddíly

Tento kurz zabalí skript jazyka R do uložené procedury. V tomto kroku vytvoříte uloženou proceduru, která pomocí jazyka R vytvoří vstupní datovou sadu, sestavíte klasifikační model pro predikce výsledků tipu a pak uložíte model do databáze.

Mezi vstupy parametrů, které tento skript používá, uvidíte input_data_1_partition_by_columns a input_data_1_order_by_columns. Vzpomeňte si, že tyto parametry představují mechanismus, podle kterého dochází k dělení modelu. Parametry se předávají jako vstupy do sp_execute_external_script ke zpracování oddílů, přičemž externí skript se spouští jednou pro každý oddíl.

Pro tuto uloženou proceduru použijte paralelismus pro rychlejší dokončení.

Po spuštění tohoto skriptu byste v Průzkumníku objektů měli vidět train_rxLogIt_per_partition v části \Programmability\Stored Procedures pod databází NYCTaxi_Sample. Měla by se zobrazit také nová tabulka použitá pro ukládání modelů: 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

Paralelní spouštění

Všimněte si, že vstupy sp_execute_external_script zahrnují @parallel=1, které slouží k povolení paralelního zpracování. Na rozdíl od předchozích verzí, od verze SQL Server 2019 nastavení @parallel=1 poskytuje pro optimalizátor dotazů silnější nápovědu, takže paralelní spuštění se stává mnohem pravděpodobnějším výsledkem.

Ve výchozím nastavení má optimalizátor dotazů tendenci pracovat v @parallel=1 u tabulek s více než 256 řádky, ale pokud to můžete zpracovat explicitně nastavením @parallel=1, jak je znázorněno v tomto skriptu.

Spropitné

Pro trénovací úlohy můžete použít @parallel s libovolným trénovacím skriptem, a to i s použitím algoritmů jiných než Microsoft-rx. V trénovacích scénářích v SQL Serveru obvykle nabízejí paralelismus pouze algoritmy RevoScaleR (s předponou rx). Pomocí nového parametru ale můžete paralelizovat skript, který volá funkce, včetně opensourcových funkcí jazyka R, nikoli speciálně navržených s danou schopností. To funguje, protože oddíly mají spřažení s určitými vlákny, takže všechny operace volané ve skriptu se spouštějí na jednotlivých oddílech na daném vlákně.

Spuštění procedury a trénování modelu

V této části skript trénuje model, který jste vytvořili a uložili v předchozím kroku. Následující příklady ukazují dva přístupy pro trénování modelu: použití celé datové sady nebo částečných dat.

Počítejte s tím, že tento krok chvíli zabere. Trénování je výpočetně náročné, trvá to několik minut. Pokud systémové prostředky, zejména paměť, nejsou pro načtení dostatečné, použijte podmnožinu dat. Druhý příklad poskytuje syntaxi.

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

Poznámka

Pokud spouštíte jiné úlohy, můžete k příkazu SELECT připojit OPTION(MAXDOP 2), pokud chcete omezit zpracování dotazů jenom na 2 jádra.

Kontrola výsledků

Výsledkem tabulky modelů by mělo být pět různých modelů na základě pěti oddílů segmentovaných podle pěti typů plateb. Modely jsou ve zdroji dat ml_models.

SELECT *
FROM ml_models

Definování postupu pro predikci výsledků

Pro bodování můžete použít stejné parametry. Následující příklad obsahuje skript jazyka R, který bude hodnotit pomocí správného modelu pro část, kterou právě zpracovává.

Stejně jako předtím vytvořte uloženou proceduru, která zabalí váš kód R.

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

Vytvoření tabulky pro ukládání předpovědí

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

Spuštění procedury a uložení předpovědí

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

Zobrazení předpovědí

Protože jsou předpovědi uložené, můžete spustit jednoduchý dotaz, který vrátí sadu výsledků.

SELECT *
FROM prediction_results;

Další kroky

V tomto kurzu jste použili sp_execute_external_script k iteraci operací nad dělenými daty. Podrobnější informace o volání externích skriptů v uložených procedurách a používání funkcí RevoScaleR najdete v následujícím kurzu.

návod pro R a SQL Server

Sdílet prostřednictvím