Tutorial: Membuat model berbasis partisi di R di SQL Server

Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru

Di SQL Server 2019, pemodelan berbasis partisi adalah kemampuan untuk membuat dan melatih model melalui data yang dipartisi. Untuk data bertingkat yang secara alami tersegmentasi ke dalam skema klasifikasi tertentu - seperti wilayah geografis, tanggal dan waktu, usia atau jenis kelamin - Anda dapat menjalankan skrip di seluruh himpunan data, dengan kemampuan untuk memodelkan, melatih, dan menilai partisi yang tetap utuh atas semua operasi ini.

Pemodelan berbasis partisi diaktifkan melalui dua parameter baru pada sp_execute_external_script:

input_data_1_partition_by_columns, yang menentukan kolom yang digunakan untuk partisi.
input_data_1_order_by_columns menentukan kolom mana yang akan diurutkan.

Dalam tutorial ini, pelajari pemodelan berbasis partisi menggunakan data sampel taksi NYC klasik dan skrip R. Kolom partisi adalah metode pembayaran.

Partisi didasarkan pada jenis pembayaran (5).
Buat dan latih model pada setiap partisi dan simpan objek dalam database.
Prediksi probabilitas hasil tip untuk setiap model partisi dengan menggunakan data sampel yang dicadangkan untuk tujuan tersebut.

Prasyarat

Untuk menyelesaikan tutorial ini, Anda harus memiliki:

Sumber daya sistem yang memadai. Himpunan data ini besar dan operasi pelatihan membutuhkan banyak sumber daya. Jika memungkinkan, gunakan sistem yang memiliki RAM setidaknya 8 GB. Atau, Anda dapat menggunakan himpunan data yang lebih kecil untuk mengatasi batasan sumber daya. Instruksi untuk mengurangi set data dicantumkan langsung pada teks.
Alat untuk eksekusi kueri T-SQL, seperti SQL Server Management Studio (SSMS).
NYCTaxi_Sample.bak, yang dapat Anda unduh dan pulihkan ke instans SQL Server lokal Anda. Ukuran file sekitar 90 MB.
Instance mesin database SQL Server 2019, dengan Layanan Pembelajaran Mesin dan integrasi R.
Tutorial ini menggunakan koneksi loopback ke SQL Server dari skrip R melalui ODBC. Oleh karena itu, Anda perlu membuat login untuk SQLRUserGroup.
Periksa ketersediaan paket R dengan mengembalikan daftar yang diformat dengan baik dari semua paket R yang saat ini diinstal dengan instans mesin database Anda:

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

Sambungkan ke database

Mulai SSMS dan sambungkan ke instans mesin database. Di Object Explorer, verifikasi database NYCTaxi_Sample ada.

Buat CalculateDistance

Database demo dilengkapi dengan fungsi skalar untuk menghitung jarak, tetapi prosedur tersimpan kami bekerja lebih baik dengan fungsi bernilai tabel. Jalankan skrip berikut untuk membuat fungsi yang CalculateDistancedigunakan dalam langkah pelatihan nanti.

Untuk mengonfirmasi bahwa fungsi telah dibuat, di Object Explorer, periksa \Programmability\Functions\Table-valued Functions di bawah database 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

Menentukan prosedur untuk membuat dan melatih model per partisi

Tutorial ini membungkus skrip R ke dalam prosedur tersimpan. Dalam langkah ini, Anda membuat prosedur tersimpan yang menggunakan R untuk membuat himpunan data input, membangun model klasifikasi untuk memprediksi hasil tip, lalu menyimpan model dalam database.

Di antara input parameter yang digunakan oleh skrip ini, Anda akan melihat input_data_1_partition_by_columns dan input_data_1_order_by_columns. Ingat bahwa parameter ini adalah mekanisme di mana pemodelan yang dipartisi terjadi. Parameter diteruskan sebagai input ke sp_execute_external_script untuk memproses partisi dengan skrip eksternal yang dijalankan sekali untuk setiap partisi.

Untuk prosedur tersimpan ini, gunakan paralelisme untuk waktu penyelesaian yang lebih cepat.

Setelah Anda menjalankan skrip ini, di Object Explorer, Anda akan melihat train_rxLogIt_per_partition di \Programmability\Stored Procedures bawah NYCTaxi_Sample database. Anda juga akan melihat tabel baru yang digunakan untuk menyimpan 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

Eksekusi paralel

Perhatikan bahwa input untuk sp_execute_external_script mencakup @parallel=1, yang digunakan untuk mengaktifkan pemrosesan paralel. Berbeda dengan rilis sebelumnya, mulai dari SQL Server 2019, menetapkan @parallel=1 memberikan petunjuk yang lebih kuat kepada pengoptimal kueri, sehingga eksekusi paralel menjadi jauh lebih mungkin terjadi.

Secara default, pengoptimal kueri cenderung beroperasi di bawah @parallel=1 pada tabel yang memiliki lebih dari 256 baris, tetapi jika Anda dapat menangani ini secara eksplisit dengan mengatur @parallel=1 seperti yang ditunjukkan dalam skrip ini.

Tip

Untuk beban kerja pelatihan, Anda dapat menggunakan @parallel dengan skrip pelatihan arbitrer apa pun, bahkan yang menggunakan algoritma non-Microsoft-rx. Biasanya, hanya algoritma RevoScaleR (dengan awalan rx) yang menawarkan paralelisme dalam skenario pelatihan di SQL Server. Tetapi dengan parameter baru, Anda dapat menjalankan skrip yang memanggil fungsi secara paralel, termasuk fungsi R sumber terbuka yang tidak secara khusus dirancang untuk kemampuan tersebut. Ini berfungsi karena partisi memiliki afinitas terhadap utas tertentu, sehingga semua operasi yang disebut dalam skrip dijalankan per partisi, pada utas yang diberikan.

Jalankan prosedur dan latih model

Di bagian ini, skrip melatih model yang Anda buat dan simpan di langkah sebelumnya. Contoh di bawah ini menunjukkan dua pendekatan untuk melatih model Anda: menggunakan seluruh himpunan data, atau sebagian data.

Perkirakan langkah ini akan memakan waktu cukup lama. Pelatihan secara komputasi intensif, membutuhkan waktu banyak menit untuk diselesaikan. Jika sumber daya sistem, terutama memori, tidak cukup untuk beban, gunakan subset data. Contoh kedua menyediakan sintaks.

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

Catatan

Jika Anda menjalankan beban kerja lain, Anda dapat menambahkan OPTION(MAXDOP 2) ke pernyataan SELECT jika Anda ingin membatasi pemrosesan kueri hanya untuk 2 inti.

Memeriksa hasil

Hasilnya dalam tabel model harus lima model yang berbeda, berdasarkan lima partisi yang disegmentasi oleh lima jenis pembayaran. Model ada di sumber data ml_models.

SELECT *
FROM ml_models

Menentukan prosedur untuk memprediksi hasil

Anda dapat menggunakan parameter yang sama untuk penilaian. Contoh berikut memuat skrip R yang akan melakukan penskoran menggunakan model yang benar untuk partisi yang sedang diproses saat ini.

Seperti sebelumnya, buat prosedur tersimpan untuk membungkus kode R Anda.

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

Membuat tabel untuk menyimpan prediksi

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

Jalankan prosedur dan simpan prediksi

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

Lihat prediksi

Karena prediksi disimpan, Anda bisa menjalankan kueri sederhana untuk mengembalikan kumpulan hasil.

SELECT *
FROM prediction_results;

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2026-08-24