SQL Server-adatok megtekintése és összegzése az R használatával (útmutató)

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

Ez a lecke bemutatja a RevoScaleR csomag funkcióit, és az alábbi feladatok elvégzését mutatja be:

• Csatlakozás az SQL Serverhez
• Adjon meg egy lekérdezést, amely tartalmazza a szükséges adatokat, vagy adjon meg egy táblát vagy nézetet
• Egy vagy több számítási környezet definiálása R-kód futtatásakor
• Igény szerint definiáljon olyan átalakításokat, amelyek az adatforrásra vonatkoznak, miközben a forrásból olvassák

SQL Server számítási környezet definiálása

Futtassa az alábbi R-utasításokat egy R-környezetben az ügyfél-munkaállomáson. Ez a szakasz a Microsoft R-ügyféllel rendelkező adatelemzési munkaállomást feltételezi, mivel tartalmazza az összes RevoScaleR-csomagot, valamint az R-eszközök alapszintű, egyszerűsített készletét. Például a Rgui.exe segítségével lefuttathatja az R-szkriptet ebben a szakaszban.

1. Ha a RevoScaleR-csomag még nincs betöltve, futtassa ezt az R-kódsort:

   library("RevoScaleR")
   

   Az idézőjelek megadása nem kötelező, ebben az esetben azonban ajánlott.

   Ha hibaüzenetet kap, győződjön meg arról, hogy az R-fejlesztési környezet olyan kódtárat használ, amely tartalmazza a RevoScaleR-csomagot. Használjon egy parancsot, például .libPaths() az aktuális tár elérési útjának megnézéséhez.

2. Hozza létre az SQL Server kapcsolati sztringét, és mentse egy R-változóba, a connStr-be.

   A "your_server_name" helyőrzőt érvényes SQL Server-példánynévre kell módosítania. A szerver nevének esetében előfordulhat, hogy csak a példány nevét tudja használni, vagy lehet, hogy a hálózattól függően a nevet teljes mértékben ki kell egészíteni.

   Sql Server-hitelesítés esetén a kapcsolat szintaxisa a következő:

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

   Windows-hitelesítés esetén a szintaxis kissé eltérő:

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

   Általában azt javasoljuk, hogy ahol csak lehetséges, használja a Windows-hitelesítést, hogy elkerülje a jelszavak mentését az R-kódban.

3. Definiálja az új számítási környezet létrehozásához használandó változókat. A számítási környezet objektumának létrehozása után R-kódot futtathat az SQL Server-példányon.

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

   • Az R ideiglenes könyvtárat használ az R-objektumok oda-vissza történő szerializálásához a munkaállomás és az SQL Server-számítógép között. Megadhatja az sqlShareDirként használt helyi könyvtárat, vagy elfogadhatja az alapértelmezettet.

   • Az sqlWait használatával jelezheti, hogy az R vár-e a kiszolgáló eredményeire. A várakozási és a nem várakozási feladatokról a Microsoft R RevoScaleR-beli elosztott és párhuzamos számítástechnika című témakörében olvashat.

   • Az sqlConsoleOutput argumentum használatával jelezheti, hogy nem szeretné látni az R-konzol kimenetét.

4. Meghívja az RxInSqlServer konstruktort, hogy hozza létre a számítási környezet objektumát a már definiált változókkal és kapcsolati sztringekkel, és mentse az új objektumot az sqlcc R változóban.

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

5. Alapértelmezés szerint a számítási környezet helyi, ezért explicit módon kell beállítania az aktív számítási környezetet.

   rxSetComputeContext(sqlcc)
   

   • Az rxSetComputeContext láthatatlanul adja vissza a korábban aktív számítási környezetet, hogy tudja használni.
   • Az rxGetComputeContext az aktív számítási környezetet adja vissza

   Vegye figyelembe, hogy a számítási környezet beállítása csak a RevoScaleR-csomagban függvényeket használó műveleteket érinti; a számítási környezet nem befolyásolja a nyílt forráskódú R-műveletek végrehajtását.

Adatforrás létrehozása az RxSqlServer használatával

A Microsoft R-kódtárak, például a RevoScaleR és a MicrosoftML használatakor az adatforrás olyan objektum, amelyet RevoScaleR-függvényekkel hoz létre. Az adatforrás-objektum olyan adathalmazt határoz meg, amelyet egy tevékenységhez szeretne használni, például modellbetanítást vagy funkciókinyeréseket. Az adatokat többféle forrásból is lekérheti, beleértve az SQL Servert is. A jelenleg támogatott források listájáért lásd az RxDataSource-t.

Korábban definiált egy kapcsolati sztringet, és ezt az információt egy R-változóba mentette. Ezeket a kapcsolati adatokat újra felhasználhatja a lekérni kívánt adatok megadásához.

1. SQL-lekérdezés mentése sztringváltozóként. A lekérdezés meghatározza a modell betanításához szükséges adatokat.

   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"
   

   Itt egy TOP záradékot használtunk a dolgok gyorsabb futtatásához, de a lekérdezés által visszaadott tényleges sorok sorrendtől függően változhatnak. Ezért az összefoglaló eredmények is eltérhetnek az alább felsoroltaktól. Nyugodtan távolítsa el a TOP záradékot.

