sp_execute_external_script (Transact-SQL)

Gilt für: SQL Server 2016 (13.x) und höher Azure SQL Managed Instance

Die sp_execute_external_script gespeicherte Prozedur führt ein Skript aus, das als Eingabeargument für die Prozedur bereitgestellt wird und mit Machine Learning Services und Spracherweiterungen verwendet wird.

Für Machine Learning Services werden Python und R unterstützte Sprachen unterstützt. Für Spracherweiterungen wird Java unterstützt, muss jedoch mit CREATE EXTERNAL LANGUAGE definiert werden.

Zum Ausführen sp_execute_external_scriptmüssen Sie zuerst Machine Learning Services oder Spracherweiterungen installieren. Weitere Informationen finden Sie unter Installieren von SQL Server Machine Learning Services (Python und R) unter Windows und Linux oder Installieren von SQL Server-Spracherweiterungen unter Windows und Linux.

Transact-SQL-Syntaxkonventionen

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

Argumente

[ @language = ] N'language'

Gibt die Skriptsprache an. "language " ist "sysname". Gültige Werte sind R, Python und jede Sprache, die mit CREATE EXTERNAL LANGUAGE definiert ist (z. B. Java).

[ @script = ] N'script'

Externes Sprachskript, das als Literal- oder Variableneingabe angegeben ist. script is nvarchar(max).

[ @input_data_1 = ] N'input_data_1'

Gibt die Eingabedaten an, die vom externen Skript in Form einer Transact-SQL-Abfrage verwendet werden. Der Datentyp von input_data_1 ist nvarchar(max).

[ @input_data_1_name = ] N'input_data_1_name'

Gibt den Namen der Variablen an, die verwendet wird, um die von @input_data_1. Der Datentyp der Variablen im externen Skript hängt von der Sprache ab. Bei R ist die Eingabevariable ein Datenrahmen. Bei Python muss die Eingabe tabellarisch sein. input_data_1_name ist "sysname". Der Standardwert ist InputDataSet.

[ @input_data_1_order_by_columns = ] N'input_data_1_order_by_columns'

Wird zum Erstellen von Partitionsmodellen pro Partition verwendet. Gibt den Namen der Spalte an, die zum Sortieren des Resultsets verwendet wird, z. B. nach Produktname. Der Datentyp der Variablen im externen Skript hängt von der Sprache ab. Bei R ist die Eingabevariable ein Datenrahmen. Bei Python muss die Eingabe tabellarisch sein.

[ @input_data_1_partition_by_columns = ] N'input_data_1_partition_by_columns'

Wird zum Erstellen von Partitionsmodellen pro Partition verwendet. Gibt den Namen der Spalte an, die zum Segmentieren von Daten verwendet wird, z. B. geografische Region oder Datum. Der Datentyp der Variablen im externen Skript hängt von der Sprache ab. Bei R ist die Eingabevariable ein Datenrahmen. Bei Python muss die Eingabe tabellarisch sein.

[ @output_data_1_name = ] N'output_data_1_name'

Gibt den Namen der Variablen im externen Skript an, die die Daten enthält, die nach Abschluss des Aufrufs der gespeicherten Prozedur an SQL Server zurückgegeben werden sollen. Der Datentyp der Variablen im externen Skript hängt von der Sprache ab. Für R muss die Ausgabe ein Datenrahmen sein. Für Python muss die Ausgabe ein Pandas-Datenframe sein. output_data_1_name ist "sysname". Der Standardwert ist OutputDataSet.

[ @parallel = ] { 0 | 1 }

Aktivieren Sie die parallele Ausführung von R-Skripts, indem Sie den @parallel Parameter auf 1. Der Standardwert für diesen Parameter ist 0 (kein Parallelismus). Wenn @parallel = 1 und die Ausgabe direkt an den Clientcomputer gestreamt wird, ist die WITH RESULT SETS Klausel erforderlich und ein Ausgabeschema muss angegeben werden.

• Für R-Skripts, die keine RevoScaleR-Funktionen verwenden, kann die Verwendung des Parameters für die @parallel Verarbeitung großer Datasets von Vorteil sein, vorausgesetzt, das Skript kann trivial parallelisiert werden. Wenn Sie z. B. die R-Funktion predict mit einem Modell verwenden, um neue Vorhersagen zu generieren, legen Sie @parallel = 1 diesen als Hinweis auf das Abfragemodul fest. Wenn die Abfrage parallelisiert werden kann, werden Zeilen gemäß der MAXDOP-Einstellung verteilt.

• Für R-Skripts, die RevoScaleR-Funktionen verwenden, wird die parallele Verarbeitung automatisch behandelt, und Sie sollten den sp_execute_external_script Aufruf nicht angeben@parallel = 1.

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

Eine Liste der Eingabeparameterdeklarationen, die im externen Skript verwendet werden.

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

