sp_execute_external_script(Transact-SQL)

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

저장 프로시저는 sp_execute_external_script 프로시저에 대한 입력 인수로 제공된 스크립트를 실행하고 Machine Learning Services 및 언어 확장과 함께 사용됩니다.

Machine Learning Services의 경우 Python 및 R 은 지원되는 언어입니다. 언어 확장의 경우 Java는 지원되지만 CREATE EXTERNAL LANGUAGE로 정의해야 합니다.

실행 sp_execute_external_script하려면 먼저 Machine Learning Services 또는 언어 확장을 설치해야 합니다. 자세한 내용은 Windows 및 Linux에 SQL Server Machine Learning Services(Python 및 R) 설치 또는 Windows 및 Linux에 SQL Server 언어 확장 설치를 참조하세요.

Transact-SQL 구문 표기 규칙

구문

sp_execute_external_script
    [ @language = ] N'language'
    , [ @script = ] N'script'
    [ , [ @input_data_1 = ] N'input_data_1' ]
    [ , [ @input_data_1_name = ] N'input_data_1_name' ]
    [ , [ @input_data_1_order_by_columns = ] N'input_data_1_order_by_columns' ]
    [ , [ @input_data_1_partition_by_columns = ] N'input_data_1_partition_by_columns' ]
    [ , [ @output_data_1_name = ] N'output_data_1_name' ]
    [ , [ @parallel = ] { 0 | 1 } ]
    [ , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ] [ , ...n ]' ]
    [ , [ @parameter1 = ] 'value1' [ OUT | OUTPUT ] [ , ...n ] ]
[ ; ]

인수

[ @language = ] N'language'

스크립트 언어를 나타냅니다. language는 sysname입니다. 유효한 값은 R, Python 및 CREATE EXTERNAL LANGUAGE(예: Java)로 정의된 모든 언어입니다.

[ @script = ] N'script'

리터럴 또는 변수 입력으로 지정된 외부 언어 스크립트입니다. 스크립트는 nvarchar(max)입니다.

[ @input_data_1 = ] N'input_data_1'

외부 스크립트에서 사용하는 입력 데이터를 Transact-SQL 쿼리 형식으로 지정합니다. input_data_1 데이터 형식은 nvarchar(max)입니다.

[ @input_data_1_name = ] N'input_data_1_name'

에 정의된 @input_data_1쿼리를 나타내는 데 사용되는 변수의 이름을 지정합니다. 외부 스크립트에서 변수의 데이터 형식은 언어에 따라 달라집니다. R의 경우 입력 변수는 데이터 프레임입니다. Python의 경우 입력은 테이블 형식이어야 합니다. input_data_1_name sysname입니다. 기본값은 InputDataSet입니다.

[ @input_data_1_order_by_columns = ] N'input_data_1_order_by_columns'

파티션별 모델을 빌드하는 데 사용됩니다. 결과 집합을 정렬하는 데 사용되는 열의 이름(예: 제품 이름)을 지정합니다. 외부 스크립트에서 변수의 데이터 형식은 언어에 따라 달라집니다. R의 경우 입력 변수는 데이터 프레임입니다. Python의 경우 입력은 테이블 형식이어야 합니다.

[ @input_data_1_partition_by_columns = ] N'input_data_1_partition_by_columns'

파티션별 모델을 빌드하는 데 사용됩니다. 지리적 지역 또는 날짜와 같은 데이터를 분할하는 데 사용되는 열의 이름을 지정합니다. 외부 스크립트에서 변수의 데이터 형식은 언어에 따라 달라집니다. R의 경우 입력 변수는 데이터 프레임입니다. Python의 경우 입력은 테이블 형식이어야 합니다.

[ @output_data_1_name = ] N'output_data_1_name'

저장 프로시저 호출이 완료될 때 SQL Server에 반환될 데이터가 포함된 외부 스크립트의 변수 이름을 지정합니다. 외부 스크립트에서 변수의 데이터 형식은 언어에 따라 달라집니다. R의 경우 출력은 데이터 프레임이어야 합니다. Python의 경우 출력은 pandas 데이터 프레임이어야 합니다. output_data_1_name sysname입니다. 기본값은 OutputDataSet입니다.

[ @parallel = ] { 0 | 1 }

