자습서: SQL Server의 R에서 파티션 기반 모델 만들기

적용 대상: SQL Server 2016(13.x) 이상 버전

SQL Server 2019의 파티션 기반 모델링은 분할된 데이터에 대한 모델을 만들고 학습시키는 기능입니다. 지리적 지역, 날짜 및 시간, 연령 또는 성별 등의 특정 분류 스키마로 자연스럽게 분할되는 계층화 데이터의 경우 모든 작업에서 온전하게 유지되는 파티션을 모델링하고, 학습시키고, 채점하는 기능을 사용하여 전체 데이터 세트에 대해 스크립트를 실행할 수 있습니다.

파티션 기반 모델링은 sp_execute_external_script에서 다음 두 가지 새 매개 변수를 통해 사용하도록 설정됩니다.

• 분할할 열을 지정하는 input_data_1_partition_by_columns입니다.
• input_data_1_order_by_columns - 정렬 기준이 되는 열을 지정합니다.

이 자습서에서는 클래식 NYC 택시 샘플 데이터 및 R 스크립트를 사용하여 파티션 기반 모델링에 대해 알아봅니다. 파티션 열은 결제 방법입니다.

사전 요구 사항

이 자습서를 완료하려면 다음 항목이 필요합니다.

• 충분한 시스템 리소스. 데이터 세트가 크고 학습 작업은 리소스를 많이 사용합니다. 되도록이면 8GB 이상의 RAM이 있는 시스템을 사용하세요. 또는 작은 데이터 세트를 사용하여 리소스 제약 조건을 피할 수 있습니다. 데이터 세트를 줄이기 위한 지침은 인라인으로 제공됩니다.

• SSMS(SQL Server Management Studio) 같은 T-SQL 쿼리 실행 도구

• NYCTaxi_Sample.bak - 로컬 SQL Server 인스턴스에 다운로드하고 복원할 수 있습니다. 파일 크기는 약 90MB입니다.

• Machine Learning Services 및 R이 통합된 SQL Server 2019 데이터베이스 엔진 인스턴스입니다.

• 이 자습서에서는 ODBC를 통해 R 스크립트에서 SQL Server로 루프백 연결 방법을 사용합니다. 따라서 SQLRUserGroup의 로그인을 만들어야 합니다.

• 현재 데이터베이스 엔진 인스턴스와 함께 설치된 모든 R 패키지의 잘 포맷된 목록을 반환하여 R 패키지의 가용성을 확인합니다.

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

데이터베이스에 연결

SSMS를 시작하고 데이터베이스 엔진 인스턴스에 연결합니다. 개체 탐색기에서 NYCTaxi_Sample 데이터베이스가 있는지 확인합니다.

CalculateDistance 만들기

데모 데이터베이스는 거리를 계산하기 위한 스칼라 함수와 함께 제공되지만, 테이블 반환 함수를 사용하는 저장 프로시저가 더 효율적입니다. 다음 스크립트를 실행하여 나중에 CalculateDistance에서 사용되는 함수를 만듭니다.

함수 생성을 확인하려면 개체 탐색기에서 \Programmability\Functions\Table-valued Functions 데이터베이스 아래의 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

파티션당 모델을 만들고 학습시키는 프로시저를 정의합니다.

이 자습서에서는 R 스크립트를 저장 프로시저에 래핑합니다. 이 단계에서는 R을 사용하여 입력 데이터 세트를 만들고, 팁 결과를 예측하기 위한 분류 모델을 빌드하고, 데이터베이스에 모델을 저장하는 저장 프로시저를 만듭니다.

이 스크립트에 사용되는 매개 변수 입력 중에서 input_data_1_partition_by_columns 및 input_data_1_order_by_columns가 보일 것입니다. 이러한 매개 변수는 분할된 모델링이 발생하는 메커니즘입니다. 이러한 매개 변수는 sp_execute_external_script에 입력으로 전달되어 파티션마다 한 번씩 실행되는 외부 스크립트를 사용하여 파티션을 처리합니다.

이 저장 프로시저의 경우 완료 시간을 단축할 수 있도록 병렬 처리를 사용하세요.

이 스크립트를 실행한 뒤 개체 탐색기에서 train_rxLogIt_per_partition 데이터베이스 아래의 \Programmability\Stored Procedures에 있는 NYCTaxi_Sample을 볼 수 있습니다. 모델을 저장하는 데 사용되는 새 테이블 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

병렬 실행

sp_execute_external_script 입력은 병렬 처리를 사용하도록 설정하는 데 사용되는 @parallel=1을 포함하고 있습니다. 이전 릴리스와는 달리, SQL Server 2019부터는 @parallel=1을 설정하면 쿼리 최적화 프로그램에 더 강력한 힌트가 제공되므로 병렬 실행의 결과를 얻을 확률이 훨씬 높습니다.

기본적으로 쿼리 최적화 프로그램은 행이 256개를 초과하는 테이블의 @parallel=1에서 작동하는 경향이 있지만, 가능하다면 이 스크립트에서 보여드리는 것처럼 @parallel=1을 설정하여 명시적으로 처리할 수 있습니다.

프로시저를 실행하고 모델 학습

이 섹션의 스크립트는 이전 단계에서 만들고 저장한 모델을 학습시킵니다. 아래 예제에서는 모델을 학습시키는 두 가지 접근 방식을 보여주는데, 하나는 전체 데이터 세트를 사용하는 방법이고, 다른 하나는 부분 데이터를 사용하는 방법입니다.

이 단계는 다소 시간이 걸립니다. 학습은 컴퓨팅 집약적이며, 완료하는 데 시간이 걸립니다. 시스템 리소스, 특히 메모리가 부하에 비해 충분하지 않은 경우 데이터 하위 세트를 사용하세요. 두 번째 예제는 구문을 제공합니다.

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

결과 확인

모델 테이블의 결과는 5가지 결제 유형에 따라 분할된 5개 파티션을 기반으로 하는 5개의 서로 다른 모델입니다. 모델은 ml_models 데이터 원본에 있습니다.

SELECT *
FROM ml_models

결과를 예측하는 프로시저 정의

동일한 매개 변수를 채점에 사용할 수 있습니다. 다음 샘플에는 현재 처리 중인 파티션에 적합한 모델을 사용하여 채점하는 R 스크립트가 포함되어 있습니다.

이전과 마찬가지로, 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

예측을 저장할 테이블 만들기

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

프로시저를 실행하고 예측 저장

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

예측 보기

예측이 저장되었으므로, 간단한 쿼리를 실행하여 결과 세트를 반환할 수 있습니다.

SELECT *
FROM prediction_results;

다음 단계

• 이 자습서에서는 sp_execute_external_script를 사용하여 분할된 데이터를 대상으로 작업을 반복합니다. 저장 프로시저의 외부 스크립트를 호출하고 RevoScaleR 함수를 사용하는 방법을 자세히 살펴보려면 다음 자습서를 계속 진행하세요.