sp_execute_external_script (Transact-SQL)

A következőkre vonatkozik: SQL Server 2016 (13.x) és újabb Felügyelt Azure SQL-példány

A sp_execute_external_script tárolt eljárás az eljárás bemeneti argumentumaként megadott szkriptet hajt végre, és Machine Learning Services és Language Extensionshasználja.

A Machine Learning Services esetében Python és R támogatott nyelvek. Nyelvi bővítmények esetén a Java támogatott, de KÜLSŐ NYELV létrehozásakell definiálni.

A sp_execute_external_scriptvégrehajtásához először telepítenie kell a Machine Learning Servicest vagy a Nyelvi bővítményeket. További információ: Sql Server Machine Learning Services (Python és R) telepítése Windows és Linux, vagy SQL Server nyelvi bővítmények telepítése Windows és Linux.

Transact-SQL szintaxis konvenciói

Szintaxis

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

Érvek

Fontos

A kiterjesztett tárolt eljárások argumentumait a Szintaxis szakaszban leírt sorrendben kell megadni. Ha a paraméterek sorrenden kívül vannak beírva, hibaüzenet jelenik meg.

[ @language = ] N'nyelv'

A szkript nyelvét jelzi. nyelvisysname. Az érvényes értékek R, Python, valamint az CREATE EXTERNAL LANGUAGE (például Java) használatával definiált nyelvek.

[ @script = ] N'szkript'

Konstans vagy változó bemenetként megadott külső nyelvi szkript. szkriptnvarchar(max).

[ @input_data_1 = ] N'input_data_1'

A külső szkript által Transact-SQL lekérdezés formájában használt bemeneti adatokat adja meg. A input_data_1 adattípusa nvarchar(max).

[ @input_data_1_name = ] N'input_data_1_name'

A @input_data_1által definiált lekérdezés megjelenítéséhez használt változó nevét adja meg. A külső szkript változójának adattípusa a nyelvtől függ. Az R esetében a bemeneti változó egy adatkeret. Python esetén a bemenetnek táblázatosnak kell lennie. input_data_1_name a sysname. Az alapértelmezett érték InputDataSet.

[ @input_data_1_order_by_columns = ] N'input_data_1_order_by_columns'

Partíciónkénti modellek készítésére szolgál. Az eredményhalmaz rendeléséhez használt oszlop nevét adja meg, például terméknév alapján. A külső szkript változójának adattípusa a nyelvtől függ. Az R esetében a bemeneti változó egy adatkeret. Python esetén a bemenetnek táblázatosnak kell lennie.

[ @input_data_1_partition_by_columns = ] N'input_data_1_partition_by_columns'

Partíciónkénti modellek készítésére szolgál. Megadja az adatok szegmentálásához használt oszlop nevét, például földrajzi régiót vagy dátumot. A külső szkript változójának adattípusa a nyelvtől függ. Az R esetében a bemeneti változó egy adatkeret. Python esetén a bemenetnek táblázatosnak kell lennie.

[ @output_data_1_name = ] N'output_data_1_name'

Megadja annak a változónak a nevét a külső szkriptben, amely a tárolt eljáráshívás befejezésekor az SQL Servernek visszaadandó adatokat tartalmazza. A külső szkript változójának adattípusa a nyelvtől függ. Az R esetében a kimenetnek adatkeretnek kell lennie. Python esetén a kimenetnek pandas-adatkeretnek kell lennie. output_data_1_name a sysname. Az alapértelmezett érték OutputDataSet.

[ @parallel = ] { 0 | 1 }

Az R-szkriptek párhuzamos végrehajtásának engedélyezése a @parallel paraméter 1beállításával. Ennek a paraméternek az alapértelmezett értéke 0 (nincs párhuzamosság). Ha @parallel = 1 és a kimenetet közvetlenül az ügyfélszámítógépre streameli, akkor a WITH RESULT SETS záradék szükséges, és meg kell adni egy kimeneti sémát.

• A RevoScaleR függvényeket nem használó R-szkriptek esetében a @parallel paraméter használata hasznos lehet nagy adathalmazok feldolgozásához, feltéve, hogy a szkript triviálisan párhuzamos. Ha például az R predict függvényt egy modellel használja új előrejelzések létrehozásához, állítsa @parallel = 1 a lekérdezési motorra. Ha a lekérdezés párhuzamosítható, a sorokat a MAXDOP beállítás szerint osztja el a rendszer.

• A RevoScaleR függvényeket használó R-szkriptek esetében a párhuzamos feldolgozás automatikusan történik, és nem szabad @parallel = 1 megadni a sp_execute_external_script híváshoz.

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

A külső szkriptben használt bemeneti paraméter-deklarációk listája.

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

A külső szkript által használt bemeneti paraméterek értékeinek listája.

Megjegyzések

Fontos

