빠른 시작: R에서 SQL 기계 학습을 사용하여 예측 모델 만들기 및 점수 매기기

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

SQL에서 실행되는 두 개의 저장 프로시저를 만들고 실행합니다. 첫 번째 저장 프로시저는 R에 포함된 mtcars 데이터 세트를 사용하여 차량에 수동 변속기가 장착되었을 확률을 예측하는 단순한 일반화된 선형 모델(GLM)을 생성합니다. 두 번째 저장 프로시저는 첫 번째 프로시저에서 생성된 모델을 호출하여 새 데이터를 기반으로 예측 세트를 출력합니다. SQL 저장 프로시저에 R 코드를 배치하면 작업이 SQL에 포함되고 재사용 가능하며 다른 저장 프로시저 및 클라이언트 애플리케이션에서 호출할 수 있습니다.

이 빠른 시작을 완료하면 다음을 배울 수 있습니다.

필수 조건

이 빠른 시작을 실행하려면 다음과 같은 필수 구성 요소가 필요합니다.

• R 스크립트가 포함된 SQL 쿼리를 실행하기 위한 도구. 이 빠른 시작에서는 Azure Data Studio를 사용합니다.

모델 만들기

모델을 만들려면 학습을 위한 원본 데이터를 만들고, 모델을 만들고, 데이터를 사용하여 학습한 다음, 새 데이터로 예측을 생성하는 데 사용할 수 있는 데이터베이스에 모델을 저장합니다.

원본 데이터 만들기

1. Azure Data Studio를 열고 인스턴스에 연결한 다음 새 쿼리 창을 엽니다.

2. 학습 데이터를 저장할 테이블을 만듭니다.

   CREATE TABLE dbo.MTCars(
       mpg decimal(10, 1) NOT NULL,
       cyl int NOT NULL,
       disp decimal(10, 1) NOT NULL,
       hp int NOT NULL,
       drat decimal(10, 2) NOT NULL,
       wt decimal(10, 3) NOT NULL,
       qsec decimal(10, 2) NOT NULL,
       vs int NOT NULL,
       am int NOT NULL,
       gear int NOT NULL,
       carb int NOT NULL
   );
   

3. 기본 제공 데이터 세트 mtcars의 데이터를 삽입합니다.

   INSERT INTO dbo.MTCars
   EXEC sp_execute_external_script @language = N'R'
       , @script = N'MTCars <- mtcars;'
       , @input_data_1 = N''
       , @output_data_1_name = N'MTCars';
   

   팁

   크고 작은 많은 데이터 세트가 R 런타임에 포함됩니다. R과 함께 설치된 데이터 집합의 목록을 가져오려면 R 명령 프롬프트에서 library(help="datasets")를 입력합니다.

모델 만들기 및 학습

자동차 속도 데이터에는 마력(hp) 및 무게(wt)라는 두 개의 열이 있습니다. 이 데이터에서 차량에 수동 변속기가 장착되었을 확률을 예측하는 일반화된 선형 모델(GLM)을 만듭니다.

모델을 빌드하려면 R 코드 내에서 수식을 정의하고 데이터를 입력 매개 변수로 전달합니다.

DROP PROCEDURE IF EXISTS generate_GLM;
GO
CREATE PROCEDURE generate_GLM
AS
BEGIN
    EXEC sp_execute_external_script
    @language = N'R'
    , @script = N'carsModel <- glm(formula = am ~ hp + wt, data = MTCarsData, family = binomial);
        trained_model <- data.frame(payload = as.raw(serialize(carsModel, connection=NULL)));'
    , @input_data_1 = N'SELECT hp, wt, am FROM MTCars'
    , @input_data_1_name = N'MTCarsData'
    , @output_data_1_name = N'trained_model'
    WITH RESULT SETS ((model VARBINARY(max)));
END;
GO

• glm의 첫 번째 인수는 수식 매개 변수로, am를 hp + wt에 종속된 것으로 정의합니다.
• 입력 데이터는 SQL 쿼리로 채워지는 변수 MTCarsData에 저장됩니다. 입력 데이터에 특정 이름을 할당하지 않으면 기본 변수 이름은 InputDataSet입니다.

데이터베이스에 모델 저장

다음으로, 모델을 예측에 사용하거나 다시 학습시킬 수 있도록 데이터베이스에 저장합니다.

