sp_execute_external_script (Transact-SQL)

Se aplica a:yes SQL Server 2016 (13.x) y versiones posteriores YesAzure SQL Managed Instance

El procedimiento almacenado sp_execute_external_script ejecuta un script proporcionado como argumento de entrada para el procedimiento y se usa con Machine Learning Services y Extensiones de lenguaje.

Para Machine Learning Services, Python y R son lenguajes compatibles. En el caso de las extensiones de lenguaje, Se admite Java, pero debe definirse con CREATE EXTERNAL LANGUAGE.

Para ejecutar sp_execute_external_script, primero debe instalar Machine Learning Services o Extensiones de lenguaje. Para obtener más información, consulte Instalación de SQL Server Machine Learning Services (Python y R) en Windows y Linux, o Instalación de extensiones de lenguaje SQL Server en Windows y Linux.

El procedimiento almacenado sp_execute_external_script ejecuta un script proporcionado como argumento de entrada para el procedimiento y se usa con Machine Learning Services en SQL Server 2017.

Para Machine Learning Services, Python y R son lenguajes compatibles.

Para ejecutar sp_execute_external_script, primero debe instalar Machine Learning Services. Para obtener más información, consulte Instalación de SQL Server Machine Learning Services (Python y R) en Windows.

El procedimiento almacenado sp_execute_external_script ejecuta un script proporcionado como argumento de entrada para el procedimiento y se usa con R Services en SQL Server 2016.

Para R Services, R es el lenguaje admitido.

Para ejecutar sp_execute_external_script, primero debe instalar R Services. Para obtener más información, consulte Instalación de SQL Server Machine Learning Services (Python y R) en Windows.

El procedimiento almacenado sp_execute_external_script ejecuta un script proporcionado como argumento de entrada para el procedimiento y se usa con Machine Learning Services en Azure SQL Managed Instance.

Para Machine Learning Services, Python y R son lenguajes compatibles.

Para ejecutar sp_execute_external_script, primero debe habilitar Machine Learning Services. Para obtener más información, consulte la documentación de Machine Learning Services en Azure SQL Managed Instance.

Topic link iconConvenciones de sintaxis de Transact-SQL

Syntax

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 ] ]

Sintaxis para SQL Server 2017 y versiones anteriores

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 ] ]

Argumentos

@language = N'language'

Indica el lenguaje de script. language es sysname. Los valores válidos son R, Python y cualquier lenguaje definido con CREATE EXTERNAL LANGUAGE (por ejemplo, Java).

Indica el lenguaje de script. language es sysname. En SQL Server 2017, los valores válidos son R y Python.

Indica el lenguaje de script. language es sysname. En SQL Server 2016, el único valor válido es R.

Indica el lenguaje de script. language es sysname. En Azure SQL Managed Instance, los valores válidos son R y Python.

@script = Script de lenguaje externo N'script' especificado como una entrada literal o variable. script es nvarchar(max).

[ @input_data_1 = N'input_data_1' ]Especifica los datos de entrada utilizados por el script externo en forma de consulta de Transact-SQL. El tipo de datos de input_data_1 es nvarchar(max).

[ @input_data_1_name = N'input_data_1_name' ] Especifica el nombre de la variable utilizada para representar la consulta definida por @input_data_1. El tipo de datos de la variable en el script externo depende del lenguaje. En el caso de R, la variable de entrada es una trama de datos. En el caso de Python, la entrada debe ser tabular. input_data_1_name es sysname. El valor predeterminado es InputDataSet.

[ @input_data_1_order_by_columns = N'input_data_1_order_by_columns' ] Se usa para compilar modelos por partición. Especifica el nombre de la columna utilizada para ordenar el conjunto de resultados, por ejemplo por nombre de producto. El tipo de datos de la variable en el script externo depende del lenguaje. En el caso de R, la variable de entrada es una trama de datos. En el caso de Python, la entrada debe ser tabular.

[ @input_data_1_partition_by_columns = N'input_data_1_partition_by_columns' ] Se usa para compilar modelos por partición. Especifica el nombre de la columna utilizada para segmentar los datos, como la región geográfica o la fecha. El tipo de datos de la variable en el script externo depende del lenguaje. En el caso de R, la variable de entrada es una trama de datos. En el caso de Python, la entrada debe ser tabular.

[ @output_data_1_name = N'output_data_1_name' ]Especifica el nombre de la variable en el script externo que contiene los datos que se van a devolver a SQL Server al finalizar la llamada al procedimiento almacenado. El tipo de datos de la variable en el script externo depende del lenguaje. Para R, la salida debe ser una trama de datos. Para Python, la salida debe ser una trama de datos pandas. output_data_1_name es sysname. El valor predeterminado es OutputDataSet.