2. Adja át a lekérdezésdefiníciót argumentumként az RxSqlServerData függvénynek.

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

   • A colClasses argumentum megadja azokat az oszloptípusokat, amelyeket az adatok SQL Server és R közötti áthelyezésekor használni kell. Ez azért fontos, mert az SQL Server más adattípusokat használ, mint az R, és több adattípust. További információ: R-kódtárak és adattípusok.

   • APerRead argumentumsorok fontosak a memóriahasználat és a hatékony számítások kezeléséhez. Az R Services továbbfejlesztett elemzési függvényei (In-Database) adattömbökben dolgozzák fel az adatokat, és köztes eredményeket halmoznak fel, és az összes adat beolvasása után visszaadják a végső számításokat. A sorokPerRead paraméterének hozzáadásával szabályozhatja, hogy hány adatsort olvas be az egyes adattömbbe feldolgozás céljából. Ha a paraméter értéke túl nagy, az adathozzáférés lassú lehet, mert nincs elegendő memória egy ilyen nagy adattömb hatékony feldolgozásához. Egyes rendszerek esetében a sorokPerRead értékének túlzottan kis értékre állítása is lassabb teljesítményt biztosíthat.

3. Ezen a ponton létrehozta az inDataSource objektumot, de nem tartalmaz adatokat. A rendszer nem kéri le az adatokat az SQL-lekérdezésből a helyi környezetbe, amíg nem futtat egy olyan függvényt, mint az rxImport vagy az rxSummary.

   Most azonban, hogy definiálta az adatobjektumokat, használhatja argumentumként más függvények számára.

Az SQL Server-adatok használata R-összefoglalókban

Ebben a szakaszban a távoli számítási környezeteket támogató R Servicesben (In-Database) található függvények közül több is kipróbálható. Az R-függvények adatforrásra való alkalmazásával feltárhatja, összegzheti és diagramba foglalhatja az SQL Server-adatokat.

1. Hívja meg az rxGetVarInfo függvényt az adatforrás változóinak és adattípusainak listájának lekéréséhez.

   Az rxGetVarInfo egy hasznos függvény; Bármilyen adatkereten vagy távoli adatobjektumban lévő adathalmazon meghívhatja, hogy olyan információkat kapjon, mint a maximális és minimális értékek, az adattípus és a faktoroszlopok szintjeinek száma.

   Érdemes lehet bármilyen adatbemenet, funkcióátalakítás vagy szolgáltatásfejlesztés után futtatni ezt a függvényt. Ezzel biztosíthatja, hogy a modellben használni kívánt összes funkció a várt adattípus legyen, és elkerülje a hibákat.

   rxGetVarInfo(data = inDataSource)
   

   Results (Eredmények)

   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. Most hívja meg az rxSummary RevoScaleR függvényt, hogy részletesebb statisztikákat kapjon az egyes változókról.

   Az rxSummary az R summary függvényen alapul, de további funkciókkal és előnyökkel rendelkezik. Az rxSummary több számítási környezetben működik, és támogatja az adattömböket. Az rxSummary használatával is átalakíthatja az értékeket, vagy összegzheti a faktorszintek alapján.

   Ebben a példában az utasok száma alapján összegzi a viteldíj összegét.

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

   • Az rxSummary első argumentuma az összegzendő képletet vagy kifejezést adja meg. Itt a függvény a F()passenger_count értékeit tényezőkké alakítja az összegzés előtt. Meg kell adnia a passenger_count tényezőváltozó minimális értékét (1) és maximális értékét (6).
   • Ha nem adja meg a kimenetre vonatkozó statisztikákat, alapértelmezés szerint az rxSummary kimenetek Középérték, StDev, Min, Max, valamint az érvényes és hiányzó megfigyelések száma.
   • Ez a példa tartalmaz néhány kódot is a függvény indításának és befejezésének nyomon követéséhez, hogy összehasonlíthassa a teljesítményt.

   Results (Eredmények)

   Ha az rxSummary függvény sikeresen fut, az alábbihoz hasonló eredményeket kell látnia, majd a statisztikák kategóriánkénti listáját.

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

Bónusz gyakorlat a nagy adatok témájában

Adjon meg egy új lekérdezési sztringet az összes sorhoz. Javasoljuk, hogy állítson be egy új adatforrás-objektumot ehhez a kísérlethez. Megpróbálhatja módosítani a rowsToRead paramétert is, hogy lássa, hogyan befolyásolja az átviteli sebességet.

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

Jótanács

Amíg ez fut, használhat egy olyan eszközt, mint a Process Explorer vagy az SQL Profiler, hogy lássa, hogyan jön létre a kapcsolat, és az R-kód az SQL Server-szolgáltatások használatával fut.

Következő lépések

Grafikonok és diagramok készítése az R használatával