Skapa datafunktioner med R och SQL Server (genomgång)

Artikel
01/02/2025

gäller för: SQL Server 2016 (13.x) och senare versioner

Datateknik är en viktig del av maskininlärning. Data kräver ofta transformering innan du kan använda dem för förutsägelsemodellering. Om data inte har de funktioner du behöver kan du skapa dem från befintliga värden.

För den här modelluppgiften, i stället för att använda de råa latitud- och longitudvärdena för upphämtnings- och avlämningsplatsen, vill du ha avståndet i miles mellan de två platserna. Om du vill skapa den här funktionen beräknar du det direkta linjära avståndet mellan två punkter med hjälp av haversinformeln.

I det här steget lär du dig två olika metoder för att skapa en funktion från data:

Använda en anpassad R-funktion
Använda en anpassad T-SQL-funktion i Transact-SQL

Målet är att skapa en ny SQL Server-uppsättning data som innehåller de ursprungliga kolumnerna plus den nya numeriska funktionen direct_distance.

Förutsättningar

Det här steget förutsätter en pågående R-session baserat på tidigare steg i den här genomgången. Den använder anslutningssträngar och datakällans objekt som skapats i dessa steg. Följande verktyg och paket används för att köra skriptet.

Rgui.exe för att köra R-kommandon
Management Studio för att köra T-SQL

Funktionalisering med R

R-språket är välkänt för sina omfattande och varierande statistiska bibliotek, men du kan fortfarande behöva skapa anpassade datatransformeringar.

Först ska vi göra det som R-användare är vana vid: hämta data till din bärbara dator och sedan köra en anpassad R-funktion, ComputeDist, som beräknar det linjära avståndet mellan två punkter som anges av latitud- och longitudvärden.

Kom ihåg att datakällans objekt som du skapade tidigare bara får de översta 1 000 raderna. Så vi definierar en fråga som hämtar alla data.

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

