R 자습서: SQL 저장 프로시저에서 예측 실행

아티클
07/08/2024

적용 대상: SQL Server 2016(13.x) 이상 Azure SQL Managed Instance

5부로 구성된 이 자습서 시리즈의 5부에서는 이전 부분에서 학습하고 저장한 모델을 사용하여 잠재적인 결과를 예측함으로써 모델을 운영하는 방법을 알아봅니다. 모델은 다른 애플리케이션에서 직접 호출할 수 있는 저장 프로시저에 래핑됩니다.

이 문서에서는 점수 매기기를 수행하는 두 가지 방법을 보여 줍니다.

일괄 처리 채점 모드: SELECT 쿼리를 저장 프로시저에 대한 입력으로 사용합니다. 저장 프로시저는 입력 사례에 해당하는 관찰 테이블을 반환합니다.
개별 점수 매기기 모드: 개별 매개 변수 값 집합을 입력으로 전달합니다. 저장 프로시저는 단일 행 또는 값을 반환합니다.

이 문서에서는 다음을 수행합니다.

일괄 처리 점수 매기기를 위한 저장 프로시저 만들기 및 사용
단일 행 점수 매기기를 위한 저장 프로시저 만들기 및 사용

1부에서는 필수 구성 요소를 설치하고 샘플 데이터베이스를 복원했습니다.

2부에서는 샘플 데이터를 검토하고 일부 플롯을 생성했습니다.

3부에서는 Transact-SQL 함수를 사용하여 원시 데이터에서 기능을 만드는 방법을 배웠습니다. 그런 다음 저장 프로시저에서 해당 함수를 호출하여 기능 값이 포함된 테이블을 만들었습니다.

4부에서는 모듈을 로드하고 SQL Server 저장 프로시저를 사용하여 모델을 생성하고 학습하는 데 필요한 함수를 호출했습니다.

기본 점수 매기기

저장 프로시저 RPredict는 PREDICT 호출을 저장 프로시저에 래핑하는 기본 구문을 보여 줍니다.

CREATE PROCEDURE [dbo].[RPredict] (@model varchar(250), @inquery nvarchar(max))
AS 
BEGIN 

DECLARE @lmodel2 varbinary(max) = (SELECT model FROM nyc_taxi_models WHERE name = @model);  
EXEC sp_execute_external_script @language = N'R',
  @script = N' 
    mod <- unserialize(as.raw(model));
    print(summary(mod))
    OutputDataSet <- data.frame(predict(mod, InputDataSet, type = "response"));
    str(OutputDataSet)
    print(OutputDataSet)
    ',
  @input_data_1 = @inquery,
  @params = N'@model varbinary(max)',
  @model = @lmodel2 
  WITH RESULT SETS (("Score" float));
END
GO

SELECT 문은 데이터베이스에서 직렬화된 모델을 가져와서 R을 사용한 추가 처리를 위해 R 변수 mod에 모델을 저장합니다.
새로운 채점 사례는 저장 프로시저의 첫 번째 매개 변수인 @inquery에 지정된 Transact-SQL 쿼리에서 가져옵니다. 쿼리 데이터를 읽으면 행이 기본 데이터 프레임인 InputDataSet에 저장됩니다. 이 데이터 프레임은 점수를 생성하는 PREDICT 함수에 전달됩니다.

OutputDataSet <- data.frame(predict(mod, InputDataSet, type = "response"));

data.frame에 단일 행이 포함될 수 있으므로 일괄 처리 또는 단일 점수 매기기에 동일한 코드를 사용할 수 있습니다.
PREDICT 함수로 반환되는 값은 운전사가 금액에 관계없이 팁을 받게 될 확률을 나타내는 float입니다.

일괄 처리 점수 매기기(예측 목록)

더 일반적인 시나리오는 일괄 처리 모드에서 여러 관찰에 대한 예측을 생성하는 것입니다. 이 단계에서는 일괄 처리 점수 매기기 작동 방식을 살펴보겠습니다.

먼저 작업할 더 작은 입력 데이터 세트를 가져옵니다. 이 쿼리는 예측에 필요한 승객 수 및 기타 기능을 사용하여 "상위 10개" 여행 목록을 만듭니다.

SELECT TOP 10 a.passenger_count AS passenger_count, a.trip_time_in_secs AS trip_time_in_secs, a.trip_distance AS trip_distance, a.dropoff_datetime AS dropoff_datetime, dbo.fnCalculateDistance(pickup_latitude, pickup_longitude, dropoff_latitude,dropoff_longitude) AS direct_distance

