Converter código R para um procedimento armazenado usando sqlrutils

Este artigo descreve os passos para usar o pacote sqlrutils para converter o seu código R para ser executado como um procedimento armazenado T-SQL. Para melhores resultados possíveis, o seu código pode precisar de ser modificado um pouco para garantir que todas as entradas possam ser parametrizadas.

Passo 1. Reescrever o Script R

Para melhores resultados, deve reescrever o seu código R para o encapsular como uma única função.

Todas as variáveis usadas pela função devem ser definidas dentro da função, ou devem ser definidas como parâmetros de entrada. Veja o código de exemplo neste artigo.

Além disso, como os parâmetros de entrada para a função R se tornarão os parâmetros de entrada do procedimento armazenado SQL, deve garantir que as suas entradas e saídas cumprem os seguintes requisitos de tipo:

Entradas

Entre os parâmetros de entrada, pode haver no máximo um quadro de dados.

Os objetos dentro do quadro de dados, bem como todos os outros parâmetros de entrada da função, devem pertencer aos seguintes R tipos de dados:

• POSIXct
• numeric
• carácter
• número inteiro
• Lógico
• raw

Se um tipo de entrada não for um dos tipos acima, precisa de ser serializado e passado para a função como raw. Neste caso, a função deve também incluir código para desserializar a entrada.

Saídas

A função pode gerar um dos seguintes:

• Uma trama de dados contendo os tipos de dados suportados. Todos os objetos no data frame devem usar um dos tipos de dados suportados.
• Uma lista nomeada, contendo no máximo um quadro de dados. Todos os membros da lista devem usar um dos tipos de dados suportados.
• Um NULL, se a sua função não devolver qualquer resultado

Passo 2. Gerar Objetos Necessários

Depois de o seu código R ser limpo e poder ser chamado como uma única função, irá usar as funções do pacote sqlrutils para preparar as entradas e saídas numa forma que pode ser passada ao construtor que realmente constrói o procedimento armazenado.

O SQLRutils fornece funções que definem o esquema e o tipo de dados de entrada, e definem o esquema e o tipo de dados de saída. Inclui também funções que podem converter R objetos para o tipo de saída desejado. Podes fazer múltiplas chamadas de função para criar os objetos necessários, dependendo dos tipos de dados que o teu código utiliza.

Entradas

Se a sua função recebe entradas, para cada entrada, chame as seguintes funções:

• setInputData se a entrada for uma trama de dados
• setInputParameter para todos os outros tipos de entrada

Quando faz cada chamada de função, é criado um objeto R que mais tarde passará como argumento para StoredProcedure, para criar o procedimento armazenado completo.

Saídas

O sqlrutils fornece múltiplas funções para converter objetos R, como listas, para o data.frame exigido pelo SQL Server. Se a sua função gerar diretamente um data frame, sem antes o envolver numa lista, pode saltar este passo. Também podes saltar a conversão neste passo se a tua função devolver NULL.

Ao converter uma lista ou obter um determinado item de uma lista, escolha entre estas funções:

• setOutputData se a variável a obter da lista for um data frame
• setOutputParameter para todos os outros membros da lista

Quando faz cada chamada de função, é criado um objeto R que mais tarde passará como argumento para StoredProcedure, para criar o procedimento armazenado completo.

Passo 3. Gerar o procedimento armazenado

Quando todos os parâmetros de entrada e saída estiverem prontos, faça uma chamada ao StoredProcedure construtor.

Usage

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

Para ilustrar, suponha que pretende criar um procedimento armazenado chamado sp_rsample com estes parâmetros:

• Utiliza uma função já existente, o foosql. A função baseava-se em código existente em R função foo, mas reescreveste a função para cumprir os requisitos descritos nesta secção, e chamaste à função atualizada foosql.
• Utiliza o dataframe queryinput como entrada
• Gera como saída um quadro de dados com o nome da variável R, sqloutput
• Queres criar o código T-SQL como um ficheiro na C:\Temp pasta, para poderes executá-lo mais tarde usando o SQL Server Management Studio

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

A saída da função é um procedimento armazenado T-SQL que pode ser executado numa instância do SQL Server 2016 (requer R Services) ou SQL Server 2017 (requer Machine Learning Services com R).

Para exemplos adicionais, consulte a ajuda do pacote, ligando help(StoredProcedure) a partir de um ambiente R.

Passo 4: Registar e executar o procedimento armazenado

Existem duas formas de executar o procedimento armazenado:

• Usando T-SQL, a partir de qualquer cliente que suporte ligações à instância do SQL Server 2016 ou SQL Server 2017
• A partir de um ambiente R

Ambos os métodos exigem que o procedimento armazenado seja registado na base de dados onde pretende utilizar o procedimento armazenado.

Registar o procedimento armazenado

Pode registar o procedimento armazenado usando R, ou pode executar a CREATE PROCEDURE instrução em T-SQL.

• A usar T-SQL. Se te sentires mais confortável com T-SQL, abre o SQL Server Management Studio (ou qualquer outro cliente que consiga executar comandos SQL DDL) e executa a CREATE PROCEDURE instrução usando o código preparado pela StoredProcedure função.

• Usando R. Enquanto ainda estiver no seu ambiente R, pode usar a registerStoredProcedure função em sqlrutils para registar o procedimento armazenado na base de dados.

  Por exemplo, poderia registar o procedimento armazenado sp_rsample na instância e base de dados definidas em sqlConnStr, fazendo esta chamada em R:

  registerStoredProcedure(sp_rsample, sqlConnStr)
  

Executar usando SQL

Depois de criado o procedimento armazenado, abrir uma ligação à base de dados SQL usando qualquer cliente que suporte T-SQL, e passar valores para quaisquer parâmetros exigidos pelo procedimento armazenado.

Executar usando R

É necessária alguma preparação adicional se quiser executar o procedimento armazenado a partir de código R, em vez de SQL Server. Por exemplo, se o procedimento armazenado requer valores de entrada, deve definir esses parâmetros de entrada antes de a função poder ser executada, e depois passar esses objetos para o procedimento armazenado no seu código R.

O processo geral de invocar o procedimento armazenado SQL preparado é o seguinte:

1. Chame getInputParameters para obter uma lista de objetos de parâmetros de entrada.
2. Defina a $query ou defina a $value para cada parâmetro de entrada.
3. Use executeStoredProcedure para executar o procedimento armazenado a partir do ambiente de desenvolvimento R, passando a lista de objetos de parâmetros de entrada que definir.

Exemplo

Este exemplo mostra as versões antes e depois de um script R que obtém dados de uma base de dados SQL Server, realiza algumas transformações nos dados e os guarda numa base de dados diferente.

Este exemplo simples é usado apenas para demonstrar como pode reorganizar o seu código R para facilitar a conversão para um procedimento armazenado.

Antes da preparação do código

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)

Após a preparação do código

Na versão atualizada, a primeira linha define o nome da função. Todo o resto do código da solução R original torna-se parte dessa função.

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

Consulte também

Referência do sqlrutils