sp_execute_external_script (Transact-SQL)

gäller för: SQL Server 2016 (13.x) och senare Azure SQL Managed Instance

Den sp_execute_external_script lagrade proceduren kör ett skript som tillhandahålls som indataargument i proceduren och används med Machine Learning Services och Language Extensions.

För Machine Learning Services stöds Python och R. För Språktillägg stöds Java men måste definieras med CREATE EXTERNAL LANGUAGE.

Om du vill köra sp_execute_external_scriptmåste du först installera Machine Learning Services eller Språktillägg. Mer information finns i Installera SQL Server Machine Learning Services (Python och R) i Windows och Linuxeller Installera SQL Server Language Extensions på Windows och Linux.

Transact-SQL syntaxkonventioner

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

Argument

[ @language = ] N'språk"

Anger skriptspråket. språk är sysname. Giltiga värden är R, Pythonoch alla språk som definieras med CREATE EXTERNAL LANGUAGE (till exempel Java).

[ @script = ] N'skript"

Externt språkskript som anges som en literal- eller variabelinmatning. skript är nvarchar(max).

[ @input_data_1 = ] N'input_data_1'

Anger indata som används av det externa skriptet i form av en Transact-SQL fråga. Datatypen för input_data_1 är nvarchar(max).

[ @input_data_1_name = ] N'input_data_1_name'

Anger namnet på variabeln som används för att representera frågan som definierats av @input_data_1. Datatypen för variabeln i det externa skriptet beror på språket. För R är indatavariabeln en dataram. För Python måste indata vara tabell. input_data_1_name är sysname. Standardvärdet är InputDataSet.

[ @input_data_1_order_by_columns = ] N'input_data_1_order_by_columns'

Används för att skapa modeller per partition. Anger namnet på den kolumn som används för att sortera resultatuppsättningen, till exempel efter produktnamn. Datatypen för variabeln i det externa skriptet beror på språket. För R är indatavariabeln en dataram. För Python måste indata vara tabell.

[ @input_data_1_partition_by_columns = ] N'input_data_1_partition_by_columns'

Används för att skapa modeller per partition. Anger namnet på kolumnen som används för att segmentera data, till exempel geografisk region eller datum. Datatypen för variabeln i det externa skriptet beror på språket. För R är indatavariabeln en dataram. För Python måste indata vara tabell.

[ @output_data_1_name = ] N'output_data_1_name'

Anger namnet på variabeln i det externa skriptet som innehåller de data som ska returneras till SQL Server när det lagrade proceduranropet har slutförts. Datatypen för variabeln i det externa skriptet beror på språket. För R måste utdata vara en dataram. För Python måste utdata vara en Pandas-dataram. output_data_1_name är sysname. Standardvärdet är OutputDataSet.

[ @parallel = ] { 0 | 1 }

Aktivera parallell körning av R-skript genom att ange parametern @parallel till 1. Standardvärdet för den här parametern är 0 (ingen parallellitet). Om @parallel = 1 och utdata strömmas direkt till klientdatorn krävs WITH RESULT SETS-satsen och ett utdataschema måste anges.

• För R-skript som inte använder RevoScaleR-funktioner kan det vara fördelaktigt att använda parametern @parallel för bearbetning av stora datamängder, förutsatt att skriptet kan parallelliseras trivialt. När du till exempel använder funktionen R predict med en modell för att generera nya förutsägelser anger du @parallel = 1 som ett tips till frågemotorn. Om frågan kan parallelliseras distribueras rader enligt inställningen MAXDOP.

• För R-skript som använder RevoScaleR-funktioner hanteras parallell bearbetning automatiskt och du bör inte ange @parallel = 1 till sp_execute_external_script-anropet.

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

En lista över indataparameterdeklarationer som används i det externa skriptet.

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

En lista med värden för de indataparametrar som används av det externa skriptet.

Anmärkningar

Viktig

Frågeträdet styrs av SQL-maskininlärning och användarna kan inte utföra godtyckliga åtgärder på frågan.

Använd sp_execute_external_script för att köra skript skrivna på ett språk som stöds. Språk som stöds är Python- och R- som används med Machine Learning Services, och alla språk som definieras med CREATE EXTERNAL LANGUAGE (till exempel Java) som används med Språktillägg.

Som standard är resultatuppsättningar som returneras av den här lagrade proceduren utdata med icke namngivna kolumner. Kolumnnamn som används i ett skript är lokala för skriptmiljön och återspeglas inte i den utdataresultatuppsättningen. Om du vill namnge resultatuppsättningskolumner använder du WITH RESULT SET-satsen i EXECUTE.

Förutom att returnera en resultatuppsättning kan du returnera skalära värden till med hjälp av OUTPUT-parametrar.

Du kan styra de resurser som används av externa skript genom att konfigurera en extern resurspool. Mer information finns i CREATE EXTERNAL RESOURCE POOL. Information om arbetsbelastningen kan hämtas från resursguvernörens katalogvyer, DMV:er och räknare. Mer information finns i Resource Governor Catalog Views, Resource Governor Related Dynamic Management Viewsoch SQL Server, external Scripts object.

Övervaka skriptkörning

Övervaka skriptkörning med hjälp av sys.dm_external_script_requests och sys.dm_external_script_execution_stats.

Parametrar för partitionsmodellering

Du kan ange två ytterligare parametrar som möjliggör modellering av partitionerade data, där partitioner baseras på en eller flera kolumner som du anger som naturligt segmenterar en datauppsättning i logiska partitioner, som skapas och endast används under skriptkörning. Kolumner som innehåller upprepade värden för ålder, kön, geografisk region, datum eller tid är några exempel som lämpar sig för partitionerade datauppsättningar.

