Bagikan melalui

Mengonversi kode R ke prosedur tersimpan menggunakan sqlrutils

Artikel ini menjelaskan langkah-langkah untuk menggunakan paket sqlrutils untuk mengonversi kode R Anda agar berjalan sebagai prosedur tersimpan T-SQL. Untuk hasil sebaik mungkin, kode Anda mungkin perlu dimodifikasi agak untuk memastikan bahwa semua input dapat diparameterkan.

Langkah 1. Menulis ulang Skrip R

Untuk hasil terbaik, Anda harus menulis ulang kode R Anda untuk merangkumnya sebagai satu fungsi.

Semua variabel yang digunakan oleh fungsi harus didefinisikan di dalam fungsi , atau harus didefinisikan sebagai parameter input. Lihat kode sampel di artikel ini.

Selain itu, karena parameter input untuk fungsi R akan menjadi parameter input prosedur tersimpan SQL, Anda harus memastikan bahwa input dan output Anda sesuai dengan persyaratan jenis berikut:

Input

Di antara parameter input, paling banyak ada satu bingkai data.

Objek di dalam bingkai data, serta semua parameter input fungsi lainnya, harus dari jenis data R berikut:

  • POSIXct
  • numeric
  • karakter
  • bilangan bulat
  • logis
  • raw

Jika jenis input bukan salah satu jenis di atas, jenis tersebut perlu diserialisasikan dan diteruskan ke fungsi sebagai mentah. Dalam hal ini, fungsi juga harus menyertakan kode untuk mendeserialisasi input.

Output

Fungsi ini dapat menghasilkan salah satu hal berikut:

  • Bingkai data yang berisi jenis data yang didukung. Semua objek dalam bingkai data harus menggunakan salah satu jenis data yang didukung.
  • Daftar bernama, berisi paling banyak satu bingkai data. Semua anggota daftar harus menggunakan salah satu jenis data yang didukung.
  • NULL, jika fungsi Anda tidak mengembalikan hasil apa pun

Langkah 2. Hasilkan Objek yang Diperlukan

Setelah kode R Anda dibersihkan dan dapat dipanggil sebagai fungsi tunggal, Anda akan menggunakan fungsi dalam paket sqlrutils untuk menyiapkan input dan output dalam formulir yang dapat diteruskan ke konstruktor yang benar-benar membangun prosedur tersimpan.

sqlrutils menyediakan fungsi yang menentukan skema dan jenis data input, dan menentukan skema dan jenis data output. Ini juga mencakup fungsi yang dapat mengonversi objek R ke jenis output yang diperlukan. Anda mungkin melakukan beberapa panggilan fungsi untuk membuat objek yang diperlukan, tergantung pada jenis data yang digunakan kode Anda.

Input

Jika fungsi Anda mengambil input, untuk setiap input, panggil fungsi berikut:

  • setInputData jika input adalah bingkai data
  • setInputParameter untuk semua jenis input lainnya

Saat Anda melakukan setiap panggilan fungsi, objek R dibuat yang nantinya akan Anda teruskan sebagai argumen ke StoredProcedure, untuk membuat prosedur tersimpan lengkap.

Output

sqlrutils menyediakan beberapa fungsi untuk mengonversi objek R seperti daftar ke data.frame yang diperlukan oleh SQL Server. Jika fungsi Anda menghasilkan bingkai data secara langsung, tanpa terlebih dahulu membungkusnya ke dalam daftar, Anda dapat melewati langkah ini. Anda juga dapat melewati konversi langkah ini jika fungsi Anda mengembalikan NULL.

Saat mengonversi daftar atau mendapatkan item tertentu dari daftar, pilih dari fungsi-fungsi ini:

  • setOutputData jika variabel yang akan didapatkan dari daftar adalah bingkai data
  • setOutputParameter untuk semua anggota daftar lainnya

Saat Anda melakukan setiap panggilan fungsi, objek R dibuat yang nantinya akan Anda teruskan sebagai argumen ke StoredProcedure, untuk membuat prosedur tersimpan lengkap.

Langkah 3. Hasilkan Prosedur Tersimpan

Ketika semua parameter input dan output siap, lakukan panggilan ke StoredProcedure konstruktor.

