sp_execute_external_script (Transact-SQL)

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

La sp_execute_external_script procédure stockée exécute un script fourni en tant qu’argument d’entrée à la procédure et est utilisé avec Machine Learning Services et les extensions de langage.

Pour Machine Learning Services, Python et R sont des langages pris en charge. Pour les extensions de langage, Java est pris en charge, mais doit être défini avec CREATE EXTERNAL LANGUAGE.

Pour exécuter sp_execute_external_script, vous devez d’abord installer Machine Learning Services ou les extensions de langage. Pour plus d’informations, consultez Installer SQL Server Machine Learning Services (Python et R) sur Windows et Linux, ou installer des extensions de langage SQL Server sur Windows et Linux.

Conventions de la syntaxe Transact-SQL

Syntaxe

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

Arguments

[ @language = ] N’language'

Indique la langue du script. la langue est sysname. Les valeurs valides sont R, Python et n’importe quel langage défini avec CREATE EXTERNAL LANGUAGE (par exemple, Java).

[ @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.

[ @input_data_1_order_by_columns = ] N’input_data_1_order_by_columns'

Utilisé pour générer des modèles par partition. Spécifie le nom de la colonne utilisée pour classer le jeu de résultats, par exemple par nom de produit. 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_partition_by_columns = ] N’input_data_1_partition_by_columns'

Utilisé pour générer des modèles par partition. Spécifie le nom de la colonne utilisée pour segmenter les données, telles que la région géographique ou la date. 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.

[ @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

Important

L’arborescence des requêtes est contrôlée par le Machine Learning SQL et les utilisateurs ne peuvent pas effectuer d’opérations arbitraires sur la requête.

Permet sp_execute_external_script d’exécuter des scripts écrits dans une langue prise en charge. Les langages pris en charge sont Python et R utilisés avec Machine Learning Services, et tout langage défini avec CREATE EXTERNAL LANGUAGE (par exemple, Java) utilisé avec les extensions de langage.

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.

Vous pouvez contrôler les ressources utilisées par des scripts externes en configurant un pool de ressources externe. Pour plus d’informations, consultez CREATE EXTERNAL RESOURCE POOL. Des informations sur la charge de travail peuvent être obtenues à partir des vues de catalogue resource governor, des vues de gestion dynamique et des compteurs. Pour plus d’informations, consultez Affichages catalogue Resource Governor, Vues de gestion dynamique associées à Resource Governor et SQL Server, objet Scripts externes.

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.

Paramètres de modélisation de partition

Vous pouvez définir deux paramètres supplémentaires qui activent la modélisation sur des données partitionnée, où les partitions sont basées sur une ou plusieurs colonnes que vous fournissez qui segmentent naturellement un jeu de données en partitions logiques, créées et utilisées uniquement pendant l’exécution du script. Les colonnes contenant des valeurs répétées pour l’âge, le sexe, la région géographique, la date ou l’heure sont quelques exemples qui se prêtent à des jeux de données partitionnés.

Les deux paramètres sont input_data_1_partition_by_columns et input_data_1_order_by_columns, où le deuxième paramètre est utilisé pour classer le jeu de résultats. Les paramètres sont passés en tant qu’entrées avec sp_execute_external_script le script externe s’exécutant une fois pour chaque partition. Pour plus d’informations et d’exemples, consultez Tutoriel : Créer des modèles basés sur des partitions dans R sur SQL Server.

Vous pouvez exécuter un script en parallèle en spécifiant @parallel = 1. Si la requête d’entrée peut être parallélisée, vous devez définir @parallel = 1 dans le cadre de vos arguments sp_execute_external_scriptsur . Par défaut, l’optimiseur de requête fonctionne sous les @parallel = 1 tables ayant plus de 256 lignes, mais si vous souhaitez gérer cela explicitement, ce script inclut le paramètre comme démonstration.

Conseil

Pour les charges de travail d’entraînement, vous pouvez utiliser @parallel avec n’importe quel script d’entraînement arbitraire, même ceux qui utilisent des algorithmes non-Microsoft-rx. En règle générale, seuls les algorithmes RevoScaleR (avec le préfixe rx) offrent un parallélisme dans les scénarios d’entraînement dans SQL Server. Toutefois, avec les nouveaux paramètres de SQL Server 2019 (15.x) et versions ultérieures, vous pouvez paralléliser un script qui appelle des fonctions non spécifiquement conçues avec cette fonctionnalité.

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

Remarque

La diffusion en continu et le traitement parallèle ne sont pris en charge que dans Êdition Entreprise. Vous pouvez inclure les paramètres dans vos requêtes dans Édition Standard sans déclencher d’erreur, mais les paramètres n’ont aucun effet et les scripts R s’exécutent dans un seul processus.

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.

C. Générer un modèle R basé sur des données à partir de SQL Server

L’exemple suivant crée une procédure stockée qui utilise sp_execute_external_script pour générer un modèle iris et retourner le modèle.

Remarque

Cet exemple nécessite l’installation anticipée du package e1071 . Pour plus d’informations, consultez Installer des packages R avec 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

Pour générer un modèle semblable à l’aide de Python, vous devez remplacer l’identificateur de langage @language=N'R' par @language = N'Python' et apporter les modifications nécessaires à l’argument @script. Autrement, tous les paramètres fonctionnent de la même manière que pour R.

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