Zobrazení a shrnutí dat SQL Serveru pomocí jazyka R (názorný postup)

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

Tato lekce vás seznámí s funkcemi v balíčku RevoScaleR a provede vás následujícími úlohami:

• Připojení k SQL Serveru
• Definujte dotaz, který obsahuje potřebná data, nebo zadejte tabulku nebo zobrazení.
• Definování jednoho nebo více výpočetních kontextů, které se mají použít při spouštění kódu jazyka R
• Volitelně můžete definovat transformace, které se použijí na zdroj dat při čtení ze zdroje.

Definování výpočetního kontextu SQL Serveru

Na klientské pracovní stanici spusťte následující příkazy jazyka R v prostředí R. Tato část předpokládá pracovní stanici datových věd s klientem Microsoft R, protože zahrnuje všechny balíčky RevoScaleR a také základní zjednodušenou sadu nástrojů R. Můžete například použít Rgui.exe ke spuštění skriptu jazyka R v této části.

1. Pokud balíček RevoScaleR ještě není načtený, spusťte tento řádek kódu jazyka R:

   library("RevoScaleR")
   

   Uvozovky jsou v tomto případě volitelné, a přesto se doporučují.

   Pokud dojde k chybě, ujistěte se, že vaše vývojové prostředí R používá knihovnu, která obsahuje balíček RevoScaleR. Použijte příkaz, například .libPaths() k zobrazení aktuální cesty knihovny.

2. Vytvořte připojovací řetězec pro SQL Server a uložte ho do proměnné R connStr.

   Zástupný symbol "your_server_name" musíte změnit na platný název instance SQL Serveru. V případě názvu serveru můžete použít pouze název instance, nebo možná budete muset plně kvalifikovat název v závislosti na vaší síti.

   Pro ověřování SQL Serveru je syntaxe připojení následující:

   connStr <- "Driver=SQL Server;Server=your_server_name;Database=nyctaxi_sample;Uid=your-sql-login;Pwd=your-login-password"
   

   U ověřování systému Windows se syntaxe trochu liší:

   connStr <- "Driver=SQL Server;Server=your_server_name;Database=nyctaxi_sample;Trusted_Connection=True"
   

   Obecně doporučujeme používat ověřování systému Windows, pokud je to možné, abyste se vyhnuli ukládání hesel v kódu R.

3. Definujte proměnné, které se mají použít při vytváření nového výpočetního kontextu. Po vytvoření objektu výpočetního kontextu ho můžete použít ke spuštění kódu R v instanci SQL Serveru.

   sqlShareDir <- paste("C:\\AllShare\\",Sys.getenv("USERNAME"),sep="")
   sqlWait <- TRUE
   sqlConsoleOutput <- FALSE
   

   • R používá dočasný adresář při serializaci objektů R mezi pracovní stanicí a počítačem s SQL Serverem. Můžete zadat místní adresář, který se používá jako sqlShareDir, nebo přijmout výchozí.

   • Pomocí sqlWait určete, jestli má R čekat na výsledky ze serveru. Diskuzi o čekání versus nečekaných úloh najdete v tématu Distribuované a paralelní výpočty s RevoScaleR v Microsoft R.

   • Pomocí argumentu sqlConsoleOutput označte, že nechcete zobrazit výstup z konzoly R.

4. Zavoláte RxInSqlServer konstruktor pro vytvoření objektu výpočetního kontextu s již definovanými proměnnými a připojovacími řetězci a uložíte nový objekt do proměnné R sqlcc.

   sqlcc <- RxInSqlServer(connectionString = connStr, shareDir = sqlShareDir, wait = sqlWait, consoleOutput = sqlConsoleOutput)
   

