sp_execute_external_script (Transact-SQL)

van toepassing op: SQL Server 2016 (13.x) en hoger Azure SQL Managed Instance

De sp_execute_external_script opgeslagen procedure voert een script uit dat is opgegeven als invoerargument voor de procedure en wordt gebruikt met Machine Learning Services en Language Extensions.

Voor Machine Learning Services worden Python- en R- ondersteund. Voor taalextensies wordt Java ondersteund, maar moet worden gedefinieerd met CREATE EXTERNAL LANGUAGE.

Als u sp_execute_external_scriptwilt uitvoeren, moet u eerst Machine Learning Services of taalextensies installeren. Zie voor meer informatie SQL Server Machine Learning Services (Python en R) installeren in Windows en Linux-, of SQL Server-taalextensies installeren in Windows en Linux-.

Transact-SQL syntaxisconventies

Syntaxis

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

Argumenten

[ @language = ] N'taal'

Geeft de scripttaal aan. taal is sysname. Geldige waarden zijn R-, Python-en elke taal die is gedefinieerd met CREATE EXTERNAL LANGUAGE (bijvoorbeeld Java).

[ @script = ] N'script'

Script voor externe taal dat is opgegeven als letterlijke of variabele invoer. script is nvarchar(max).

[ @input_data_1 = ] N'input_data_1'

Hiermee geeft u de invoergegevens op die door het externe script worden gebruikt in de vorm van een Transact-SQL query. Het gegevenstype van input_data_1 is nvarchar(max).

[ @input_data_1_name = ] N'input_data_1_name'

Hiermee geeft u de naam op van de variabele die wordt gebruikt om de query weer te geven die is gedefinieerd door @input_data_1. Het gegevenstype van de variabele in het externe script is afhankelijk van de taal. Voor R is de invoervariabele een gegevensframe. Voor Python moet invoer in tabelvorm zijn. input_data_1_name is sysname. De standaardwaarde is InputDataSet-.

[ @input_data_1_order_by_columns = ] N'input_data_1_order_by_columns'

Wordt gebruikt om modellen per partitie te bouwen. Hiermee geeft u de naam op van de kolom die wordt gebruikt om de resultatenset te orden, bijvoorbeeld op productnaam. Het gegevenstype van de variabele in het externe script is afhankelijk van de taal. Voor R is de invoervariabele een gegevensframe. Voor Python moet invoer in tabelvorm zijn.

[ @input_data_1_partition_by_columns = ] N'input_data_1_partition_by_columns'

Wordt gebruikt om modellen per partitie te bouwen. Hiermee geeft u de naam op van de kolom die wordt gebruikt voor het segmenteren van gegevens, zoals geografische regio of datum. Het gegevenstype van de variabele in het externe script is afhankelijk van de taal. Voor R is de invoervariabele een gegevensframe. Voor Python moet invoer in tabelvorm zijn.

[ @output_data_1_name = ] N'output_data_1_name'

Hiermee geeft u de naam van de variabele in het externe script die de gegevens bevat die moeten worden geretourneerd naar SQL Server na voltooiing van de opgeslagen procedure aanroep. Het gegevenstype van de variabele in het externe script is afhankelijk van de taal. Voor R moet de uitvoer een gegevensframe zijn. Voor Python moet de uitvoer een pandas-gegevensframe zijn. output_data_1_name is sysname. De standaardwaarde is OutputDataSet-.

[ @parallel = ] { 0 | 1 }

Schakel parallelle uitvoering van R-scripts in door de parameter @parallel in te stellen op 1. De standaardwaarde voor deze parameter is 0 (geen parallelle uitvoering). Als @parallel = 1 en de uitvoer rechtstreeks naar de clientcomputer wordt gestreamd, is de WITH RESULT SETS-component vereist en moet een uitvoerschema worden opgegeven.

• Voor R-scripts die geen RevoScaleR-functies gebruiken, kan het nuttig zijn om grote gegevenssets te verwerken met behulp van de parameter @parallel, ervan uitgaande dat het script triviaal kan worden geparallelliseerd. Wanneer u bijvoorbeeld de functie R predict gebruikt met een model om nieuwe voorspellingen te genereren, stelt u @parallel = 1 in als hint voor de query-engine. Als de query kan worden geparallelliseerd, worden rijen gedistribueerd volgens de MAXDOP--instelling.

• Voor R-scripts die gebruikmaken van RevoScaleR-functies, wordt parallelle verwerking automatisch verwerkt en moet u geen @parallel = 1 opgeven voor de sp_execute_external_script-aanroep.

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