FROM (SELECT medallion, hack_license, pickup_datetime, passenger_count,trip_time_in_secs,trip_distance, dropoff_datetime, pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude FROM nyctaxi_sample)a

LEFT OUTER JOIN

(SELECT medallion, hack_license, pickup_datetime FROM nyctaxi_sample TABLESAMPLE (70 percent) REPEATABLE (98052)    )b

ON a.medallion=b.medallion AND a.hack_license=b.hack_license 
AND a.pickup_datetime=b.pickup_datetime
WHERE b.medallion IS NULL

샘플 결과

passenger_count   trip_time_in_secs    trip_distance  dropoff_datetime          direct_distance
1                 283                  0.7            2013-03-27 14:54:50.000   0.5427964547
1                 289                  0.7            2013-02-24 12:55:29.000   0.3797099614
1                 214                  0.7            2013-06-26 13:28:10.000   0.6970098661

Management Studio에서 RPredictBatchOutput이라는 저장 프로시저를 만듭니다.

CREATE PROCEDURE [dbo].[RPredictBatchOutput] (@model varchar(250), @inquery nvarchar(max))
AS
BEGIN
DECLARE @lmodel2 varbinary(max) = (SELECT model FROM nyc_taxi_models WHERE name = @model);
EXEC sp_execute_external_script 
  @language = N'R',
  @script = N'
    mod <- unserialize(as.raw(model));
    print(summary(mod))
    OutputDataSet <- data.frame(predict(mod, InputDataSet, type = "response"));
    str(OutputDataSet)
    print(OutputDataSet)
  ',
  @input_data_1 = @inquery,
  @params = N'@model varbinary(max)',
  @model = @lmodel2
  WITH RESULT SETS ((Score float));
END

변수에 쿼리 텍스트를 제공하고 저장 프로시저에 매개 변수로 전달합니다.

-- Define the input data
DECLARE @query_string nvarchar(max)
SET @query_string='SELECT TOP 10 a.passenger_count as passenger_count, a.trip_time_in_secs AS trip_time_in_secs, a.trip_distance AS trip_distance, a.dropoff_datetime AS dropoff_datetime, dbo.fnCalculateDistance(pickup_latitude, pickup_longitude, dropoff_latitude,dropoff_longitude) AS direct_distance FROM  (SELECT medallion, hack_license, pickup_datetime, passenger_count,trip_time_in_secs,trip_distance, dropoff_datetime, pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude FROM nyctaxi_sample  )a   LEFT OUTER JOIN (SELECT medallion, hack_license, pickup_datetime FROM nyctaxi_sample TABLESAMPLE (70 percent) REPEATABLE (98052))b ON a.medallion=b.medallion AND a.hack_license=b.hack_license AND a.pickup_datetime=b.pickup_datetime WHERE b.medallion is null'

-- Call the stored procedure for scoring and pass the input data
EXEC [dbo].[RPredictBatchOutput] @model = 'RTrainLogit_model', @inquery = @query_string;

저장 프로시저에서 각 상위 10개의 여정에 대한 예측을 나타내는 일련의 값을 반환합니다. 그러나 상위 여행은 비교적 이동 거리가 짧은 1인 승객 여행으로, 운전기사가 팁을 받을 가능성이 낮습니다.

팁

단순히 "팁 있음" 및 "팁 없음" 결과를 반환하는 대신 예측의 확률 점수를 반환한 다음 Score 열 값에 WHERE 절을 적용하여 0.5 또는 0.7과 같은 임계값을 사용해서 "팁 가능성 높음" 또는 "팁 가능성 낮음"으로 점수를 분류할 수도 있습니다. 이 단계는 저장 프로시저에 포함되지 않지만 구현하기 쉽습니다.

여러 입력의 단일 행 채점

일부 경우에는 여러 입력 값을 전달하고 이러한 값을 기준으로 단일 예측을 가져와야 할 수 있습니다. 예를 들어 저장 프로시저를 호출하고 해당 애플리케이션 사용자가 입력 또는 선택한 입력을 제공하도록 Excel 워크시트, 웹 애플리케이션 또는 Reporting Services 보고서를 설정할 수 있습니다.

