Tutorial: Membuat model berbasis partisi di R pada SQL Server

  • Artikel

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

Pada 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 untuk dipartisi.
  • 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.
  • Memprediksi probabilitas hasil tip atas setiap model partisi, menggunakan data sampel yang dicadangkan untuk tujuan tersebut.

Prasyarat

Untuk menyelesaikan tutorial ini, Anda harus memiliki:

  • Sumber daya sistem yang memadai. Himpunan data besar dan operasi pelatihan intensif sumber daya. Jika memungkinkan, gunakan sistem yang memiliki setidaknya RAM 8 GB. Atau, Anda dapat menggunakan himpunan data yang lebih kecil untuk mengatasi batasan sumber daya. Instruksi untuk mengurangi himpunan data sebaris.

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

  • SQL Server instans mesin database 2019, dengan Layanan Pembelajaran Mesin dan integrasi R.

  • Tutorial ini menggunakan koneksi loopback untuk 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) ))

Menyambungkan ke database

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

Membuat 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 CalculateDistance digunakan dalam langkah pelatihan nanti.

Untuk mengonfirmasi bahwa fungsi telah dibuat, di Object Explorer, periksa di \Programmability\Functions\Table-valued FunctionsNYCTaxi_Sample bawah database.

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 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 yang lebih cepat hingga selesai.

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 sp_execute_external_script mencakup @parallel=1, digunakan untuk mengaktifkan pemrosesan paralel. Berbeda dengan rilis sebelumnya, mulai SQL Server 2019, pengaturan @parallel=1 memberikan petunjuk yang lebih kuat ke pengoptimal kueri, membuat eksekusi paralel menjadi hasil yang jauh lebih mungkin.

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 workoad 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 dalam SQL Server. Tetapi dengan parameter baru, Anda dapat memparalelkan skrip yang memanggil fungsi, termasuk fungsi R sumber terbuka, tidak secara khusus direkayasa dengan kemampuan tersebut. Ini berfungsi karena partisi memiliki afinitas untuk utas tertentu, sehingga semua operasi yang disebut dalam skrip dijalankan berdasarkan 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.

Harapkan langkah ini memakan waktu cukup lama. Pelatihan secara komputasi intensif, membutuhkan waktu beberapa 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 menjadi 2 inti.

Memeriksa hasil

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

SELECT *
FROM ml_models

Menentukan prosedur untuk memprediksi hasil

Anda dapat menggunakan parameter yang sama untuk penilaian. Sampel berikut berisi skrip R yang akan menilai menggunakan model yang benar untuk partisi yang saat ini sedang diproses.

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

Melihat prediksi

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

SELECT *
FROM prediction_results;

Langkah berikutnya

  • Dalam tutorial ini, Anda menggunakan sp_execute_external_script untuk melakukan iterasi operasi melalui data yang dipartisi. Untuk melihat lebih dekat panggilan skrip eksternal dalam prosedur tersimpan dan menggunakan fungsi RevoScaleR, lanjutkan dengan tutorial berikut.

panduan untuk R dan SQL Server