Öğretici: SQL Server'da R'de bölüm tabanlı modeller oluşturma

Şunlar için geçerlidir: SQL Server 2016 (13.x) ve sonraki sürümleri

SQL Server 2019'da bölüm tabanlı modelleme, bölümlenmiş veriler üzerinde model oluşturup eğitebilme özelliğidir. Coğrafi bölgeler, tarih ve saat, yaş veya cinsiyet gibi belirli bir sınıflandırma şemasına doğal olarak segmentlere ayrılmış katmanlı veriler için, tüm bu işlemler boyunca bozulmadan kalan bölümleri modelleme, eğitme ve puanlama özelliğiyle veri kümesinin tamamında betik çalıştırabilirsiniz.

Bölüm tabanlı modelleme, sp_execute_external_script iki yeni parametreyle etkinleştirilir:

input_data_1_partition_by_columns, bölümleme ölçütü olarak bir sütun belirtir.
input_data_1_order_by_columns hangi sütunların sıralanması gerektiğini belirtir.

Bu öğreticide, klasik NYC taksi örnek verilerini ve R betiğini kullanarak bölüm tabanlı modellemeyi öğrenin. Bölümleme sütunu ödeme yöntemidir.

Bölümler ödeme türlerini (5) temel alır.
Her bölümde model oluşturup eğitin ve nesneleri veritabanında depolayın.
Bu amaçla ayrılmış örnek verileri kullanarak her bölüm modeli üzerinde ipucu sonuçlarının olasılığını tahmin edin.

Önkoşullar

Bu öğreticiyi tamamlamak için aşağıdakilere sahip olmanız gerekir:

Yeterli sistem kaynağı. Veri kümesi büyük ve eğitim işlemleri yoğun kaynak kullanır. Mümkünse, en az 8 GB RAM'e sahip bir sistem kullanın. Alternatif olarak, kaynak kısıtlamalarına geçici bir çözüm bulmak için daha küçük veri kümeleri kullanabilirsiniz. Veri kümesini küçültmeye yönelik talimatlar satır içi verilmektedir.
SQL Server Management Studio (SSMS) gibi T-SQL sorgu yürütme aracı.
yerel SQL Server örneğiniz için indirip geri yükleyebileceğiniz NYCTaxi_Sample.bak. Dosya boyutu yaklaşık 90 MB'tır.
Machine Learning Services ve R tümleştirmesi ile SQL Server 2019 veritabanı altyapısı örneği.
Öğreticide ODBC üzerinden bir R betiğinden SQL Server'a geri döngü bağlantısı kullanılır. Bu nedenle , SQLRUserGroup için bir oturum açma oluşturmanız gerekir.
Veritabanı altyapısı örneğiniz ile yüklü olan tüm R paketlerinin iyi biçimlendirilmiş bir listesini döndürerek R paketlerinin kullanılabilirliğini denetleyin:

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

Veritabanına bağlanın

SSMS'yi başlatın ve veritabanı altyapısı örneğine bağlanın. Nesne Gezgini'ndeNYCTaxi_Sample veritabanının var olduğunu doğrulayın.

CalculateDistance'i Oluştur

Örnek veritabanı, mesafeyi hesaplamak için skaler bir işlev ile birlikte gelir, ancak saklı prosedürümüz, tablo değerli bir işlev ile daha iyi çalışır. Daha sonra CalculateDistance kullanılan işlevi oluşturmak için aşağıdaki betiği çalıştırın.

İşlevin oluşturulduğunu onaylamak için Nesne Gezgini'nde veritabanının altındaki öğesini \Programmability\Functions\Table-valued FunctionsNYCTaxi_Sample denetleyin.

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

Her bölüm için model oluşturma ve eğitme için bir yordam tanımla

Bu öğretici, R betiğini saklı bir yordama sarmalar. Bu adımda, giriş veri kümesi oluşturmak, ipucu sonuçlarını tahmin etmek için bir sınıflandırma modeli oluşturmak ve ardından modeli veritabanında depolamak için R kullanan bir saklı yordam oluşturursunuz.

Bu betik tarafından kullanılan parametre girişleri arasında input_data_1_partition_by_columns ve input_data_1_order_by_columns ifadelerini görürsünüz. Bu parametrelerin bölümlenmiş modellemenin gerçekleştiği mekanizma olduğunu hatırlayın. Parametreler, bölüm başına bir kez yürütülen dış betik ile bölümleri işlemek için sp_execute_external_script'e giriş olarak aktarılır.

