Ubah kode R/Python untuk dijalankan dalam instans SQL Server (Dalam Database)

Artikel
05/30/2023

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

Artikel ini menyediakan panduan tingkat tinggi tentang cara memodifikasi kode R atau Python untuk dijalankan sebagai prosedur tersimpan SQL Server untuk meningkatkan performa saat mengakses data SQL.

Saat Anda memindahkan kode R/Python dari IDE lokal atau lingkungan lain ke SQL Server, kode umumnya berfungsi tanpa modifikasi lebih lanjut. Ini terutama berlaku untuk kode sederhana, seperti fungsi yang mengambil beberapa input dan mengembalikan nilai. Juga lebih mudah untuk port solusi yang menggunakan paketrevoscalepyRevoScaleR/, yang mendukung eksekusi dalam konteks eksekusi yang berbeda dengan perubahan minimal. Perhatikan bahwa MicrosoftML berlaku untuk SQL Server 2016 (13.x), SQL Server 2017 (14.x), dan SQL Server 2019 (15.x), dan tidak muncul di SQL Server 2022 (16.x).

Namun, kode Anda mungkin memerlukan perubahan besar jika salah satu hal berikut ini berlaku:

Anda menggunakan pustaka yang mengakses jaringan atau yang tidak dapat diinstal pada SQL Server.
Kode melakukan panggilan terpisah ke sumber data di luar SQL Server, seperti lembar kerja Excel, file pada berbagi, dan database lainnya.
Anda ingin membuat parameter prosedur tersimpan dan menjalankan kode dalam parameter @scriptsp_execute_external_script.
Solusi asli Anda mencakup beberapa langkah yang mungkin lebih efisien di lingkungan produksi jika dijalankan secara independen, seperti persiapan data atau rekayasa fitur vs. pelatihan model, penilaian, atau pelaporan.
Anda ingin mengoptimalkan performa dengan mengubah pustaka, menggunakan eksekusi paralel, atau membongkar beberapa pemrosesan ke SQL Server.

Langkah 1. Persyaratan dan sumber daya rencana

Paket

Tentukan paket mana yang diperlukan dan pastikan paket berfungsi SQL Server.
Instal paket terlebih dahulu, di pustaka paket default yang digunakan oleh Layanan Pembelajaran Mesin. Pustaka pengguna tidak didukung.

Sumber data

Jika Anda ingin menyematkan kode di sp_execute_external_script, identifikasi sumber data primer dan sekunder.
- Sumber data utama adalah himpunan data besar, seperti data pelatihan model, atau data input untuk prediksi. Rencanakan untuk memetakan himpunan data terbesar Anda ke parameter input sp_execute_external_script.
- Sumber data sekunder biasanya merupakan himpunan data yang lebih kecil, seperti daftar faktor, atau variabel pengelompokan tambahan.
Saat ini, sp_execute_external_script hanya mendukung satu himpunan data sebagai input ke prosedur tersimpan. Namun, Anda dapat menambahkan beberapa input skalar atau biner.

Panggilan prosedur tersimpan yang didahului oleh EXECUTE tidak dapat digunakan sebagai input untuk sp_execute_external_script. Anda dapat menggunakan kueri, tampilan, atau pernyataan SELECT valid lainnya.
Tentukan output yang Anda butuhkan. Jika Anda menjalankan kode menggunakan sp_execute_external_script, prosedur tersimpan hanya dapat menghasilkan satu bingkai data sebagai hasilnya. Namun, Anda juga dapat menghasilkan beberapa output skalar, termasuk plot dan model dalam format biner, serta nilai skalar lainnya yang berasal dari kode atau parameter SQL.

Jenis data

Untuk melihat secara mendetail pemetaan jenis data antara R/Python dan SQL Server, lihat artikel berikut ini:

Lihat jenis data yang digunakan dalam kode R/Python Anda dan lakukan hal berikut:

Buat daftar periksa kemungkinan masalah jenis data.

Semua jenis data R/Python didukung oleh SQL Server Machine Learning Services. Namun, SQL Server mendukung berbagai jenis data yang lebih besar daripada R atau Python. Oleh karena itu, beberapa konversi jenis data implisit dilakukan saat memindahkan data SQL Server ke dan dari kode Anda. Anda mungkin perlu secara eksplisit mentransmisikan atau mengonversi beberapa data.