Penggunaan

StoredProcedure (func, spName, ..., filePath = NULL ,dbName = NULL, connectionString = NULL, batchSeparator = "GO")

Untuk mengilustrasikan, asumsikan bahwa Anda ingin membuat prosedur tersimpan bernama sp_rsample dengan parameter ini:

  • Menggunakan foosql fungsi yang ada. Fungsi ini didasarkan pada kode yang ada di foo fungsi R, tetapi Anda menulis ulang fungsi agar sesuai dengan persyaratan seperti yang dijelaskan di bagian ini, dan menamai fungsi yang diperbarui sebagai foosql.
  • Menggunakan queryinput bingkai data sebagai input
  • Menghasilkan sebagai output bingkai data dengan nama variabel R, sqloutput
  • Anda ingin membuat kode T-SQL sebagai file di C:\Temp folder, sehingga Anda dapat menjalankannya menggunakan SQL Server Management Studio nanti
StoredProcedure (foosql, sp_rsample, queryinput, sqloutput, filePath = "C:\\Temp")

Catatan

Karena Anda menulis file ke sistem file, Anda dapat menghilangkan argumen yang menentukan koneksi database.

Output fungsi adalah prosedur tersimpan T-SQL yang dapat dijalankan pada instans SQL Server 2016 (memerlukan Layanan R) atau SQL Server 2017 (memerlukan Layanan Pembelajaran Mesin dengan R).

Untuk contoh tambahan, lihat bantuan paket, dengan memanggil help(StoredProcedure) dari lingkungan R.

Langkah 4. Daftar dan Jalankan Prosedur Tersimpan

Ada dua cara untuk menjalankan prosedur tersimpan:

  • Menggunakan T-SQL, dari klien mana pun yang mendukung koneksi ke instans SQL Server 2016 atau SQL Server 2017
  • Dari lingkungan R

Kedua metode mengharuskan prosedur tersimpan didaftarkan dalam database tempat Anda ingin menggunakan prosedur tersimpan.

Mendaftarkan prosedur tersimpan

Anda dapat mendaftarkan prosedur tersimpan menggunakan R, atau Anda dapat menjalankan pernyataan CREATE PROCEDURE di T-SQL.

  • Menggunakan T-SQL. Jika Anda lebih nyaman dengan T-SQL, buka SQL Server Management Studio (atau klien lain yang dapat menjalankan perintah SQL DDL) dan jalankan pernyataan CREATE PROCEDURE menggunakan kode yang disiapkan oleh StoredProcedure fungsi .

  • Menggunakan R. Saat Anda masih berada di lingkungan R, Anda dapat menggunakan registerStoredProcedure fungsi di sqlrutils untuk mendaftarkan prosedur tersimpan dengan database.

    Misalnya, Anda dapat mendaftarkan prosedur tersimpan sp_rsample dalam instans dan database yang ditentukan dalam sqlConnStr, dengan melakukan panggilan R ini:

    registerStoredProcedure(sp_rsample, sqlConnStr)

Penting

Terlepas dari apakah Anda menggunakan R atau SQL, Anda harus menjalankan pernyataan menggunakan akun yang memiliki izin untuk membuat objek database baru.

Jalankan menggunakan SQL

Setelah prosedur tersimpan dibuat, buka koneksi ke database SQL menggunakan klien apa pun yang mendukung T-SQL, dan teruskan nilai untuk parameter apa pun yang diperlukan oleh prosedur tersimpan.

Jalankan menggunakan R

Beberapa persiapan tambahan diperlukan jika Anda ingin menjalankan prosedur tersimpan dari kode R, bukan dari SQL Server. Misalnya, jika prosedur tersimpan memerlukan nilai input, Anda harus mengatur parameter input tersebut sebelum fungsi dapat dijalankan, lalu meneruskan objek tersebut ke prosedur tersimpan dalam kode R Anda.

Proses keseluruhan panggilan prosedur tersimpan SQL yang disiapkan adalah sebagai berikut:

  1. Panggil getInputParameters untuk mendapatkan daftar objek parameter input.
  2. $query Tentukan atau atur $value untuk setiap parameter input.
  3. Gunakan executeStoredProcedure untuk menjalankan prosedur tersimpan dari lingkungan pengembangan R, meneruskan daftar objek parameter input yang Anda tetapkan.

