Criar recursos de dados usando R e SQL Server (passo a passo)

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

A engenharia de dados é uma parte importante do aprendizado de máquina. Os dados geralmente exigem transformação antes que você possa usá-los para modelagem preditiva. Se os dados não tiverem os recursos necessários, você poderá projetá-los a partir de valores existentes.

Para esta tarefa de modelagem, em vez de usar os valores brutos de latitude e longitude do local de embarque e desembarque, você gostaria de ter a distância em milhas entre os dois locais. Para criar esse recurso, calcule a distância linear direta entre dois pontos, usando a fórmula haversine.

Nesta etapa, aprenda dois métodos diferentes para criar um recurso a partir de dados:

O objetivo é criar um novo conjunto de dados do SQL Server que inclua as colunas originais mais o novo recurso numérico, direct_distance.

Pré-requisitos

Esta etapa pressupõe uma sessão R contínua com base nas etapas anteriores neste passo a passo. Ele usa as cadeias de conexão e os objetos de fonte de dados criados nessas etapas. As seguintes ferramentas e pacotes são usados para executar o script.

• Rgui.exe executar comandos R
• Management Studio para executar T-SQL

Featurização utilizando R

A linguagem R é bem conhecida por suas bibliotecas estatísticas ricas e variadas, mas você ainda pode precisar criar transformações de dados personalizadas.

Primeiro, vamos fazer isso da maneira que os usuários de R estão acostumados: obter os dados em seu laptop e, em seguida, executar uma função R personalizada, ComputeDist, que calcula a distância linear entre dois pontos especificados por valores de latitude e longitude.

1. Lembre-se de que o objeto de fonte de dados criado anteriormente obtém apenas as 1000 linhas principais. Então, vamos definir uma consulta que obtém todos os dados.

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

2. Crie um novo objeto de fonte de dados usando a consulta.

   featureDataSource <- RxSqlServerData(sqlQuery = bigQuery,colClasses = c(pickup_longitude = "numeric", pickup_latitude = "numeric", dropoff_longitude = "numeric", dropoff_latitude = "numeric", passenger_count  = "numeric", trip_distance  = "numeric", trip_time_in_secs  = "numeric", direct_distance  = "numeric"), connectionString = connStr);
   

   • RxSqlServerData pode utilizar uma instrução que consiste numa consulta SELECT válida, fornecida como argumento do parâmetro sqlQuery, ou o nome de um objeto de tabela, fornecido como o parâmetro table.

   • Se desejar obter exemplos de dados de uma tabela, use o parâmetro sqlQuery, defina parâmetros de amostragem usando a cláusula T-SQL TABLESAMPLE e defina o argumento rowBuffering como FALSE.

3. Execute o código a seguir para criar a função R personalizada. ComputeDist recebe dois pares de valores de latitude e longitude e calcula a distância linear entre eles, retornando a distância em milhas.

   env <- new.env();
   env$ComputeDist <- function(pickup_long, pickup_lat, dropoff_long, dropoff_lat){
     R <- 6371/1.609344 #radius in mile
     delta_lat <- dropoff_lat - pickup_lat
     delta_long <- dropoff_long - pickup_long
     degrees_to_radians = pi/180.0
     a1 <- sin(delta_lat/2*degrees_to_radians)
     a2 <- as.numeric(a1)^2
     a3 <- cos(pickup_lat*degrees_to_radians)
     a4 <- cos(dropoff_lat*degrees_to_radians)
     a5 <- sin(delta_long/2*degrees_to_radians)
     a6 <- as.numeric(a5)^2
     a <- a2+a3*a4*a6
     c <- 2*atan2(sqrt(a),sqrt(1-a))
     d <- R*c
     return (d)
   }
   

   • A primeira linha define um novo ambiente. Em R, um ambiente pode ser usado para encapsular espaços de nome em pacotes e afins. Você pode usar a função search() para exibir os ambientes em seu espaço de trabalho. Para exibir os objetos em um ambiente específico, digite ls(<envname>).
   • As linhas que começam com $env.ComputeDist contêm o código que define a fórmula haversina, que calcula a distância grande círculo entre dois pontos de uma esfera.

