Gegevensfuncties maken met R en SQL Server (overzicht)

Artikel
01/02/2025

Van toepassing op: SQL Server 2016 (13.x) en latere versies

Data engineering is een belangrijk onderdeel van machine learning. Voor gegevens is vaak transformatie vereist voordat u deze kunt gebruiken voor voorspellende modellering. Als de gegevens niet over de functies beschikken die u nodig hebt, kunt u ze op basis van bestaande waarden ontwerpen.

Voor deze modelleringstaak, in plaats van de onbewerkte breedte- en lengtegraadwaarden van de ophaal- en afleverlocatie te gebruiken, wilt u de afstand tussen de twee locaties hebben. Als u deze functie wilt maken, berekent u de directe lineaire afstand tussen twee punten met behulp van de haversineformule.

In deze stap leert u twee verschillende methoden voor het maken van een functie op basis van gegevens:

Een aangepaste R-functie gebruiken
Een aangepaste T-SQL-functie gebruiken in Transact-SQL

Het doel is om een nieuwe SQL Server-set gegevens te maken die de oorspronkelijke kolommen plus de nieuwe numerieke functie bevat, direct_distance.

Voorwaarden

In deze stap wordt ervan uitgegaan dat er een lopende R-sessie is op basis van de vorige stappen in deze handleiding. Hierbij worden de verbindingsreeksen en gegevensbronobjecten gebruikt die in deze stappen zijn gemaakt. De volgende hulpprogramma's en pakketten worden gebruikt om het script uit te voeren.

Rgui.exe R-opdrachten uitvoeren
Management Studio voor het uitvoeren van T-SQL

Featurization met R

De R-taal staat bekend om zijn rijke en gevarieerde statistische bibliotheken, maar u moet mogelijk nog steeds aangepaste gegevenstransformaties maken.

Laten we het eerst doen zoals R-gebruikers gewend zijn: de gegevens op uw laptop ophalen en vervolgens een aangepaste R-functie uitvoeren, ComputeDist-, waarmee de lineaire afstand tussen twee punten wordt berekend die is opgegeven door de breedtegraad- en lengtegraadwaarden.

Houd er rekening mee dat het gegevensbronobject dat u eerder hebt gemaakt alleen de bovenste 1000 rijen krijgt. We gaan dus een query definiëren waarmee alle gegevens worden opgehaald.

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

