sp_execute_external_script (Transact-SQL)

Dotyczy: SQL Server 2016 (13.x) i nowszych azure SQL Managed Instance

Transact-SQL konwencje składni

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

EXEC 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' ]
    [ , [ @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 ] ]

Argumenty

[ @language = ] N'language'

[ @script = ] Skrypt N'"

Skrypt języka zewnętrznego określony jako dane wejściowe literału lub zmiennej. skrypt jest nvarchar(max).

[ @input_data_1 = ] N'input_data_1'

Określa dane wejściowe używane przez skrypt zewnętrzny w postaci zapytania Transact-SQL. Typ danych input_data_1 to nvarchar(max).

[ @input_data_1_name = ] N'input_data_1_name'

Określa nazwę zmiennej używanej do reprezentowania zapytania zdefiniowanego przez @input_data_1. Typ danych zmiennej w skrycie zewnętrznym zależy od języka. W przypadku języka R zmienna wejściowa jest ramką danych. W przypadku języka Python dane wejściowe muszą być tabelaryczne. input_data_1_name jest nazwa_systemu. Wartość domyślna to InputDataSet.

[ @output_data_1_name = ] N'output_data_1_name'

Określa nazwę zmiennej w skrypce zewnętrznym, która zawiera dane, które mają zostać zwrócone do programu SQL Server po zakończeniu wywołania procedury składowanej. Typ danych zmiennej w skrycie zewnętrznym zależy od języka. W przypadku języka R dane wyjściowe muszą być ramką danych. W przypadku języka Python dane wyjściowe muszą być ramką danych biblioteki pandas. output_data_1_name jest nazwa_systemu. Wartość domyślna to OutputDataSet.

[ @parallel = ] { 0 | 1 }

Włącz równoległe wykonywanie skryptów języka R, ustawiając parametr @parallel na 1. Wartość domyślna dla tego parametru to 0 (brak równoległości). Jeśli @parallel = 1 i dane wyjściowe są przesyłane strumieniowo bezpośrednio do maszyny klienckiej, wymagana jest klauzula WITH RESULT SETS i należy określić schemat wyjściowy.

• W przypadku skryptów języka R, które nie używają funkcji RevoScaleR, użycie parametru @parallel może być korzystne dla przetwarzania dużych zestawów danych, przy założeniu, że skrypt może być trywialnie równoległy. Na przykład w przypadku używania funkcji R predict z modelem do generowania nowych przewidywań ustaw @parallel = 1 jako wskazówkę dla aparatu zapytań. Jeśli zapytanie może być zrównoleglizowane, wiersze są dystrybuowane zgodnie z ustawieniem MAXDOP.

• W przypadku skryptów języka R korzystających z funkcji RevoScaleR przetwarzanie równoległe jest obsługiwane automatycznie i nie należy określać @parallel = 1 wywołania sp_execute_external_script.

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

Lista deklaracji parametrów wejściowych używanych w skryfikcie zewnętrznym.

[ @parameter1 = ] 'wartość1' [ OUT | OUTPUT ] [ , ... n ]

Lista wartości parametrów wejściowych używanych przez skrypt zewnętrzny.

Uwagi

Domyślnie zestawy wyników zwracane przez tę procedurę składowaną są danymi wyjściowymi bez nazw kolumn. Nazwy kolumn używane w skrytecie są lokalne dla środowiska skryptów i nie są odzwierciedlane w wyjściowym zestawie wyników. Aby nazwać kolumny zestawu wyników, użyj klauzuli WITH RESULT SETEXECUTE.

Oprócz zwracania zestawu wyników można zwrócić wartości skalarne do parametrów WYJŚCIOWYch.

Monitorowanie wykonywania skryptu

Monitorowanie wykonywania skryptu przy użyciu sys.dm_external_script_requests i sys.dm_external_script_execution_stats.

Wykonywanie przesyłania strumieniowego dla skryptów języka Python i R