4. Depois de definir a função, você a aplica aos dados para criar uma nova coluna de recurso, direct_distance. Mas antes de executar a transformação, altere o contexto de computação para Local.

   rxSetComputeContext("local");
   

5. Chame a função rxDataStep para obter os dados de engenharia de características e aplique a função env$ComputeDist aos dados que estão na memória.

   start.time <- proc.time();
   
   changed_ds <- rxDataStep(inData = featureDataSource,
   transforms = list(direct_distance=ComputeDist(pickup_longitude,pickup_latitude, dropoff_longitude, dropoff_latitude),
   tipped = "tipped", fare_amount = "fare_amount", passenger_count = "passenger_count",
   trip_time_in_secs = "trip_time_in_secs",  trip_distance="trip_distance",
   pickup_datetime = "pickup_datetime",  dropoff_datetime = "dropoff_datetime"),
   transformEnvir = env,
   rowsPerRead=500,
   reportProgress = 3);
   
   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 generate features.", sep=""));
   

   • A função rxDataStep suporta vários métodos para modificar dados no local. Para obter mais informações, consulte este artigo: Como transformar e subdefinir dados no Microsoft R

   No entanto, alguns pontos dignos de nota em relação ao rxDataStep:

   Em outras fontes de dados, você pode usar os argumentos varsToKeep e varsToDrop, mas eles não têm suporte para fontes de dados do SQL Server. Portanto, neste exemplo, usamos o argumento transforms para especificar as colunas de passagem e as colunas transformadas. Além disso, ao ser executado em um contexto de computação do SQL Server, o argumento inData só pode usar uma fonte de dados do SQL Server.

   O código anterior também pode produzir uma mensagem de aviso quando executado em conjuntos de dados maiores. Quando o número de linhas vezes o número de colunas a serem criadas excede um valor predefinido (o padrão é 3.000.000), rxDataStep retorna um alerta e as linhas no quadro de dados retornado serão truncadas. Para remover o aviso, você pode modificar o maxRowsByCols argumento na função rxDataStep. No entanto, se maxRowsByCols for muito grande, você poderá ter problemas ao carregar o quadro de dados na memória.

6. Opcionalmente, você pode chamar rxGetVarInfo para inspecionar o esquema da fonte de dados transformada.

   rxGetVarInfo(data = changed_ds);
   

Featurização usando Transact-SQL

Neste exercício, aprenda a realizar a mesma tarefa usando funções SQL em vez de funções R personalizadas.

Alterne para SQL Server Management Studio ou outro editor de consultas para executar o script T-SQL.

1. Use uma função SQL, chamada fnCalculateDistance. A função já deve existir no banco de dados NYCTaxi_Sample. No Pesquisador de Objetos, verifique se a função existe navegando neste caminho: Bancos de dados > NYCTaxi_Sample > Funções de programação >> Funções com valor escalar > dbo.fnCalculateDistance.

   Se a função não existir, use o SQL Server Management Studio para gerá-la no banco de dados NYCTaxi_Sample.

   CREATE FUNCTION [dbo].[fnCalculateDistance] (@Lat1 float, @Long1 float, @Lat2 float, @Long2 float)
   -- User-defined function calculates the direct distance between two geographical coordinates.
   RETURNS decimal(28, 10)
   AS
   BEGIN
     DECLARE @distance decimal(28, 10)
     -- Convert to radians
     SET @Lat1 = @Lat1 / 57.2958
     SET @Long1 = @Long1 / 57.2958
     SET @Lat2 = @Lat2 / 57.2958
     SET @Long2 = @Long2 / 57.2958
     -- Calculate distance
     SET @distance = (SIN(@Lat1) * SIN(@Lat2)) + (COS(@Lat1) * COS(@Lat2) * COS(@Long2 - @Long1))
     --Convert to miles
     IF @distance <> 0
     BEGIN
       SET @distance = 3958.75 * ATAN(SQRT(1 - POWER(@distance, 2)) / @distance);
     END
     RETURN @distance
   END
   

