sp_execute_external_script (Transact-SQL)

Şunlar için geçerlidir: SQL Server 2016 (13.x) ve üzeri Azure SQL Yönetilen Örneği

sp_execute_external_script saklı yordamı, yordama giriş bağımsız değişkeni olarak sağlanan bir betiği yürütür ve Machine Learning Services ve Dil Uzantılarıile birlikte kullanılır.

Machine Learning Services için python ve R desteklenen dillerdir. Dil Uzantıları için Java desteklenir ancak create EXTERNAL LANGUAGEile tanımlanmalıdır.

sp_execute_external_scriptyürütmek için önce Machine Learning Services veya Dil Uzantıları'nı yüklemeniz gerekir. Daha fazla bilgi için bkz. Windows'a SQL Server Machine Learning Services (Python ve R) yükleme ve Linuxveya Sql Server Dil Uzantılarını Windows ve Linuxyükleme .

Transact-SQL söz dizimi kuralları

Sözdizimi

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

Bağımsız değişken

Önemli

Genişletilmiş saklı yordamlar için bağımsız değişkenler, Sözdizimi bölümünde açıklandığı gibi belirli bir sırada girilmelidir. Parametreler sıra dışı girilirse bir hata iletisi oluşur.

[ @language = ] N'dil'

Betik dilini gösterir. dil sysname. Geçerli değerler R, pythonve create EXTERNAL LANGUAGE (örneğin Java) ile tanımlanan tüm dillerdir.

[ @script = ] N'betiği'

Değişmez değer veya değişken girişi olarak belirtilen dış dil betiği. betiknvarchar(max).

[ @input_data_1 = ] N'input_data_1'

Dış betik tarafından kullanılan giriş verilerini bir Transact-SQL sorgusu biçiminde belirtir. input_data_1 veri türünvarchar(max) .

[ @input_data_1_name = ] N'input_data_1_name'

@input_data_1tarafından tanımlanan sorguyu temsil etmek için kullanılan değişkenin adını belirtir. Dış betikteki değişkenin veri türü dile bağlıdır. R için giriş değişkeni bir veri çerçevesidir. Python için girişin tablosal olması gerekir. input_data_1_name sysname. Varsayılan değer InputDataSet.

[ @input_data_1_order_by_columns = ] N'input_data_1_order_by_columns'

Bölüm başına model oluşturmak için kullanılır. Sonuç kümesini sıralamak için kullanılan sütunun adını (örneğin, ürün adına göre) belirtir. Dış betikteki değişkenin veri türü dile bağlıdır. R için giriş değişkeni bir veri çerçevesidir. Python için girişin tablosal olması gerekir.

[ @input_data_1_partition_by_columns = ] N'input_data_1_partition_by_columns'

Bölüm başına model oluşturmak için kullanılır. Coğrafi bölge veya tarih gibi verileri segmentlere ayırmak için kullanılan sütunun adını belirtir. Dış betikteki değişkenin veri türü dile bağlıdır. R için giriş değişkeni bir veri çerçevesidir. Python için girişin tablosal olması gerekir.

[ @output_data_1_name = ] N'output_data_1_name'

Saklı yordam çağrısı tamamlandıktan sonra SQL Server'a döndürülecek verileri içeren dış betikteki değişkenin adını belirtir. Dış betikteki değişkenin veri türü dile bağlıdır. R için çıkış bir veri çerçevesi olmalıdır. Python için çıkış bir pandas veri çerçevesi olmalıdır. output_data_1_name sysname. Varsayılan değer OutputDataSetdeğeridir.

[ @parallel = ] { 0 | 1 }

@parallel parametresini 1olarak ayarlayarak R betiklerinin paralel yürütülmesini etkinleştirin. Bu parametre için varsayılan değer 0 'dir (paralellik yoktur). @parallel = 1 ve çıkış doğrudan istemci makinesine akışla aktarılıyorsa, WITH RESULT SETS yan tümcesi gerekir ve bir çıkış şeması belirtilmelidir.

• RevoScaleR işlevlerini kullanmayan R betikleri için @parallel parametresinin kullanılması, betiğin önemsiz bir şekilde paralelleştirilebileceği varsayılarak büyük veri kümelerini işlemek için yararlı olabilir. Örneğin, R predict işlevini bir modelle kullanarak yeni tahminler oluştururken @parallel = 1 sorgu altyapısına bir ipucu olarak ayarlayın. Sorgu paralelleştirilebilirse, satırlar MAXDOP ayarına göre dağıtılır.

• RevoScaleR işlevlerini kullanan R betikleri için paralel işleme otomatik olarak işlenir ve @parallel = 1 çağrısına sp_execute_external_script belirtmemeniz gerekir.

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