Skapa ett nytt datakällobjekt genom att använda frågan.
```
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 kan antingen ta en fråga som består av en giltig SELECT-fråga, som anges som argument för parametern sqlQuery eller namnet på ett tabellobjekt, som anges som tabell parameter.
- Om du vill exempeldata från en tabell måste du använda parametern sqlQuery, definiera samplingsparametrar med T-SQL TABLESAMPLE-satsen och ange argumentet rowBuffering till FALSE.
Kör följande kod för att skapa den anpassade R-funktionen. ComputeDist tar in två par latitud- och longitudvärden och beräknar det linjära avståndet mellan dem och returnerar avståndet i miles.
```
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)
}
```
- Den första raden definierar en ny miljö. I R kan en miljö användas för att kapsla in namnrymder i paket. Du kan använda funktionen search() för att visa miljöerna på din arbetsyta. Om du vill visa objekten i en specifik miljö skriver du ls(<envname>).
- Raderna som börjar med $env.ComputeDist innehåller koden som definierar haversinformeln, som beräknar det stora cirkelavståndet mellan två punkter på en sfär.
När du har definierat funktionen använder du den på data för att skapa en ny funktionskolumn, direct_distance. men innan du kör omvandlingen ändrar du beräkningskontexten till lokal.
```
rxSetComputeContext("local");
```
Anropa funktionen rxDataStep för att hämta feature engineering-data och tillämpa funktionen env$ComputeDist på data i minnet.
```
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=""));
```
- Funktionen rxDataStep stöder olika metoder för att ändra data på plats. Mer information finns i den här artikeln: How to transform and subset data in Microsoft R
Men ett par punkter som är värda att notera när det gäller rxDataStep:

I andra datakällor kan du använda argumenten varsToKeep och varsToDrop, men dessa stöds inte för SQL Server-datakällor. I det här exemplet har vi därför använt -transformen och-argumentet för att ange både pass-through-kolumnerna och de transformerade kolumnerna. När du kör i en SQL Server-beräkningskontext kan argumentet inData bara ta en SQL Server-datakälla.

Föregående kod kan också skapa ett varningsmeddelande när den körs på större datamängder. När antalet rader gånger antalet kolumner som skapas överskrider ett angivet värde (standardvärdet är 3 000 000), returnerar rxDataStep en varning och antalet rader i den returnerade dataramen trunkeras. Om du vill ta bort varningen kan du ändra argumentet maxRowsByCols i funktionen rxDataStep. Men om maxRowsByCols är för stor kan det uppstå problem när du läser in dataramen i minnet.
Du kan också anropa rxGetVarInfo- för att inspektera schemat för den transformerade datakällan.
```
rxGetVarInfo(data = changed_ds);
```

Featurisering med Transact-SQL

I den här övningen får du lära dig hur du utför samma uppgift med hjälp av SQL-funktioner i stället för anpassade R-funktioner.

Växla till SQL Server Management Studio eller någon annan frågeredigerare för att köra T-SQL-skriptet.

Använd en SQL-funktion med namnet fnCalculateDistance. Funktionen bör redan finnas i NYCTaxi_Sample-databasen. I Object Explorer kontrollerar du att funktionen finns genom att navigera i den här sökvägen: Databaser > NYCTaxi_Sample > Programmability > Functions > Scalar-valued Functions > dbo.fnCalculateDistance.

Om funktionen inte finns använder du SQL Server Management Studio för att generera funktionen i NYCTaxi_Sample-databasen.

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

I Management Studio kör du följande Transact-SQL-instruktion i ett nytt frågefönster från alla program som stöder Transact-SQL för att se hur funktionen fungerar.

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

Om du vill infoga värden direkt i en ny tabell (du måste skapa dem först) kan du lägga till en INTO--sats som anger tabellnamnet.

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

Du kan också anropa SQL-funktionen från R-koden. Växla tillbaka till Rgui och lagra SQL-funktionaliseringsfrågan i en R-variabel.
```
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)"
```
Tips

Den här frågan har ändrats för att få ett mindre dataexempel för att göra den här genomgången snabbare. Du kan ta bort TABLESAMPLE-satsen om du vill hämta alla data. Beroende på din miljö kanske det dock inte går att läsa in den fullständiga datamängden i R, vilket resulterar i ett fel.

Använd följande kodrader för att anropa funktionen Transact-SQL från R-miljön och tillämpa den på de data som definierats i funktionenEngineeringQuery.

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)

Nu när den nya funktionen har skapats anropar du rxGetVarsInfo för att skapa en sammanfattning av data i funktionstabellen.
```
rxGetVarInfo(data = featureDataSource)
```
Resultat
```
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
```
Not

I vissa fall kan du få ett felmeddelande som det här: EXECUTE-behörigheten nekades för objektet "fnCalculateDistance" Kontrollera i så fall att inloggningen du använder har behörighet att köra skript och skapa objekt i databasen, inte bara på instansen. Kontrollera schemat för objektet, fnCalculateDistance. Om objektet skapades av databasägaren och inloggningen tillhör rollen db_datareader måste du ge inloggningen explicit behörighet att köra skriptet.

Jämföra R-funktioner och SQL-funktioner

Kommer du ihåg den här kodbiten som användes för att tajla R-koden?

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

Du kan prova att använda det här med det anpassade SQL-funktionsexemplet för att se hur lång tid datatransformeringen tar när du anropar en SQL-funktion. Prova också att växla beräkningskontexter med rxSetComputeContext och jämföra tidsinställningarna.

Dina tider kan variera avsevärt, beroende på nätverkets hastighet och maskinvarukonfigurationen. I de konfigurationer som vi testade var Transact-SQL-funktionsmetoden snabbare än att använda en anpassad R-funktion. Därför har vi använt funktionen Transact-SQL för dessa beräkningar i efterföljande steg.

Tips

Ofta är feature engineering med hjälp av Transact-SQL snabbare än R. T-SQL innehåller till exempel snabbare fönsterfunktioner och rangordningsfunktioner som kan tillämpas på vanliga datavetenskapsberäkningar, till exempel glidande medelvärden och n-kvantiler. Välj den mest effektiva metoden baserat på dina data och aktiviteter.

Nästa steg

Skapa en R-modell och spara i SQL

Dela via