5. Ve výchozím nastavení je výpočetní kontext místní, takže musíte explicitně nastavit aktivní výpočetní kontext.

   rxSetComputeContext(sqlcc)
   

   • rxSetComputeContext vrátí dříve aktivní výpočetní kontext neviditelně, abyste ho mohli použít.
   • rxGetComputeContext vrátí aktivní výpočetní kontext.

   Všimněte si, že nastavení výpočetního kontextu má vliv pouze na operace, které používají funkce v balíčku RevoScaleR ; výpočetní kontext nemá vliv na způsob provádění opensourcových operací R.

Vytvoření zdroje dat pomocí RxSqlServeru

Při použití knihoven Microsoft R, jako jsou RevoScaleR a MicrosoftML, je zdrojem dat objekt, který vytvoříte pomocí funkcí RevoScaleR. Objekt zdroje dat určuje sadu dat, která chcete použít pro úlohu, jako je trénování modelu nebo extrakce funkcí. Můžete získat data z různých zdrojů, včetně SQL Serveru. Seznam aktuálně podporovaných zdrojů najdete v tématu RxDataSource.

Dříve jste definovali připojovací řetězec a uložili jste informace do proměnné R. Pomocí informací o připojení můžete zadat data, která chcete získat.

1. Uložte dotaz SQL jako řetězcovou proměnnou. Dotaz definuje data pro trénování modelu.

   sampleDataQuery <- "SELECT TOP 1000 tipped, fare_amount, passenger_count,trip_time_in_secs,trip_distance, pickup_datetime, dropoff_datetime, pickup_longitude, pickup_latitude, dropoff_longitude, dropoff_latitude FROM nyctaxi_sample"
   

   Použili jsme klauzuli TOP k tomu, aby věci běžely rychleji, ale konkrétní řádky vrácené dotazem se mohou lišit v závislosti na pořadí položek. Proto se souhrnné výsledky můžou lišit i od níže uvedených výsledků. Klidně odstraňte klauzuli TOP.

2. Předejte definici dotazu jako argument funkci RxSqlServerData .

   inDataSource <- RxSqlServerData(
     sqlQuery = sampleDataQuery,
     connectionString = connStr,
     colClasses = c(pickup_longitude = "numeric", pickup_latitude = "numeric",
     dropoff_longitude = "numeric", dropoff_latitude = "numeric"),
     rowsPerRead=500
     )
   

   • Argument colClasses určuje typy sloupců, které se mají použít při přesouvání dat mezi SQL Serverem a R. To je důležité, protože SQL Server používá různé datové typy než R a více datových typů. Další informace najdete v tématu Knihovny jazyka R a datové typy.

   • Argument rowsPerRead je důležitý pro správu využití paměti a efektivních výpočtů. Většina vylepšených analytických funkcí ve službách R Services (In-Database) zpracovává data v blocích a shromažďuje přechodné výsledky a vrací konečné výpočty po přečtení všech dat. Přidáním parametru rowsPerRead můžete řídit, kolik řádků dat se čte do každého bloku pro zpracování. Pokud je hodnota tohoto parametru příliš velká, může být přístup k datům pomalý, protože nemáte dostatek paměti pro efektivní zpracování takového velkého bloku dat. V některých systémech může nastavení řádkůPerRead na příliš malou hodnotu také poskytovat pomalejší výkon.

3. V tuto chvíli jste vytvořili objekt inDataSource , ale neobsahuje žádná data. Data se nepřečtou z dotazu SQL do místního prostředí, dokud nespustíte funkci, jako je rxImport nebo rxSummary.

   Teď ale, když jste definovali datové objekty, můžete je použít jako argument pro jiné funkce.

Použití dat SQL Serveru v souhrnech jazyka R

V této části si vyzkoušíte několik funkcí poskytovaných ve službách R Services (In-Database), které podporují vzdálené výpočetní kontexty. Použitím funkcí jazyka R na zdroj dat můžete zkoumat, sumarizovat a grafovat data SQL Serveru.

