Vytváření datových funkcí pomocí R a SQL Serveru (názorný postup)

platí pro: SQL Server 2016 (13.x) a novější verze

Příprava dat je důležitou součástí strojového učení. Data často vyžadují transformaci, než je budete moct použít k prediktivnímu modelování. Pokud data neobsahují funkce, které potřebujete, můžete je zkonstruovat z existujících hodnot.

Pro tuto úlohu modelování byste místo použití původních hodnot zeměpisné šířky a délky místa vyzvednutí a místa pro odložení chtěli mít vzdálenost mezi těmito dvěma místy vyjádřenou v mílích. K vytvoření této funkce vypočítáte přímou lineární vzdálenost mezi dvěma body pomocí vzorce haversine.

V tomto kroku se seznámíte se dvěma různými metodami pro vytvoření funkce z dat:

Použití vlastní funkce jazyka R
Použití vlastní funkce T-SQL v Transact-SQL

Cílem je vytvořit novou sadu dat SQL Serveru, která obsahuje původní sloupce a novou číselnou funkci direct_distance.

Požadavky

Tento krok předpokládá probíhající relaci jazyka R na základě předchozích kroků v tomto průvodci. Používá připojovací řetězce a objekty zdroje dat vytvořené v těchto krocích. Ke spuštění skriptu se používají následující nástroje a balíčky.

Rgui.exe ke spuštění příkazů jazyka R
Management Studio pro spuštění T-SQL

Featurizace pomocí jazyka R

Jazyk R je dobře známý pro své bohaté a pestré statistické knihovny, ale přesto možná budete muset vytvořit vlastní transformace dat.

Nejprve to uděláme tak, jak jsou uživatelé R zvyklí: načíst data do přenosného počítače a pak spustit vlastní funkci R ComputeDist, která vypočítá lineární vzdálenost mezi dvěma body určenými hodnotami zeměpisné šířky a délky.

Mějte na paměti, že objekt zdroje dat, který jste vytvořili dříve, získá pouze prvních 1000 řádků. Pojďme tedy definovat dotaz, který získá všechna 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";

Vytvořte nový objekt zdroje dat pomocí dotazu.
```
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 může buď použít dotaz skládající se z platného dotazu SELECT, který je zadaný jako argument parametru sqlQuery , nebo název objektu tabulky, který je zadaný jako parametr tabulky .
- Pokud chcete vzorkovat data z tabulky, musíte použít parametr sqlQuery , definovat parametry vzorkování pomocí klauzule T-SQL TABLESAMPLE a nastavit argument rowBuffering na FALSE.
Spuštěním následujícího kódu vytvořte vlastní funkci R. ComputeDist přebírá dva páry hodnot zeměpisné šířky a délky a vypočítá lineární vzdálenost mezi nimi a vrátí vzdálenost v mílích.
```
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)
}
```
- První řádek definuje nové prostředí. V jazyce R lze prostředí použít k zapouzdření názvových prostorů v balíčcích a podobných případech. Funkci můžete použít search() k zobrazení prostředí ve vašem pracovním prostoru. Chcete-li zobrazit objekty v určitém prostředí, zadejte ls(<envname>).
- Řádky začínající kódem $env.ComputeDist definující vzorec haversine, který vypočítá vzdálenost velkého kruhu mezi dvěma body na kouli.
Když jste funkci definovali, použijete ji na data a vytvoříte nový sloupec funkcí direct_distance. ale před spuštěním transformace změňte výpočetní kontext na místní.
```
rxSetComputeContext("local");
```
Zavolejte funkci rxDataStep, abyste získali data inženýrství funkcí, a funkci env$ComputeDist aplikujte na data v paměti.
```
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=""));
```
- Funkce rxDataStep podporuje různé metody pro úpravu zavedených dat. Další informace najdete v tomto článku: Jak transformovat a vytvořit podmnožinu dat v Microsoft R
Nicméně, několik bodů stojí za zmínku o rxDataStep:

V jiných zdrojích dat můžete použít argumenty varsToKeep a varsToDrop, ale nejsou podporované pro zdroje dat SQL Serveru. Proto jsme v tomto příkladu použili argument transformace k určení průchozích sloupců i transformovaných sloupců. Při spuštění ve výpočetním kontextu SQL Serveru může argument inData přebírat pouze zdroj dat SQL Serveru.

Předchozí kód může při spuštění na větších datových sadách vytvořit také zprávu s upozorněním. Pokud součin počtu řádků a počtu vytvářených sloupců překročí nastavenou hodnotu (výchozí hodnota je 3 000 000), funkce rxDataStep vrátí upozornění a počet řádků ve vráceném datovém rámci bude zkrácen. Pokud chcete upozornění odebrat, můžete upravit argument maxRowsByCols ve funkci rxDataStep. Pokud je ale maxRowsByCols příliš velký, může dojít k problémům při načítání datového rámce do paměti.
Volitelně můžete volat rxGetVarInfo a zkontrolovat schéma transformovaného zdroje dat.
```
rxGetVarInfo(data = changed_ds);
```

Featurizace pomocí Transact-SQL

V tomto cvičení se naučíte, jak provést stejnou úlohu pomocí funkcí SQL místo vlastních funkcí jazyka R.

Přepněte na SQL Server Management Studio (SSMS) nebo jiný editor dotazů a spusťte skript T-SQL.

Použijte funkci SQL s názvem fnCalculateDistance. Funkce by již měla existovat v databázi NYCTaxi_Sample. V Průzkumníku objektů ověřte, že tato funkce existuje, a to tak, že přejdete na tuto cestu: Databáze > NYCTaxi_Sample > Programovatelnost > Funkce > Skalární funkce > dbo.fnCalculateDistance.

Pokud tato funkce neexistuje, vygenerujte funkci v databázi NYCTaxi_Sample pomocí aplikace SQL Server Management Studio.

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

V sadě Management Studio spusťte v novém okně dotazu následující příkaz Transact-SQL z libovolné aplikace, která podporuje Transact-SQL, a podívejte se, jak tato funkce funguje.

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

Pokud chcete vložit hodnoty přímo do nové tabulky (musíte ji nejprve vytvořit), můžete přidat klauzuli INTO určující název tabulky.

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

Funkci SQL můžete také volat z kódu jazyka R. Přepněte zpět na Rgui a uložte dotaz featurizace SQL do proměnné jazyka 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)"
```
Návod

