다음을 통해 공유

Python 자습서: T-SQL을 사용하여 Python 모델 학습 및 저장

  • 아티클

적용 대상: SQL Server 2017(14.x) 이상 Azure SQL Managed Instance

이 5부 튜토리얼 시리즈의 4부에서는 파이썬 패키지 scikit-learn 및 revoscalepy를 사용하여 기계 학습 모델을 훈련하는 방법을 배울 것입니다. 이러한 Python 라이브러리는 SQL Server 머신 러닝과 함께 이미 설치되어 있습니다.

모듈을 로드하고 필요한 기능을 호출하여 SQL Server 저장 절차를 사용하여 모델을 만들고 교육합니다. 이 모델에는 이 자습서 시리즈의 이전 부분에서 엔지니어링한 데이터 기능이 필요합니다. 마지막으로 학습된 모델을 SQL Server 테이블에 저장합니다.

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

  • SQL 저장 프로시저를 사용하여 모델 만들기 및 학습
  • 학습된 모델을 SQL 테이블에 저장

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

2부에서는 샘플 데이터를 탐색하고 몇 가지 플롯을 생성했습니다.

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

5부에서는 4부에서 학습시키고 저장한 모델을 운영하는 방법을 알아봅니다.

샘플 데이터를 학습 세트 및 테스트 세트로 분할

  1. PyTrainTestSplit라는 저장 프로시저를 만들어 nyctaxi_sample 테이블의 데이터를 nyctaxi_sample_training 및 nyctaxi_sample_testing 두 부분으로 나눕니다.

    생성을 위해 다음 코드를 실행합니다.

    DROP PROCEDURE IF EXISTS PyTrainTestSplit;
GO

CREATE PROCEDURE [dbo].[PyTrainTestSplit] (@pct int)
AS

DROP TABLE IF EXISTS dbo.nyctaxi_sample_training
SELECT * into nyctaxi_sample_training FROM nyctaxi_sample WHERE (ABS(CAST(BINARY_CHECKSUM(medallion,hack_license)  as int)) % 100) < @pct

DROP TABLE IF EXISTS dbo.nyctaxi_sample_testing
SELECT * into nyctaxi_sample_testing FROM nyctaxi_sample
WHERE (ABS(CAST(BINARY_CHECKSUM(medallion,hack_license)  as int)) % 100) > @pct
GO

  2. 사용자 지정 분할을 사용하여 데이터를 나누려면 저장 프로시저를 실행하고 학습 집합에 할당할 데이터의 백분율을 나타내는 정수 매개 변수를 제공합니다. 예를 들어 다음 문은 학습 집합에 데이터의 60%를 할당합니다.

    EXEC PyTrainTestSplit 60
GO

로지스틱 회귀 모델 빌드

데이터가 준비되면 모델을 학습하는 데 사용할 수 있습니다. 학습 데이터 테이블을 입력으로 사용하여 일부 Python 코드를 실행하는 저장 프로시저를 호출하여 이 작업을 수행합니다. 이 자습서에서는 이진 분류 모델인 두 가지 모델을 만듭니다.

  • 저장 프로시저 PyTrainScikit는 scikit-learn 패키지를 사용하여 팁 예측 모델을 만듭니다.
  • 저장 프로시저 TrainTipPredictionModelRxPy는 revoscalepy 패키지를 사용하여 팁 예측 모델을 만듭니다.

저장된 각 프로시저는 제공한 입력 데이터를 사용하여 로지스틱 회귀 모형을 만들고 훈련합니다. 모든 Python 코드는 시스템 저장 프로시저 sp_execute_external_script에 래핑됩니다.

새 데이터에 대한 모델을 더 쉽게 재학습할 수 있도록 호출을 다른 저장 프로시저의 sp_execute_external_script에 래핑하고 새 학습 데이터를 매개 변수로 전달합니다. 이 섹션에서는 해당 프로세스를 안내합니다.

PyTrainScikit

  1. Management Studio에서 새 Query 창을 열고 다음 문을 실행하여 저장 프로시저 PyTrainScikit를 만듭니다. 저장 프로시저에 입력 데이터의 정의가 이미 포함되어 있어 입력 쿼리를 제공할 필요가 없습니다.

    DROP PROCEDURE IF EXISTS PyTrainScikit;
GO

CREATE PROCEDURE [dbo].[PyTrainScikit] (@trained_model varbinary(max) OUTPUT)
AS
BEGIN
EXEC sp_execute_external_script
  @language = N'Python',
  @script = N'
import numpy
import pickle
from sklearn.linear_model import LogisticRegression

##Create SciKit-Learn logistic regression model
X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]
y = numpy.ravel(InputDataSet[["tipped"]])

SKLalgo = LogisticRegression()
logitObj = SKLalgo.fit(X, y)