A lekérdezési fát sql-gépi tanulás vezérli, és a felhasználók nem végezhetnek tetszőleges műveleteket a lekérdezésen.

A sp_execute_external_script használatával támogatott nyelven írt szkripteket hajthat végre. A támogatott nyelvek a Machine Learning Servicesben használt Python és R, valamint a CREATE EXTERNAL LANGUAGE (például Java) nyelvkiterjesztésekkel definiált nyelvek.

Alapértelmezés szerint a tárolt eljárás által visszaadott eredményhalmazok névtelen oszlopokkal rendelkező kimenetek. A szkriptekben használt oszlopnevek helyiek a szkriptelési környezethez, és nem jelennek meg a kimeneti eredményhalmazban. Az eredményhalmaz oszlopainak elnevezéséhez használja az WITH RESULT SET záradékát.

Az eredményhalmaz visszaadása mellett skaláris értékeket is visszaadhat a KIMENETI paraméterek használatával.

Külső erőforráskészlet konfigurálásával szabályozhatja a külső szkriptek által használt erőforrásokat. További információ: KÜLSŐ ERŐFORRÁSKÉSZLET LÉTREHOZÁSA. A számítási feladatra vonatkozó információk az erőforrás-kormányzó katalógusnézeteiből, DMV-jeiből és számlálóiból kérhetők le. További információ: Erőforrás-kormányzókatalógus-nézetek, Erőforrás-kormányzóval kapcsolatos dinamikus felügyeleti nézetek és SQL Server, Külső szkriptek objektum.

Szkript végrehajtásának figyelése

Szkriptvégrehajtás figyelése sys.dm_external_script_requests és sys.dm_external_script_execution_statshasználatával.

Partíciómodellezés paraméterei

Két további paramétert is beállíthat, amelyek lehetővé teszik a particionált adatok modellezését, ahol a partíciók egy vagy több olyan oszlopon alapulnak, amelyek természetes módon logikai partíciókra szegmentáltak egy adatkészletet, amelyeket csak a szkript végrehajtása során hoztak létre és használnak. A kor, a nem, a földrajzi régió, a dátum vagy az idő ismétlődő értékeit tartalmazó oszlopok néhány példa, amelyek particionált adatkészletekhez adnak lehetőséget.

A két paraméter input_data_1_partition_by_columns és input_data_1_order_by_columns, ahol a második paramétert használja az eredményhalmaz rendeléséhez. A paraméterek bemenetként lesznek átadva sp_execute_external_script a külső szkripttel, amely minden partícióhoz egyszer fut. További információkért és példákért lásd oktatóanyagot: Partícióalapú modellek létrehozása az R-ben AZ SQL Server.

A szkriptet párhuzamosan is végrehajthatja a @parallel = 1megadásával. Ha a bemeneti lekérdezés párhuzamosítható, akkor az argumentumok részeként @parallel = 1sp_execute_external_script. A lekérdezésoptimalizáló alapértelmezés szerint @parallel = 1 alatt működik a több mint 256 sorból álló táblákon, de ha explicit módon szeretné kezelni ezt a szkriptet, ez a szkript bemutatóként tartalmazza a paramétert.

Borravaló

A számítási feladatok betanításához @parallel bármilyen tetszőleges betanítási szkripttel használhatja, még a nem Microsoft-rx algoritmusokat használók is. Az SQL Server betanítási forgatókönyveiben általában csak a RevoScaleR algoritmusok (az rx előtaggal) kínálnak párhuzamosságot. Az SQL Server 2019 (15.x) és újabb verzióinak új paramétereivel azonban párhuzamossá tehet egy olyan szkriptet, amely nem kifejezetten ezzel a képességgel tervezett függvényeket hív meg.

Streamelés végrehajtása Python- és R-szkriptekhez

A streamelés lehetővé teszi, hogy a Python- vagy R-szkript több adattal működjön, mint amennyi a memóriában elfér. A streamelés során átadott sorok számának szabályozásához adjon meg egy egész számot a paraméterhez, @r_rowsPerRead a @params gyűjteményben. Ha például egy nagyon széles adatokat használó modellt tanít be, beállíthatja az értéket úgy, hogy kevesebb sort olvasson be, hogy az összes sor egyetlen adattömbben legyen elküldve. Ezzel a paraméterrel kezelheti az egyszerre beolvasott és feldolgozott sorok számát a kiszolgáló teljesítményproblémáinak csökkentése érdekében.

A streamelés @r_rowsPerRead paraméterét és a @parallel argumentumot is tippnek kell tekinteni. A tipp alkalmazásához olyan SQL-lekérdezési tervet kell létrehozni, amely párhuzamos feldolgozást is tartalmaz. Ha ez nem lehetséges, a párhuzamos feldolgozás nem engedélyezhető.

Jegyzet

