sp_execute_external_script (T-SQL)

Berlaku untuk: SQL Server 2016 (13.x) dan yang lebih baru Azure SQL Managed Instance

Prosedur tersimpan sp_execute_external_script menjalankan skrip yang disediakan sebagai argumen input untuk prosedur, dan digunakan dengan Layanan Pembelajaran Mesin dan Ekstensi Bahasa.

Untuk Layanan Pembelajaran Mesin, Python dan R adalah bahasa yang didukung. Untuk Ekstensi Bahasa, Java didukung tetapi harus didefinisikan dengan CREATE EXTERNAL LANGUAGE.

Untuk menjalankan sp_execute_external_script, Anda harus terlebih dahulu menginstal Layanan Pembelajaran Mesin atau Ekstensi Bahasa. Untuk informasi selengkapnya, lihat Menginstal SQL Server Machine Learning Services (Python dan R) di Windows dan Linux, atau Menginstal Ekstensi Bahasa SQL Server di Windows dan Linux.

Prosedur tersimpan sp_execute_external_script menjalankan skrip yang disediakan sebagai argumen input untuk prosedur, dan digunakan dengan Layanan Pembelajaran Mesin pada SQL Server 2017.

Untuk Layanan Pembelajaran Mesin, Python dan R adalah bahasa yang didukung.

Untuk menjalankan sp_execute_external_script, Anda harus terlebih dahulu menginstal Layanan Pembelajaran Mesin. Untuk informasi selengkapnya, lihat Menginstal SQL Server Machine Learning Services (Python dan R) di Windows.

Prosedur tersimpan sp_execute_external_script menjalankan skrip yang disediakan sebagai argumen input untuk prosedur, dan digunakan dengan Layanan R pada SQL Server 2016.

Untuk Layanan R, R adalah bahasa yang didukung.

Untuk menjalankan sp_execute_external_script, Anda harus menginstal R Services terlebih dahulu. Untuk informasi selengkapnya, lihat Menginstal SQL Server Machine Learning Services (Python dan R) di Windows.

Prosedur tersimpan sp_execute_external_script menjalankan skrip yang disediakan sebagai argumen input untuk prosedur, dan digunakan dengan Layanan Pembelajaran Mesin di Azure SQL Managed Instance.

Untuk Layanan Pembelajaran Mesin, Python dan R adalah bahasa yang didukung.

Untuk menjalankan sp_execute_external_script, Anda harus terlebih dahulu mengaktifkan Layanan Pembelajaran Mesin. Untuk informasi selengkapnya, lihat Layanan Pembelajaran Mesin dalam dokumentasi Azure SQL Managed Instance.

tautan topikIkon Konvensi Sintaks Transact-SQL

Sintaks

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

Sintaks untuk SQL Server 2017 dan yang lebih lama

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

Argumen

@language = N'language'

Menunjukkan bahasa skrip. bahasa adalah sysname. Nilai yang valid adalah R, Python, dan bahasa apa pun yang ditentukan dengan CREATE EXTERNAL LANGUAGE (misalnya, Java).

Menunjukkan bahasa skrip. bahasa adalah sysname. Pada SQL Server 2017, nilai yang valid adalah R dan Python.

Menunjukkan bahasa skrip. bahasa adalah sysname. Pada SQL Server 2016, satu-satunya nilai yang valid adalah R.

Menunjukkan bahasa skrip. bahasa adalah sysname. Dalam Azure SQL Managed Instance, nilai yang valid adalah R dan Python.

@script = Skrip bahasa eksternal N'script yang ditentukan sebagai input literal atau variabel. skrip adalah nvarchar(max).

[ @input_data_1 = N'input_data_1' ] Menentukan data input yang digunakan oleh skrip eksternal dalam bentuk kueri Transact-SQL. Jenis data input_data_1 adalah nvarchar(max).

[ @input_data_1_name = N'input_data_1_name' ] Menentukan nama variabel yang digunakan untuk mewakili kueri yang ditentukan oleh @input_data_1. Jenis data variabel dalam skrip eksternal bergantung pada bahasa. Dalam kasus R, variabel input adalah bingkai data. Dalam kasus Python, input harus tabular. input_data_1_name adalah sysname. Nilai defaultnya adalah InputDataSet.