2. No Management Studio, em uma nova janela de consulta, execute a seguinte instrução Transact-SQL de qualquer aplicativo que ofereça suporte a Transact-SQL para ver como a função funciona.

   USE nyctaxi_sample
   GO
   
   SELECT tipped, fare_amount, passenger_count,trip_time_in_secs,trip_distance, pickup_datetime, dropoff_datetime,
   dbo.fnCalculateDistance(pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude) as direct_distance, pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude 
   FROM nyctaxi_sample
   

3. Para inserir valores diretamente em uma nova tabela (você precisa criá-la primeiro), você pode adicionar uma cláusula INTO especificando o nome da tabela.

   USE nyctaxi_sample
   GO
   
   SELECT tipped, fare_amount, passenger_count, trip_time_in_secs, trip_distance, pickup_datetime, dropoff_datetime,
   dbo.fnCalculateDistance(pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude) as direct_distance, pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude
   INTO NewFeatureTable
   FROM nyctaxi_sample
   

4. Você também pode chamar a função SQL do código R. Regresse ao Rgui e armazene a consulta de featurização SQL numa variável R.

   featureEngineeringQuery = "SELECT tipped, fare_amount, passenger_count,
       trip_time_in_secs,trip_distance, pickup_datetime, dropoff_datetime,
       dbo.fnCalculateDistance(pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as direct_distance,
       pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude
       FROM nyctaxi_sample
       tablesample (1 percent) repeatable (98052)"
   

   Dica

   Esta consulta foi modificada para obter uma amostra menor de dados, para tornar este passo a passo mais rápido. Você pode remover a cláusula TABLESAMPLE se quiser obter todos os dados; no entanto, dependendo do seu ambiente, pode não ser possível carregar o datset completo em R, resultando em um erro.

5. Use as seguintes linhas de código para chamar a função Transact-SQL do seu ambiente R e aplicá-la aos dados definidos em featureEngineeringQuery.

   featureDataSource = RxSqlServerData(sqlQuery = featureEngineeringQuery,
     colClasses = c(pickup_longitude = "numeric", pickup_latitude = "numeric",
       dropoff_longitude = "numeric", dropoff_latitude = "numeric",
       passenger_count  = "numeric", trip_distance  = "numeric",
       trip_time_in_secs  = "numeric", direct_distance  = "numeric"),
     connectionString = connStr)
   

6. Agora que o novo recurso foi criado, chame rxGetVarsInfo para criar um resumo dos dados na tabela de recursos.

   rxGetVarInfo(data = featureDataSource)
   

   Resultados

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

   Observação

   Em alguns casos, você pode receber um erro como este: A permissão EXECUTE foi negada no objeto 'fnCalculateDistance' Em caso afirmativo, certifique-se de que o login que você está usando tenha permissões para executar scripts e criar objetos no banco de dados, não apenas na instância. Verifique o esquema para o objeto, fnCalculateDistance. Se o objeto foi criado pelo proprietário do banco de dados e seu login pertence à função db_datareader, você precisará conceder permissões explícitas de login para executar o script.

Comparando funções R e funções SQL

Lembra-se deste pedaço de código usado para cronometrar o código R?

start.time <- proc.time()
<your code here>
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 generate features.", sep=""))

Você pode tentar usar isso com o exemplo de função personalizada SQL para ver quanto tempo a transformação de dados leva ao chamar uma função SQL. Além disso, tente alternar contextos de computação com rxSetComputeContext e compare os tempos.

Os tempos podem variar significativamente, dependendo da velocidade da rede e da configuração de hardware. Nas configurações que testamos, a abordagem de função Transact-SQL foi mais rápida do que usar uma função R personalizada. Portanto, usamos a função Transact-SQL para esses cálculos nas etapas subsequentes.

Próximos passos