매개 변수1를 .로 설정하여 R 스크립트의 병렬 실행을 사용하도록 설정합니다@parallel. 이 매개 변수의 기본값은 병렬 처리 없음입니다 0 . 출력이 클라이언트 컴퓨터 WITH RESULT SETS 로 직접 스트리밍되는 경우 @parallel = 1 절이 필요하며 출력 스키마를 지정해야 합니다.

• RevoScaleR 함수를 사용하지 않는 R 스크립트의 경우 스크립트를 간단하게 병렬 처리할 수 있다고 가정하면 매개 변수를 사용하면 @parallel 큰 데이터 세트를 처리하는 데 도움이 될 수 있습니다. 예를 들어 모델에서 R predict 함수를 사용하여 새 예측을 생성하는 경우 쿼리 엔진에 대한 힌트로 설정합니다 @parallel = 1 . 쿼리를 병렬 처리할 수 있으면 MAXDOP 설정에 따라 행이 분산됩니다.

• RevoScaleR 함수를 사용하는 R 스크립트의 경우 병렬 처리가 자동으로 처리되며 호출에 sp_execute_external_script 지정 @parallel = 1 해서는 안 됩니다.

[ @params = ] N'@parameter_name data_type' [ OUT | OUTPUT ] [ , ... n ]

외부 스크립트에 사용되는 입력 매개 변수 선언 목록입니다.

[ @parameter1 = ] 'value1' [ OUT | OUTPUT ] [ , ... n ]

외부 스크립트에서 사용하는 입력 매개 변수의 값 목록입니다.

설명

Important

쿼리 트리는 SQL 기계 학습에 의해 제어되며 사용자는 쿼리에 대해 임의 작업을 수행할 수 없습니다.

지원되는 언어로 작성된 스크립트를 실행하는 데 사용합니다 sp_execute_external_script . 지원되는 언어는 Machine Learning Services에서 사용되는 Python 및 R 이며, 언어 확장과 함께 사용되는 CREATE EXTERNAL LANGUAGE (예: Java)로 정의된 모든 언어입니다.

기본적으로 이 저장 프로시저에서 반환된 결과 집합은 명명되지 않은 열이 있는 출력입니다. 스크립트 내에서 사용되는 열 이름은 스크립팅 환경에 로컬이며 출력된 결과 집합에 반영되지 않습니다. 결과 집합 열의 이름을 지정하려면 EXECUTE 절을 WITH RESULT SET 사용합니다.

결과 집합을 반환하는 것 외에도 출력 매개 변수를 사용하여 스칼라 값을 반환할 수 있습니다.

외부 리소스 풀을 구성하여 외부 스크립트에서 사용하는 리소스를 제어할 수 있습니다. 자세한 내용은 CREATE EXTERNAL RESOURCE POOL을 참조 하세요. 워크로드에 대한 정보는 리소스 관리자 카탈로그 뷰, DMV 및 카운터에서 가져올 수 있습니다. 자세한 내용은 Resource Governor 카탈로그 뷰, 리소스 관리자 관련 동적 관리 뷰 및 SQL Server, 외부 스크립트 개체를 참조하세요.

스크립트 실행 모니터링

sys.dm_external_script_requests 및 sys.dm_external_script_execution_stats 사용하여 스크립트 실행을 모니터링합니다.

파티션 모델링을 위한 매개 변수

분할된 데이터에 모델링을 사용하도록 설정하는 두 개의 추가 매개 변수를 설정할 수 있습니다. 여기서 파티션은 데이터 집합을 논리적 파티션으로 자연스럽게 분할하는 하나 이상의 열을 기반으로 하며 스크립트 실행 중에만 만들어 사용됩니다. 연령, 성별, 지리적 지역, 날짜 또는 시간에 대한 반복 값을 포함하는 열은 분할된 데이터 집합에 적합한 몇 가지 예입니다.

두 매개 변수는 input_data_1_partition_by_columns input_data_1_order_by_columns 두 번째 매개 변수를 사용하여 결과 집합을 정렬합니다. 매개 변수는 모든 파티션에 sp_execute_external_script 대해 한 번씩 실행되는 외부 스크립트를 사용하여 입력으로 전달됩니다. 자세한 내용 및 예제는 자습서: SQL Server의 R에서 파티션 기반 모델 만들기를 참조 하세요.