1. 모델을 저장할 테이블을 만듭니다.

   모델을 만드는 R 패키지의 출력은 일반적으로 이진 개체입니다. 따라서 모델을 저장하는 테이블은 varbinary(max) 형식의 열을 제공해야 합니다.

   CREATE TABLE GLM_models (
       model_name varchar(30) not null default('default model') primary key,
       model varbinary(max) not null
   );
   

2. 다음 Transact-SQL 문을 실행하여 저장 프로시저를 호출하고, 모델을 생성하고, 만든 테이블에 저장합니다.

   INSERT INTO GLM_models(model)
   EXEC generate_GLM;
   

   팁

   이 코드를 두 번째로 실행하면 "Violation of PRIMARY KEY constraint...Cannot insert duplicate key in object dbo.stopping_distance_models"라는 오류가 발생합니다. 이 오류를 방지하는 한 가지 옵션은 각 새 모델의 이름을 업데이트하는 것입니다. 예를 들어 이름을 좀 더 설명적인 이름으로 변경하고 모델 유형, 만든 날짜 등을 포함할 수 있습니다.

   UPDATE GLM_models
   SET model_name = 'GLM_' + format(getdate(), 'yyyy.MM.HH.mm', 'en-gb')
   WHERE model_name = 'default model'
   

학습된 모델을 사용하여 새 데이터 채점

점수 매기기는 학습된 모델에 공급되는 새 데이터를 기반으로 예측, 확률 또는 기타 값을 생성하는 것을 의미하는 데이터 과학에서 사용되는 용어입니다. 이전 섹션에서 만든 모델을 사용하여 새 데이터에 대한 예측 점수를 매깁니다.

새 데이터의 테이블 만들기

먼저 새 데이터를 사용하여 테이블을 만듭니다.

CREATE TABLE dbo.NewMTCars(
	hp INT NOT NULL
	, wt DECIMAL(10,3) NOT NULL
	, am INT NULL
)
GO

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (110, 2.634)

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (72, 3.435)

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (220, 5.220)

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (120, 2.800)
GO

수동 전송 예측

모델을 기반으로 예측을 얻으려면 다음을 수행하는 SQL 스크립트를 작성해야 합니다.

1. 원하는 모델을 가져옵니다.
2. 새 입력 데이터 가져오기
3. 해당 모델과 호환되는 R 예측 함수를 호출합니다.

시간이 지남에 따라 테이블에는 여러 R 모델이 포함될 수 있으며, 모두 서로 다른 매개 변수 또는 알고리즘을 사용하여 빌드되거나 서로 다른 데이터 하위 집합에서 학습될 수 있습니다. 이 예에서는 default model이라는 모델을 사용합니다.

DECLARE @glmmodel varbinary(max) = 
    (SELECT model FROM dbo.GLM_models WHERE model_name = 'default model');

EXEC sp_execute_external_script
    @language = N'R'
    , @script = N'
            current_model <- unserialize(as.raw(glmmodel));
            new <- data.frame(NewMTCars);
            predicted.am <- predict(current_model, new, type = "response");
            str(predicted.am);
            OutputDataSet <- cbind(new, predicted.am);
            '
    , @input_data_1 = N'SELECT hp, wt FROM dbo.NewMTCars'
    , @input_data_1_name = N'NewMTCars'
    , @params = N'@glmmodel varbinary(max)'
    , @glmmodel = @glmmodel
WITH RESULT SETS ((new_hp INT, new_wt DECIMAL(10,3), predicted_am DECIMAL(10,3)));

위의 스크립트는 다음 단계를 수행합니다.

• SELECT 문을 사용하여 테이블에서 단일 모델을 가져와서 입력 매개 변수로 전달합니다.

• 테이블에서 모델을 검색한 후 모델의 unserialize 함수를 호출합니다.

• 모델에 적절한 인수를 사용하여 predict 함수를 적용하고 새 입력 데이터를 제공합니다.

결과

수동 전송 확률을 예측하기 위한 결과 집합

PREDICT (Transact-SQL) 문을 사용하여 저장된 모델을 기반으로 예측 값 또는 점수를 생성할 수 있습니다.

다음 단계

SQL 기계 학습을 사용하는 R 자습서에 대한 자세한 내용은 다음을 참조하세요.

• R 자습서