Een lijst met invoerparameterdeclaraties die worden gebruikt in het externe script.

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

Een lijst met waarden voor de invoerparameters die door het externe script worden gebruikt.

Opmerkingen

Belangrijk

De querystructuur wordt beheerd door SQL Machine Learning en gebruikers kunnen geen willekeurige bewerkingen uitvoeren op de query.

Gebruik sp_execute_external_script om scripts uit te voeren die zijn geschreven in een ondersteunde taal. Ondersteunde talen zijn Python- en R- die worden gebruikt met Machine Learning Services en elke taal die is gedefinieerd met CREATE EXTERNAL LANGUAGE (bijvoorbeeld Java) die wordt gebruikt met Taalextensies.

Standaard worden resultatensets die door deze opgeslagen procedure worden geretourneerd, uitgevoerd met niet-benoemde kolommen. Kolomnamen die in een script worden gebruikt, zijn lokaal in de scriptomgeving en worden niet weergegeven in de uitvoerresultatenset. Gebruik de WITH RESULT SET component van EXECUTE-om kolommen van resultatensets een naam te geven.

Naast het retourneren van een resultatenset kunt u scalaire waarden retourneren voor het gebruik van OUTPUT-parameters.

U kunt de resources beheren die door externe scripts worden gebruikt door een externe resourcegroep te configureren. Zie CREATE EXTERNAL RESOURCE POOLvoor meer informatie. Informatie over de workload kan worden verkregen via de resource governor-catalogusweergaven, DMV's en tellers. Zie Resource Governor Catalog Views, Resource Governor Related Dynamic Management Viewsen SQL Server, External Scripts objectvoor meer informatie.

Scriptuitvoering bewaken

Bewaak de uitvoering van scripts met behulp van sys.dm_external_script_requests en sys.dm_external_script_execution_stats.

Parameters voor partitionering

U kunt twee extra parameters instellen die modellering inschakelen voor gepartitioneerde gegevens, waarbij partities zijn gebaseerd op een of meer kolommen die u op natuurlijke wijze opgeeft dat een gegevensset wordt gesegmenteerd in logische partities, die alleen worden gemaakt en gebruikt tijdens het uitvoeren van het script. Kolommen met herhalende waarden voor leeftijd, geslacht, geografische regio, datum of tijd zijn enkele voorbeelden die zich lenen voor gepartitioneerde gegevenssets.

De twee parameters zijn input_data_1_partition_by_columns en input_data_1_order_by_columns, waarbij de tweede parameter wordt gebruikt om de resultatenset te orden. De parameters worden doorgegeven als invoer aan sp_execute_external_script met het externe script dat eenmaal voor elke partitie wordt uitgevoerd. Zie Zelfstudie: Partitiemodellen maken in R op SQL Servervoor meer informatie en voorbeelden.

U kunt het script parallel uitvoeren door @parallel = 1op te geven. Als de invoerquery kan worden geparallelliseerd, moet u @parallel = 1 instellen als onderdeel van uw argumenten op sp_execute_external_script. De queryoptimalisatie werkt standaard onder @parallel = 1 voor tabellen met meer dan 256 rijen, maar als u dit expliciet wilt verwerken, bevat dit script de parameter als demonstratie.

Fooi

Voor trainingsworkloads kunt u @parallel gebruiken met elk willekeurig trainingsscript, zelfs degenen die gebruikmaken van niet-Microsoft-rx-algoritmen. Normaal gesproken bieden alleen RevoScaleR-algoritmen (met het rx-voorvoegsel) parallelle uitvoering in trainingsscenario's in SQL Server. Maar met de nieuwe parameters in SQL Server 2019 (15.x) en latere versies kunt u een script parallelliseren dat functies aanroept die niet specifiek zijn ontworpen met die mogelijkheid.

Streaming-uitvoering voor Python- en R-scripts

Met streaming kan het Python- of R-script werken met meer gegevens dan in het geheugen past. Als u het aantal rijen wilt bepalen dat tijdens het streamen is doorgegeven, geeft u een geheel getal op voor de parameter, @r_rowsPerRead in de @params verzameling. Als u bijvoorbeeld een model traint dat zeer brede gegevens gebruikt, kunt u de waarde aanpassen om minder rijen te lezen, zodat alle rijen in één segment gegevens kunnen worden verzonden. U kunt deze parameter ook gebruiken om het aantal rijen te beheren dat tegelijkertijd wordt gelezen en verwerkt, om prestatieproblemen met de server te beperken.