Contoh

Contoh ini memperlihatkan versi sebelum dan sesudah skrip R yang mendapatkan data dari database SQL Server, melakukan beberapa transformasi pada data, dan menyimpannya ke database yang berbeda.

Contoh sederhana ini hanya digunakan untuk menunjukkan bagaimana Anda dapat mengatur ulang kode R Anda untuk mempermudah konversi ke prosedur tersimpan.

Sebelum persiapan kode

sqlConnFrom <- "Driver={ODBC Driver 13 for SQL Server};Server=MyServer01;Database=AirlineSrc;Trusted_Connection=Yes;"
  
sqlConnTo <- "Driver={ODBC Driver 13 for SQL Server};Server=MyServer01;Database=AirlineTest;Trusted_Connection=Yes;"
  
sqlQueryAirline <- "SELECT TOP 10000 ArrDelay, CRSDepTime, DayOfWeek FROM [AirlineDemoSmall]"
  
dsSqlFrom <- RxSqlServerData(sqlQuery = sqlQueryAirline, connectionString = sqlConnFrom)
  
dsSqlTo <- RxSqlServerData(table = "cleanData", connectionString = sqlConnTo)
  
xFunc <- function(data) {
    data$CRSDepHour <- as.integer(trunc(data$CRSDepTime))
    return(data)
    }
  
xVars <- c("CRSDepTime")
  
sqlCompute <- RxInSqlServer(numTasks = 4, connectionString = sqlConnTo)
  
rxOpen(dsSqlFrom)
rxOpen(dsSqlTo)
  
if (rxSqlServerTableExists("cleanData", connectionString = sqlConnTo))   {
    rxSqlServerDropTable("cleanData")}
  
rxDataStep(inData = dsSqlFrom, 
     outFile = dsSqlTo,
     transformFunc = xFunc,
     transformVars = xVars,
     overwrite = TRUE)

Catatan

Ketika Anda menggunakan koneksi ODBC daripada memanggil fungsi RxSqlServerData , Anda harus membuka koneksi menggunakan rxOpen sebelum Anda dapat melakukan operasi pada database.

Setelah persiapan kode

Dalam versi yang diperbarui, baris pertama menentukan nama fungsi. Semua kode lain dari solusi R asli menjadi bagian dari fungsi itu.

myetl1function <- function() { 
   sqlConnFrom <- "Driver={ODBC Driver 13 for SQL Server};Server=MyServer01;Database=Airline01;Trusted_Connection=Yes;"
   sqlConnTo <- "Driver={ODBC Driver 13 for SQL Server};Server=MyServer02;Database=Airline02;Trusted_Connection=Yes;"
    
   sqlQueryAirline <- "SELECT TOP 10000 ArrDelay, CRSDepTime, DayOfWeek FROM [AirlineDemoSmall]"

   dsSqlFrom <- RxSqlServerData(sqlQuery = sqlQueryAirline, connectionString = sqlConnFrom)
  
   dsSqlTo <- RxSqlServerData(table = "cleanData", connectionString = sqlConnTo)
  
   xFunc <- function(data) {
     data$CRSDepHour <- as.integer(trunc(data$CRSDepTime))
     return(data)}
  
   xVars <- c("CRSDepTime")
  
   sqlCompute <- RxInSqlServer(numTasks = 4, connectionString = sqlConnTo)
  
   if (rxSqlServerTableExists("cleanData", connectionString = sqlConnTo)) {rxSqlServerDropTable("cleanData")}
  
   rxDataStep(inData = dsSqlFrom, 
        outFile = dsSqlTo,
        transformFunc = xFunc,
        transformVars = xVars,
        overwrite = TRUE)
   return(NULL)
}

Catatan

Meskipun Anda tidak perlu membuka koneksi ODBC secara eksplisit sebagai bagian dari kode Anda, koneksi ODBC masih diperlukan untuk menggunakan sqlrutils.

Lihat juga

referensi sqlrutils

  • Last updated on