[ @input_data_1_order_by_columns = N'input_data_1_order_by_columns' ] Digunakan untuk membangun model per partisi. Menentukan nama kolom yang digunakan untuk mengurutkan tataan hasil, misalnya menurut nama produk. Jenis data variabel dalam skrip eksternal bergantung pada bahasa. Dalam kasus R, variabel input adalah bingkai data. Dalam kasus Python, input harus tabular.

[ @input_data_1_partition_by_columns = N'input_data_1_partition_by_columns' ] Digunakan untuk membangun model per partisi. Menentukan nama kolom yang digunakan untuk menyegmentasi data, seperti wilayah geografis atau tanggal. Jenis data variabel dalam skrip eksternal bergantung pada bahasa. Dalam kasus R, variabel input adalah bingkai data. Dalam kasus Python, input harus tabular.

[ @output_data_1_name = N'output_data_1_name' ]Menentukan nama variabel dalam skrip eksternal yang berisi data yang akan dikembalikan ke SQL Server setelah menyelesaikan panggilan prosedur tersimpan. Jenis data variabel dalam skrip eksternal bergantung pada bahasa. Untuk R, output harus berupa bingkai data. Untuk Python, output harus berupa bingkai data pandas. output_data_1_name adalah sysname. Nilai defaultnya adalah OutputDataSet.

[ @parallel = 0 | 1 ] Aktifkan eksekusi paralel skrip R dengan mengatur parameter ke @parallel 1. Default untuk parameter ini adalah 0 (tanpa paralelisme). Jika @parallel = 1 dan output sedang dialirkan langsung ke komputer klien, maka WITH RESULT SETS klausa diperlukan dan skema output harus ditentukan.

  • Untuk skrip R yang tidak menggunakan fungsi RevoScaleR, menggunakan @parallel parameter dapat bermanfaat untuk memproses himpunan data besar, dengan asumsi skrip dapat diparalelkan secara sepele. Misalnya, saat menggunakan fungsi R predict dengan model untuk menghasilkan prediksi baru, atur @parallel = 1 sebagai petunjuk ke mesin kueri. Jika kueri dapat diparalelkan, baris didistribusikan sesuai dengan pengaturan MAXDOP .

  • Untuk skrip R yang menggunakan fungsi RevoScaleR, pemrosesan paralel ditangani secara otomatis dan Anda tidak boleh menentukan @parallel = 1 ke panggilan sp_execute_external_script .

[ @params = N'@parameter_name data_type [ OUT | OUTPUT ] [ ,...n ]' ] Daftar deklarasi parameter input yang digunakan dalam skrip eksternal.

[ @parameter1 = 'value1' [ OUT | OUTPUT ] [ ,...n ] ] Daftar nilai untuk parameter input yang digunakan oleh skrip eksternal.

Keterangan

Penting

Pohon kueri dikendalikan oleh pembelajaran mesin SQL dan pengguna tidak dapat melakukan operasi arbitrer pada kueri.

Gunakan sp_execute_external_script untuk menjalankan skrip yang ditulis dalam bahasa yang didukung. Bahasa yang didukung adalah Python dan R yang digunakan dengan Layanan Pembelajaran Mesin, dan bahasa apa pun yang ditentukan dengan CREATE EXTERNAL LANGUAGE (misalnya, Java) yang digunakan dengan Ekstensi Bahasa.

Gunakan sp_execute_external_script untuk menjalankan skrip yang ditulis dalam bahasa yang didukung. Bahasa yang didukung adalah Python dan R di SQL Server 2017 Machine Learning Services.

Gunakan sp_execute_external_script untuk menjalankan skrip yang ditulis dalam bahasa yang didukung. Satu-satunya bahasa yang didukung adalah R dalam SQL Server 2016 R Services.

Gunakan sp_execute_external_script untuk menjalankan skrip yang ditulis dalam bahasa yang didukung. Bahasa yang didukung adalah Python dan R di Azure SQL Managed Instance Machine Learning Services.