[ @parallel = 0 | 1 ] Habilite la ejecución en paralelo de scripts de R estableciendo el @parallel parámetro en 1. El valor predeterminado para este parámetro es 0 (sin paralelismo). Si @parallel = 1 y la salida se transmiten directamente al equipo cliente, se requiere la WITH RESULT SETS cláusula y se debe especificar un esquema de salida.

  • En el caso de los scripts de R que no usan funciones de RevoScaleR, el uso del @parallel parámetro puede ser beneficioso para procesar grandes conjuntos de datos, suponiendo que el script se pueda paralelizar trivialmente. Por ejemplo, al usar la función R predict con un modelo para generar nuevas predicciones, establezca @parallel = 1 como sugerencia en el motor de consultas. Si la consulta se puede paralelizar, las filas se distribuyen según la configuración MAXDOP .

  • En el caso de los scripts de R que usan funciones de RevoScaleR, el procesamiento paralelo se controla automáticamente y no se debe especificar @parallel = 1 en la llamada sp_execute_external_script .

[ @params = N'@parameter_name data_type [ OUT | OUTPUT ] [ ,...n ]' ] Lista de declaraciones de parámetros de entrada que se usan en el script externo.

[ @parameter1 = 'value1' [ OUT | OUTPUT ] [ ,...n ] ] Lista de valores para los parámetros de entrada utilizados por el script externo.

Comentarios

Importante

El árbol de consulta se controla mediante SQL aprendizaje automático y los usuarios no pueden realizar operaciones arbitrarias en la consulta.

Use sp_execute_external_script para ejecutar scripts escritos en un lenguaje compatible. Los lenguajes admitidos son Python y R que se usan con Machine Learning Services y cualquier lenguaje definido con CREATE EXTERNAL LANGUAGE (por ejemplo, Java) usado con extensiones de lenguaje.

Use sp_execute_external_script para ejecutar scripts escritos en un lenguaje compatible. Los lenguajes admitidos son Python y R en SQL Server 2017 Machine Learning Services.

Use sp_execute_external_script para ejecutar scripts escritos en un lenguaje compatible. El único lenguaje admitido es R en SQL Server 2016 R Services.

Use sp_execute_external_script para ejecutar scripts escritos en un lenguaje compatible. Los lenguajes admitidos son Python y R en Azure SQL Managed Instance Machine Learning Services.

De forma predeterminada, los conjuntos de resultados devueltos por este procedimiento almacenado son resultados con columnas sin nombre. Los nombres de columna usados en un script son locales para el entorno de scripting y no se reflejan en el conjunto de resultados generado. Para asignar un nombre a las columnas del conjunto de resultados, use la WITH RESULT SET cláusula de EXECUTE.

Además de devolver un conjunto de resultados, puede devolver valores escalares al uso de parámetros OUTPUT.

Puede controlar los recursos usados por scripts externos mediante la configuración de un grupo de recursos externo. Para obtener más información, vea CREATE EXTERNAL RESOURCE POOL (Transact-SQL) . La información sobre la carga de trabajo se puede obtener de las vistas de catálogo del regulador de recursos, las DMV y los contadores. Para obtener más información, vea Resource Governor vistas de catálogo (Transact-SQL),Resource Governor vistas de administración dinámica relacionadas (Transact-SQL) y SQL Server, objeto scripts externos.

Supervisión de la ejecución de scripts

Supervise la ejecución de scripts mediante sys.dm_external_script_requests y sys.dm_external_script_execution_stats.

Parámetros para el modelado de particiones

Puede establecer dos parámetros adicionales que permitan el modelado en datos con particiones, donde las particiones se basan en una o varias columnas que proporcione que segmente de forma natural un conjunto de datos en particiones lógicas creadas y usadas solo durante la ejecución del script. Las columnas que contienen valores repetidos para age, gender, geographic region, date or time, son algunos ejemplos que se prestan a conjuntos de datos particionados.

Los dos parámetros son input_data_1_partition_by_columns y input_data_1_order_by_columns, donde se usa el segundo parámetro para ordenar el conjunto de resultados. Los parámetros se pasan como entradas a sp_execute_external_script con el script externo que se ejecuta una vez para cada partición. Para obtener más información y ejemplos, vea Tutorial: Creación de modelos basados en particiones.

Puede ejecutar el script en paralelo especificando @parallel=1. Si la consulta de entrada se puede paralelizar, debe establecer @parallel=1 como parte de los argumentos sp_execute_external_scripten . De forma predeterminada, el optimizador de consultas funciona en @parallel=1 en tablas que tienen más de 256 filas, pero si desea controlar esto explícitamente, este script incluye el parámetro como demostración.

