SQL Server-gegevens weergeven en samenvatten met behulp van R (overzicht)

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

In deze les maakt u kennis met functies in het RevoScaleR-pakket en doorloopt u de volgende taken:

Een SQL Server-rekencontext definiëren

Voer de volgende R-instructies uit in een R-omgeving op het clientwerkstation. In deze sectie wordt uitgegaan van een data science-werkstation met Microsoft R Client, omdat deze alle RevoScaleR-pakketten bevat, evenals een eenvoudige, lichtgewicht set R-hulpprogramma's. U kunt bijvoorbeeld Rgui.exe gebruiken om het R-script in deze sectie uit te voeren.

1. Als het RevoScaleR-pakket nog niet is geladen, voert u deze regel R-code uit:

   library("RevoScaleR")
   

   De aanhalingstekens zijn optioneel, in dit geval wel aanbevolen.

   Als er een fout optreedt, moet u ervoor zorgen dat uw R-ontwikkelomgeving een bibliotheek gebruikt die het RevoScaleR-pakket bevat. Gebruik een opdracht zoals .libPaths() om het huidige bibliotheekpad weer te geven.

2. Maak de verbindingsreeks voor SQL Server en sla deze op in een R-variabele, connStr.

   U moet de tijdelijke aanduiding 'your_server_name' wijzigen naar de naam van een geldige SQL Server-exemplaar. Voor de servernaam kunt u mogelijk alleen de exemplaarnaam gebruiken of moet u de naam volledig kwalificeren, afhankelijk van uw netwerk.

   Voor SQL Server-verificatie is de verbindingssyntaxis als volgt:

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

   Voor Windows-verificatie is de syntaxis iets anders:

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

   Over het algemeen raden we u aan waar mogelijk Windows-authenticatie te gebruiken om te voorkomen dat wachtwoorden in uw R-code worden opgeslagen.

3. Definieer variabelen die moeten worden gebruikt bij het maken van een nieuwe rekencontext. Nadat u het rekencontextobject hebt gemaakt, kunt u dit gebruiken om R-code uit te voeren op het SQL Server-exemplaar.

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

   • R gebruikt een tijdelijke map bij het serialiseren van R-objecten tussen uw werkstation en de SQL Server-computer. U kunt de lokale map opgeven die wordt gebruikt als sqlShareDir of de standaardwaarde accepteren.

   • Gebruik sqlWait om aan te geven of R moet wachten op resultaten van de server. Zie Gedistribueerde en parallelle computing met RevoScaleR in Microsoft R voor een bespreking van wachtende versus niet-wachtende taken.

   • Gebruik het argument sqlConsoleOutput om aan te geven dat u geen uitvoer van de R-console wilt zien.

4. U roept de RxInSqlServer-constructor aan om het rekencontextobject te maken met de variabelen en verbindingsreeksen die al zijn gedefinieerd en slaat het nieuwe object op in de R-variabele SQLCC.

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

5. De rekencontext is standaard lokaal, dus u moet expliciet de actieve rekencontext instellen.

   rxSetComputeContext(sqlcc)
   

   • rxSetComputeContext retourneert de eerder actieve rekencontext onzichtbaar, zodat u deze kunt gebruiken
   • rxGetComputeContext retourneert de actieve rekencontext

   Houd er rekening mee dat het instellen van een rekencontext alleen van invloed is op bewerkingen die gebruikmaken van functies in het RevoScaleR-pakket ; de rekencontext heeft geen invloed op de manier waarop opensource R-bewerkingen worden uitgevoerd.

Een gegevensbron maken met RxSqlServer

Wanneer u de Microsoft R-bibliotheken zoals RevoScaleR en MicrosoftML gebruikt, is een gegevensbron een object dat u maakt met behulp van RevoScaleR-functies. Het gegevensbronobject geeft een aantal set gegevens op die u wilt gebruiken voor een taak, zoals modeltraining of functieextractie. U kunt gegevens ophalen uit verschillende bronnen, waaronder SQL Server. Zie RxDataSource voor de lijst met momenteel ondersteunde bronnen.

Eerder hebt u een verbindingsreeks gedefinieerd en die informatie opgeslagen in een R-variabele. U kunt deze verbindingsgegevens opnieuw gebruiken om de gegevens op te geven die u wilt ophalen.

1. Sla een SQL-query op als een tekenreeksvariabele. De query definieert de gegevens voor het trainen van het model.

   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"
   

   We hebben hier een TOP-component gebruikt om dingen sneller uit te voeren, maar de werkelijke rijen die door de query worden geretourneerd, kunnen variëren, afhankelijk van de volgorde. Uw samenvattingsresultaten kunnen daarom ook afwijken van de onderstaande resultaten. U kunt de TOP-component gerust verwijderen.