Nilai NULL didukung. Namun, R menggunakan na konstruksi data untuk mewakili nilai yang hilang, yang mirip dengan null.
Pertimbangkan untuk menghilangkan dependensi pada data yang tidak dapat digunakan oleh R: misalnya, jenis data rowid dan GUID dari SQL Server tidak dapat dikonsumsi oleh R dan akan menghasilkan kesalahan.

Langkah 2. Mengonversi atau mengemas ulang kode

Berapa banyak Anda mengubah kode tergantung pada apakah Anda berniat untuk mengirimkan kode dari klien jarak jauh untuk dijalankan dalam konteks komputasi SQL Server, atau berniat untuk menyebarkan kode sebagai bagian dari prosedur tersimpan. Yang terakhir dapat memberikan performa dan keamanan data yang lebih baik, meskipun memberlakukan beberapa persyaratan tambahan.

Tentukan data input utama Anda sebagai kueri SQL sedapat mungkin untuk menghindari pergerakan data.

Saat menjalankan kode dalam prosedur tersimpan, Anda dapat melewati beberapa input skalar . Untuk parameter apa pun yang ingin Anda gunakan dalam output, tambahkan kata kunci OUTPUT .

Misalnya, input @model_name skalar berikut berisi nama model, yang juga kemudian dimodifikasi oleh skrip R, dan output di kolomnya sendiri dalam hasil:

-- declare a local scalar variable which will be passed into the R script
DECLARE @local_model_name AS NVARCHAR (50) = 'DefaultModel';

-- The below defines an OUTPUT variable in the scope of the R script, called model_name
-- Syntactically, it is defined by using the @model_name name. Be aware that the sequence
-- of these parameters is very important. Mandatory parameters to sp_execute_external_script
-- must appear first, followed by the additional parameter definitions like @params, etc.
EXECUTE sp_execute_external_script @language = N'R', @script = N'
  model_name <- "Model name from R script"
  OutputDataSet <- data.frame(InputDataSet$c1, model_name)'
  , @input_data_1 = N'SELECT 1 AS c1'
  , @params = N'@model_name nvarchar(50) OUTPUT'
  , @model_name = @local_model_name OUTPUT;

-- optionally, examine the new value for the local variable:
SELECT @local_model_name;

Variabel apa pun yang Anda berikan sebagai parameter prosedur tersimpan sp_execute_external_script harus dipetakan ke variabel dalam kode. Secara default, variabel dipetakan berdasarkan nama. Semua kolom dalam himpunan data input juga harus dipetakan ke variabel dalam skrip.

Misalnya, asumsikan skrip R Anda berisi rumus seperti ini:
```
formula <- ArrDelay ~ CRSDepTime + DayOfWeek + CRSDepHour:DayOfWeek
```
Kesalahan muncul jika himpunan data input tidak berisi kolom dengan nama yang cocok ArrDelay, CRSDepTime, DayOfWeek, CRSDepHour, dan DayOfWeek.
Dalam beberapa kasus, skema output harus didefinisikan terlebih dahulu untuk hasilnya.

Misalnya, untuk menyisipkan data ke dalam tabel, Anda harus menggunakan klausul WITH RESULT SET untuk menentukan skema.

Skema output juga diperlukan jika skrip menggunakan argumen @parallel=1. Alasannya adalah bahwa beberapa proses mungkin dibuat oleh SQL Server untuk menjalankan kueri secara paralel, dengan hasil yang dikumpulkan di akhir. Oleh karena itu, skema output harus disiapkan sebelum proses paralel dapat dibuat.

Dalam kasus lain, Anda dapat menghilangkan skema hasil dengan menggunakan opsi WITH RESULT SETS UNDEFINED. Pernyataan ini mengembalikan himpunan data dari skrip tanpa memberi nama kolom atau menentukan jenis data SQL.
Pertimbangkan untuk menghasilkan waktu atau pelacakan data menggunakan T-SQL daripada R/Python.

Misalnya, Anda dapat melewati waktu sistem atau informasi lain yang digunakan untuk audit dan penyimpanan dengan menambahkan panggilan T-SQL yang diteruskan ke hasil, daripada menghasilkan data serupa dalam skrip.

Meningkatkan performa dan keamanan

Hindari menulis prediksi atau hasil menengah ke file. Tulis prediksi ke tabel sebagai gantinya untuk menghindari pergerakan data.

Jalankan semua kueri terlebih dahulu, dan tinjau rencana kueri SQL Server untuk mengidentifikasi tugas yang dapat dilakukan secara paralel.

Jika kueri input dapat diparalelkan, atur @parallel=1 sebagai bagian dari argumen Anda untuk sp_execute_external_script.