Sugerencia

En las cargas de trabajo de entrenamiento, puede usar @parallel con cualquier script de entrenamiento arbitrario, incluso aquellos que usan algoritmos rx que no son de Microsoft. Normalmente, solo los algoritmos de RevoScaleR (con el prefijo rx) ofrecen paralelismo en escenarios de entrenamiento de SQL Server. Pero con los nuevos parámetros de SQL Server 2019 y versiones posteriores, puede paralelizar un script que llama a funciones no diseñadas específicamente con esa funcionalidad.

Ejecución de streaming para scripts de Python y R

El streaming permite que el script de Python o R funcione con más datos de los que pueden caber en la memoria. Para controlar el número de filas pasadas durante el streaming, especifique un valor entero para el parámetro , @r_rowsPerRead en la @params colección . Por ejemplo, si va a entrenar un modelo que usa datos muy amplios, puede ajustar el valor para leer menos filas, para asegurarse de que todas las filas se pueden enviar en un fragmento de datos. También puede usar este parámetro para administrar el número de filas que se leen y procesan a la vez para mitigar los problemas de rendimiento del servidor.

Tanto el @r_rowsPerRead parámetro para streaming como el @parallel argumento deben considerarse sugerencias. Para que se aplique la sugerencia, debe ser posible generar un plan de consulta de SQL que incluya el procesamiento paralelo. Si esto no es posible, no se puede habilitar el procesamiento paralelo.

Nota

El streaming y el procesamiento paralelo solo se admiten en Enterprise Edition. Puede incluir los parámetros en las consultas en Standard Edition sin generar un error, pero los parámetros no tienen ningún efecto y los scripts de R se ejecutan en un único proceso.

Restricciones

Tipos de datos

Los siguientes tipos de datos no se admiten cuando se usan en la consulta de entrada o los parámetros del procedimiento de sp_execute_external_script y devuelven un error de tipo no admitido.

Como solución alternativa, convierta la columna o el valor en un tipo admitido en Transact-SQL antes de enviarlo al script externo.

  • cursor

  • timestamp

  • datetime2, datetimeoffset, time

  • sql_variant

  • text, image

  • xml

  • hierarchyid, geometry, geography

  • Tipos definidos por el usuario CLR

En general, cualquier conjunto de resultados que no se pueda asignar a un tipo de datos transact-SQL, se genera como NULL.

Restricciones específicas de R

Si la entrada incluye valores datetime que no se ajustan al intervalo permitido de valores en R, los valores se convierten en NA. Esto es necesario porque SQL aprendizaje automático permite un mayor intervalo de valores que se admite en el lenguaje R.

Los valores float (por ejemplo, +Inf, -Inf, NaN) no se admiten en SQL aprendizaje automático aunque ambos lenguajes usen IEEE 754. El comportamiento actual simplemente envía los valores a SQL directamente; como resultado, el cliente SQL produce un error. Por lo tanto, estos valores se convierten en NULL.

Permisos

Requiere el permiso de base de datos EXECUTE ANY EXTERNAL SCRIPT .

Ejemplos

Esta sección contiene ejemplos de cómo se puede usar este procedimiento almacenado para ejecutar scripts de R o Python mediante Transact-SQL.

A Devolver un conjunto de datos de R a SQL Server

En el ejemplo siguiente se crea un procedimiento almacenado que usa sp_execute_external_script para devolver el conjunto de datos Iris incluido con 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. Creación de un modelo de Python y generar puntuaciones a partir de él

En este ejemplo se muestra cómo usar sp_execute_external_script para generar puntuaciones en un modelo de Python simple.

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

Los encabezados de columna usados en el código de Python no se generan para SQL Server; por lo tanto, use la instrucción WITH RESULT para especificar los nombres de columna y los tipos de datos para SQL usar.

C. Generación de un modelo de R basado en datos de SQL Server

En el ejemplo siguiente se crea un procedimiento almacenado que usa sp_execute_external_script para generar un modelo iris y devolver el modelo.

Nota

En este ejemplo se requiere una instalación anticipada del paquete e1071. Para obtener más información, consulte Instalación de paquetes de R adicionales en SQL Server.

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

Para generar un modelo similar mediante Python, tendría que cambiar el identificador de idioma de @language=N'R' a @language = N'Python'y realizar las modificaciones necesarias para el argumento @script. En caso contrario, todos los parámetros funcionan del mismo modo que para R.

Para puntuar, también puede usar la función nativa PREDICT, que es normalmente más rápida porque evita llamar al runtime de Python o R.

Consulte también