Secara default, tataan hasil yang dikembalikan oleh prosedur tersimpan ini adalah output dengan kolom yang tidak disebutkan namanya. Nama kolom yang digunakan dalam skrip bersifat lokal untuk lingkungan pembuatan skrip dan tidak tercermin dalam tataan hasil yang dihasilkan. Untuk memberi nama kolom tataan WITH RESULT SET hasil, gunakan klausa .EXECUTE

Selain mengembalikan tataan hasil, Anda dapat mengembalikan nilai skalar untuk menggunakan parameter OUTPUT.

Anda dapat mengontrol sumber daya yang digunakan oleh skrip eksternal dengan mengonfigurasi kumpulan sumber daya eksternal. Untuk informasi selengkapnya, lihat MEMBUAT KUMPULAN SUMBER DAYA EKSTERNAL (Transact-SQL). Informasi tentang beban kerja dapat diperoleh dari tampilan katalog gubernur sumber daya, DMV, dan penghitung. Untuk informasi selengkapnya, lihat Resource Governor Tampilan Katalog (Transact-SQL), Resource Governor Tampilan Manajemen Dinamis Terkait (Transact-SQL), dan SQL Server, Objek Skrip Eksternal.

Memantau eksekusi skrip

Pantau eksekusi skrip menggunakan sys.dm_external_script_requests dan sys.dm_external_script_execution_stats.

Parameter untuk pemodelan partisi

Anda dapat mengatur dua parameter tambahan yang memungkinkan pemodelan pada data yang dipartisi, di mana partisi didasarkan pada satu atau beberapa kolom yang Anda berikan yang secara alami membagmentasikan himpunan data ke dalam partisi logis yang dibuat dan hanya digunakan selama eksekusi skrip. Kolom yang berisi nilai berulang untuk usia, jenis kelamin, wilayah geografis, tanggal atau waktu, adalah beberapa contoh yang meminjamkan diri mereka ke himpunan data yang dipartisi.

Dua parameter input_data_1_partition_by_columns dan input_data_1_order_by_columns, di mana parameter kedua digunakan untuk mengurutkan tataan hasil. Parameter diteruskan sebagai input ke sp_execute_external_script dengan skrip eksternal yang dijalankan sekali untuk setiap partisi. Untuk informasi dan contoh selengkapnya, lihat Tutorial: Membuat model berbasis partisi.

Anda dapat menjalankan skrip secara paralel dengan menentukan @parallel=1. Jika kueri input dapat diparalelkan, Anda harus mengatur @parallel=1 sebagai bagian dari argumen Anda ke sp_execute_external_script. Secara default, pengoptimal kueri beroperasi di bawah @parallel=1 pada tabel yang memiliki lebih dari 256 baris, tetapi jika Anda ingin menangani ini secara eksplisit, skrip ini menyertakan parameter sebagai demonstrasi.

Tip

Untuk workoad pelatihan, Anda dapat menggunakan @parallel dengan skrip pelatihan arbitrer apa pun, bahkan yang menggunakan algoritma non-Microsoft-rx. Biasanya, hanya algoritma RevoScaleR (dengan awalan rx) yang menawarkan paralelisme dalam skenario pelatihan dalam SQL Server. Tetapi dengan parameter baru di SQL Server 2019 dan yang lebih baru, Anda dapat memparalelkan skrip yang memanggil fungsi yang tidak direkayasa secara khusus dengan kemampuan tersebut.

Eksekusi streaming untuk skrip Python dan R

Streaming memungkinkan skrip Python atau R bekerja dengan lebih banyak data daripada yang dapat pas dalam memori. Untuk mengontrol jumlah baris yang diteruskan selama streaming, tentukan nilai bilangan bulat untuk parameter , @r_rowsPerRead dalam @params koleksi. Misalnya, jika Anda melatih model yang menggunakan data yang sangat luas, Anda dapat menyesuaikan nilai untuk membaca lebih sedikit baris, untuk memastikan bahwa semua baris dapat dikirim dalam satu potongan data. Anda mungkin juga menggunakan parameter ini untuk mengelola jumlah baris yang dibaca dan diproses sekaligus, untuk mengurangi masalah performa server.