2. Geef de querydefinitie door als argument aan de functie RxSqlServerData .

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

   • Het argument colClasses geeft de kolomtypen op die moeten worden gebruikt bij het verplaatsen van de gegevens tussen SQL Server en R. Dit is belangrijk omdat SQL Server verschillende gegevenstypen gebruikt dan R en meer gegevenstypen. Zie R-bibliotheken en -gegevenstypen voor meer informatie.

   • Het argument rowsPerRead is belangrijk voor het beheren van geheugengebruik en efficiënte berekeningen. De meeste verbeterde analytische functies in R Services (In-Database) verwerken gegevens in segmenten en verzamelen tussenliggende resultaten, waarbij de uiteindelijke berekeningen worden geretourneerd nadat alle gegevens zijn gelezen. Door de parameter rowsPerRead toe te voegen, kunt u bepalen hoeveel rijen gegevens in elk segment worden gelezen voor verwerking. Als de waarde van deze parameter te groot is, kan de gegevenstoegang traag zijn omdat u onvoldoende geheugen hebt om zo'n groot deel gegevens efficiënt te verwerken. Op sommige systemen kan het instellen van rowsPerRead op een overmatig kleine waarde ook tragere prestaties bieden.

3. Op dit moment hebt u het inDataSource-object gemaakt, maar het bevat geen gegevens. De gegevens worden pas opgehaald uit de SQL-query in de lokale omgeving als u een functie zoals rxImport of rxSummary uitvoert.

   Nu u echter de gegevensobjecten hebt gedefinieerd, kunt u deze gebruiken als argument voor andere functies.

De SQL Server-gegevens gebruiken in R-samenvattingen

In deze sectie gaat u verschillende functies uitproberen die worden geleverd in R Services (In-Database) die ondersteuning bieden voor externe rekencontexten. Door R-functies toe te passen op de gegevensbron, kunt u de SQL Server-gegevens verkennen, samenvatten en weergeven.

1. Roep de functie rxGetVarInfo aan om een lijst met de variabelen in de gegevensbron en hun gegevenstypen op te halen.

   rxGetVarInfo is een handige functie; u kunt deze aanroepen in elk gegevensframe of op een set gegevens in een extern gegevensobject om informatie op te halen, zoals de maximum- en minimumwaarden, het gegevenstype en het aantal niveaus in factorkolommen.

   Overweeg deze functie uit te voeren na elk soort gegevensinvoer, functietransformatie of functie-engineering. Als u dit doet, kunt u ervoor zorgen dat alle functies die u in uw model wilt gebruiken, van het verwachte gegevenstype zijn en fouten voorkomen.

   rxGetVarInfo(data = inDataSource)
   

   resultaten

   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. Roep nu de RevoScaleR-functie rxSummary aan om gedetailleerdere statistieken over afzonderlijke variabelen te krijgen.

   rxSummary is gebaseerd op de R-functie summary , maar heeft enkele extra functies en voordelen. rxSummary werkt in meerdere rekencontexten en ondersteunt segmentering. U kunt rxSummary ook gebruiken om waarden te transformeren of samen te vatten op basis van factorniveaus.

   In dit voorbeeld geeft u een overzicht van het tariefbedrag op basis van het aantal passagiers.

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

   • Het eerste argument voor rxSummary geeft de formule of term aan waarop moet worden samengevat. Hier wordt de F() functie gebruikt om de waarden in passenger_count te converteren naar factoren voordat deze worden samengevat. U moet ook de minimumwaarde (1) en de maximumwaarde (6) voor de variabele passenger_count-factor opgeven.
   • Als u de statistieken voor uitvoer niet opgeeft, geeft u standaard rxSummary-uitvoer Gemiddelde, StDev, Min, Max en het aantal geldige en ontbrekende waarnemingen op.
   • Dit voorbeeld bevat ook code om de tijd bij te houden waarop de functie wordt gestart en voltooid, zodat u de prestaties kunt vergelijken.

   resultaten

   Als de rxSummary-functie succesvol wordt uitgevoerd, ziet u resultaten zoals deze, gevolgd door een lijst met statistieken per categorie.

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

Bonusoefening over big data

Probeer een nieuwe querytekenreeks te definiëren met alle rijen. U wordt aangeraden een nieuw gegevensbronobject in te stellen voor dit experiment. U kunt ook proberen de parameter rowsToRead te wijzigen om te zien hoe dit van invloed is op de doorvoer.

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

Volgende stappen