sp_execute_external_script (Transact-SQL)

Si applica a: SQL Server 2016 (13.x) e versioni successive Istanza gestita di SQL di Azure

La sp_execute_external_script stored procedure esegue uno script fornito come argomento di input per la procedura e viene usato con Machine Learning Services ed estensioni del linguaggio.

Per Machine Learning Services, Python e R sono linguaggi supportati. Per le estensioni del linguaggio, Java è supportato, ma deve essere definito con CREATE EXTERNAL LANGUAGE.

Per eseguire sp_execute_external_script, è prima necessario installare Machine Learning Services o le estensioni del linguaggio. Per altre informazioni, vedere Installare Machine Learning Services (Python e R) di SQL Server in Windows e Linux oppure installare le estensioni del linguaggio di SQL Server in Windows e Linux.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

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

Argomenti

[ @language = ] N'language'

Indica il linguaggio di script. language è sysname. I valori validi sono R, Python e qualsiasi linguaggio definito con CREATE EXTERNAL LANGUAGE (ad esempio, Java).

[ @script = ] N'script'

Script del linguaggio esterno specificato come input letterale o variabile. script è nvarchar(max).

[ @input_data_1 = ] N'input_data_1'

Specifica i dati di input usati dallo script esterno sotto forma di query Transact-SQL. Il tipo di dati di input_data_1 è nvarchar(max).

[ @input_data_1_name = ] N'input_data_1_name'

Specifica il nome della variabile utilizzata per rappresentare la query definita da @input_data_1. Il tipo di dati della variabile nello script esterno dipende dal linguaggio. Per R, la variabile di input è un frame di dati. Per Python, l'input deve essere tabulare. input_data_1_name è sysname. Il valore predefinito è InputDataSet.

[ @input_data_1_order_by_columns = ] N'input_data_1_order_by_columns'

Usato per compilare modelli per partizione. Specifica il nome della colonna utilizzata per ordinare il set di risultati, ad esempio in base al nome del prodotto. Il tipo di dati della variabile nello script esterno dipende dal linguaggio. Per R, la variabile di input è un frame di dati. Per Python, l'input deve essere tabulare.

[ @input_data_1_partition_by_columns = ] N'input_data_1_partition_by_columns'

Usato per compilare modelli per partizione. Specifica il nome della colonna utilizzata per segmentare i dati, ad esempio l'area geografica o la data. Il tipo di dati della variabile nello script esterno dipende dal linguaggio. Per R, la variabile di input è un frame di dati. Per Python, l'input deve essere tabulare.

[ @output_data_1_name = ] N'output_data_1_name'

Specifica il nome della variabile nello script esterno che contiene i dati da restituire a SQL Server al termine della chiamata alla stored procedure. Il tipo di dati della variabile nello script esterno dipende dal linguaggio. Per R, l'output deve essere un frame di dati. Per Python, l'output deve essere un frame di dati pandas. output_data_1_name è sysname. Il valore predefinito è OutputDataSet.

[ @parallel = ] { 0 | 1 }

Abilitare l'esecuzione parallela degli script R impostando il @parallel parametro su 1. Il valore predefinito per questo parametro è 0 (nessun parallelismo). Se @parallel = 1 e l'output viene trasmesso direttamente al computer client, la WITH RESULT SETS clausola è obbligatoria e deve essere specificato uno schema di output.

• Per gli script R che non usano le funzioni RevoScaleR, l'uso del @parallel parametro può essere utile per l'elaborazione di set di dati di grandi dimensioni, presupponendo che lo script possa essere facilmente parallelizzato. Ad esempio, quando si usa la funzione R predict con un modello per generare nuove stime, impostare @parallel = 1 come hint per il motore di query. Se la query può essere parallelizzata, le righe vengono distribuite in base all'impostazione MAXDOP .

• Per gli script R che usano le funzioni RevoScaleR, l'elaborazione parallela viene gestita automaticamente e non è consigliabile specificare @parallel = 1 per la sp_execute_external_script chiamata.

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

