sp_execute_external_script (Transact-SQL)

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

El sp_execute_external_script procedimiento almacenado 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 de SQL Server en Windows y Linux.

Convenciones de sintaxis de Transact-SQL

Sintaxis

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

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).

[ @script = ] N'script'

Script de lenguaje externo 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 una consulta 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 idioma. Para R, la variable de entrada es una trama de datos. Para 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 idioma. Para R, la variable de entrada es una trama de datos. Para 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 idioma. Para R, la variable de entrada es una trama de datos. Para 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 tras la finalización de la llamada al procedimiento almacenado. El tipo de datos de la variable en el script externo depende del idioma. 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 1en . El valor predeterminado de 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 sp_execute_external_script llamada.

[ @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 el aprendizaje automático de SQL y los usuarios no pueden realizar operaciones arbitrarias en la consulta.

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

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 EXECUTE.

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

Puede controlar los recursos utilizados por scripts externos configurando un grupo de recursos externo. Para obtener más información, consulte CREATE EXTERNAL RESOURCE POOL. La información sobre la carga de trabajo se puede obtener de las vistas del catálogo del regulador de recursos, las DMV y los contadores. Para obtener más información, vea Vistas de catálogo de Resource Governor, Vistas de administración dinámica relacionadas con Resource Governor 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, creados y usados solo durante la ejecución del script. Las columnas que contienen valores repetidos para edad, sexo, región geográfica, fecha u hora, 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 en R en SQL Server.

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 (15.x) y versiones posteriores, puede paralelizar un script que llama a funciones que no están 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 está entrenando un modelo que usa datos muy amplios, podría 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 al mismo tiempo 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 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 de 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.

Limitaciones

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 y devuelven un error de sp_execute_external_script tipo no admitido.

Como solución alternativa, CAST la columna o el valor a 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 el aprendizaje automático de SQL permite un intervalo mayor de valores de los que se admiten en el lenguaje R.

Los valores float (por ejemplo, +Inf, -Inf, NaN) no se admiten en el aprendizaje automático de SQL 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 EXECUTE ANY EXTERNAL SCRIPT database.

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 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. 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 sencillo.

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 en SQL Server; Por lo tanto, use la instrucción WITH RESULT para especificar los nombres de columna y los tipos de datos para que SQL los use.

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 la instalación anticipada del paquete e1071 . Para obtener más información, consulte Instalación de paquetes de R con 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

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.

Contenido relacionado

• Aprendizaje automático de SQL
• ¿Qué son las extensiones de lenguaje de SQL Server?
• Procedimientos almacenados del sistema (Transact-SQL)
• CREATE EXTERNAL LIBRARY (Transact-SQL)
• sp_prepare (Transact SQL)
• sp_configure (Transact-SQL)
• Opción de configuración de servidor Scripts externos habilitados
• SERVERPROPERTY (Transact-SQL)
• SQL Server, objeto Scripts externos
• sys.dm_external_script_requests
• sys.dm_external_script_execution_stats