Pemrosesan paralel dengan bendera ini biasanya dimungkinkan kapan saja SQL Server dapat bekerja dengan tabel yang dipartisi atau mendistribusikan kueri di antara beberapa proses dan mengagregasi hasil di akhir. Pemrosesan paralel dengan bendera ini biasanya tidak dimungkinkan jika Anda melatih model menggunakan algoritma yang mengharuskan semua data dibaca, atau jika Anda perlu membuat agregat.
Tinjau kode Anda untuk menentukan apakah ada langkah-langkah yang dapat dilakukan secara independen, atau dilakukan lebih efisien, dengan menggunakan panggilan prosedur tersimpan terpisah. Misalnya, Anda mungkin mendapatkan performa yang lebih baik dengan melakukan rekayasa fitur atau ekstraksi fitur secara terpisah dan menyimpan nilai ke tabel.
Cari cara untuk menggunakan T-SQL daripada kode R/Python untuk komputasi berbasis set.

Misalnya, solusi R ini menunjukkan bagaimana fungsi T-SQL yang ditentukan pengguna dan R dapat melakukan tugas rekayasa fitur yang sama: Ilmu Data Panduan End-to-End.
Konsultasikan dengan pengembang database untuk menentukan cara untuk meningkatkan performa dengan menggunakan fitur SQL Server seperti tabel yang dioptimalkan memori, atau, jika Anda memiliki Edisi Perusahaan, Resource Governor.
Jika Anda menggunakan R, maka jika memungkinkan ganti fungsi R konvensional dengan fungsi RevoScaleR yang mendukung eksekusi terdistribusi. Untuk informasi selengkapnya, lihat Perbandingan Fungsi Base R dan RevoScaleR.

Langkah 3. Bersiap untuk penerapan

Beri tahu administrator agar paket dapat diinstal dan diuji sebelum menyebarkan kode Anda.

Dalam lingkungan pengembangan, mungkin tidak apa-apa untuk menginstal paket sebagai bagian dari kode Anda, tetapi ini adalah praktik buruk di lingkungan produksi.

Pustaka pengguna tidak didukung, terlepas dari apakah Anda menggunakan prosedur tersimpan atau menjalankan kode R/Python dalam konteks komputasi SQL Server.

Mengemas kode R/Python Anda dalam prosedur tersimpan

Buat fungsi yang ditentukan pengguna T-SQL, menyematkan kode Anda menggunakan pernyataan sp-execute-external-script .
Jika Anda memiliki kode R yang kompleks, gunakan paket R sqlrutils untuk mengonversi kode Anda. Paket ini dirancang untuk membantu pengguna R berpengalaman menulis kode prosedur tersimpan yang baik. Anda menulis ulang kode R Anda sebagai fungsi tunggal dengan input dan output yang ditentukan dengan jelas, lalu menggunakan paket sqlrutils untuk menghasilkan input dan output dalam format yang benar. Paket sqlrutils menghasilkan kode prosedur tersimpan lengkap untuk Anda, dan juga dapat mendaftarkan prosedur tersimpan dalam database.

Untuk informasi dan contoh selengkapnya, lihat sqlrutils (SQL).

Mengintegrasikan dengan alur kerja lain

Manfaatkan alat T-SQL dan proses ETL. Lakukan rekayasa fitur, ekstraksi fitur, dan pembersihan data terlebih dahulu sebagai bagian dari alur kerja data.

Saat Anda bekerja di lingkungan pengembangan khusus, Anda dapat menarik data ke komputer Anda, menganalisis data secara berulang, lalu menulis atau menampilkan hasilnya. Namun, ketika kode mandiri dimigrasikan ke SQL Server, sebagian besar proses ini dapat disederhanakan atau didelegasikan ke alat SQL Server lainnya.
Gunakan strategi visualisasi yang aman dan asinkron.

Pengguna SQL Server sering tidak dapat mengakses file di server, dan alat klien SQL biasanya tidak mendukung perangkat grafis R/Python. Jika Anda membuat plot atau grafik lain sebagai bagian dari solusi, pertimbangkan untuk mengekspor plot sebagai data biner dan menyimpan ke tabel, atau menulis.
Membungkus fungsi prediksi dan penilaian dalam prosedur tersimpan untuk akses langsung oleh aplikasi.

Langkah berikutnya

Untuk melihat contoh bagaimana solusi R dan Python dapat disebarkan di SQL Server, lihat tutorial berikut:

Tutorial R

Pengembangan SQL untuk ilmuwan data R