Elenco di dichiarazioni di parametri di input utilizzate nello script esterno.

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

Elenco di valori per i parametri di input usati dallo script esterno.

Osservazioni:

Importante

L'albero delle query è controllato da Machine Learning SQL e gli utenti non possono eseguire operazioni arbitrarie sulla query.

Usare sp_execute_external_script per eseguire script scritti in un linguaggio supportato. I linguaggi supportati sono Python e R usati con Machine Learning Services e qualsiasi linguaggio definito con CREATE EXTERNAL LANGUAGE (ad esempio, Java) usato con le estensioni del linguaggio.

Per impostazione predefinita, i set di risultati restituiti da questa stored procedure vengono restituiti con colonne senza nome. I nomi di colonna usati all'interno di uno script sono locali per l'ambiente di scripting e non si riflettono nel set di risultati restituito. Per assegnare un nome alle colonne del set di risultati, usare la WITH RESULT SET clausola EXECUTE.

Oltre a restituire un set di risultati, è possibile restituire valori scalari all'uso dei parametri OUTPUT.

È possibile controllare le risorse usate da script esterni configurando un pool di risorse esterno. Per altre informazioni, vedere CREATE EXTERNAL RESOURCE POOL. Le informazioni sul carico di lavoro possono essere ottenute dalle viste del catalogo di Resource Governor, dalle DMV e dai contatori. Per altre informazioni, vedere Viste del catalogo di Resource Governor, Viste a gestione dinamica correlate a Resource Governor e SQL Server, oggetto Script esterni.

Monitorare l'esecuzione di script

Monitorare l'esecuzione di script usando sys.dm_external_script_requests e sys.dm_external_script_execution_stats.

Parametri per la modellazione delle partizioni

È possibile impostare due parametri aggiuntivi che consentono la modellazione sui dati partizionati, in cui le partizioni sono basate su una o più colonne fornite che segmentano naturalmente un set di dati in partizioni logiche, create e usate solo durante l'esecuzione dello script. Le colonne contenenti valori ripetuti per età, sesso, area geografica, data o ora sono alcuni esempi che si prestano a set di dati partizionati.

I due parametri sono input_data_1_partition_by_columns e input_data_1_order_by_columns, dove viene usato il secondo parametro per ordinare il set di risultati. I parametri vengono passati come input a sp_execute_external_script con lo script esterno in esecuzione una volta per ogni partizione. Per altre informazioni ed esempi, vedere Esercitazione: Creare modelli basati su partizioni in R in SQL Server.

È possibile eseguire lo script in parallelo specificando @parallel = 1. Se la query di input può essere parallelizzata, è necessario impostare @parallel = 1 come parte degli argomenti su sp_execute_external_script. Per impostazione predefinita, Query Optimizer opera @parallel = 1 in nelle tabelle con più di 256 righe, ma se si vuole gestire in modo esplicito questo script include il parametro come dimostrazione.

Suggerimento

Per i carichi di lavoro di training, è possibile usare @parallel con qualsiasi script di training arbitrario, anche quelli che usano algoritmi non Microsoft Rx. In genere, solo gli algoritmi RevoScaleR (con il prefisso rx) offrono il parallelismo negli scenari di training in SQL Server. Tuttavia, con i nuovi parametri in SQL Server 2019 (15.x) e versioni successive, è possibile parallelizzare uno script che chiama funzioni non appositamente progettato con tale funzionalità.

Esecuzione di streaming per script Python e R

Lo streaming consente di usare lo script Python o R con più dati di quanto possa essere inserito in memoria. Per controllare il numero di righe passate durante lo streaming, specificare un valore intero per il parametro , @r_rowsPerRead nella @params raccolta. Ad esempio, se si esegue il training di un modello che usa dati molto ampi, è possibile modificare il valore in modo da leggere meno righe, per assicurarsi che tutte le righe possano essere inviate in un unico blocco di dati. È anche possibile usare questo parametro per gestire il numero di righe in lettura ed elaborazione contemporaneamente, per attenuare i problemi di prestazioni del server.

