Converter código R em um procedimento armazenado usando sqlrutils

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

Etapa 1. Reescrever script em R

Para obter os melhores resultados, você deve reescrever o código R para encapsulá-lo 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. Consulte 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 do SQL, você deve garantir que suas entradas e saídas estejam em conformidade com 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 ser dos seguintes tipos de dados R:

• POSIXct
• numeric
• caractere
• inteiro
• lógico
• raw

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

Outputs

A função pode gerar um dos seguintes:

• Um quadro de dados que contém os tipos de dados com suporte. Todos os objetos no quadro de dados devem usar um dos tipos de dados com suporte.
• Uma lista nomeada, que contém no máximo um quadro de dados. Todos os membros da lista devem usar um dos tipos de dados com suporte.
• Um valor NULL, se sua função não retorna nenhum resultado

Etapa 2. Gerar objetos necessários

Depois que o código R tiver sido limpo e puder ser chamado como uma única função, você usará as funções no pacote sqlrutils para preparar as entradas e saídas em um formulário que pode ser passado para o construtor que realmente compila o procedimento armazenado.

O sqlrutils fornece funções que definem o esquema e o tipo de dados de entrada e definem o tipo e o esquema de dados de saída. Ele também inclui funções que podem converter objetos R no tipo de saída necessário. Você pode fazer várias chamadas de função para criar os objetos necessários, dependendo dos tipos de dados usados pelo código.

Entradas

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

• setInputData se a entrada for um quadro de dados
• setInputParameter para todos os outros tipos de entrada

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

Outputs

O sqlrutils fornece várias funções para converter objetos R, como listas para data.frame exigido por SQL Server. Se sua função gerar um quadro de dados diretamente, sem primeiro encaplhá-lo em uma lista, você poderá ignorar esta etapa. Você também pode ignorar a conversão desta etapa se sua função retornar NULL.

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

• setOutputData se a variável a ser obtido da lista for um quadro de dados
• setOutputParameter para todos os outros membros da lista

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

Etapa 3. Gerar o procedimento armazenado

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

Usage

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

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

• Usa uma função existente foosql. A função foi baseada no código existente na função R foo, mas você reescreveu a função para estar em conformidade com os requisitos, conforme descrito nesta seção, e nomeou a função atualizada como foosql.
• Usa o data frame queryinput como entrada
• Gera como saída um quadro de dados com o nome da variável R, sqloutput
• Você deseja criar o código T-SQL como um arquivo na C:\Temp pasta, para que possa executá-lo usando SQL Server Management Studio posteriormente

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

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

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

Etapa 4. Registrar e executar o procedimento armazenado

Há duas maneiras de executar o procedimento armazenado:

• Usando o T-SQL, de qualquer cliente que dê suporte a conexões com a instância do SQL Server 2016 ou SQL Server 2017
• De um ambiente R

Ambos os métodos exigem que o procedimento armazenado seja registrado no banco de dados em que você pretende usar o procedimento armazenado.

Registrar o procedimento armazenado

Você pode registrar o procedimento armazenado usando R ou executar a CREATE PROCEDURE instrução no T-SQL.

• Usando o T-SQL. Se você estiver mais confortável com o T-SQL, abra SQL Server Management Studio (ou qualquer outro cliente que possa executar comandos DDL do SQL) e execute a CREATE PROCEDURE instrução usando o código preparado pela StoredProcedure função.

• Usando R. Enquanto você ainda estiver em seu ambiente R, poderá usar a função registerStoredProcedure do sqlrutils para registrar o procedimento armazenado no banco de dados.

  Por exemplo, você pode registrar o procedimento armazenado sp_rsample na instância e no banco de dados definido no sqlConnStr, fazendo esta chamada R:

  registerStoredProcedure(sp_rsample, sqlConnStr)
  

Executar usando o SQL

Depois que o procedimento armazenado tiver sido criado, abra uma conexão com o banco de dados SQL usando qualquer cliente que dê suporte ao T-SQL e passe valores para todos os parâmetros exigidos pelo procedimento armazenado.

Executar usando R

Alguma preparação adicional será necessária se você quiser executar o procedimento armazenado do código R, em vez de SQL Server. Por exemplo, se o procedimento armazenado exigir valores de entrada, você deverá definir esses parâmetros de entrada antes que a função possa ser executada e, em seguida, passar esses objetos para o procedimento armazenado em seu código R.

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

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

Exemplo

Este exemplo mostra as versões anteriores e posteriores de um script R que obtém dados de um banco de dados SQL Server, executa algumas transformações nos dados e os salva em um banco de dados diferente.

Este exemplo simples é usado apenas para demonstrar como você pode reorganizar seu código R para facilitar a conversão em 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 outro 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