Tento dotaz byl upraven ke získání menšího vzorku dat, což urychlí tento návod. Klauzuli TABLESAMPLE můžete odebrat, pokud chcete získat všechna data; V závislosti na vašem prostředí ale nemusí být možné načíst úplnou datovou sadu do jazyka R, což vede k chybě.

Pomocí následujících řádků kódu volejte funkci Transact-SQL z prostředí R a použijte ji na data definovaná v 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)

Teď, když je nová funkce vytvořena, zavolejte rxGetVarsInfo a vytvořte souhrn dat v tabulce funkcí.
```
rxGetVarInfo(data = featureDataSource)
```
Results
```
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
```
Poznámka:

V některých případech se může zobrazit chyba podobná této: Oprávnění EXECUTE bylo u objektu 'fnCalculateDistance' odepřeno. Pokud ano, ujistěte se, že přihlašovací jméno, které používáte, má oprávnění ke spouštění skriptů a vytváření objektů v databázi, nejen v instanci. Zkontrolujte schéma objektu fnCalculateDistance. Pokud objekt vytvořil vlastník databáze a vaše přihlášení patří k roli db_datareader, musíte udělit přihlašovací explicitní oprávnění ke spuštění skriptu.

Porovnání funkcí R a funkcí SQL

Pamatujete si tuto část kódu, která se používá k časování kódu 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=""))

Můžete to zkusit použít s příkladem vlastní funkce SQL a zjistit, jak dlouho trvá transformace dat při volání funkce SQL. Zkuste také přepnout výpočetní kontexty pomocí rxSetComputeContext a porovnat časování.

Vaše časy se můžou výrazně lišit v závislosti na rychlosti sítě a konfiguraci hardwaru. V konfiguracích, které jsme otestovali, byl přístup funkce Transact-SQL rychlejší než použití vlastní funkce R. Proto jsme pro tyto výpočty použili funkci Transact-SQL v dalších krocích.

Návod

Velmi často bude příprava funkcí využívajících Transact-SQL rychlejší než R. T-SQL například obsahuje funkce rychlého okna a řazení, které je možné použít u běžných výpočtů datových věd, jako jsou klouzavé průměry a n-dlaždice. Zvolte nejúčinnější metodu na základě vašich dat a úkolů.

Další kroky

Sestavení modelu R a uložení do SQL

Váš názor

Byla tato stránka užitečná?

Last updated on 2025-11-25