Exibir e resumir dados do SQL Server usando R (passo a passo)

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores

Esta lição apresenta as funções no pacote RevoScaleR e orienta você nas seguintes tarefas:

• Conectar-se ao SQL Server
• Defina uma consulta com os dados de que precisa ou especifique uma tabela ou vista
• Definir um ou mais contextos de computação para usar ao executar o código R
• Opcionalmente, defina transformações que são aplicadas à fonte de dados enquanto ela está sendo lida da fonte

Definir um contexto de computação do SQL Server

Execute as seguintes instruções R em um ambiente R na estação de trabalho cliente. Esta seção assume uma estação de trabalho de ciência de dados com o Microsoft R Client, porque inclui todos os pacotes RevoScaleR, bem como um conjunto básico e leve de ferramentas R. Por exemplo, você pode usar Rgui.exe para executar o script R nesta seção.

1. Se o pacote RevoScaleR ainda não estiver carregado, execute esta linha de código R:

   library("RevoScaleR")
   

   As aspas são opcionais, neste caso, embora recomendadas.

   Se você receber um erro, certifique-se de que seu ambiente de desenvolvimento R está usando uma biblioteca que inclui o pacote RevoScaleR. Use um comando como .libPaths() para exibir o caminho da biblioteca atual.

2. Crie a cadeia de conexão para o SQL Server e salve-a em uma variável R, connStr.

   Você deve alterar o espaço reservado "your_server_name" para um nome de instância válido do SQL Server. Para o nome do servidor, talvez seja possível usar apenas o nome da instância ou talvez seja necessário qualificá-lo totalmente, dependendo da rede.

   Para autenticação do SQL Server, a sintaxe de conexão é a seguinte:

   connStr <- "Driver=SQL Server;Server=your_server_name;Database=nyctaxi_sample;Uid=your-sql-login;Pwd=your-login-password"
   

   Para autenticação do Windows, a sintaxe é um pouco diferente:

   connStr <- "Driver=SQL Server;Server=your_server_name;Database=nyctaxi_sample;Trusted_Connection=True"
   

   Geralmente, recomendamos que você use a autenticação do Windows sempre que possível, para evitar salvar senhas em seu código R.

3. Defina variáveis a serem usadas na criação de um novo contexto de computação. Depois de criar o objeto de contexto de computação, você pode usá-lo para executar o código R na instância do SQL Server.

   sqlShareDir <- paste("C:\\AllShare\\",Sys.getenv("USERNAME"),sep="")
   sqlWait <- TRUE
   sqlConsoleOutput <- FALSE
   

   • R usa um diretório temporário ao serializar objetos R entre sua estação de trabalho e o computador SQL Server. Você pode especificar o diretório local que é usado como sqlShareDir ou aceitar o padrão.

   • Use sqlWait para indicar se você deseja que R aguarde os resultados do servidor. Para uma discussão sobre trabalhos que aguardam versus os que não aguardam, consulte Computação distribuída e paralela com RevoScaleR no Microsoft R.

   • Use o argumento sqlConsoleOutput para indicar que você não deseja ver a saída do console R.

4. Você chama o construtor RxInSqlServer para criar o objeto de contexto de computação com as variáveis e cadeias de conexão já definidas e salvar o novo objeto na variável R sqlcc.

   sqlcc <- RxInSqlServer(connectionString = connStr, shareDir = sqlShareDir, wait = sqlWait, consoleOutput = sqlConsoleOutput)
   

5. Por padrão, o contexto de computação é local, portanto, você precisa definir explicitamente o contexto de computação ativo .

   rxSetComputeContext(sqlcc)
   

   • rxSetComputeContext retorna o contexto de computação anteriormente ativo invisivelmente para que você possa usá-lo
   • rxGetComputeContext retorna o contexto de computação ativo

   Observe que a definição de um contexto de computação afeta apenas as operações que usam funções no pacote RevoScaleR ; o contexto de computação não afeta a maneira como as operações R de código aberto são executadas.