1. Voláním funkce rxGetVarInfo získáte seznam proměnných ve zdroji dat a jejich datových typech.

   rxGetVarInfo je praktická funkce; Můžete ho volat na libovolný datový rámec nebo sadu dat ve vzdáleném datovém objektu, abyste získali informace, jako jsou maximum a minimální hodnoty, datový typ a počet úrovní ve sloupcích faktoru.

   Zvažte spuštění této funkce po jakémkoli druhu vstupu dat, transformace funkcí nebo přípravy funkcí. Tím zajistíte, že všechny funkce, které chcete použít v modelu, jsou očekávaného datového typu a vyhnout se chybám.

   rxGetVarInfo(data = inDataSource)
   

   výsledky

   Var 1: tipped, Type: integer
   Var 2: fare_amount, Type: numeric
   Var 3: passenger_count, Type: integer
   Var 4: trip_time_in_secs, Type: numeric, Storage: int64
   Var 5: trip_distance, Type: numeric
   Var 6: pickup_datetime, Type: character
   Var 7: dropoff_datetime, Type: character
   Var 8: pickup_longitude, Type: numeric
   Var 9: pickup_latitude, Type: numeric
   Var 10: dropoff_longitude, Type: numeric
   

2. Teď zavolejte funkci RevoScaleR rxSummary , abyste získali podrobnější statistiku o jednotlivých proměnných.

   rxSummary je založen na funkci R summary , ale má některé další funkce a výhody. rxSummary funguje v několika výpočetních kontextech a podporuje vytváření bloků dat. RxSummary můžete také použít k transformaci hodnot nebo k sumarizaci na základě úrovní faktoru.

   V tomto příkladu shrnete částku jízdného na základě počtu cestujících.

   start.time <- proc.time()
   rxSummary(~fare_amount:F(passenger_count,1,6), data = inDataSource)
   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 summarize the inDataSource.", sep=""))
   

   • První argument rxSummary určuje vzorec nebo termín, podle které se má sumarizovat. F() Funkce se zde používá k převodu hodnot v passenger_count na faktory před shrnutím. Musíte také zadat minimální hodnotu (1) a maximální hodnotu (6) pro proměnnou passenger_count faktoru.
   • Pokud nezadáte statistiky pro výstup, ve výchozím nastavení rxSummary vypíše průměr, směrodatnou odchylku, minimum, maximum a počet platných a chybějících pozorování.
   • Tento příklad obsahuje také kód ke sledování času spuštění a dokončení funkce, abyste mohli porovnat výkon.

   výsledky

   Pokud se funkce rxSummary úspěšně spustí, měli byste vidět podobné výsledky, za kterými následuje seznam statistik podle kategorie.

   rxSummary(formula = ~fare_amount:F(passenger_count, 1,6), data = inDataSource)
   Data: inDataSource (RxSqlServerData Data Source)
   Number of valid observations: 1000
   

Bonusové cvičení o big data

Zkuste definovat nový řetězec dotazu se všemi řádky. Pro tento experiment doporučujeme nastavit nový objekt zdroje dat. Můžete také zkusit změnit parametr rowsToRead a zjistit, jak ovlivňuje propustnost.

bigDataQuery  <- "SELECT tipped, fare_amount, passenger_count,trip_time_in_secs,trip_distance, pickup_datetime, dropoff_datetime, pickup_longitude, pickup_latitude, dropoff_longitude, dropoff_latitude FROM nyctaxi_sample"

bigDataSource <- RxSqlServerData(
      sqlQuery = bigDataQuery,
      connectionString = connStr,
      colClasses = c(pickup_longitude = "numeric", pickup_latitude = "numeric",
      dropoff_longitude = "numeric", dropoff_latitude = "numeric"),
      rowsPerRead=500
      )

start.time <- proc.time()
rxSummary(~fare_amount:F(passenger_count,1,6), data = bigDataSource)
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 summarize the inDataSource.", sep=""))

Návod

Když je to spuštěné, můžete pomocí nástroje, jako je Průzkumník procesů nebo SQL Profiler, zjistit, jak se připojení vytvoří a kód R se spouští pomocí služeb SQL Serveru.

Další kroky

Vytváření grafů a diagramů pomocí jazyka R