sp_execute_external_script (T-SQL)

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

Prosedur sp_execute_external_script tersimpan menjalankan skrip yang disediakan sebagai argumen input untuk prosedur, dan digunakan dengan Pembelajaran Mesin Services dan Language Extensions.

Untuk layanan Pembelajaran Mesin, Python dan R adalah bahasa yang didukung. Untuk Ekstensi Bahasa, Java didukung tetapi harus ditentukan 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 Pembelajaran Mesin Services (Python dan R) di Windows dan Linux, atau Menginstal Ekstensi Bahasa SQL Server di Windows dan Linux.

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

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).

[ @script = ] N'script'

Skrip bahasa eksternal 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. Untuk R, variabel input adalah bingkai data. Untuk Python, input harus bertabel. 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. Untuk R, variabel input adalah bingkai data. Untuk Python, input harus bertabel.

[ @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 mengelompokan data, seperti wilayah geografis atau tanggal. Jenis data variabel dalam skrip eksternal bergantung pada bahasa. Untuk R, variabel input adalah bingkai data. Untuk Python, input harus bertabel.

[ @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 panda. output_data_1_name adalah sysname. Nilai defaultnya adalah OutputDataSet.

[ @parallel = ] { 0 | 1 }

Aktifkan eksekusi paralel skrip R dengan mengatur @parallel parameter ke 1. Default untuk parameter ini adalah 0 (tidak ada paralelisme). Jika @parallel = 1 dan output sedang dialirkan langsung ke komputer klien, maka WITH RESULT SETS klausul 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 trivial. 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 sp_execute_external_script panggilan.

[ @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.

Secara default, kumpulan 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 hasil, gunakan WITH RESULT SET klausul 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. Informasi tentang beban kerja dapat diperoleh dari tampilan katalog gubernur sumber daya, DMV, dan penghitung. Untuk informasi selengkapnya, lihat Tampilan Katalog Resource Governor, Tampilan Manajemen Dinamis Terkait Resource Governor, 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 sediakan yang secara alami mengelompokkan himpunan data ke dalam partisi logis, dibuat dan digunakan hanya 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 di R di SQL Server.

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 di SQL Server. Tetapi dengan parameter baru di SQL Server 2019 (15.x) dan versi yang lebih baru, Anda dapat menyejajarkan 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 pada satu waktu, untuk mengurangi masalah performa server.

@r_rowsPerRead Parameter untuk streaming dan @parallel argumen harus dianggap petunjuk. Agar petunjuk diterapkan, dimungkinkan untuk menghasilkan rencana kueri SQL yang mencakup 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 tidak didukung saat digunakan dalam kueri input atau parameter sp_execute_external_script prosedur, 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.

• Kursor
• timestamp
• datetime2, datetimeoffset, time
• aql_variant
• teks, gambar
• xml
• hierarki, geometri, geografi
• Jenis yang ditentukan pengguna CLR

Secara umum, kumpulan hasil apa pun 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 mengizinkan 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 sp_execute_external_script yang menggunakan untuk mengembalikan himpunan data Iris yang disertakan dengan 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. 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 tidak dihasilkan ke 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 sp_execute_external_script yang menggunakan untuk menghasilkan model iris dan mengembalikan model.

Catatan

Contoh ini memerlukan penginstalan lanjutan paket e1071 . Untuk informasi selengkapnya, lihat Menginstal paket R dengan 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

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.

Konten terkait

• Pembelajaran mesin SQL
• Apa itu Ekstensi Bahasa SQL Server?
• Prosedur tersimpan sistem (Transact-SQL)
• BUAT PUSTAKA EKSTERNAL (Transact-SQL)
• sp_prepare (Transact SQL)
• sp_configure (T-SQL)
• Opsi Konfigurasi Server yang diaktifkan skrip eksternal
• SERVERPROPERTY (Transact-SQL)
• SQL Server, objek Skrip Eksternal
• sys.dm_external_script_requests
• sys.dm_external_script_execution_stats