Przesyłanie strumieniowe umożliwia skryptowi języka Python lub R pracę z większą ilością danych niż w pamięci. Aby kontrolować liczbę wierszy przekazywanych podczas przesyłania strumieniowego, określ wartość całkowitą parametru @r_rowsPerRead w kolekcji @params. Jeśli na przykład trenujesz model, który używa bardzo szerokich danych, możesz dostosować wartość w celu odczytania mniejszej liczby wierszy, aby upewnić się, że wszystkie wiersze mogą być wysyłane w jednym kawałku danych. Możesz również użyć tego parametru do zarządzania liczbą wierszy odczytywanych i przetwarzanych jednocześnie, aby rozwiązać problemy z wydajnością serwera.

Zarówno parametr @r_rowsPerRead do przesyłania strumieniowego, jak i argument @parallel należy wziąć pod uwagę wskazówki. Aby wskazówka była stosowana, należy wygenerować plan zapytania SQL obejmujący przetwarzanie równoległe. Jeśli nie jest to możliwe, nie można włączyć przetwarzania równoległego.

Ograniczenia

Typy danych

Następujące typy danych nie są obsługiwane w przypadku użycia w zapytaniu wejściowym lub parametrach procedury sp_execute_external_script i zwracanie nieobsługiwanego błędu typu.

Aby obejść ten problem, CAST kolumnę lub wartość do obsługiwanego typu w Transact-SQL przed wysłaniem go do skryptu zewnętrznego.

• kursora
• znacznika czasu
• datetime2, datetimeoffset, godzina
• sql_variant
• tekstobrazu
• xml
• hierarchyid, geometria , geografia
• Typy zdefiniowane przez użytkownika środowiska CLR

Ogólnie rzecz biorąc, każdy zestaw wyników, którego nie można zamapować na typ danych Transact-SQL, to dane wyjściowe NULL.

Ograniczenia specyficzne dla języka R

Jeśli dane wejściowe zawierają wartości daty/godziny, które nie pasują do dopuszczalnego zakresu wartości w języku R, wartości są konwertowane na NA. Jest to wymagane, ponieważ uczenie maszynowe SQL zezwala na większy zakres wartości niż jest obsługiwane w języku R.

Wartości zmiennoprzecinkowe (na przykład +Inf, -Inf, NaN) nie są obsługiwane w uczeniu maszynowym SQL, mimo że oba języki używają standardu IEEE 754. Bieżące zachowanie wysyła tylko wartości bezpośrednio do bazy danych SQL; w rezultacie klient SQL zgłasza błąd. W związku z tym te wartości są konwertowane na NULL.

Uprawnienia

Wymaga uprawnień DO WYKONYWANIA DOWOLNEGO ZEWNĘTRZNEGO SKRYPTU bazy danych.

Przykłady

Ta sekcja zawiera przykłady użycia tej procedury składowanej do wykonywania skryptów języka R lub Python przy użyciu języka Transact-SQL.

A. Zwracanie zestawu danych języka R do programu SQL Server

Poniższy przykład tworzy procedurę składowaną, która używa sp_execute_external_script do zwrócenia zestawu danych Iris dołączonego do języka 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. Tworzenie modelu języka Python i generowanie na jego podstawie wyników

W tym przykładzie pokazano, jak używać sp_execute_external_script do generowania wyników w prostym modelu języka Python.

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

Nagłówki kolumn używane w kodzie języka Python nie są danymi wyjściowymi programu SQL Server; w związku z tym użyj instrukcji WITH RESULT, aby określić nazwy kolumn i typy danych, które mają być używane przez język SQL.

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

W przypadku oceniania można również użyć natywnej funkcji PREDICT, która jest zwykle szybsza, ponieważ pozwala uniknąć wywoływania środowiska uruchomieniowego języka Python lub R.

Powiązana zawartość

• uczenia maszynowego SQL
• Co to są rozszerzenia języka programu SQL Server?
• System procedur składowanych (Transact-SQL)
• CREATE EXTERNAL LIBRARY (Transact-SQL)
• sp_prepare (Transact SQL)
• sp_configure (Transact-SQL)
• opcji konfiguracji serwera z włączoną obsługą skryptów zewnętrznych
• SERVERPROPERTY (Transact-SQL)
• sql Server, obiekt skryptów zewnętrznych
• sys.dm_external_script_requests
• sys.dm_external_script_execution_stats