##Serialize model
trained_model = pickle.dumps(logitObj)
',
@input_data_1 = N'
select tipped, fare_amount, passenger_count, trip_time_in_secs, trip_distance, 
dbo.fnCalculateDistance(pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as direct_distance
from nyctaxi_sample_training
',
@input_data_1_name = N'InputDataSet',
@params = N'@trained_model varbinary(max) OUTPUT',
@trained_model = @trained_model OUTPUT;
;
END;
GO

  2. 다음 SQL 문을 실행하여 학습된 모델을 nyc_taxi_models 테이블에 삽입합니다.

    DECLARE @model VARBINARY(MAX);
EXEC PyTrainScikit @model OUTPUT;
INSERT INTO nyc_taxi_models (name, model) VALUES('SciKit_model', @model);

    데이터를 처리하고 모델을 맞추는 데 몇 분 정도 걸릴 수 있습니다. Python의 stdout 스트림에 파이프되는 메시지는 Management Studio의 메시지 창에 표시됩니다. 예시:

    STDOUT message(s) from external script:
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy

  3. nyc_taxi_models테이블을 엽니다. 열 model에 직렬화된 모델이 포함된 새로운 행이 하나 추가된 것을 볼 수 있습니다.

    SciKit_model
0x800363736B6C6561726E2E6C696E6561....

TrainTipPredictionModelRxPy

이 저장 프로시저는 revoscalepy Python 패키지를 사용합니다. 여기에는 R 언어의 RevoScaleR 패키지에 제공된 것과 유사한 개체, 변환 및 알고리즘이 포함됩니다.

revoscalepy를 사용하면 로지스틱 및 선형 회귀, 의사 결정 트리 등과 같은 인기 있는 알고리즘을 사용하여 원격 컴퓨팅 컨텍스트를 만들고, 컴퓨팅 컨텍스트 간에 데이터를 이동하고, 데이터를 변환하고, 예측 모델을 학습할 수 있습니다. 자세한 내용은 SQL Server 의 revoscalepy 모듈 및 revoscalepy 함수 참조를 참조하세요.

  1. Management Studio에서 새 Query 창을 열고 다음 문을 실행하여 저장 프로시저 TrainTipPredictionModelRxPy를 만듭니다. 저장 프로시저에 입력 데이터의 정의가 이미 포함되어 있으므로 입력 쿼리를 제공할 필요가 없습니다.

    DROP PROCEDURE IF EXISTS TrainTipPredictionModelRxPy;
GO

CREATE PROCEDURE [dbo].[TrainTipPredictionModelRxPy] (@trained_model varbinary(max) OUTPUT)
AS
BEGIN
EXEC sp_execute_external_script 
  @language = N'Python',
  @script = N'
import numpy
import pickle
from revoscalepy.functions.RxLogit import rx_logit

## Create a logistic regression model using rx_logit function from revoscalepy package
logitObj = rx_logit("tipped ~ passenger_count + trip_distance + trip_time_in_secs + direct_distance", data = InputDataSet);

## Serialize model
trained_model = pickle.dumps(logitObj)
',
@input_data_1 = N'
select tipped, fare_amount, passenger_count, trip_time_in_secs, trip_distance, 
dbo.fnCalculateDistance(pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as direct_distance
from nyctaxi_sample_training
',
@input_data_1_name = N'InputDataSet',
@params = N'@trained_model varbinary(max) OUTPUT',
@trained_model = @trained_model OUTPUT;
;
END;
GO

    이 저장 프로시저는 모델 학습의 일부로 다음 단계를 수행합니다.

    • SELECT 쿼리는 사용자 지정 스칼라 함수 fnCalculateDistance 를 적용하여 승하차 위치 사이의 직접 거리를 계산합니다. 쿼리 결과는 기본 파이썬 입력 변수인 InputDataset에 저장됩니다.
    • 이진 변수 tipped는 label 또는 결과 열로 사용되며, 모델은 passenger_count, trip_distance, trip_time_in_secs, direct_distance 등의 기능 열을 사용하여 맞춰집니다.
    • 학습된 모델은 직렬화되어 Python 변수 logitObj에 저장됩니다. T-SQL 키워드 OUTPUT을 추가하여 저장 프로시저의 출력으로 변수를 추가할 수 있습니다. 다음 단계에서는 해당 변수를 사용하여 모델의 이진 코드를 데이터베이스 테이블 nyc_taxi_models 삽입합니다. 이 메커니즘을 사용하면 모델을 쉽게 저장하고 재사용할 수 있습니다.

  2. 다음과 같이 저장 프로시저를 실행하여 학습된 revoscalepy 모델을 nyc_taxi_models 테이블에 삽입합니다.

    DECLARE @model VARBINARY(MAX);
EXEC TrainTipPredictionModelRxPy @model OUTPUT;
INSERT INTO nyc_taxi_models (name, model) VALUES('revoscalepy_model', @model);

    데이터를 처리하고 모델을 맞추는 데 시간이 걸릴 수 있습니다. Python의 stdout 스트림에 파이프되는 메시지는 Management Studio의 메시지 창에 표시됩니다. 예시:

    STDOUT message(s) from external script:
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy

  3. nyc_taxi_models테이블을 엽니다. 열 model에 직렬화된 모델이 포함된 새로운 행이 하나 추가된 것을 볼 수 있습니다.

    revoscalepy_model
0x8003637265766F7363616c....

다음 단계에서는 학습된 모델을 사용하여 예측을 만듭니다.

다음 단계

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

  • SQL 저장 프로시저를 사용하여 모델 만들기 및 학습
  • 학습된 모델을 SQL 테이블에 저장 완료

Python 자습서: 저장 프로시저에 포함된 Python을 사용하여 예측 실행