Dış betikte kullanılan giriş parametresi bildirimlerinin listesi.

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

Dış betik tarafından kullanılan giriş parametreleri için değerlerin listesi.

Açıklamalar

Önemli

Sorgu ağacı SQL makine öğrenmesi tarafından denetleniyor ve kullanıcılar sorgu üzerinde rastgele işlemler gerçekleştiremiyor.

Desteklenen bir dilde yazılmış betikleri yürütmek için sp_execute_external_script kullanın. Desteklenen diller, Machine Learning Services ile kullanılan Python ve R ve Dil Uzantıları ile kullanılan CREATE EXTERNAL LANGUAGE (örneğin Java) ile tanımlanan tüm dillerdir.

Varsayılan olarak, bu saklı yordam tarafından döndürülen sonuç kümeleri adsız sütunlarla çıkıştır. Betik içinde kullanılan sütun adları, betik oluşturma ortamında yereldir ve çıkış sonucu kümesine yansıtılamaz. Sonuç kümesi sütunlarını adlandırmak için WITH RESULT SET yan tümcesini kullanın.

Sonuç kümesi döndürmeye ek olarak, OUTPUT parametrelerini kullanarak skaler değerler döndürebilirsiniz.

Dış kaynak havuzunu yapılandırarak dış betikler tarafından kullanılan kaynakları denetleyebilirsiniz. Daha fazla bilgi için bkz. CREATE EXTERNAL RESOURCE POOL. İş yüküyle ilgili bilgiler kaynak yöneticisi katalog görünümlerinden, DMV'lerden ve sayaçlardan alınabilir. Daha fazla bilgi için bkz . Resource governor katalog görünümleri, Resource governor ile ilgili dinamik yönetim görünümleri ve SQL Server, Dış Betikler nesnesi.

Betik yürütmeyi izleme

sys.dm_external_script_requests ve sys.dm_external_script_execution_statskullanarak betik yürütmeyi izleyin.

Bölüm modelleme parametreleri

Bölümlenmiş verilerde modellemeyi etkinleştiren iki ek parametre ayarlayabilirsiniz; burada bölümler, veri kümesini doğal olarak mantıksal bölümlere bölen ve yalnızca betik yürütme sırasında oluşturulan ve kullanılan bir veya daha fazla sütunu temel alır. Yaş, cinsiyet, coğrafi bölge, tarih veya saat için yinelenen değerler içeren sütunlar, bölümlenmiş veri kümelerine ödünç veren birkaç örnektir.

İki parametre input_data_1_partition_by_columns ve input_data_1_order_by_columns, burada sonuç kümesini sıralamak için ikinci parametre kullanılır. Parametreler, her bölüm için bir kez yürütülen dış betik ile sp_execute_external_script giriş olarak geçirilir. Daha fazla bilgi ve örnek için bkz. Öğretici: SQL Serverüzerinde R'de bölüm tabanlı modeller oluşturma.

@parallel = 1belirterek betiği paralel olarak yürütebilirsiniz. Giriş sorgusu paralel hale getirilebiliyorsa, @parallel = 1 bağımsız değişkenlerinizin bir parçası olarak sp_execute_external_scriptolarak ayarlamanız gerekir. Varsayılan olarak, sorgu iyileştiricisi 256'dan fazla satırı olan tablolarda @parallel = 1 altında çalışır, ancak bunu açıkça işlemek istiyorsanız, bu betik bir gösterim olarak parametresini içerir.

Bahşiş

Eğitim iş yükleri için, microsoft-rx olmayan algoritmalar kullananlar bile @parallel herhangi bir rastgele eğitim betiğiyle kullanabilirsiniz. Genellikle yalnızca RevoScaleR algoritmaları (rx ön eki ile) SQL Server'daki eğitim senaryolarında paralellik sunar. Ancak SQL Server 2019 (15.x) ve sonraki sürümlerindeki yeni parametrelerle, bu özellik ile özel olarak tasarlanmış olmayan işlevleri çağıran bir betiği paralelleştirebilirsiniz.

Python ve R betikleri için akış yürütme

Akış, Python veya R betiğinin belleğe sığabilenden daha fazla veriyle çalışmasını sağlar. Akış sırasında geçirilen satır sayısını denetlemek için, @r_rowsPerRead koleksiyonunda @params parametresi için bir tamsayı değeri belirtin. Örneğin, çok geniş veri kullanan bir model eğitirseniz, tüm satırların bir veri öbeği halinde gönderilebilmesini sağlamak için değeri daha az satır okuyacak şekilde ayarlayabilirsiniz. Sunucu performansı sorunlarını azaltmak için bu parametreyi, bir kerede okunan ve işlenen satır sayısını yönetmek için de kullanabilirsiniz.