Maak een nieuw gegevensbronobject met behulp van de query.
```
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 een query maken die bestaat uit een geldige SELECT-query, opgegeven als argument voor de parameter sqlQuery of de naam van een tabelobject, opgegeven als de parameter tabel.
- Als u voorbeeldgegevens uit een tabel wilt gebruiken, moet u de parameter sqlQuery gebruiken, steekproevenparameters definiëren met behulp van de T-SQL TABLESAMPLE-component en de rowBuffering argument instellen op FALSE.
Voer de volgende code uit om de aangepaste R-functie te maken. ComputeDist neemt twee paren breedtegraad- en lengtegraadwaarden in beslag en berekent de lineaire afstand ertussen, waardoor de afstand in mijlen wordt geretourneerd.
```
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)
}
```
- De eerste regel definieert een nieuwe omgeving. In R kan een omgeving worden gebruikt voor het inkapselen van naamruimten in pakketten en dergelijke. U kunt de functie search() gebruiken om de omgevingen in uw werkruimte weer te geven. Als u de objecten in een specifieke omgeving wilt weergeven, typt u ls(<envname>).
- De regels die beginnen met $env.ComputeDist bevatten de code waarmee de haversinusformule wordt gedefinieerd, waarmee de grote cirkelafstand tussen twee punten op een bol wordt berekend.
Nadat u de functie hebt gedefinieerd, past u deze toe op de gegevens om een nieuwe functiekolom te maken, direct_distance. maar voordat u de transformatie uitvoert, wijzigt u de rekencontext in lokaal.
```
rxSetComputeContext("local");
```
Roep de rxDataStep--functie aan om de feature engineering gegevens op te halen en pas de env$ComputeDist-functie toe op de gegevens in het geheugen.
```
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=""));
```
- De functie rxDataStep ondersteunt verschillende methoden voor het wijzigen van gegevens. Zie dit artikel voor meer informatie: Hoe gegevens te transformeren en onder te verdelen in Microsoft R
Een aantal punten die het vermelden waard zijn met betrekking tot rxDataStep:

In andere gegevensbronnen kunt u de argumenten varsToKeep en varsToDropgebruiken, maar deze worden niet ondersteund voor SQL Server-gegevensbronnen. Daarom hebben we in dit voorbeeld het transformaties argument gebruikt om zowel de passthrough-kolommen als de getransformeerde kolommen op te geven. Wanneer het in een SQL Server-rekencontext wordt uitgevoerd, kan het argument inData alleen een SQL Server-gegevensbron gebruiken.

Met de voorgaande code kan ook een waarschuwingsbericht worden weergegeven wanneer deze wordt uitgevoerd op grotere gegevenssets. Wanneer het aantal rijen vermenigvuldigd met het aantal kolommen groter is dan een ingestelde waarde (de standaardwaarde is 3.000.000), geeft rxDataStep een waarschuwing en wordt het aantal rijen in het resulterende gegevensframe ingekort. Als u de waarschuwing wilt verwijderen, kunt u de maxRowsByCols argument wijzigen in de functie rxDataStep. Als maxRowsByCols echter te groot is, kan het zijn dat u problemen ondervindt bij het laden van het gegevensframe in het geheugen.
U kunt desgewenst rxGetVarInfo aanroepen om het schema van de getransformeerde gegevensbron te controleren.
```
rxGetVarInfo(data = changed_ds);
```

Kenmerkextractie met behulp van Transact-SQL

In deze oefening leert u hoe u dezelfde taak kunt uitvoeren met behulp van SQL-functies in plaats van aangepaste R-functies.

Schakel over naar SQL Server Management Studio- of een andere query-editor om het T-SQL-script uit te voeren.

Gebruik een SQL-functie met de naam fnCalculateDistance. De functie moet al bestaan in de NYCTaxi_Sample-database. Controleer in Object Explorer of de functie bestaat door het volgende pad te volgen: Databases > NYCTaxi_Sample > Programmability > Functions > Scalar-valued Functions > dbo.fnCalculateDistance.

Als de functie niet bestaat, gebruikt u SQL Server Management Studio om de functie te genereren in de NYCTaxi_Sample-database.

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

Voer in Management Studio in een nieuw queryvenster de volgende Transact-SQL instructie uit van elke toepassing die ondersteuning biedt voor Transact-SQL om te zien hoe de functie werkt.

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

Als u waarden rechtstreeks in een nieuwe tabel wilt invoegen (u moet deze eerst maken), kunt u een INTO-component toevoegen die de tabelnaam opgeeft.

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

U kunt de SQL-functie ook aanroepen vanuit R-code. Ga terug naar Rgui en sla de SQL-featurization-query op in een R-variabele.
```
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)"
```
Tip

Deze query is gewijzigd om een kleiner voorbeeld van gegevens te krijgen, zodat deze procedure sneller verloopt. U kunt de TABLESAMPLE-component verwijderen als u alle gegevens wilt ophalen; Afhankelijk van uw omgeving is het echter mogelijk niet mogelijk om de volledige gegevensset in R te laden, wat resulteert in een fout.

Gebruik de volgende coderegels om de Transact-SQL-functie aan te roepen vanuit uw R-omgeving en deze toe te passen op de gegevens die zijn gedefinieerd in 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)

Nu de nieuwe functie is gemaakt, roept u rxGetVarsInfo aan om een samenvatting van de gegevens in de functietabel te maken.
```
rxGetVarInfo(data = featureDataSource)
```
Resultaten
```
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
```
Notitie

In sommige gevallen krijgt u mogelijk een foutmelding zoals deze: De EXECUTE-machtiging is geweigerd voor het object fnCalculateDistance Als dat het geval is, moet u ervoor zorgen dat de aanmelding die u gebruikt, machtigingen heeft om scripts uit te voeren en objecten in de database te maken, niet alleen op het exemplaar. Controleer het schema voor het object fnCalculateDistance. Als het object is gemaakt door de database-eigenaar en uw aanmelding deel uitmaakt van de rol db_datareader, moet u de aanmelding expliciete machtigingen geven om het script uit te voeren.

R-functies en SQL-functies vergelijken

Herinnert u zich dit stukje code dat wordt gebruikt om de R-code te timen?

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

U kunt dit proberen met het voorbeeld van de aangepaste SQL-functie om te zien hoe lang de gegevenstransformatie duurt bij het aanroepen van een SQL-functie. Probeer ook om te schakelen tussen rekencontexten met rxSetComputeContext en vergelijk de tijdsinstellingen.

Uw tijden kunnen aanzienlijk variëren, afhankelijk van uw netwerksnelheid en uw hardwareconfiguratie. In de configuraties die we hebben getest, was de methode Transact-SQL functie sneller dan het gebruik van een aangepaste R-functie. Daarom gebruiken we de functie Transact-SQL voor deze berekeningen in de volgende stappen.

Tip

Zeer vaak is functie-engineering met behulp van Transact-SQL sneller dan R. T-SQL bevat bijvoorbeeld snel venster- en classificatiefuncties die kunnen worden toegepast op algemene gegevenswetenschapberekeningen, zoals zwevende gemiddelden en n-tegels. Kies de meest efficiënte methode op basis van uw gegevens en taken.

Volgende stappen

een R-model bouwen en opslaan in SQL

Delen via