Eine Liste der Werte für die Eingabeparameter, die vom externen Skript verwendet werden.

Hinweise

Wichtig

Die Abfragestruktur wird von SQL Machine Learning gesteuert, und Benutzer können keine beliebigen Vorgänge für die Abfrage ausführen.

Wird sp_execute_external_script verwendet, um Skripts auszuführen, die in einer unterstützten Sprache geschrieben wurden. Unterstützte Sprachen sind Python und R, die mit Machine Learning Services verwendet werden, und jede sprache, die mit CREATE EXTERNAL LANGUAGE (z. B. Java) definiert ist, die mit Spracherweiterungen verwendet wird.

Standardmäßig werden von dieser gespeicherten Prozedur zurückgegebene Resultsets mit nicht benannten Spalten ausgegeben. Spaltennamen, die in einem Skript verwendet werden, sind lokal in der Skriptumgebung und werden nicht im ausgegebenen Resultset widergespiegelt. Verwenden Sie die WITH RESULT SET Execute-Klausel, um Ergebnissatzspalten zu benennen.

Zusätzlich zum Zurückgeben eines Resultsets können Sie skalare Werte mithilfe von OUTPUT-Parametern zurückgeben.

Sie können die von externen Skripts verwendeten Ressourcen steuern, indem Sie einen externen Ressourcenpool konfigurieren. Weitere Informationen finden Sie unter CREATE EXTERNAL RESOURCE POOL. Informationen über die Arbeitsauslastung können aus den Katalogansichten des Ressourcengouverneurs, DMVs und Zählern abgerufen werden. Weitere Informationen finden Sie unter Ressourcengouverneur-Katalogansichten, Ressourcensteuerungsansichten im Zusammenhang mit dynamischen Verwaltungsansichten und SQL Server- und externen Skripts-Objekt.

Überwachen der Skriptausführung

Überwachen Sie die Skriptausführung mithilfe von sys.dm_external_script_requests und sys.dm_external_script_execution_stats.

Parameter für die Partitionsmodellierung

Sie können zwei zusätzliche Parameter festlegen, die die Modellierung von partitionierten Daten ermöglichen, wobei Partitionen auf einer oder mehreren Spalten basieren, die Sie bereitstellen, die natürlich einen Datensatz in logische Partitionen segmentieren, erstellt und nur während der Skriptausführung verwendet werden. Spalten, die wiederholte Werte für Alter, Geschlecht, geografische Region, Datum oder Uhrzeit enthalten, sind einige Beispiele, die sich für partitionierte Datensätze eignen.

Die beiden Parameter sind input_data_1_partition_by_columns und input_data_1_order_by_columns, wobei der zweite Parameter verwendet wird, um das Resultset zu ordnen. Die Parameter werden als Eingaben sp_execute_external_script übergeben, an die das externe Skript einmal für jede Partition ausgeführt wird. Weitere Informationen und Beispiele finden Sie im Lernprogramm: Erstellen partitionsbasierter Modelle in R auf SQL Server.

Sie können skripts parallel ausführen, indem Sie angeben @parallel = 1. Wenn die Eingabeabfrage parallelisiert werden kann, sollten Sie als Teil der Argumente auf @parallel = 1 sp_execute_external_script. Standardmäßig wird der Abfrageoptimierer unter Tabellen mit mehr als 256 Zeilen ausgeführt @parallel = 1 . Wenn Sie dies jedoch explizit behandeln möchten, enthält dieses Skript den Parameter als Demonstration.

Tipp

Für Trainingsworkloads können Sie @parallel mit einem beliebigen arbiträren Trainingsskript verwenden, sogar solche, die nicht von Microsoft stammende RX-Algorithmen verwenden In der Regel bieten nur RevoScaleR-Algorithmen (mit dem RX-Präfix) Parallelität in Trainingsszenarios in SQL Server. Mit den neuen Parametern in SQL Server 2019 (15.x) und höheren Versionen können Sie jedoch ein Skript parallelisieren, das Funktionen aufruft, die nicht speziell mit dieser Funktion entwickelt wurden.

Streamingausführung für Python- und R-Skripts

Streaming ermöglicht es dem Python- oder R-Skript, mit mehr Daten zu arbeiten, als in den Arbeitsspeicher passen können. Um die Anzahl der zeilen zu steuern, die während des Streamings übergeben werden, geben Sie einen ganzzahligen Wert für den Parameter @r_rowsPerRead in der @params Auflistung an. Wenn Sie beispielsweise ein Modell trainieren, das sehr breite Daten verwendet, können Sie den Wert so anpassen, dass weniger Zeilen gelesen werden, um sicherzustellen, dass alle Zeilen in einem Datenabschnitt gesendet werden können. Sie können diesen Parameter auch verwenden, um die Anzahl der gleichzeitig gelesenen und verarbeiteten Zeilen zu verwalten, um Serverleistungsprobleme zu vermeiden.

Sowohl der @r_rowsPerRead Parameter für Streaming als auch das @parallel Argument sollten als Hinweise betrachtet werden. Damit der Hinweis angewendet werden kann, muss es möglich sein, einen SQL-Abfrageplan zu generieren, der die parallele Verarbeitung umfasst. Wenn dies nicht möglich ist, kann die parallele Verarbeitung nicht aktiviert werden.

Hinweis

Streaming und parallele Verarbeitung werden nur in Enterprise Edition unterstützt. Sie können die Parameter in Ihre Abfragen in Standard Edition einschließen, ohne einen Fehler zu auslösen, aber die Parameter haben keine Auswirkungen, und R-Skripts werden in einem einzigen Prozess ausgeführt.

Begrenzungen

Datentypen

Die folgenden Datentypen werden nicht unterstützt, wenn sie in der Eingabeabfrage oder den Parametern der sp_execute_external_script Prozedur verwendet werden, und es wird ein Fehler vom Typ "Nicht unterstützt" zurückgegeben.

Als Problemumgehung wird CAST die Spalte oder der Wert an einen unterstützten Typ in Transact-SQL gesendet, bevor sie an das externe Skript gesendet wird.

• Cursor
• timestamp
• datetime2, datetimeoffset, time
• sql_variant
• Text, Bild
• xml
• hierarchyid, geometry, geography
• Benutzerdefinierte CLR-Typen

Im Allgemeinen wird jedes Resultset, das keinem Transact-SQL-Datentyp zugeordnet werden kann, ausgegeben als NULL.

Spezifische Einschränkungen für R

Wenn die Eingabe Datum-/Uhrzeitwerte enthält, die nicht dem zulässigen Wertebereich in R entsprechen, werden die Werte in "R" konvertiertNA. Dies ist erforderlich, da SQL Machine Learning einen größeren Wertebereich zulässt, als in der Sprache R unterstützt wird.

Float-Werte (z. B+Inf. , , -InfNaN) werden in SQL Machine Learning nicht unterstützt, obwohl beide Sprachen IEEE 754 verwenden. Aktuelles Verhalten sendet nur die Werte direkt an SQL; Daher löst der SQL-Client einen Fehler aus. Daher werden diese Werte in NULL.

Berechtigungen

Erfordert EXECUTE ANY EXTERNAL SCRIPT database permission.

Beispiele

Dieser Abschnitt enthält Beispiele dafür, wie diese gespeicherte Prozedur zum Ausführen von R- oder Python-Skripts mit Transact-SQL verwendet werden kann.

A. Zurückgeben eines R-Datasets an SQL Server

Im folgenden Beispiel wird eine gespeicherte Prozedur erstellt, die verwendet wird, sp_execute_external_script um das Iris-Dataset zurückzugeben, das in R enthalten ist.

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. Erstellen eines Python-Modells und Generieren von Bewertungen daraus

In diesem Beispiel wird veranschaulicht, wie sp_execute_external_script Sie Bewertungen für ein einfaches Python-Modell generieren.

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

Spaltenüberschriften, die im Python-Code verwendet werden, werden nicht in SQL Server ausgegeben. Verwenden Sie daher die WITH RESULT-Anweisung, um die Zu verwendenden Spaltennamen und Datentypen für SQL anzugeben.

C. Generieren eines R-Modells basierend auf Daten aus SQL Server

Im folgenden Beispiel wird eine gespeicherte Prozedur erstellt, die verwendet wird, sp_execute_external_script um ein Irismodell zu generieren und das Modell zurückzugeben.

Hinweis

In diesem Beispiel ist eine vorab installation des e1071-Pakets erforderlich. Weitere Informationen finden Sie unter Installieren von R-Paketen mit 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

Um ein ähnliches Modell mithilfe von Python zu generieren, ändern Sie die Sprachen-ID von @language=N'R' zu @language = N'Python' und nehmen die notwendigen Änderungen im @script-Argument vor. Alle anderen Parameter funktionieren genauso wie bei R.

Zur Bewertung können Sie auch die native PREDICT-Funktion verwenden, die in der Regel schneller ist, weil sie die Python- bzw. R-Laufzeit nicht aufrufen muss.

Zugehöriger Inhalt

• SQL Machine Learning
• Was sind SQL Server-Spracherweiterungen?
• Gespeicherte Systemprozeduren (Transact-SQL)
• CREATE EXTERNAL LIBRARY (Transact-SQL)
• sp_prepare (Transact SQL)
• sp_configure (Transact-SQL)
• Externe Skripts aktiviert (Serverkonfigurationsoption)
• SERVERPROPERTY (Transact-SQL)
• SQL Server-Objekt, Externe Skripts
• sys.dm_external_script_requests
• sys.dm_external_script_execution_stats