Hem akış için @r_rowsPerRead parametresi hem de @parallel bağımsız değişkeni ipuçları olarak değerlendirilmelidir. İpucunun uygulanması için paralel işleme içeren bir SQL sorgu planı oluşturmak mümkün olmalıdır. Bu mümkün değilse paralel işleme etkinleştirilemiyor.

Not

Akış ve paralel işleme yalnızca Enterprise Edition'da desteklenir. Parametreleri, hata oluşturmadan Standard Edition'daki sorgularınıza ekleyebilirsiniz, ancak parametrelerin hiçbir etkisi yoktur ve R betikleri tek bir işlemde çalıştırılır.

Sınırlama

Veri türleri

Giriş sorgusunda veya sp_execute_external_script yordamının parametrelerinde kullanıldığında aşağıdaki veri türleri desteklenmez ve desteklenmeyen tür hatası döndürür.

Geçici bir çözüm olarak, sütunu veya değeri dış betike göndermeden önce Transact-SQL'da desteklenen bir türe CAST.

• İmleç
• zaman damgası
• datetime2, datetimeoffsetsaat
• sql_variant
• metinresim
• xml
• hierarchyid, geometri, coğrafya
• CLR kullanıcı tanımlı türler

Genel olarak, bir Transact-SQL veri türüne eşlenebilen sonuç kümeleri NULLolarak çıktılanır.

R'ye özgü kısıtlamalar

Giriş R'deki izin verilen değer aralığına uymayan datetime değerleri içeriyorsa, değerler NAdönüştürülür. SQL makine öğrenmesi R dilinde desteklenenden daha geniş bir değer aralığına izin verdikçe bu gereklidir.

Kayan değerler (örneğin, +Inf, -Inf, NaN), her iki dil de IEEE 754 kullansa bile SQL makine öğrenmesinde desteklenmez. Geçerli davranış yalnızca değerleri doğrudan SQL'e gönderir; sonuç olarak, SQL istemcisi bir hata oluşturur. Bu nedenle, bu değerler NULLdönüştürülür.

İzinler

HERHANGİ Bİr HARİÇ BETIK VERITABANı YÜRÜTME izni gerektirir.

Örnekler

Bu bölüm, transact-SQL kullanarak R veya Python betiklerini yürütmek için bu saklı yordamın nasıl kullanılabileceğini gösteren örnekler içerir.

A. R veri kümesini SQL Server'a döndürme

Aşağıdaki örnek, R ile birlikte gelen Iris veri kümesini döndürmek için sp_execute_external_script kullanan bir saklı yordam oluşturur.

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 modeli oluşturma ve bu modelden puanlar oluşturma

Bu örnekte, basit bir Python modelinde puan oluşturmak için sp_execute_external_script nasıl kullanılacağı gösterilmektedir.

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

Python kodunda kullanılan sütun başlıkları SQL Server'a çıkış değildir; bu nedenle, SQL'in kullanacağını sütun adlarını ve veri türlerini belirtmek için WITH RESULT deyimini kullanın.

C. SQL Server'dan alınan verileri temel alan bir R modeli oluşturma

Aşağıdaki örnek, bir iris modeli oluşturmak ve modeli döndürmek için sp_execute_external_script kullanan bir saklı yordam oluşturur.

Not

Bu örnek, e1071 paketinin önceden yüklenmesini gerektirir. Daha fazla bilgi için bkz. sqlmlutilsile R paketlerini yükleme .

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

Python kullanarak benzer bir model oluşturmak için @language=N'R' olan dil tanımlayıcısını @language = N'Python'olarak değiştirir ve @script bağımsız değişkeninde gerekli değişiklikleri yaparsınız. Aksi takdirde, tüm parametreler R ile aynı şekilde çalışır.

Puanlama için python veya R çalışma zamanını çağırmaktan kaçındığı için genellikle daha hızlı olan yerel PREDICT işlevini de kullanabilirsiniz.

İlgili içerik

• SQL makine öğrenmesi
• SQL Server Dil Uzantıları nedir?
• Sistem saklı yordamları (Transact-SQL)
• CREATE EXTERNAL LIBRARY (Transact-SQL)
• sp_prepare (Transact SQL)
• sp_configure (Transact-SQL)
• Dış betikler etkinleştirilmiş sunucu yapılandırma seçeneği
• SERVERPROPERTY (Transact-SQL)
• SQL Server, Dış Betikler nesnesi
• sys.dm_external_script_requests
• sys.dm_external_script_execution_stats