Bu saklı yordamda, daha hızlı tamamlanması için paralellik kullanın.

Bu betiği çalıştırdıktan sonra, Nesne Gezgini'nde, NYCTaxi_Sample veritabanının altında \Programmability\Stored Procedurestrain_rxLogIt_per_partition görmeniz gerekiyor. Modelleri depolamak için kullanılan yeni bir tablo da görmeniz gerekir: 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

Paralel yürütme

sp_execute_external_script girişlerinin, @parallel=1 paralel işlemeyi etkinleştirmek için kullanıldığına dikkat edin. ÖNCEKI sürümlerden farklı olarak, SQL Server 2019'dan başlayarak, ayar @parallel=1 sorgu iyileştiricisine daha güçlü bir ipucu sağlar ve paralel yürütmeyi çok daha olası bir sonuç haline getirir.

Varsayılan olarak, sorgu iyileştiricisi 256'dan fazla satırı olan tablolarda @parallel=1 altında çalışmaya eğilimindedir, ancak bunu bu betikte gösterildiği gibi @parallel=1 ayarlayarak açıkça yönetebilirsiniz.

Tip

Eğitim iş yükleri için, microsoft-rx olmayan algoritmalar kullananlar bile @parallel herhangi bir rastgele eğitim betiğiyle kullanabilirsiniz. Genellikle yalnızca RevoScaleR algoritmaları (rx ön eki ile) SQL Server'daki eğitim senaryolarında paralellik sunar. Ancak yeni parametreyle, özellikle bu yetenek için tasarlanmamış olanlar da dahil olmak üzere, açık kaynak R işlevleri ve diğer işlevleri çağıran bir betiği paralelleştirebilirsiniz. Bu, bölümlerin belirli iş parçacıklarına yakınlığı olması nedeniyle çalışır, bu nedenle betikte çağrılan tüm işlemler, verilen iş parçacığında bölüm bazında yürütülür.

Prosedürü çalıştırın ve modeli eğitin

Bu bölümde betik, önceki adımda oluşturup kaydettiğiniz modeli eğitir. Aşağıdaki örneklerde modelinizi eğitmenize yönelik iki yaklaşım gösterilmektedir: veri kümesinin tamamını veya kısmi verileri kullanma.

Bu adımın biraz zaman almasını bekleyebilirsiniz. Eğitim işlem açısından yoğundur ve tamamlanması çok dakika sürer. Özellikle bellek olmak üzere sistem kaynakları yük için yetersizse, verilerin bir alt kümesini kullanın. İkinci örnek söz dizimini sağlar.

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

Uyarı

Başka iş yükleri çalıştırıyorsanız, sorgu işlemeyi yalnızca 2 çekirdekle sınırlamak istiyorsanız SELECT deyimine ekleyebilirsiniz OPTION(MAXDOP 2) .

Sonuçları kontrol et

Modeller tablosundaki sonuç, beş ödeme türüne göre segmentlere ayrılmış beş bölüm temelinde beş farklı model olmalıdır. Modeller veri kaynağındadır ml_models .

SELECT *
FROM ml_models

Sonuçları tahmin etmek için bir yordam tanımlama

Puanlama için aynı parametreleri kullanabilirsiniz. Aşağıdaki örnek, o anda işlemekte olduğu bölüm için doğru modeli kullanarak puanlayacak bir R betiği içerir.

Daha önce olduğu gibi, R kodunuzu sarmalamak için bir saklı yordam oluşturun.

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

Tahminleri depolamak için tablo oluşturma

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

Yordamı çalıştırma ve tahminleri kaydetme

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

Tahminleri görüntüleme

Tahminler depolandığından, sonuç kümesi döndürmek için basit bir sorgu çalıştırabilirsiniz.

SELECT *
FROM prediction_results;

Sonraki Adımlar

Bu öğreticide, bölümlenmiş veriler üzerinde işlemleri yinelemek için sp_execute_external_script kullandınız. Saklı yordamlarda dış betikleri çağırmaya ve RevoScaleR işlevlerini kullanmaya daha yakından bakmak için aşağıdaki öğreticiyle devam edin.

R ve SQL Server için izlenecek yol

Geri Bildirim

Bu sayfayı yararlı buldunuz mu?

Last updated on 2025-11-25