sp_execute_external_script(Transact-SQL)

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

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

Machine Learning 서비스의 경우 PythonR은 지원되는 언어입니다. 언어 확장의 경우 Java는 지원되지만 CREATE EXTERNAL LANGUAGE로 정의해야 합니다.

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

sp_execute_external_script 저장 프로시저는 프로시저에 대한 입력 인수로 제공된 스크립트를 실행하고 SQL Server 2017의 Machine Learning Services와 함께 사용됩니다.

Machine Learning 서비스의 경우 PythonR은 지원되는 언어입니다.

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

sp_execute_external_script 저장 프로시저는 프로시저에 대한 입력 인수로 제공된 스크립트를 실행하고 SQL Server 2016에서 R Services와 함께 사용됩니다.

R Services의 경우 R 은 지원되는 언어입니다.

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

sp_execute_external_script 저장 프로시저는 프로시저에 대한 입력 인수로 제공된 스크립트를 실행하고 Azure SQL Managed Instance Machine Learning Services와 함께 사용됩니다.

Machine Learning 서비스의 경우 PythonR은 지원되는 언어입니다.

sp_execute_external_script 실행하려면 먼저 Machine Learning Services를 사용하도록 설정해야 합니다. 자세한 내용은 Azure SQL Managed Instance 설명서의 Machine Learning Services를 참조하세요.

Topic link iconTransact-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 ] ]

SQL Server 2017 이하 구문

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' ]  
    [ , @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'

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

스크립트 언어를 나타냅니다. 언어sysname입니다. 2017년 SQL Server 유효한 값은 RPython입니다.

스크립트 언어를 나타냅니다. 언어sysname입니다. 2016년 SQL Server 유효한 값은 R뿐입니다.

스크립트 언어를 나타냅니다. 언어sysname입니다. Azure SQL Managed Instance 유효한 값은 RPython입니다.

@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_namesysname입니다. 기본값은 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_namesysname입니다. 기본값은 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 ] ] 외부 스크립트에서 사용하는 입력 매개 변수의 값 목록입니다.

설명

중요

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

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

sp_execute_external_script 사용하여 지원되는 언어로 작성된 스크립트를 실행합니다. 지원되는 언어는 SQL Server 2017 Machine Learning Services의 PythonR입니다.

sp_execute_external_script 사용하여 지원되는 언어로 작성된 스크립트를 실행합니다. 유일하게 지원되는 언어는 SQL Server 2016 R Services의 R입니다.

sp_execute_external_script 사용하여 지원되는 언어로 작성된 스크립트를 실행합니다. 지원되는 언어는 Azure SQL Managed Instance Machine Learning Services의 PythonR입니다.

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

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

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

스크립트 실행 모니터링

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

파티션 모델링에 대한 매개 변수

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

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

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

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

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

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

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

참고

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

제한

데이터 유형

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

해결 방법으로 열 또는 값을 Transact-SQL 지원되는 형식으로 캐스팅한 후 외부 스크립트로 보냅니다.

  • cursor

  • timestamp

  • datetime2, datetimeoffset, time

  • sql_variant

  • text, image

  • xml

  • hierarchyid, geometry, geography

  • CLR 사용자 정의 형식

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

R 관련 제한 사항

입력에 R에서 허용되는 값 범위에 맞지 않는 날짜/시간 값이 포함된 경우 값은 NA로 변환됩니다. 이는 SQL 기계 학습이 R 언어에서 지원되는 것보다 더 큰 범위의 값을 허용하기 때문에 필요합니다.

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

사용 권한

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

예제

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

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

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

DROP PROC IF EXISTS get_iris_dataset;  
go  
CREATE PROC 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 패키지를 미리 설치해야 합니다. 자세한 내용은 SQL Server 추가 R 패키지 설치를 참조하세요.

DROP PROC IF EXISTS generate_iris_model;
GO
CREATE PROC 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과 똑같이 작동합니다.

점수 매기기의 경우 네이티브 PREDICT 함수를 사용할 수도 있으며, 이 함수는 Python 또는 R 런타임 호출을 방지하기 때문에 일반적으로 더 빠릅니다.

참조