sp_execute_external_script (Transact-SQL)

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

Хранимая sp_execute_external_script процедура выполняет скрипт, предоставленный в качестве входного аргумента для процедуры, и используется с Машинное обучение службами и расширениями языка.

Для служб Машинное обучение поддерживаются языки Python и R. Для расширений языка Java поддерживается, но необходимо определить с помощью CREATE EXTERNAL LANGUAGE.

Для выполнения sp_execute_external_scriptнеобходимо сначала установить Машинное обучение службы или расширения языка. Дополнительные сведения см. в статье "Установка sql Server Машинное обучение Services (Python и R) в Windows и Linux, а также установка расширений языка SQL Server в Windows и Linux.

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

Указывает язык скрипта. язык — 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 }

Включите параллельное выполнение скриптов 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 ]

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

Замечания

Внимание

Дерево запросов управляется машинным обучением SQL, и пользователи не могут выполнять произвольные операции с запросом.

Используется sp_execute_external_script для выполнения скриптов, написанных на поддерживаемом языке. Поддерживаемые языки — Python и R, используемые с службами Машинное обучение, и любой язык, определенный с помощью CREATE EXTERNAL LANGUAGE (например, Java), используемый с расширениями языка.

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

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

Вы можете управлять ресурсами, используемыми внешними скриптами, путем настройки внешнего пула ресурсов. Дополнительные сведения см. в разделе CREATE EXTERNAL RESOURCE POOL. Сведения о рабочей нагрузке можно получить из представлений каталога регулятора ресурсов, динамических административных представлений и счетчиков. Дополнительные сведения см. в разделе "Представления каталога регулятора ресурсов", "Связанные динамические административные представления" и "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 данных во внешний скрипт, выполняющийся один раз для каждой секции. Дополнительные сведения и примеры см. в руководстве по созданию моделей на основе секций в R на SQL Server.

Вы можете выполнять скрипт параллельно, указав @parallel = 1. Если входной запрос можно параллелизировать, необходимо задать @parallel = 1 в качестве части аргументов sp_execute_external_scriptзначение . По умолчанию оптимизатор запросов работает в @parallel = 1 таблицах с более чем 256 строками, но если вы хотите явно обработать это, этот скрипт включает параметр в качестве демонстрации.

Совет

Для рабочих нагрузок обучения можно использовать @parallel с любым произвольным скриптом обучения, даже при использовании алгоритмов, отличных от Microsoft RX. Как правило, в SQL Server параллелизм в скриптах обучения предусмотрен только в алгоритмах RevoScaleR (с префиксом RX). Но с новыми параметрами в SQL Server 2019 (15.x) и более поздних версиях можно параллелизировать скрипт, который вызывает функции, не специально разработанные с этой возможностью.

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

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

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

Примечание.

Потоковая и параллельная обработка поддерживаются только в выпуск Enterprise. Параметры можно включить в запросы в выпуск Standard без возникновения ошибки, но параметры не влияют, а скрипты R выполняются в одном процессе.

Ограничения

Типы данных

Следующие типы данных не поддерживаются при использовании во входном запросе или параметрах 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.

В. Создание модели R на основе данных из SQL Server

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

Примечание.

В этом примере требуется предварительная установка пакета e1071 . Дополнительные сведения см. в разделе "Установка пакетов R с помощью sqlmlutils".

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.

Для оценки можно также применять собственную функцию 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