이 섹션에서는 승객 수, 주행 거리 등의 여러 입력을 받아들이는 저장 프로시저를 사용해서 단일 예측을 생성하는 방법을 알아봅니다. 저장 프로시저는 이전에 저장된 R 모델을 기반으로 점수를 만듭니다.

외부 애플리케이션에서 저장 프로시저를 호출하는 경우 데이터가 R 모델의 요구 사항과 일치하는지 확인합니다. 여기에는 입력 데이터를 R 데이터 형식으로 캐스팅 또는 변환할 수 있는지 확인, 데이터 형식 및 데이터 길이의 유효성 검사 등이 포함될 수 있습니다.

저장 프로시저 RPredictSingleRow를 만듭니다.

CREATE PROCEDURE [dbo].[RPredictSingleRow] @model varchar(50), @passenger_count int = 0, @trip_distance float = 0, @trip_time_in_secs int = 0, @pickup_latitude float = 0, @pickup_longitude float = 0, @dropoff_latitude float = 0, @dropoff_longitude float = 0
AS
BEGIN
DECLARE @inquery nvarchar(max) = N'SELECT * FROM [dbo].[fnEngineerFeatures](@passenger_count, @trip_distance, @trip_time_in_secs,  @pickup_latitude, @pickup_longitude, @dropoff_latitude, @dropoff_longitude)';
DECLARE @lmodel2 varbinary(max) = (SELECT model FROM nyc_taxi_models WHERE name = @model);
EXEC sp_execute_external_script  
  @language = N'R',
  @script = N'  
    mod <- unserialize(as.raw(model));  
    print(summary(mod));  
    OutputDataSet <- data.frame(predict(mod, InputDataSet, type = "response"));
    str(OutputDataSet);
    print(OutputDataSet); 
    ',  
  @input_data_1 = @inquery,  
  @params = N'@model varbinary(max),@passenger_count int,@trip_distance float,@trip_time_in_secs int ,  @pickup_latitude float ,@pickup_longitude float ,@dropoff_latitude float ,@dropoff_longitude float', @model = @lmodel2, @passenger_count =@passenger_count, @trip_distance=@trip_distance, @trip_time_in_secs=@trip_time_in_secs, @pickup_latitude=@pickup_latitude, @pickup_longitude=@pickup_longitude, @dropoff_latitude=@dropoff_latitude, @dropoff_longitude=@dropoff_longitude  
  WITH RESULT SETS ((Score float));  
END

값을 수동으로 제공하여 사용해 보세요.

새 쿼리 창을 열고 저장 프로시저를 호출하여 각 매개 변수에 대한 값을 제공합니다. 매개 변수는 모델에서 사용하는 기능 열을 나타내며 필수입니다.

EXEC [dbo].[RPredictSingleRow] @model = 'RTrainLogit_model',
@passenger_count = 1,
@trip_distance = 2.5,
@trip_time_in_secs = 631,
@pickup_latitude = 40.763958,
@pickup_longitude = -73.973373,
@dropoff_latitude =  40.782139,
@dropoff_longitude = -73.977303

또는 저장 프로시저에 대한 매개 변수에 대해 지원되는 이 짧은 형식을 사용합니다.

EXEC [dbo].[RPredictSingleRow] 'RTrainLogit_model', 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303

결과는 이러한 상위 10개 여행에서 팁을 받을 확률이 낮다(0)는 것을 나타내는데, 이는 모두 비교적 짧은 거리를 여행하는 1인 승객 여행이기 때문입니다.

결론

저장 프로시저에 R 코드를 포함하는 방법을 배웠으므로 이제 이러한 방법을 확장하여 고유한 모델을 빌드할 수 있습니다. Transact-SQL과 통합되어 보다 쉽게 예측에 대한 R 모델을 배포하고 모델 다시 학습을 엔터프라이즈 데이터 워크플로의 일부로 통합할 수 있습니다.

다음 단계

이 문서에서는 다음 작업을 수행합니다.

일괄 처리 채점을 위한 저장 프로시저 만들기 및 사용
단일 행 채점을 위한 저장 프로시저 만들기 및 사용

R에 대한 자세한 내용은 SQL Server의 R 확장을 참조하세요.

다음을 통해 공유

R 자습서: SQL 저장 프로시저에서 예측 실행

기본 점수 매기기

일괄 처리 점수 매기기(예측 목록)

여러 입력의 단일 행 채점

결론

다음 단계

피드백

피드백

추가 리소스