A streamelés és a párhuzamos feldolgozás csak az Enterprise Editionben támogatott. A paramétereket a Standard Editionben hiba nélkül is felveheti a lekérdezésekbe, de a paramétereknek nincs hatása, és az R-szkriptek egyetlen folyamatban futnak.

Korlátozások

Adattípusok

Az alábbi adattípusok nem támogatottak, ha a bemeneti lekérdezésben vagy a sp_execute_external_script eljárás paramétereiben használják, és nem támogatott típushibát ad vissza.

Kerülő megoldásként CAST az oszlopot vagy az értéket egy támogatott típusra a Transact-SQL, mielőtt elküldené a külső szkriptbe.

• kurzor
• időbélyeg
• datetime2, datetimeoffset, idő
• sql_variant
• szöveg, kép
• xml
• hierarchiaazonosító, geometria, földrajzi
• Felhasználó által definiált CLR-típusok

Általánosságban elmondható, hogy minden olyan eredményhalmaz, amely nem képezhető le Transact-SQL adattípusra, a kimenet NULL.

Az R-re vonatkozó korlátozások

Ha a bemenet olyan datetime értékeket tartalmaz, amelyek nem felelnek meg az R megengedett értéktartományának, az értékek NAlesznek konvertálva. Erre azért van szükség, mert az SQL machine learning az R nyelv által támogatottnál nagyobb értéktartományt engedélyez.

A lebegőpontos értékek (például +Inf, -Inf, NaN) nem támogatottak az SQL machine learningben annak ellenére, hogy mindkét nyelv az IEEE 754-et használja. Az aktuális viselkedés csak közvetlenül küldi el az értékeket az SQL-nek; Ennek eredményeképpen az SQL-ügyfél hibát jelez. Ezért ezek az értékek NULLlesznek konvertálva.

Engedélyek

BÁRMELY KÜLSŐ SZKRIPT-adatbázis engedélyének végrehajtása szükséges.

Példák

Ez a szakasz példákat tartalmaz arra, hogyan használható ez a tárolt eljárás R- vagy Python-szkriptek Transact-SQL használatával történő végrehajtására.

Egy. R-adatkészlet visszaadása az SQL Serverre

Az alábbi példa egy tárolt eljárást hoz létre, amely sp_execute_external_script használatával adja vissza az R-ben található Írisz-adatkészletet.

DROP PROCEDURE IF EXISTS get_iris_dataset;
GO

CREATE PROCEDURE get_iris_dataset
AS
BEGIN
    EXECUTE 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. Python-modell létrehozása és pontszámok létrehozása belőle

Ez a példa bemutatja, hogyan hozhat létre pontszámokat az sp_execute_external_script egy egyszerű Python-modellen.

CREATE PROCEDURE [dbo].[py_generate_customer_scores]
AS
BEGIN
    -- Input query to generate the customer data
    DECLARE @input_query AS NVARCHAR (MAX) = N'SELECT customer, orders, items, cost FROM dbo.Sales.Orders';
    EXECUTE 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

A Python-kódban használt oszlopfejlécek nem az SQL Serveren jelennek meg; Ezért a WITH RESULT utasítással adja meg a használni kívánt SQL-hez tartozó oszlopneveket és adattípusokat.

C. R-modell létrehozása az SQL Server adatai alapján

Az alábbi példa létrehoz egy tárolt eljárást, amely sp_execute_external_script használ egy íriszmodell létrehozásához és a modell visszaadásához.

Jegyzet

Ebben a példában a e1071 csomag előzetes telepítését igényli. További információ: R-csomagok telepítése sqlmlutilshasználatával.

DROP PROCEDURE IF EXISTS generate_iris_model;
GO

CREATE PROCEDURE generate_iris_model
AS
BEGIN
    EXECUTE 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

Ha hasonló modellt szeretne létrehozni a Python használatával, a nyelvi azonosítót @language=N'R'-ról @language = N'Python'-ra módosítaná, és szükséges módosításokat hajtana végre a @script argumentumon. Ellenkező esetben az összes paraméter ugyanúgy működik, mint az R esetében.

A pontozáshoz használhatja a natív PREDICT függvényt is, amely általában gyorsabb, mert nem hívja meg a Python- vagy R-futtatókörnyezetet.

Kapcsolódó tartalom

• SQL machine learning
• Mik az SQL Server nyelvi bővítményei?
• rendszer által tárolt eljárások (Transact-SQL)
• KÜLSŐ KÓDTÁR LÉTREHOZÁSA (Transact-SQL)
• sp_prepare (Transact SQL)
• sp_configure (Transact-SQL)
• külső szkriptek engedélyezett kiszolgálókonfigurációs lehetőség
• SERVERPROPERTY (Transact-SQL)
• SQL Server, Külső szkriptek objektum
• sys.dm_external_script_requests
• sys.dm_external_script_execution_stats