Adatszolgáltatások létrehozása az R és az SQL Server használatával (útmutató)

Cikk
01/02/2025

A következőkre vonatkozik: SQL Server 2016 (13.x) és újabb verziók

Az adatfejlesztés fontos része a gépi tanulásnak. Az adatok gyakran átalakítást igényelnek, mielőtt prediktív modellezésre használhatja. Ha az adatok nem rendelkeznek a szükséges funkciókkal, a meglévő értékek alapján is megtervezheti őket.

Ebben a modellezési feladatban a felvétel és a legördülő hely nyers szélességi és hosszúsági értékei helyett a két hely közötti távolságot szeretné mérföldben meghatározni. A funkció létrehozásához kiszámítja a két pont közötti közvetlen lineáris távolságot a haversine képlethasználatával.

Ebben a lépésben két különböző módszert ismer meg egy szolgáltatás adatokból való létrehozásához:

Egyéni R-függvény használata
Egyéni T-SQL-függvény használata Transact-SQL

A cél egy új SQL Server-adatkészlet létrehozása, amely tartalmazza az eredeti oszlopokat, valamint az új numerikus funkciót, direct_distance.

Előfeltételek

Ez a lépés feltételez egy folyamatban lévő R-munkamenetet az útmutató korábbi lépései alapján. Az ezekben a lépésekben létrehozott kapcsolati sztringeket és adatforrás-objektumokat használja. A szkript futtatásához az alábbi eszközöket és csomagokat használja a rendszer.

R-parancsok futtatásához Rgui.exe
A Management Studio a T-SQL futtatásához

Jellemzők kinyerése R használatával

Az R nyelv jól ismert a gazdag és változatos statisztikai kódtárakról, de előfordulhat, hogy egyéni adatátalakításokat kell létrehoznia.

Először is tegyük meg úgy, ahogyan az R-felhasználók megszokták: szerezzük be az adatokat a laptopra, majd futtassunk egy egyéni R-függvényt, ComputeDist, amely kiszámítja a szélességi és hosszúsági értékek által megadott két pont közötti lineáris távolságot.

Ne feledje, hogy a korábban létrehozott adatforrás-objektum csak a legfelső 1000 sort kapja meg. Definiáljunk tehát egy lekérdezést, amely lekéri az összes adatot.

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