를 지정하여 스크립트를 병렬로 실행할 수 있습니다 @parallel = 1. 입력 쿼리를 병렬 처리할 수 있는 경우 인수sp_execute_external_script의 일부로 설정 @parallel = 1 해야 합니다. 기본적으로 쿼리 최적화 프로그램은 256개 이상의 행이 있는 테이블에서 작동 @parallel = 1 하지만 명시적으로 처리하려는 경우 이 스크립트에는 매개 변수가 데모로 포함됩니다.

팁

학습 워크로드의 경우 비-Microsoft-rx 알고리즘을 사용 중이어도 임의의 학습 스크립트에 @parallel을 사용할 수 있습니다. 일반적으로 RevoScaleR 알고리즘(rx 접두사 포함)만이 SQL Server의 학습 시나리오에서 병렬 처리를 제공합니다. 그러나 SQL Server 2019(15.x) 이상 버전의 새 매개 변수를 사용하면 해당 기능으로 특별히 엔지니어링되지 않은 함수를 호출하는 스크립트를 병렬화할 수 있습니다.

Python 및 R 스크립트에 대한 스트리밍 실행

스트리밍을 사용하면 Python 또는 R 스크립트가 메모리에 들어갈 수 있는 것보다 많은 데이터로 작업할 수 있습니다. 스트리밍 중에 전달되는 행 수를 제어하려면 컬렉션에서 매개 변수 @r_rowsPerRead @params 의 정수 값을 지정합니다. 예를 들어 매우 넓은 데이터를 사용하는 모델을 학습하는 경우 값을 조정하여 행을 더 적게 읽어 모든 행을 하나의 데이터 청크로 보낼 수 있도록 할 수 있습니다. 이 매개 변수를 사용하여 서버 성능 문제를 완화하기 위해 한 번에 읽고 처리하는 행 수를 관리할 수도 있습니다.

스트리밍에 @r_rowsPerRead 대한 매개 변수와 인수는 @parallel 모두 힌트로 간주되어야 합니다. 힌트를 적용하려면 병렬 처리를 포함하는 SQL 쿼리 계획을 생성할 수 있어야 합니다. 가능하지 않은 경우 병렬 처리를 사용하도록 설정할 수 없습니다.

참고 항목

스트리밍 및 병렬 처리는 Enterprise Edition에서만 지원됩니다. 오류를 발생하지 않고 Standard Edition에서 쿼리에 매개 변수를 포함할 수 있지만 매개 변수는 효과가 없으며 R 스크립트는 단일 프로세스에서 실행됩니다.

제한 사항

데이터 유형

다음 데이터 형식은 프로시저의 sp_execute_external_script 입력 쿼리 또는 매개 변수에서 사용할 때 지원되지 않으며 지원되지 않는 형식 오류를 반환합니다.

해결 방법으로 CAST , 외부 스크립트로 보내기 전에 Transact-SQL에서 지원되는 형식에 대한 열 또는 값입니다.

• cursor
• timestamp
• datetime2, datetimeoffset, time
• sql_variant
• text, image
• xml
• hierarchyid, geometry, geography
• CLR 사용자 정의 형식

일반적으로 Transact-SQL 데이터 형식에 매핑할 수 없는 결과 집합은 다음과 같이 NULL출력됩니다.

R과 관련된 제한 사항

입력에 R에서 허용되는 값 범위에 맞지 않는 datetime 값이 포함되어 있으면 값이 변환NA됩니다. SQL 기계 학습은 R 언어에서 지원되는 것보다 더 큰 범위의 값을 허용하기 때문에 이 작업이 필요합니다.

두 언어 모두 IEEE 754를 사용하더라도 부동 소수 자릿수 값(예 +Inf: , -Inf, NaN)은 SQL 기계 학습에서 지원되지 않습니다. 현재 동작은 값을 SQL로 직접 보냅니다. 결과적으로 SQL 클라이언트는 오류를 throw합니다. 따라서 이러한 값은 .로 NULL변환됩니다.

사용 권한

EXECUTE ANY EXTERNAL SCRIPT 데이터베이스 권한이 필요합니다.

예제

이 섹션에는 Transact-SQL을 사용하여 R 또는 Python 스크립트를 실행하는 데 이 저장 프로시저를 사용하는 방법에 대한 예제가 포함되어 있습니다.

A. SQL Server에 R 데이터 집합 반환

