sp_execute_external_script (Transact-SQL)

Область применения: SQL Server 2016 (13.x) и более поздних версий Управляемый экземпляр SQL Azure

Соглашения о синтаксисе 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 ] ]
[ ; ]

EXEC 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'

[ @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.

[ @output_data_1_name = ] N'output_data_1_name'

Указывает имя переменной во внешнем скрипте, который содержит данные, возвращаемые в SQL Server после завершения вызова хранимой процедуры. Тип данных переменной во внешнем скрипте зависит от языка. Для R выходные данные должны быть кадром данных. Для Python выходные данные должны быть кадром данных pandas. output_data_1_name — sysname. Значением по умолчанию является OutputDataSet.

[ @parallel = ] { 0 | 1 }

Включите параллельное выполнение скриптов R, задав @parallel параметру 1значение . Значением по умолчанию для этого параметра является 0 (без параллелизма). Если @parallel = 1 и выходные данные передаются непосредственно на клиентский компьютер, необходимо WITH RESULT SETS указать предложение и указать выходную схему.

• Для скриптов R, которые не используют функции RevoScaleR, использование @parallel параметра может оказаться полезным для обработки больших наборов данных, предполагая, что скрипт можно тривиально параллелизировать. Например, при использовании функции R predict с моделью для создания новых прогнозов задайте @parallel = 1 в качестве указания обработчику запросов. Если запрос может быть параллелизован, строки распределяются в соответствии с параметром MAXDOP .

• Для скриптов R, использующих функции RevoScaleR, параллельная обработка обрабатывается автоматически, и не следует указывать @parallel = 1 вызов sp_execute_external_script .

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

Список объявлений входных параметров, используемых во внешнем скрипте.

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

Список значений входных параметров, используемых внешним скриптом.

Замечания

По умолчанию результирующие наборы, возвращаемые этой хранимой процедурой, выводятся с неназванными столбцами. Имена столбцов, используемые в скрипте, являются локальными для среды сценариев и не отражаются в выходном результирующем наборе. Чтобы назвать столбцы результирующего набора, используйте WITH RESULT SET предложение EXECUTE.

Помимо возврата результирующий набор, можно возвращать скалярные значения для использования параметров OUTPUT.

Мониторинг выполнения скрипта

Мониторинг выполнения скрипта с помощью sys.dm_external_script_requests и sys.dm_external_script_execution_stats.

Потоковая передача скриптов Python и R

Потоковая передача позволяет скрипту Python или R работать с большим объемом данных, чем может быть в памяти. Чтобы контролировать количество строк, передаваемых во время потоковой передачи, укажите целочисленное значение параметра @r_rowsPerRead в @params коллекции. Например, если вы обучаете модель, использующую очень широкие данные, можно настроить значение для чтения меньше строк, чтобы обеспечить отправку всех строк в одном блоке данных. Этот параметр также можно использовать для управления числом строк, считываемых и обрабатываемых одновременно, для устранения проблем с производительностью сервера.

@r_rowsPerRead И параметр потоковой передачи, и @parallel аргумент должны считаться указаниями. Для применения указания необходимо создать план sql-запросов, включающий параллельную обработку. Если это невозможно, параллельная обработка не может быть включена.

Ограничения

Типы данных

Следующие типы данных не поддерживаются при использовании во входном запросе или параметрах sp_execute_external_script процедуры и возвращают ошибку неподдерживаемого типа.

В качестве обходного решения CAST столбец или значение поддерживаемого типа в Transact-SQL перед отправкой в внешний скрипт.

• курсор
• timestamp
• datetime2, datetimeoffset, time
• sql_variant
• текст, изображение
• xml
• hierarchyid, geometry, geography
• Определяемые пользователем типы CLR

Как правило, любой результирующий набор, который не может быть сопоставлен с типом данных Transact-SQL, выводится как NULL.

Ограничения, относящиеся к R

Если входные данные содержат значения datetime , которые не соответствуют допустимому диапазону значений в R, значения преобразуются в NA. Это необходимо, так как машинное обучение SQL разрешает более широкий диапазон значений, чем поддерживается на языке R.

Значения с плавающей запятой (например, +Inf, -Inf) NaNне поддерживаются в машинном обучении SQL, даже если оба языка используют IEEE 754. Текущее поведение просто отправляет значения в SQL напрямую; В результате клиент SQL выдает ошибку. Поэтому эти значения преобразуются в NULL.

Разрешения

Требуется разрешение БАЗЫ данных EXECUTE ANY EXTERNAL SCRIPT.

Примеры

В этом разделе содержатся примеры того, как эту хранимую процедуру можно использовать для выполнения скриптов R или Python с помощью Transact-SQL.

А. Возврат набора данных R в SQL Server

В следующем примере создается хранимая процедура, которая используется sp_execute_external_script для возврата набора данных Iris, включенного в R.

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 и формирование оценок на ее основе

В этом примере показано, как создавать sp_execute_external_script оценки в простой модели Python.

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.

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

Для оценки можно также применять собственную функцию PREDICT, которая обычно выполняется быстрее, так как не вызывает среду выполнения Python или R.

Связанный контент

• Машинное обучение SQL
• Что такое расширения языка SQL Server?
• Системные хранимые процедуры (Transact-SQL)
• CREATE EXTERNAL LIBRARY (Transact-SQL)
• sp_prepare (Transact SQL)
• sp_configure (Transact-SQL)
• Параметр конфигурации сервера external scripts enabled
• SERVERPROPERTY (Transact-SQL)
• SQL Server, объект External Scripts
• sys.dm_external_script_requests
• sys.dm_external_script_execution_stats