Convertire il codice R in una stored procedure usando sqlrutils

Questo articolo descrive i passaggi per l'uso del pacchetto sqlrutils per convertire il codice R da eseguire come stored procedure T-SQL. Per ottenere risultati ottimali, potrebbe essere necessario modificare il codice per assicurarsi che tutti gli input possano essere parametrizzati.

Passaggio 1: Riscrivere lo script R

Per ottenere risultati ottimali, è necessario riscrivere il codice R per incapsularlo come singola funzione.

Tutte le variabili usate dalla funzione devono essere definite all'interno della funzione o devono essere definite come parametri di input. Vedere il codice di esempio in questo articolo.

Inoltre, poiché i parametri di input per la funzione R diventeranno i parametri di input della stored procedure SQL, è necessario assicurarsi che gli input e gli output siano conformi ai requisiti di tipo seguenti:

Inputs

Tra i parametri di input, può essere presente al massimo un frame di dati.

Gli oggetti all'interno del frame di dati, nonché tutti gli altri parametri di input della funzione, devono essere dei tipi di dati R seguenti:

• POSIXct
• numerico
• character
• integer
• logical
• raw

Se un tipo di input non è uno dei tipi precedenti, deve essere serializzato e passato alla funzione come non elaborato. In questo caso, la funzione deve includere anche il codice per deserializzare l'input.

Outputs

La funzione può restituire uno dei seguenti elementi:

• Frame di dati contenente i tipi di dati supportati. Tutti gli oggetti nel frame di dati devono usare uno dei tipi di dati supportati.
• Elenco denominato contenente al massimo un frame di dati. Tutti i membri dell'elenco devono usare uno dei tipi di dati supportati.
• Valore NULL, se la funzione non restituisce alcun risultato

Passaggio 2: Generare oggetti obbligatori

Dopo che il codice R è stato pulito e può essere chiamato come singola funzione, si useranno le funzioni nel pacchetto sqlrutils per preparare gli input e gli output in un modulo che può essere passato al costruttore che compila effettivamente la stored procedure.

sqlrutils fornisce funzioni che definiscono lo schema e il tipo di dati di input e definiscono lo schema e il tipo di dati di output. Include anche funzioni che possono convertire gli oggetti R nel tipo di output richiesto. È possibile effettuare più chiamate di funzione per creare gli oggetti necessari, a seconda dei tipi di dati usati dal codice.

Inputs

Se la funzione accetta input, per ogni input chiamare le funzioni seguenti:

• setInputData se l'input è un frame di dati
• setInputParameter per tutti gli altri tipi di input

Quando si effettua ogni chiamata di funzione, viene creato un oggetto R che verrà successivamente passato come argomento a StoredProcedureper creare la stored procedure completa.

Outputs

sqlrutils fornisce più funzioni per la conversione di oggetti R, ad esempio elenchi nel file data.frame richiesto da SQL Server. Se la funzione restituisce direttamente un frame di dati, senza prima eseguirne il wrapping in un elenco, è possibile ignorare questo passaggio. È anche possibile ignorare la conversione di questo passaggio se la funzione restituisce NULL.

Quando si converte un elenco o si ottiene un elemento specifico da un elenco, scegliere tra queste funzioni:

• setOutputData se la variabile da ottenere dall'elenco è un frame di dati
• setOutputParameter per tutti gli altri membri dell'elenco

Quando si effettua ogni chiamata di funzione, viene creato un oggetto R che verrà successivamente passato come argomento a StoredProcedureper creare la stored procedure completa.

Passaggio 3. Genera la stored procedure

Quando tutti i parametri di input e output sono pronti, effettuare una chiamata al StoredProcedure costruttore.

Utilizzo

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

Per illustrare, si supponga di voler creare una stored procedure denominata sp_rsample con questi parametri:

• Usa una funzione esistente foosql. La funzione era basata sul codice esistente nella funzione R foo, ma si riscrive la funzione in modo che sia conforme ai requisiti come descritto in questa sezione e denominata la funzione aggiornata come foosql.
• Usa il frame di dati queryinput come input
• Genera come output un frame di dati con il nome della variabile R, sqloutput
• Si vuole creare il codice T-SQL come file nella C:\Temp cartella, in modo che sia possibile eseguirlo usando SQL Server Management Studio versioni successive

StoredProcedure (foosql, sp_rsample, queryinput, sqloutput, filePath = "C:\\Temp")

Il risultato della funzione è una stored procedure T-SQL che può essere eseguita su un'istanza di SQL Server 2016 (richiede R Services) o SQL Server 2017 (richiede Machine Learning Services con R).

Per ulteriori esempi, consultare la guida del pacchetto richiamando help(StoredProcedure) nell'ambiente R.

Passaggio 4: Registrazione ed esecuzione della procedura memorizzata

È possibile eseguire la stored procedure in due modi:

• Uso di T-SQL da qualsiasi client che supporta le connessioni all'istanza di SQL Server 2016 o SQL Server 2017
• Da un ambiente R

Entrambi i metodi richiedono che la stored procedure sia registrata nel database in cui si intende usarla.

Registrare la stored procedure

È possibile registrare la stored procedure usando R oppure eseguire l'istruzione CREATE PROCEDURE in T-SQL.

• Uso di T-SQL. Se si ha maggiore familiarità con T-SQL, aprire SQL Server Management Studio (o qualsiasi altro client in grado di eseguire comandi DDL SQL) ed eseguire l'istruzione CREATE PROCEDURE usando il codice preparato dalla StoredProcedure funzione.

• Uso di R. Mentre si è ancora nell'ambiente R, è possibile usare la funzione registerStoredProcedure in sqlrutils per registrare la procedura archiviata nel database.

  Ad esempio, è possibile registrare la stored procedure sp_rsample nell'istanza e nel database definito in sqlConnStr effettuando questa chiamata R:

  registerStoredProcedure(sp_rsample, sqlConnStr)
  

Esecuzione con SQL

Dopo aver creato la stored procedure, aprire una connessione al database SQL usando qualsiasi client che supporta T-SQL e passare i valori per tutti i parametri richiesti dalla stored procedure.

Esegui con R

È necessaria una preparazione aggiuntiva se si vuole eseguire la stored procedure dal codice R, invece di SQL Server. Ad esempio, se la stored procedure richiede valori di input, è necessario impostare tali parametri di input prima che la funzione possa essere eseguita e quindi passare tali oggetti alla stored procedure nel codice R.

Il processo complessivo di chiamata alla stored procedure SQL preparata è il seguente:

1. Chiamare getInputParameters per ottenere un elenco di oggetti parametro di input.
2. Definire un $query oggetto o impostare un $value oggetto per ogni parametro di input.
3. Utilizzare executeStoredProcedure per eseguire la stored procedure dall'ambiente di sviluppo R, passando l'elenco degli oggetti dei parametri di input configurati.

Esempio di

Questo esempio illustra le versioni precedenti e successive di uno script R che recupera dati da un database SQL Server, esegue alcune trasformazioni sui dati e le salva in un database diverso.

Questo semplice esempio viene usato solo per illustrare come è possibile riorganizzare il codice R per semplificare la conversione in una stored procedure.

Prima della preparazione del codice

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)

Dopo la preparazione del codice

Nella versione aggiornata la prima riga definisce il nome della funzione. Tutto l'altro codice della soluzione R originale diventa parte di tale funzione.

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

Vedi anche

Informazioni di riferimento su sqlrutils