De två parametrarna är input_data_1_partition_by_columns och input_data_1_order_by_columns, där den andra parametern används för att sortera resultatuppsättningen. Parametrarna skickas som indata till sp_execute_external_script med det externa skriptet körs en gång för varje partition. Mer information och exempel finns i Självstudie: Skapa partitionsbaserade modeller i R på SQL Server.

Du kan köra skript parallellt genom att ange @parallel = 1. Om indatafrågan kan parallelliseras bör du ange @parallel = 1 som en del av argumenten till sp_execute_external_script. Som standard fungerar frågeoptimeraren under @parallel = 1 på tabeller som har fler än 256 rader, men om du vill hantera detta explicit innehåller det här skriptet parametern som en demonstration.

Dricks

För träningsarbetsbelastningar kan du använda @parallel med valfritt godtyckligt träningsskript, även de som använder algoritmer som inte är Microsoft-rx. Vanligtvis erbjuder endast RevoScaleR-algoritmer (med rx-prefixet) parallellitet i träningsscenarier i SQL Server. Men med de nya parametrarna i SQL Server 2019 (15.x) och senare versioner kan du parallellisera ett skript som anropar funktioner som inte är specifikt utformade med den funktionen.

Körning av direktuppspelning för Python- och R-skript

Med strömning kan Python- eller R-skriptet fungera med mer data än vad som får plats i minnet. Om du vill styra antalet rader som skickas under strömning anger du ett heltalsvärde för parametern @r_rowsPerRead i samlingen @params. Om du till exempel tränar en modell som använder mycket breda data kan du justera värdet för att läsa färre rader för att säkerställa att alla rader kan skickas i ett datasegment. Du kan också använda den här parametern för att hantera antalet rader som läs- och bearbetas samtidigt för att minska serverprestandaproblem.

Både parametern @r_rowsPerRead för direktuppspelning och argumentet @parallel bör betraktas som tips. För att tipset ska tillämpas måste det vara möjligt att generera en SQL-frågeplan som innehåller parallell bearbetning. Om detta inte är möjligt kan parallell bearbetning inte aktiveras.

Not

Direktuppspelning och parallell bearbetning stöds endast i Enterprise Edition. Du kan inkludera parametrarna i dina frågor i Standard Edition utan att skapa ett fel, men parametrarna har ingen effekt och R-skript körs i en enda process.

Begränsningar

Datatyper

Följande datatyper stöds inte när de används i indatafrågan eller parametrarna i sp_execute_external_script proceduren och returnerar ett typfel som inte stöds.

Som en lösning CAST kolumnen eller värdet till en typ som stöds i Transact-SQL innan du skickar den till det externa skriptet.

• markören
• tidsstämpel
• datetime2, datetimeoffset, tid
• sql_variant
• text, bild
• XML-
• hierarchyid, geometri, geografi
• ANVÄNDARDEFINIERADE CLR-typer

I allmänhet är alla resultatuppsättningar som inte kan mappas till en Transact-SQL datatyp utdata som NULL.

Begränsningar som är specifika för R

Om indata innehåller datetime- värden som inte passar det tillåtna intervallet med värden i R konverteras värdena till NA. Detta krävs eftersom SQL-maskininlärning tillåter ett större antal värden än vad som stöds på R-språket.

Flyttalvärden (till exempel +Inf, -Inf, NaN) stöds inte i SQL-maskininlärning även om båda språken använder IEEE 754. Det aktuella beteendet skickar bara värdena direkt till SQL. Därför utlöser SQL-klienten ett fel. Därför konverteras dessa värden till NULL.

Behörigheter

Kräver BEHÖRIGHETEN KÖR VALFRI EXTERN SKRIPTdatabas.

Exempel

Det här avsnittet innehåller exempel på hur den här lagrade proceduren kan användas för att köra R- eller Python-skript med Hjälp av Transact-SQL.

A. Returnera en R-datauppsättning till SQL Server

I följande exempel skapas en lagrad procedur som använder sp_execute_external_script för att returnera Iris-datauppsättningen som ingår i 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. Skapa en Python-modell och generera poäng från den

Det här exemplet visar hur du använder sp_execute_external_script för att generera poäng på en enkel Python-modell.

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

Kolumnrubriker som används i Python-kod matas inte ut till SQL Server. Använd därför WITH RESULT-instruktionen för att ange kolumnnamn och datatyper som SQL ska använda.

C. Generera en R-modell baserat på data från SQL Server

I följande exempel skapas en lagrad procedur som använder sp_execute_external_script för att generera en irismodell och returnera modellen.

Not

Det här exemplet kräver förhandsinstallation av paketet e1071. Mer information finns i Installera R-paket med 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

Om du vill generera en liknande modell med Python ändrar du språkidentifieraren från @language=N'R' till @language = N'Python'och gör nödvändiga ändringar i argumentet @script. Annars fungerar alla parametrar på samma sätt som för R.

För bedömning kan du också använda den inbyggda funktionen PREDICT, vilket vanligtvis är snabbare eftersom den undviker att anropa Python- eller R-körningen.

Relaterat innehåll

• SQL-maskininlärning
• Vad är SQL Server Language Extensions?
• System lagrade procedurer (Transact-SQL)
• SKAPA EXTERNT BIBLIOTEK (Transact-SQL)
• sp_prepare (Transact SQL)
• sp_configure (Transact-SQL)
• externa skript aktiverade serverkonfigurationsalternativet
• SERVERPROPERTY (Transact-SQL)
• SQL Server externt skriptobjekt
• sys.dm_external_script_requests
• sys.dm_external_script_execution_stats