Hozzon létre egy új adatforrás-objektumot a lekérdezés használatával.
```
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 vagy egy érvényes SELECT lekérdezésből álló kérés lehet, amely az sqlQuery paraméter argumentumaként van megadva, vagy egy táblaobjektum neve, amely a tábla paraméterként van megadva.
- Ha egy táblából szeretne adatokat mintázni, az sqlQuery paramétert kell használnia, a T-SQL TABLESAMPLE záradék használatával meg kell határoznia a mintavételezési paramétereket, és a sorbuffering argumentumot HAMIS értékre kell állítania.
Futtassa az alábbi kódot az egyéni R függvény létrehozásához. A ComputeDist két szélességi és hosszúsági értéket vesz fel, és kiszámítja a köztük lévő lineáris távolságot, és mérföldekben adja vissza a távolságot.
```
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)
}
```
- Az első sor egy új környezetet határoz meg. Az R-ben egy környezet használható a csomagok névtereinek beágyazására. A search() függvénnyel megtekintheti a munkaterület környezeteit. Ha egy adott környezetben szeretné megtekinteni az objektumokat, írja be a ls(<envname>).
- A $env.ComputeDist kezdődő sorok tartalmazzák a haversine képletet meghatározó kódot, amely kiszámítja a nagykörű távolság a gömb két pontja között.
A függvény definiálása után az adatokra alkalmazva létrehoz egy új funkcióoszlopot, direct_distance. de az átalakítás futtatása előtt módosítsa a számítási környezetet helyire.
```
rxSetComputeContext("local");
```
Hívja meg az rxDataStep függvényt a funkciótervezési adatok lekéréséhez, és alkalmazza a env$ComputeDist függvényt a memóriában lévő adatokra.
```
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=""));
```
- Az rxDataStep függvény különböző módszereket támogat az adatok helyben történő módosításához. További információért olvassa el ezt a cikket: Adatok átalakítása és részhalmaz készítése a Microsoft R-ben
Azonban érdemes megjegyezni néhány pontot az rxDataStep-re vonatkozóan:

Más adatforrásokban használhatja a varsToKeep és a varsToDropargumentumokat, de az SQL Server-adatforrások esetében ezek nem támogatottak. Ezért ebben a példában a transzformációs argumentumot használtuk, hogy megadjuk mind az átmenő, mind az átalakított oszlopokat. Ha SQL Server számítási környezetben fut, az inData argumentum csak SQL Server-adatforrást vehet igénybe.

Az előző kód figyelmeztető üzenetet is képes generálni, ha nagyobb adatkészleteken fut. Amikor a létrehozott sorok és oszlopok számának szorzata meghaladja a megadott értéket (az alapértelmezett érték 3 000 000), az rxDataStep figyelmeztetést ad, és a visszaadott adatkeret sorainak száma levágásra kerül. A figyelmeztetés eltávolításához módosíthatja a maxRowsByCols argumentumot az rxDataStep függvényben. Ha azonban maxRowsByCols túl nagy, problémák léphetnek fel az adatkeret memóriába való betöltésekor.
Igény szerint meghívhatja rxGetVarInfo az átalakított adatforrás sémájának vizsgálatához.
```
rxGetVarInfo(data = changed_ds);
```

Jellemzéssel Transact-SQL használatával

Ebben a gyakorlatban megtudhatja, hogyan végezheti el ugyanazt a feladatot az egyéni R-függvények helyett az SQL-függvényekkel.

Váltson SQL Server Management Studio vagy egy másik lekérdezésszerkesztőre a T-SQL-szkript futtatásához.

Használjon egy fnCalculateDistancenevű SQL-függvényt. A függvénynek már léteznie kell a NYCTaxi_Sample adatbázisban. Az Object Explorerben ellenőrizze, hogy létezik-e a függvény a következő elérési úton: Adatbázisok > NYCTaxi_Sample > Programozhatóság > Függvények > skaláris értékű függvények > dbo.fnCalculateDistance.

Ha a függvény nem létezik, az SQL Server Management Studio használatával hozza létre a függvényt a NYCTaxi_Sample adatbázisban.

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

A Management Studióban egy új lekérdezési ablakban futtassa az alábbi Transact-SQL utasítást minden olyan alkalmazásból, amely támogatja a Transact-SQL a függvény működésének megtekintéséhez.

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

Ha közvetlenül szeretne értékeket beszúrni egy új táblába (először létre kell hoznia), hozzáadhat egy INTO záradékot, amely megadja a tábla nevét.

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

Az SQL-függvényt R-kódból is meghívhatja. Váltson vissza az Rgui-re, és tárolja az SQL featurization lekérdezést egy R változóban.
```
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)"
```
Borravaló

Ezt a lekérdezést úgy módosítottuk, hogy egy kisebb adatmintát kapjon, hogy gyorsabb legyen az útmutató. Ha az összes adatot le szeretné szerezni, eltávolíthatja a TABLESAMPLE záradékot; A környezettől függően azonban előfordulhat, hogy nem lehet betölteni a teljes adathalmazt az R-be, ami hibát eredményez.

Az alábbi kódsorokkal hívja meg a Transact-SQL függvényt az R-környezetből, és alkalmazza az adatokat a featureEngineeringQuery-ben.

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)

Most, hogy létrejött az új funkció, hívja meg rxGetVarsInfo a funkciótáblában szereplő adatok összegzésének létrehozásához.
```
rxGetVarInfo(data = featureDataSource)
```
eredmények
```
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
```
Jegyzet

Bizonyos esetekben a következőhöz hasonló hibaüzenet jelenhet meg: Az "fnCalculateDistance" objektum végrehajtási engedélyének megtagadása Ha igen, győződjön meg arról, hogy a használt bejelentkezés rendelkezik szkriptek futtatására és objektumok létrehozására az adatbázisban, nem csak a példányon. Ellenőrizze az fnCalculateDistance objektum sémáját. Ha az objektumot az adatbázis tulajdonosa hozta létre, és a bejelentkezés a db_datareader szerepkörhöz tartozik, explicit engedélyeket kell adnia a szkript futtatásához.

R-függvények és SQL-függvények összehasonlítása

Emlékszel erre a kódra, amelyet az R-kód időzítésére használtak?

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

Az egyéni SQL-függvény példáját kipróbálva láthatja, mennyi ideig tart az adattranszformáció egy SQL-függvény meghívása során. Emellett próbálja meg a számítási környezeteket az rxSetComputeContext használatával váltani, és hasonlítsa össze az időzítéseket.

Az idő jelentősen eltérhet a hálózati sebességtől és a hardverkonfigurációtól függően. A tesztelt konfigurációkban a Transact-SQL függvény megközelítése gyorsabb volt, mint egy egyéni R-függvény használata. Ezért a következő lépésekben a Transact-SQL függvényt használjuk ezekhez a számításokhoz.

Borravaló

A Transact-SQL használatával végzett funkciófejlesztés gyakran gyorsabb lesz, mint az R. A T-SQL például olyan gyors ablakozási és rangsorolási függvényeket tartalmaz, amelyek olyan általános adatelemzési számításokra alkalmazhatók, mint a mozgó átlagok gördülése és n-csempék. Az adatok és a feladat alapján válassza ki a leghatékonyabb módszert.

Következő lépések

Készíts egy R-modellt, és mentsd el az SQL-be

Megosztás a következőn keresztül: