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 gespeicherten Prozedur führt ein Skript aus, das als Eingabeargument für die Prozedur bereitgestellt wird, und wird mit Machine Learning Services und Spracherweiterungen verwendet.

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

Um sp_execute_external_script auszuführen, müssen Sie zunächst Machine Learning Services oder Spracherweiterungen installieren. Weitere Informationen finden Sie unter Installieren SQL Server Machine Learning Services (Python und R) unter Windows und Linux oder Installieren 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 mit CREATE EXTERNAL LANGUAGE definierte Sprache (z. B. Java).

@script = N'script' Externes Sprachskript, das als Literal- oder Variableneingabe angegeben ist. script ist 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 durch @input_data_1definierte Abfrage darzustellen. Der Datentyp der Variablen im externen Skript hängt von der Sprache ab. Im Fall von R ist die Eingabevariable ein Datenrahmen. Im Fall von 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 Modellen 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. Im Fall von R ist die Eingabevariable ein Datenrahmen. Im Fall von Python muss die Eingabe tabellarisch sein.

[ @input_data_1_partition_by_columns = N'input_data_1_partition_by_columns' ] Wird zum Erstellen von Modellen 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. Im Fall von R ist die Eingabevariable ein Datenrahmen. Im Fall von Python muss die Eingabe tabellarisch sein.

[ @output_data_1_name = N'output_data_1_name' ]Gibt den Namen der Variablen im externen Skript an, das 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-Datenrahmen 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 festlegen. Der Standardwert für diesen Parameter ist 0 (keine Parallelität). Wenn @parallel = 1 und die Ausgabe direkt an den Clientcomputer gestreamt wird, ist die WITH RESULT SETS -Klausel erforderlich, und es muss ein Ausgabeschema angegeben werden.

• Bei 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 beispielsweise die R-Funktion predict mit einem Modell verwenden, um neue Vorhersagen zu generieren, legen Sie als Hinweis auf die Abfrage-Engine fest @parallel = 1 . Wenn die Abfrage parallelisiert werden kann, werden die Zeilen gemäß der MAXDOP-Einstellung verteilt.

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

[ @params = N'@parameter_name data_type [ OUT | OUTPUT ] [ ,...n ]' ] Eine Liste der Eingabeparameterdeklarationen, die im externen Skript verwendet werden.

[ @parameter1 = 'value1' [ OUT | OUTPUT ] [ ,...n ] ] Eine Liste der Werte für die Eingabeparameter, die vom externen Skript verwendet werden.

Bemerkungen

Wichtig

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

Verwenden Sie sp_execute_external_script , 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, sowie jede mit CREATE EXTERNAL LANGUAGE definierte Sprache (z. B. Java), die mit Spracherweiterungen verwendet wird.

Standardmäßig werden von dieser gespeicherten Prozedur zurückgegebene Resultsets mit unbenannten Spalten ausgegeben. Spaltennamen, die in einem Skript verwendet werden, sind lokal für die Skriptumgebung und werden nicht im ausgegebenen Resultset widergespiegelt. Verwenden Sie die WITH RESULT SET -Klausel von EXECUTE, um Resultsetspalten 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 (Transact-SQL). Informationen zur Workload können aus den Ressourcenkontrolle-Katalogsichten, DMVs und Leistungsindikatoren abgerufen werden. Weitere Informationen finden Sie unter Resource Governor Katalogsichten (Transact-SQL),Resource Governor Verwandte dynamische Verwaltungssichten (Transact-SQL) und SQL Server, Objekt für externe Skripts.

Ü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 für partitionierte Daten ermöglichen. Dabei basieren Partitionen auf einer oder mehreren Spalten, die Sie bereitstellen, die ein Dataset natürlich in logische Partitionen segmentieren, die nur während der Skriptausführung erstellt und verwendet werden. Spalten, die wiederholte Werte für Alter, Geschlecht, geografische Region, Datum oder Uhrzeit enthalten, sind einige Beispiele, die sich für partitionierte Datasets 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 sortieren. Die Parameter werden als Eingaben an sp_execute_external_script übergeben, wobei das externe Skript einmal für jede Partition ausgeführt wird. Weitere Informationen und Beispiele finden Sie unter Tutorial: Erstellen partitionsbasierter Modelle.

Sie können das Skript parallel ausführen, indem Sie angeben @parallel=1. Wenn die Eingabeabfrage parallelisiert werden kann, sollten Sie als Teil ihrer Argumente auf sp_execute_external_scriptfestlegen@parallel=1. Standardmäßig wird der Abfrageoptimierer für @parallel=1 Tabellen mit mehr als 256 Zeilen unter ausgeführt. 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 und höher 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. Um die Anzahl der Während des Streamings übergebenen Zeilen zu steuern, 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 anpassen, um weniger Zeilen zu lesen, um sicherzustellen, dass alle Zeilen in einem Datenblock gesendet werden können. Sie können diesen Parameter auch verwenden, um die Anzahl der Zeilen zu verwalten, die gleichzeitig gelesen und verarbeitet werden, um Serverleistungsprobleme zu minimieren.

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 parallele Verarbeitung umfasst. Wenn dies nicht möglich ist, kann die Parallelverarbeitung nicht aktiviert werden.

Hinweis

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

Beschränkungen

Datentypen

Die folgenden Datentypen werden nicht unterstützt, wenn sie in der Eingabeabfrage oder den Parametern sp_execute_external_script Prozedur verwendet werden, und geben einen nicht unterstützten Typfehler zurück.

Als Problemumgehung müssen Sie die Spalte oder den Wert in einen unterstützten Typ in Transact-SQL umwandeln, bevor Sie ihn an das externe Skript senden.

• Cursor

• timestamp

• datetime2, datetimeoffset, time

• sql_variant

• Text, Bild

• xml

• hierarchyid, geometry, geography

• Benutzerdefinierte CLR-Typen

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

Einschränkungen speziell für R

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

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

Berechtigungen

Erfordert die EXECUTE ANY EXTERNAL SCRIPT-Datenbankberechtigung .

Beispiele

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

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

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

DROP PROC IF EXISTS get_iris_dataset;  
go  
CREATE PROC 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 sie verwenden sp_execute_external_script , um Bewertungen für ein einfaches Python-Modell zu 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 in Python-Code verwendet werden, werden nicht an SQL Server ausgegeben. Verwenden Sie daher die WITH RESULT-Anweisung, um die 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 sp_execute_external_script verwendet, um ein Irismodell zu generieren und das Modell zurückzugeben.

Hinweis

Für dieses Beispiel ist eine vorausgesetzte Installation des e1071-Pakets erforderlich. Weitere Informationen finden Sie unter Installieren zusätzlicher R-Pakete auf SQL Server.

DROP PROC IF EXISTS generate_iris_model;
GO
CREATE PROC 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.

Siehe auch

• SQL Machine Learning
• 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, Externes Skriptobjekt
• sys.dm_external_script_requests
• sys.dm_external_script_execution_stats