다음 예제에서는 R에 포함된 아이리스 데이터 세트를 반환하는 데 사용하는 sp_execute_external_script 저장 프로시저를 만듭니다.

DROP PROCEDURE IF EXISTS get_iris_dataset;
GO
CREATE PROCEDURE get_iris_dataset
AS
BEGIN
    EXEC sp_execute_external_script @language = N'R',
        @script = N'iris_data <- iris;',
        @input_data_1 = N'',
        @output_data_1_name = N'iris_data'
    WITH RESULT SETS((
        "Sepal.Length" FLOAT NOT NULL,
        "Sepal.Width" FLOAT NOT NULL,
        "Petal.Length" FLOAT NOT NULL,
        "Petal.Width" FLOAT NOT NULL,
        "Species" VARCHAR(100)
    ));
END;
GO

B. Python 모델 만들기 및 해당 모델에서 점수 생성

이 예제에서는 간단한 Python 모델에서 점수를 생성하는 데 사용하는 sp_execute_external_script 방법을 보여 줍니다.

CREATE PROCEDURE [dbo].[py_generate_customer_scores]
AS
BEGIN
    -- Input query to generate the customer data
    DECLARE @input_query NVARCHAR(MAX) = N'SELECT customer, orders, items, cost FROM dbo.Sales.Orders'

    EXEC sp_execute_external_script @language = N'Python',
        @script = N'
import pandas as pd
from sklearn.cluster import KMeans

# Get data from input query
customer_data = my_input_data

# Define the model
n_clusters = 4
est = KMeans(n_clusters=n_clusters, random_state=111).fit(customer_data[["orders","items","cost"]])
clusters = est.labels_
customer_data["cluster"] = clusters

OutputDataSet = customer_data
',
        @input_data_1 = @input_query,
        @input_data_1_name = N'my_input_data'
    WITH RESULT SETS((
        "CustomerID" INT,
        "Orders" FLOAT,
        "Items" FLOAT,
        "Cost" FLOAT,
        "ClusterResult" FLOAT
    ));
END;
GO

Python 코드에 사용되는 열 머리글은 SQL Server에 출력되지 않습니다. 따라서 WITH RESULT 문을 사용하여 SQL에서 사용할 열 이름과 데이터 형식을 지정합니다.

C. SQL Server의 데이터를 기반으로 R 모델 생성

다음 예제에서는 붓꽃 모델을 생성하고 모델을 반환하는 데 사용하는 sp_execute_external_script 저장 프로시저를 만듭니다.

참고 항목

이 예제에서는 e1071 패키지를 미리 설치해야 합니다. 자세한 내용은 sqlmlutils를 사용하여 R 패키지 설치를 참조 하세요.

DROP PROCEDURE IF EXISTS generate_iris_model;
GO
CREATE PROCEDURE generate_iris_model
AS
BEGIN
    EXEC sp_execute_external_script @language = N'R',
        @script = N'
      library(e1071);
      irismodel <-naiveBayes(iris_data[,1:4], iris_data[,5]);
      trained_model <- data.frame(payload = as.raw(serialize(irismodel, connection=NULL)));
',
        @input_data_1 = N'select "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species" from iris_data',
        @input_data_1_name = N'iris_data',
        @output_data_1_name = N'trained_model'
    WITH RESULT SETS((model VARBINARY(MAX)));
END;
GO

Python을 사용하여 비슷한 모델을 생성하려면 언어 식별자를 @language=N'R'에서 @language = N'Python'으로 변경하고 @script 인수를 필요한 대로 수정합니다. 그렇지 않으면 모든 매개 변수가 R과 동일한 방식으로 작동합니다.

점수 매기기를 위해 Python 또는 R 런타임 호출을 방지하므로 일반적으로 더 빠른 네이티브 PREDICT 함수를 사용할 수도 있습니다.

관련 콘텐츠

• SQL Machine Learning
• SQL Server 언어 확장이란 무엇인가요?
• 시스템 저장 프로시저(Transact-SQL)
• CREATE EXTERNAL LIBRARY(Transact-SQL)
• sp_prepare(Transact SQL)
• sp_configure(Transact-SQL)
• 외부 스크립트 설정 서버 구성 옵션
• SERVERPROPERTY(Transact-SQL)
• SQL Server, 외부 스크립트 개체
• sys.dm_external_script_requests
• sys.dm_external_script_execution_stats