Criar uma fonte de dados usando RxSqlServer

Ao usar as bibliotecas do Microsoft R, como RevoScaleR e MicrosoftML, uma fonte de dados é um objeto criado usando funções RevoScaleR. O objeto de fonte de dados especifica algum conjunto de dados que você deseja usar para uma tarefa, como treinamento de modelo ou extração de recursos. Você pode obter dados de várias fontes, incluindo o SQL Server. Para obter a lista de fontes atualmente suportadas, consulte RxDataSource.

Anteriormente, você definiu uma cadeia de conexão e salvou essas informações em uma variável R. Você pode reutilizar essas informações de conexão para especificar os dados que deseja obter.

1. Salve uma consulta SQL como uma variável de cadeia de caracteres. A consulta define os dados para treinar o modelo.

   sampleDataQuery <- "SELECT TOP 1000 tipped, fare_amount, passenger_count,trip_time_in_secs,trip_distance, pickup_datetime, dropoff_datetime, pickup_longitude, pickup_latitude, dropoff_longitude, dropoff_latitude FROM nyctaxi_sample"
   

   Usámos uma cláusula TOP aqui para acelerar o processamento, mas as linhas reais retornadas pela consulta SQL podem variar dependendo da ordem. Assim, os resultados do resumo também podem ser diferentes dos listados abaixo. Sinta-se à vontade para remover a cláusula TOP.

2. Passe a definição de consulta como um argumento para a função RxSqlServerData .

   inDataSource <- RxSqlServerData(
     sqlQuery = sampleDataQuery,
     connectionString = connStr,
     colClasses = c(pickup_longitude = "numeric", pickup_latitude = "numeric",
     dropoff_longitude = "numeric", dropoff_latitude = "numeric"),
     rowsPerRead=500
     )
   

   • O argumento colClasses especifica os tipos de coluna a serem usados ao mover os dados entre o SQL Server e R. Isso é importante porque o SQL Server usa tipos de dados diferentes de R e mais tipos de dados. Para obter mais informações, consulte Bibliotecas R e tipos de dados.

   • O argumento rowsPerRead é importante para gerenciar o uso de memória e cálculos eficientes. A maioria das funções analíticas aprimoradas em R Services (In-Database) processa dados em partes e acumula resultados intermediários, retornando os cálculos finais depois que todos os dados foram lidos. Ao adicionar o parâmetro rowsPerRead , você pode controlar quantas linhas de dados são lidas em cada parte para processamento. Se o valor desse parâmetro for muito grande, o acesso aos dados pode ser lento porque você não tem memória suficiente para processar com eficiência um pedaço tão grande de dados. Em alguns sistemas, definir rowsPerRead para um valor excessivamente pequeno também pode fornecer um desempenho mais lento.

3. Neste ponto, você criou o objeto inDataSource , mas ele não contém dados. Os dados não são extraídos da consulta SQL para o ambiente local até que você execute uma função como rxImport ou rxSummary.

   No entanto, agora que você definiu os objetos de dados, você pode usá-lo como argumento para outras funções.

Usar os dados do SQL Server em resumos R

Nesta seção, você experimentará várias das funções fornecidas no R Services (In-Database) que suportam contextos de computação remota. Ao aplicar funções R à fonte de dados, você pode explorar, resumir e criar um gráfico dos dados do SQL Server.