@r_rowsPerRead Parameter untuk streaming dan @parallel argumen harus dianggap sebagai petunjuk. Agar petunjuk diterapkan, dimungkinkan untuk menghasilkan rencana kueri SQL yang menyertakan pemrosesan paralel. Jika ini tidak memungkinkan, pemrosesan paralel tidak dapat diaktifkan.

Catatan

Streaming dan pemrosesan paralel hanya didukung di Edisi Perusahaan. Anda dapat menyertakan parameter dalam kueri Anda di Edisi Standar tanpa menimbulkan kesalahan, tetapi parameter tidak berpengaruh dan skrip R berjalan dalam satu proses.

Batasan

Jenis data

Jenis data berikut ini tidak didukung saat digunakan dalam kueri input atau parameter prosedur sp_execute_external_script , dan mengembalikan kesalahan jenis yang tidak didukung.

Sebagai solusinya, CAST kolom atau nilai ke jenis yang didukung di Transact-SQL sebelum mengirimkannya ke skrip eksternal.

  • cursor

  • timestamp

  • datetime2, datetimeoffset, time

  • aql_variant

  • teks, gambar

  • xml

  • hierarki,geometri, geografi

  • Jenis yang ditentukan pengguna CLR

Secara umum, setiap tataan hasil yang tidak dapat dipetakan ke jenis data Transact-SQL, adalah output sebagai NULL.

Pembatasan khusus untuk R

Jika input menyertakan nilai tanggalwaktu yang tidak sesuai dengan rentang nilai yang diizinkan dalam R, nilai dikonversi ke NA. Ini diperlukan karena pembelajaran mesin SQL memungkinkan rentang nilai yang lebih besar daripada yang didukung dalam bahasa R.

Nilai float (misalnya, +Inf, -Inf, NaN) tidak didukung dalam pembelajaran mesin SQL meskipun kedua bahasa menggunakan IEEE 754. Perilaku saat ini hanya mengirim nilai ke SQL secara langsung; akibatnya, klien SQL melemparkan kesalahan. Oleh karena itu, nilai-nilai ini dikonversi ke NULL.

Izin

Memerlukan izin database EXECUTE ANY EXTERNAL SCRIPT .

Contoh

Bagian ini berisi contoh bagaimana prosedur tersimpan ini dapat digunakan untuk menjalankan skrip R atau Python menggunakan Transact-SQL.

J. Mengembalikan himpunan data R ke SQL Server

Contoh berikut membuat prosedur tersimpan yang menggunakan sp_execute_external_script untuk mengembalikan himpunan data Iris yang disertakan dengan R.

DROP PROC IF EXISTS get_iris_dataset;  
go  
CREATE PROC 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. Membuat model Python dan menghasilkan skor darinya

Contoh ini menggambarkan cara menggunakan sp_execute_external_script untuk menghasilkan skor pada model Python sederhana.

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

Judul kolom yang digunakan dalam kode Python bukan output untuk SQL Server; oleh karena itu, gunakan pernyataan WITH RESULT untuk menentukan nama kolom dan jenis data untuk digunakan SQL.

C. Membuat model R berdasarkan data dari SQL Server

Contoh berikut membuat prosedur tersimpan yang menggunakan sp_execute_external_script untuk menghasilkan model iris dan mengembalikan model.

Catatan

Contoh ini memerlukan penginstalan lanjutan paket e1071. Untuk informasi selengkapnya, lihat Menginstal paket R tambahan di SQL Server.

DROP PROC IF EXISTS generate_iris_model;
GO
CREATE PROC 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

Untuk menghasilkan model serupa menggunakan Python, Anda akan mengubah pengidentifikasi bahasa dari @language=N'R' ke @language = N'Python', dan membuat modifikasi yang diperlukan ke @script argumen . Jika tidak, semua parameter berfungsi dengan cara yang sama seperti untuk R.

Untuk penilaian, Anda juga dapat menggunakan fungsi PREDICT asli, yang biasanya lebih cepat karena menghindari panggilan runtime Python atau R.

Lihat juga