Tworzenie funkcji danych przy użyciu języka R i programu SQL Server (przewodnik)

Dotyczy: SQL Server 2016 (13.x) i nowsze wersje

Inżynieria danych jest ważną częścią uczenia maszynowego. Dane często wymagają przekształcenia, zanim będzie można go używać do modelowania predykcyjnego. Jeśli dane nie mają potrzebnych funkcji, możesz je zaprojektować na podstawie istniejących wartości.

W przypadku tego zadania modelowania, zamiast używać surowych wartości szerokości geograficznej i długości geograficznej lokalizacji odbioru i oddania, należy określić odległość w milach między dwiema lokalizacjami. Aby utworzyć tę funkcję, obliczysz bezpośrednią odległość liniową między dwoma punktami przy użyciu formuły haversine.

W tym kroku poznasz dwie różne metody tworzenia funkcji na podstawie danych:

• Używanie niestandardowej funkcji języka R
• Używanie niestandardowej funkcji T-SQL w Transact-SQL

Celem jest utworzenie nowego zestawu danych programu SQL Server, który zawiera oryginalne kolumny oraz nową funkcję liczbową, direct_distance.

Warunki wstępne

W tym kroku założono, że trwa sesja języka R oparta na poprzednich krokach w tym przewodniku. Używa on parametrów połączenia i obiektów źródła danych utworzonych w tych krokach. Następujące narzędzia i pakiety są używane do uruchamiania skryptu.

• Rgui.exe do uruchamiania poleceń języka R
• Narzędzie Management Studio do uruchamiania poleceń T-SQL

Cechowanie przy użyciu języka R

Język R jest dobrze znany ze swoich bogatych i zróżnicowanych bibliotek statystycznych, ale nadal może być konieczne utworzenie niestandardowych przekształceń danych.

Najpierw zróbmy to tak, jak użytkownicy języka R są przyzwyczajeni do: pobierania danych na laptopa, a następnie uruchamiania niestandardowej funkcji języka R, ComputeDist, która oblicza liniową odległość między dwoma punktami określonymi przez wartości szerokości geograficznej i długości geograficznej.

1. Pamiętaj, że utworzony wcześniej obiekt źródła danych pobiera tylko 1000 pierwszych wierszy. Zdefiniujmy więc zapytanie, które pobiera wszystkie dane.

   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. Utwórz nowy obiekt źródła danych przy użyciu zapytania.

   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 może przyjmować zapytanie składające się z prawidłowego zapytania SELECT, przekazanego jako argument parametru sqlQuery, lub nazwę obiektu tabeli, przekazaną jako parametr table.

   • Jeśli chcesz próbkować dane z tabeli, musisz użyć parametru sqlQuery, zdefiniować parametry próbkowania przy użyciu klauzuli T-SQL TABLESAMPLE i ustawić argument rowBuffering na FALSE.

3. Uruchom następujący kod, aby utworzyć niestandardową funkcję języka R. ComputeDist przyjmuje dwie pary wartości szerokości geograficznej i długości geograficznej i oblicza odległość liniową między nimi, zwracając odległość w milach.

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

   • Pierwszy wiersz definiuje nowe środowisko. W języku R środowisko może służyć do hermetyzacji przestrzeni nazw w pakietach i takich. Aby wyświetlić środowiska w obszarze roboczym, możesz użyć funkcji search(). Aby wyświetlić obiekty w określonym środowisku, wpisz ls(<envname>).
   • Wiersze rozpoczynające się od $env.ComputeDist zawierają kod, który definiuje formułę haversine, która oblicza odległości wielkiego okręgu między dwoma punktami na sferze.

4. Po zdefiniowaniu funkcji należy zastosować ją do danych w celu utworzenia nowej kolumny funkcji direct_distance. ale przed uruchomieniem przekształcenia zmień kontekst obliczeniowy na lokalny.

   rxSetComputeContext("local");
   

5. Wywołaj funkcję rxDataStep, aby uzyskać dane inżynieryjne funkcji, i zastosuj funkcję env$ComputeDist do danych w pamięci.

   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=""));
   

   • Funkcja rxDataStep obsługuje różne metody modyfikowania danych w miejscu. Aby uzyskać więcej informacji, zobacz ten artykuł: Jak przekształcać i podzestaw danych w usłudze Microsoft R

   Warto jednak zauważyć kilka punktów dotyczących rxDataStep:

   W innych źródłach danych można użyć argumentów varsToKeep i varsToDrop, ale nie są one obsługiwane w przypadku źródeł danych programu SQL Server. W związku z tym w tym przykładzie użyliśmy argumentu transformacji, aby określić zarówno kolumny przekazywane, jak i kolumny przekształcone. Ponadto w przypadku uruchamiania w kontekście obliczeniowym programu SQL Server argument inData może przyjmować tylko źródło danych programu SQL Server.

   Powyższy kod może również wygenerować komunikat ostrzegawczy podczas uruchamiania w większych zestawach danych. Gdy iloczyn liczby wierszy i liczby kolumn, które są tworzone, przekracza ustawioną wartość (wartość domyślna to 3 000 000), funkcja rxDataStep zwraca ostrzeżenie, a liczba wierszy w zwróconej ramce danych zostanie obcięta. Aby usunąć ostrzeżenie, możesz zmodyfikować argument maxRowsByCols w funkcji rxDataStep. Jednak jeśli maxRowsByCols jest zbyt duże, podczas ładowania ramki danych do pamięci mogą wystąpić problemy.

