sp_execute_external_script (Transact-SQL)

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL Managed Instance

Conventions de la syntaxe 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 ] ]

Arguments

[ @language = ] N’language'

[ @script = ] N’script'

Script de langage externe spécifié en tant qu’entrée littérale ou variable. le script est nvarchar(max).

[ @input_data_1 = ] N’input_data_1'

Spécifie les données d’entrée utilisées par le script externe sous la forme d’une requête Transact-SQL. Le type de données de input_data_1 est nvarchar(max).

[ @input_data_1_name = ] N’input_data_1_name'

Spécifie le nom de la variable utilisée pour représenter la requête définie par @input_data_1. Le type de données de la variable dans le script externe dépend du langage. Pour R, la variable d’entrée est une trame de données. Pour Python, l’entrée doit être tabulaire. input_data_1_name est sysname. La valeur par défaut est InputDataSet.

[ @output_data_1_name = ] N’output_data_1_name'

Spécifie le nom de la variable dans le script externe qui contient les données à retourner à SQL Server à la fin de l’appel de procédure stockée. Le type de données de la variable dans le script externe dépend du langage. Pour R, la sortie doit être une trame de données. Pour Python, la sortie doit être une trame de données pandas. output_data_1_name est sysname. La valeur par défaut est OutputDataSet.

[ @parallel = ] { 0 | 1 }

Activez l’exécution parallèle de scripts R en définissant le @parallel paramètre sur 1. La valeur par défaut de ce paramètre est 0 (aucun parallélisme). Si @parallel = 1 et la sortie est diffusée directement sur l’ordinateur client, la WITH RESULT SETS clause est requise et un schéma de sortie doit être spécifié.

• Pour les scripts R qui n’utilisent pas de fonctions RevoScaleR, l’utilisation du @parallel paramètre peut être bénéfique pour le traitement de grands jeux de données, en supposant que le script peut être parallélisé trivialement. Par exemple, lorsque vous utilisez la fonction R predict avec un modèle pour générer de nouvelles prédictions, définissez @parallel = 1 comme indicateur le moteur de requête. Si la requête peut être parallélisée, les lignes sont distribuées en fonction du paramètre MAXDOP .

• Pour les scripts R qui utilisent des fonctions RevoScaleR, le traitement parallèle est géré automatiquement et vous ne devez pas spécifier @parallel = 1 l’appel sp_execute_external_script .

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

Liste des déclarations de paramètres d’entrée utilisées dans le script externe.

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

Liste des valeurs des paramètres d’entrée utilisés par le script externe.

Notes

Par défaut, les jeux de résultats retournés par cette procédure stockée sont générés avec des colonnes sans nom. Les noms de colonnes utilisés dans un script sont locaux dans l’environnement de script et ne sont pas reflétés dans le jeu de résultats généré. Pour nommer les colonnes du jeu de résultats, utilisez la WITH RESULT SET clause EXECUTE.

En plus de retourner un jeu de résultats, vous pouvez retourner des valeurs scalaires à l’aide de paramètres OUTPUT.

Surveiller l’exécution du script

Surveillez l’exécution du script à l’aide de sys.dm_external_script_requests et de sys.dm_external_script_execution_stats.

Exécution de streaming pour les scripts Python et R

La diffusion en continu permet au script Python ou R d’utiliser plus de données que de mémoire. Pour contrôler le nombre de lignes passées pendant la diffusion en continu, spécifiez une valeur entière pour le paramètre, @r_rowsPerRead dans la @params collection. Par exemple, si vous entraînez un modèle qui utilise des données très larges, vous pouvez ajuster la valeur pour lire moins de lignes pour vous assurer que toutes les lignes peuvent être envoyées dans un bloc de données. Vous pouvez également utiliser ce paramètre pour gérer le nombre de lignes lues et traitées à la fois pour atténuer les problèmes de performances du serveur.

Le @r_rowsPerRead paramètre de diffusion en continu et l’argument @parallel doivent être considérés comme des indicateurs. Pour que l’indicateur soit appliqué, il doit être possible de générer un plan de requête SQL qui inclut un traitement parallèle. Si ce n’est pas possible, le traitement parallèle ne peut pas être activé.

Limites

Types de données

Les types de données suivants ne sont pas pris en charge lorsqu’ils sont utilisés dans la requête d’entrée ou les paramètres de sp_execute_external_script procédure et retournent une erreur de type non prise en charge.

Pour contourner ce problème, CAST la colonne ou la valeur d’un type pris en charge dans Transact-SQL avant de l’envoyer au script externe.

• cursor
• timestamp
• datetime2, datetimeoffset, heure
• sql_variant
• texte, image
• xml
• hierarchyid, geometry, geography
• types CLR définis par l'utilisateur

En règle générale, tout jeu de résultats qui ne peut pas être mappé à un type de données Transact-SQL est de sortie en tant que NULL.

Restrictions spécifiques à R

Si l’entrée inclut des valeurs datetime qui ne correspondent pas à la plage autorisée de valeurs en R, les valeurs sont converties NAen . Cela est nécessaire, car le Machine Learning SQL autorise une plus grande plage de valeurs que celle prise en charge dans le langage R.

Les valeurs float (par exemple, +Inf, , -InfNaN) ne sont pas prises en charge dans le Machine Learning SQL, même si les deux langages utilisent IEEE 754. Le comportement actuel envoie simplement les valeurs directement à SQL ; par conséquent, le client SQL génère une erreur. Par conséquent, ces valeurs sont converties en NULL.

autorisations

Nécessite l’autorisation de base de données EXECUTE ANY EXTERNAL SCRIPT.

Exemples

Cette section contient des exemples de la façon dont cette procédure stockée peut être utilisée pour exécuter des scripts R ou Python à l’aide de Transact-SQL.

A. Renvoyer un jeu de données R à SQL Server

L’exemple suivant crée une procédure stockée qui utilise sp_execute_external_script pour retourner le jeu de données Iris inclus avec 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. Créer un modèle Python et générer des scores à partir de ce modèle

Cet exemple montre comment utiliser sp_execute_external_script pour générer des scores sur un modèle 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

Les en-têtes de colonne utilisés dans le code Python ne sont pas générés vers SQL Server ; utilisez donc l’instruction WITH RESULT pour spécifier les noms de colonnes et les types de données que SQL doit utiliser.

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

Pour calculer les scores, vous pouvez également utiliser la fonction PREDICT native, qui est généralement plus rapide car elle évite d’appeler le runtime Python ou R.

Contenu connexe

• Machine Learning SQL
• Présentation des extensions de langage SQL Server
• Procédures stockées système (Transact-SQL)
• CREATE EXTERNAL LIBRARY (Transact-SQL)
• sp_prepare (Transact SQL)
• sp_configure (Transact-SQL)
• external scripts enabled (option de configuration de serveur)
• SERVERPROPERTY (Transact-SQL)
• SQL Server, objet Scripts externes
• sys.dm_external_script_requests
• sys.dm_external_script_execution_stats