Zowel de parameter @r_rowsPerRead voor streaming als het argument @parallel moet worden beschouwd als hints. Om de hint toe te passen, moet het mogelijk zijn om een SQL-queryplan te genereren dat parallelle verwerking omvat. Als dit niet mogelijk is, kan parallelle verwerking niet worden ingeschakeld.

Notitie

Streaming en parallelle verwerking worden alleen ondersteund in Enterprise Edition. U kunt de parameters opnemen in uw query's in Standard Edition zonder een fout op te geven, maar de parameters hebben geen effect en R-scripts worden in één proces uitgevoerd.

Beperkingen

Gegevenstypen

De volgende gegevenstypen worden niet ondersteund bij gebruik in de invoerquery of parameters van sp_execute_external_script procedure en retourneren een niet-ondersteunde typefout.

Als tijdelijke oplossing CAST u de kolom of waarde naar een ondersteund type in Transact-SQL voordat u deze naar het externe script verzendt.

• cursor
• tijdstempel
• datetime2, datetimeoffset, tijd
• sql_variant
• tekstafbeelding
• xml--
• hierarchyid, geometrie, geografie
• Door de gebruiker gedefinieerde CLR-typen

Over het algemeen wordt een resultatenset die niet kan worden toegewezen aan een Transact-SQL gegevenstype, uitgevoerd als NULL.

Beperkingen die specifiek zijn voor R

Als de invoer datum/tijd waarden bevat die niet voldoen aan het toegestane bereik van waarden in R, worden waarden geconverteerd naar NA. Dit is vereist omdat SQL Machine Learning een groter bereik van waarden toestaat dan wordt ondersteund in de R-taal.

Float-waarden (bijvoorbeeld +Inf, -Inf, NaN) worden niet ondersteund in SQL Machine Learning, ook al gebruiken beide talen IEEE 754. Huidig gedrag verzendt alleen de waarden rechtstreeks naar SQL; Als gevolg hiervan genereert de SQL-client een fout. Daarom worden deze waarden geconverteerd naar NULL.

Machtigingen

HIERVOOR IS EEN MACHTIGING VOOR DE EXTERNE SCRIPT-database UITVOEREN vereist.

Voorbeelden

Deze sectie bevat voorbeelden van hoe deze opgeslagen procedure kan worden gebruikt voor het uitvoeren van R- of Python-scripts met behulp van Transact-SQL.

Een. Een R-gegevensset retourneren naar SQL Server

In het volgende voorbeeld wordt een opgeslagen procedure gemaakt die gebruikmaakt van sp_execute_external_script om de Iris-gegevensset te retourneren die is opgenomen 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. Een Python-model maken en er scores van genereren

In dit voorbeeld ziet u hoe u sp_execute_external_script gebruikt om scores te genereren op een eenvoudig Python-model.

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

Kolomkoppen die in Python-code worden gebruikt, worden niet uitgevoerd naar SQL Server; Gebruik daarom de instructie WITH RESULT om de kolomnamen en gegevenstypen op te geven die sql moet gebruiken.

C. Een R-model genereren op basis van gegevens van SQL Server

In het volgende voorbeeld wordt een opgeslagen procedure gemaakt die gebruikmaakt van sp_execute_external_script om een irismodel te genereren en het model te retourneren.

Notitie

Voor dit voorbeeld is een geavanceerde installatie van het e1071-pakket vereist. Zie R-pakketten installeren met sqlmlutilsvoor meer informatie.

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

Als u een vergelijkbaar model wilt genereren met behulp van Python, wijzigt u de taal-id van @language=N'R' in @language = N'Python'en moet u de benodigde wijzigingen aanbrengen in het argument @script. Anders werken alle parameters op dezelfde manier als voor R.

Voor scoren kunt u ook de systeemeigen PREDICT--functie gebruiken. Dit is doorgaans sneller omdat hiermee geen Python- of R-runtime wordt aangeroepen.

Verwante inhoud

• SQL Machine Learning-
• Wat zijn SQL Server-taalextensies?
• systeem opgeslagen procedures (Transact-SQL)
• CREATE EXTERNAL LIBRARY (Transact-SQL)
• sp_prepare (Transact SQL)
• sp_configure (Transact-SQL)
• externe scripts waarvoor Serverconfiguratieoptie is ingeschakeld
• SERVERPROPERTY (Transact-SQL)
• SQL Server, extern scripts-object
• sys.dm_external_script_requests
• sys.dm_external_script_execution_stats