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.
Хранимая sp_execute_external_script
процедура выполняет скрипт, предоставленный в качестве входного аргумента для процедуры, и используется с службами Машинное обучение в SQL Server 2017 (14.x).
Для служб Машинное обучение поддерживаются языки Python и R.
Для выполнения sp_execute_external_script
необходимо сначала установить службы Машинное обучение. Дополнительные сведения см. в статье "Установка SQL Server Машинное обучение Services (Python и R) в Windows.
Хранимая sp_execute_external_script
процедура выполняет скрипт, предоставленный в качестве входного аргумента для процедуры, и используется со службами R в SQL Server 2016 (13.x).
Для служб R язык R является поддерживаемым языком.
Для выполнения sp_execute_external_script
необходимо сначала установить службы R. Дополнительные сведения см. в статье "Установка SQL Server Машинное обучение Services (Python и R) в Windows.
Хранимая sp_execute_external_script
процедура выполняет скрипт, предоставленный в качестве входного аргумента процедуры, и используется с службами Машинное обучение в Управляемый экземпляр SQL Azure.
Для служб Машинное обучение поддерживаются языки Python и R.
Для выполнения sp_execute_external_script
необходимо сначала включить службы Машинное обучение. Дополнительные сведения см. в Управляемый экземпляр 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 ] ]
[ ; ]
Синтаксис для SQL Server 2017 и предыдущих версий
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'
Указывает язык скрипта. язык — sysname. Допустимые значения: R, Python и любой язык, определенный с помощью CREATE EXTERNAL LANGUAGE (например, Java).
Указывает язык скрипта. язык — sysname. В SQL Server 2017 (14.x) допустимые значения — R и Python.
Указывает язык скрипта. язык — sysname. В SQL Server 2016 (13.x) единственным допустимым значением является R.
Указывает язык скрипта. язык — sysname. В Управляемый экземпляр SQL Azure допустимые значения — R и Python.
[ @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
параметра может оказаться полезным для обработки больших наборов данных, предполагая, что скрипт можно тривиально параллелизировать. Например, при использовании функции Rpredict
с моделью для создания новых прогнозов задайте@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), используемый с расширениями языка.
Используется sp_execute_external_script
для выполнения скриптов, написанных на поддерживаемом языке. Поддерживаемые языки — Python и R в службах SQL Server 2017 (14.x Машинное обучение).
Используется sp_execute_external_script
для выполнения скриптов, написанных на поддерживаемом языке. Единственным поддерживаемым языком является R в службах R SQL Server 2016 (13.x).
Используется sp_execute_external_script
для выполнения скриптов, написанных на поддерживаемом языке. Поддерживаемые языки — Python и R в службах Управляемый экземпляр SQL Azure Машинное обучение.
По умолчанию результирующие наборы, возвращаемые этой хранимой процедурой, выводятся с неназванными столбцами. Имена столбцов, используемые в скрипте, являются локальными для среды сценариев и не отражаются в выходном результирующем наборе. Чтобы назвать столбцы результирующего набора, используйте WITH RESULT SET
предложение EXECUTE
.
Помимо возврата результирующий набор, можно возвращать скалярные значения для использования параметров OUTPUT.
Вы можете управлять ресурсами, используемыми внешними скриптами, путем настройки внешнего пула ресурсов. Дополнительные сведения см. в разделе CREATE EXTERNAL RESOURCE POOL (Transact-SQL). Сведения о рабочей нагрузке можно получить из представлений каталога регулятора ресурсов, динамических административных представлений и счетчиков. Дополнительные сведения см. в статьях "Представления каталога регулятора ресурсов" (Transact-SQL), связанные с динамическими представлениями управления ресурсами (Transact-SQL) и 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
данных во внешний скрипт, выполняющийся один раз для каждой секции. Дополнительные сведения и примеры см. в руководстве по созданию моделей на основе секций.
Вы можете выполнять скрипт параллельно, указав @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.
C. Создание модели R на основе данных из SQL Server
В следующем примере создается хранимая процедура, которая используется sp_execute_external_script
для создания модели ириса и возврата модели.
Примечание.
В этом примере требуется предварительная установка пакета e1071 . Дополнительные сведения см. в разделе "Установка дополнительных пакетов R" на SQL Server.
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