6. Opcjonalnie możesz wywołać rxGetVarInfo, aby sprawdzić schemat przekształconego źródła danych.

   rxGetVarInfo(data = changed_ds);
   

Cechowanie przy użyciu Transact-SQL

W tym ćwiczeniu dowiesz się, jak wykonać to samo zadanie przy użyciu funkcji SQL zamiast niestandardowych funkcji języka R.

Przejdź do programu SQL Server Management Studio lub innego edytora zapytań, aby uruchomić skrypt języka T-SQL.

1. Użyj funkcji SQL o nazwie fnCalculateDistance. Funkcja powinna już istnieć w bazie danych NYCTaxi_Sample. W Eksploratorze obiektów sprawdź, czy funkcja istnieje, przechodząc do tej ścieżki: Databases > NYCTaxi_Sample > Programmability > Functions > Scalar-valued Functions > dbo.fnCalculateDistance.

   Jeśli funkcja nie istnieje, użyj programu SQL Server Management Studio, aby wygenerować funkcję w bazie danych 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. W programie Management Studio w nowym oknie zapytania uruchom następującą instrukcję Transact-SQL z dowolnej aplikacji obsługującej Transact-SQL, aby zobaczyć, jak działa funkcja.

   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. Aby wstawić wartości bezpośrednio do nowej tabeli (musisz ją najpierw utworzyć), możesz dodać klauzulę INTO określającą nazwę tabeli.

   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. Funkcję SQL można również wywołać z poziomu kodu języka R. Wróć do języka Rgui i zapisz zapytanie cechowania SQL w zmiennej języka 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)"
   

   Napiwek

   ** To zapytanie zostało zmodyfikowane w celu uzyskania mniejszej próbki danych, aby przyspieszyć realizację tego procesu. Jeśli chcesz uzyskać wszystkie dane, możesz usunąć klauzulę TABLESAMPLE; jednak w zależności od środowiska może nie być możliwe załadowanie pełnego zestawu danych do języka R, co spowoduje błąd.

5. Użyj następujących wierszy kodu, aby wywołać funkcję Transact-SQL ze środowiska języka R i zastosować je do danych zdefiniowanych w 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. Po utworzeniu nowej funkcji wywołaj rxGetVarsInfo, aby utworzyć podsumowanie danych w tabeli funkcji.

   rxGetVarInfo(data = featureDataSource)
   

   wyniki

   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
   

   Notatka

   W niektórych przypadkach może wystąpić błąd podobny do następującego: Uprawnienie EXECUTE zostało odrzucone w obiekcie "fnCalculateDistance" Jeśli tak, upewnij się, że używany identyfikator logowania ma uprawnienia do uruchamiania skryptów i tworzenia obiektów w bazie danych, a nie tylko w instancji. Sprawdź schemat obiektu fnCalculateDistance. Jeśli obiekt został utworzony przez właściciela bazy danych, a Twoje konto należy do roli db_datareader, musisz nadać mu jawne uprawnienia do uruchomienia skryptu.

Porównywanie funkcji języka R i funkcji SQL

Pamiętasz ten fragment kodu używany do mierzenia czasu działania kodu 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=""))

Możesz spróbować użyć tego przykładu funkcji niestandardowej SQL, aby zobaczyć, jak długo trwa przekształcanie danych podczas wywoływania funkcji SQL. Spróbuj również przełączyć konteksty obliczeniowe za pomocą rxSetComputeContext i porównać czas wykonania.

Czasy mogą się znacznie różnić w zależności od szybkości sieci i konfiguracji sprzętu. W testowanych konfiguracjach funkcja Transact-SQL była szybsza niż użycie niestandardowej funkcji R. W związku z tym użyjemy funkcji Transact-SQL dla tych obliczeń w kolejnych krokach.

Napiwek

Bardzo często inżynieria cech przy użyciu Transact-SQL będzie szybsza niż R. Na przykład język T-SQL zawiera funkcje szybkiego okna i klasyfikowania, które można zastosować do typowych obliczeń nauki o danych, takich jak kroczące średnie ruchome i n-kafelki. Wybierz najbardziej wydajną metodę na podstawie danych i zadań.

Następne kroki

Utwórz model R i zapisz do SQL