Sia il parametro per lo @r_rowsPerRead streaming che l'argomento @parallel devono essere considerati hint. Per applicare l'hint, è necessario generare un piano di query SQL che include l'elaborazione parallela. Se non è possibile, l'elaborazione parallela non può essere abilitata.

Nota

Il flusso e l'elaborazione parallela sono supportati solo in edizione Enterprise. È possibile includere i parametri nelle query in edizione Standard senza generare un errore, ma i parametri non hanno alcun effetto e gli script R vengono eseguiti in un singolo processo.

Limiti

Tipo di dati

I tipi di dati seguenti non sono supportati quando vengono usati nella query di input o nei parametri della routine e restituiscono un errore di sp_execute_external_script tipo non supportato.

Come soluzione alternativa, CAST la colonna o il valore a un tipo supportato in Transact-SQL prima di inviarlo allo script esterno.

• cursor
• timestamp
• datetime2, datetimeoffset, time
• sql_variant
• text, image
• xml
• hierarchyid, geometry, geography
• Tipi CLR definiti dall'utente

In generale, qualsiasi set di risultati di cui non è possibile eseguire il mapping a un tipo di dati Transact-SQL viene restituito come NULL.

Restrizioni specifiche di R

Se l'input include valori datetime che non rientrano nell'intervallo consentito di valori in R, i valori vengono convertiti in NA. Questa operazione è necessaria perché l'apprendimento automatico di SQL consente un intervallo di valori superiore a quello supportato nel linguaggio R.

I valori float (ad esempio, +Inf, -Inf, ) NaNnon sono supportati in Machine Learning SQL anche se entrambi i linguaggi usano IEEE 754. Il comportamento corrente invia solo i valori direttamente a SQL; di conseguenza, il client SQL genera un errore. Di conseguenza, questi valori vengono convertiti in NULL.

Autorizzazioni

È richiesta l'autorizzazione EXECUTE ANY EXTERNAL SCRIPT per il database.

Esempi

Questa sezione contiene esempi di come questa stored procedure può essere usata per eseguire script R o Python usando Transact-SQL.

R. Restituire un set di dati R a SQL Server

Nell'esempio seguente viene creata una stored procedure che usa sp_execute_external_script per restituire il set di dati Iris incluso in 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. Creare un modello Python e generare punteggi da esso

Questo esempio illustra come usare sp_execute_external_script per generare punteggi in un modello Python semplice.

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

Le intestazioni di colonna usate nel codice Python non vengono restituite in SQL Server; utilizzare pertanto l'istruzione WITH RESULT per specificare i nomi di colonna e i tipi di dati da usare per SQL.

C. Generare un modello R basato sui dati di SQL Server

Nell'esempio seguente viene creata una stored procedure che usa sp_execute_external_script per generare un modello iris e restituire il modello.

Nota

Questo esempio richiede l'installazione anticipata del pacchetto e1071 . Per altre informazioni, vedere Installare pacchetti 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

Per generare un modello simile tramite Python, è necessario modificare l'identificatore del linguaggio da @language=N'R' a @language = N'Python' e apportare le modifiche necessarie all'argomento @script. In caso contrario, tutti i parametri funzionano allo stesso modo di R.

Per il punteggio, è anche possibile usare la funzione nativa PREDICT, che è in genere più veloce, perché consente di evitare la chiamata al runtime di Python o R.

Contenuto correlato

• Machine Learning SQL
• Cosa sono le estensioni del linguaggio di SQL Server?
• Stored procedure di sistema (Transact-SQL)
• CREATE EXTERNAL LIBRARY (Transact-SQL)
• sp_prepare (Transact SQL)
• sp_configure (Transact-SQL)
• Opzione di configurazione del server external scripts enabled
• SERVERPROPERTY (Transact-SQL)
• SQL Server, oggetto External Scripts
• sys.dm_external_script_requests
• sys.dm_external_script_execution_stats