1. Chame a função rxGetVarInfo para obter uma lista das variáveis na fonte de dados e seus tipos de dados.

   rxGetVarInfo é uma função útil; Você pode chamá-lo em qualquer quadro de dados ou em um conjunto de dados em um objeto de dados remoto para obter informações como os valores máximo e mínimo, o tipo de dados e o número de níveis em colunas de fatores.

   Considere executar essa função após qualquer tipo de entrada de dados, transformação de recursos ou engenharia de recursos. Ao fazer isso, você pode garantir que todos os recursos que deseja usar em seu modelo sejam do tipo de dados esperado e evitar erros.

   rxGetVarInfo(data = inDataSource)
   

   Resultados

   Var 1: tipped, Type: integer
   Var 2: fare_amount, Type: numeric
   Var 3: passenger_count, Type: integer
   Var 4: trip_time_in_secs, Type: numeric, Storage: int64
   Var 5: trip_distance, Type: numeric
   Var 6: pickup_datetime, Type: character
   Var 7: dropoff_datetime, Type: character
   Var 8: pickup_longitude, Type: numeric
   Var 9: pickup_latitude, Type: numeric
   Var 10: dropoff_longitude, Type: numeric
   

2. Agora, chame a função RevoScaleR rxSummary para obter estatísticas mais detalhadas sobre variáveis individuais.

   rxSummary é baseado na função R summary , mas tem alguns recursos e vantagens adicionais. O rxSummary funciona em vários contextos de computação e suporta fragmentação. Você também pode usar rxSummary para transformar valores ou resumir com base em níveis de fatores.

   Neste exemplo, você resume o valor da tarifa com base no número de passageiros.

   start.time <- proc.time()
   rxSummary(~fare_amount:F(passenger_count,1,6), data = inDataSource)
   used.time <- proc.time() - start.time
   print(paste("It takes CPU Time=", round(used.time[1]+used.time[2],2)," seconds,
     Elapsed Time=", round(used.time[3],2),
     " seconds to summarize the inDataSource.", sep=""))
   

   • O primeiro argumento para rxSummary especifica a fórmula ou termo pelo qual resumir. Aqui, a F() função é usada para converter os valores em passenger_count em fatores antes de resumir. Você também precisa especificar o valor mínimo (1) e o valor máximo (6) para a variável fator passenger_count.
   • Se você não especificar as estatísticas a serem produzidas, por padrão, rxSummary lança Mean, StDev, Min, Max e o número de observações válidas e ausentes.
   • Este exemplo também inclui algum código para controlar a hora em que a função é iniciada e concluída, para que você possa comparar o desempenho.

   Resultados

   Se a função rxSummary for executada com êxito, você verá resultados como estes, seguidos por uma lista de estatísticas por categoria.

   rxSummary(formula = ~fare_amount:F(passenger_count, 1,6), data = inDataSource)
   Data: inDataSource (RxSqlServerData Data Source)
   Number of valid observations: 1000
   

Exercício de bónus sobre big data

Tente definir uma nova cadeia de caracteres de consulta com todas as linhas. Recomendamos que você configure um novo objeto de fonte de dados para este experimento. Você também pode tentar alterar o parâmetro rowsToRead para ver como ele afeta a taxa de transferência.

bigDataQuery  <- "SELECT tipped, fare_amount, passenger_count,trip_time_in_secs,trip_distance, pickup_datetime, dropoff_datetime, pickup_longitude, pickup_latitude, dropoff_longitude, dropoff_latitude FROM nyctaxi_sample"

bigDataSource <- RxSqlServerData(
      sqlQuery = bigDataQuery,
      connectionString = connStr,
      colClasses = c(pickup_longitude = "numeric", pickup_latitude = "numeric",
      dropoff_longitude = "numeric", dropoff_latitude = "numeric"),
      rowsPerRead=500
      )

start.time <- proc.time()
rxSummary(~fare_amount:F(passenger_count,1,6), data = bigDataSource)
used.time <- proc.time() - start.time
print(paste("It takes CPU Time=", round(used.time[1]+used.time[2],2)," seconds,
  Elapsed Time=", round(used.time[3],2),
  " seconds to summarize the inDataSource.", sep=""))

Sugestão

Enquanto isso está em execução, você pode usar uma ferramenta como o Process Explorer ou o SQL Profiler para ver como a conexão é feita e o código R é executado usando os serviços do SQL Server.

Próximos passos

Crie gráficos e plotagens usando R