Visa och sammanfatta SQL Server-data med R (genomgång)

gäller för: SQL Server 2016 (13.x) och senare versioner

Den här lektionen beskriver funktionerna i RevoScaleR-paketet och vägleder dig genom följande uppgifter:

Definiera en SQL Server-beräkningskontext

Kör följande R-instruktioner i en R-miljö på klientarbetsstationen. I det här avsnittet förutsätts en datavetenskaplig arbetsstation med Microsoft R Client, eftersom den innehåller alla RevoScaleR-paket samt en grundläggande, enkel uppsättning R-verktyg. Du kan till exempel använda Rgui.exe för att köra R-skriptet i det här avsnittet.

1. Om RevoScaleR-paketet inte redan har lästs in kör du den här R-kodraden:

   library("RevoScaleR")
   

   Citattecknen är valfria, i det här fallet, men rekommenderas.

   Om du får ett fel kontrollerar du att R-utvecklingsmiljön använder ett bibliotek som innehåller RevoScaleR-paketet. Använd ett kommando som .libPaths() för att visa den aktuella bibliotekssökvägen.

2. Skapa anslutningssträngen för SQL Server och spara den i en R-variabel, connStr.

   Du måste ändra platshållaren "your_server_name" till ett giltigt SQL Server-instansnamn. För servernamnet kanske du bara kan använda instansnamnet, eller så kan du behöva kvalificera namnet fullständigt, beroende på ditt nätverk.

   För SQL Server-autentisering är anslutningssyntaxen följande:

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

   För Windows-autentisering är syntaxen lite annorlunda:

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

   I allmänhet rekommenderar vi att du använder Windows-autentisering där det är möjligt för att undvika att spara lösenord i R-koden.

3. Definiera variabler som ska användas för att skapa en ny beräkningskontext. När du har skapat beräkningskontextobjektet kan du använda det för att köra R-kod på SQL Server-instansen.

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

   • R använder en tillfällig katalog när R-objekt serialiseras fram och tillbaka mellan din arbetsstation och SQL Server-datorn. Du kan ange den lokala katalog som används som sqlShareDir eller acceptera standardvärdet.

   • Använd sqlWait för att ange om du vill att R ska vänta på resultat från servern. En diskussion om väntande jobb jämfört med jobb som inte väntar finns i Distribuerad och parallell databehandling med RevoScaleR i Microsoft R.

   • Använd argumentet sqlConsoleOutput för att ange att du inte vill se utdata från R-konsolen.

4. Du anropar konstruktorn RxInSqlServer för att skapa beräkningskontextobjektet med de variabler och anslutningssträngar som redan har definierats och sparar det nya objektet i R-variabeln sqlcc.

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

5. Som standard är beräkningskontexten lokal, så du måste uttryckligen ange den aktiva beräkningskontexten.

   rxSetComputeContext(sqlcc)
   

   • rxSetComputeContext returnerar den tidigare aktiva beräkningskontexten osynligt så att du kan använda den
   • rxGetComputeContext returnerar den aktiva beräkningskontexten

   Observera att inställningen av en beräkningskontext endast påverkar åtgärder som använder funktioner i RevoScaleR-paketet . beräkningskontexten påverkar inte hur R-åtgärder med öppen källkod utförs.

Skapa en datakälla med RxSqlServer

När du använder Microsoft R-bibliotek som RevoScaleR och MicrosoftML är en datakälla ett objekt som du skapar med hjälp av RevoScaleR-funktioner. Datakällans objekt anger en uppsättning data som du vill använda för en uppgift, till exempel modellträning eller extrahering av funktioner. Du kan hämta data från en mängd olika källor, inklusive SQL Server. Listan över källor som stöds finns i RxDataSource.

Tidigare definierade du en anslutningssträng och sparade informationen i en R-variabel. Du kan återanvända anslutningsinformationen för att ange de data som du vill hämta.

1. Spara en SQL-fråga som en strängvariabel. Frågan definierar data för att träna modellen.

   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"
   

   Vi har använt en TOP-sats här för att få saker att köras snabbare, men de faktiska rader som returneras av frågan kan variera beroende på ordning. Sammanfattningsresultaten kan därför också skilja sig från dem som anges nedan. Ta bort TOP-satsen.

2. Skicka frågedefinitionen som ett argument till funktionen RxSqlServerData .

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

   • Argumentet colClasses anger de kolumntyper som ska användas när du flyttar data mellan SQL Server och R. Detta är viktigt eftersom SQL Server använder olika datatyper än R och fler datatyper. Mer information finns i R-bibliotek och datatyper.

   • Argumentet rowsPerRead är viktigt för att hantera minnesanvändning och effektiva beräkningar. De flesta av de förbättrade analysfunktionerna i R Services (In-Database) bearbetar data i segment och ackumulerar mellanliggande resultat och returnerar de slutliga beräkningarna när alla data har lästs. Genom att lägga till parametern rowsPerRead kan du styra hur många rader med data som läss in i varje segment för bearbetning. Om värdet för den här parametern är för stort kan dataåtkomsten vara långsam eftersom du inte har tillräckligt med minne för att effektivt bearbeta ett så stort datasegment. I vissa system kan inställningen av raderPerRead till ett alltför litet värde också ge långsammare prestanda.

3. Nu har du skapat inDataSource-objektet , men det innehåller inga data. Data hämtas inte från SQL-frågan till den lokala miljön förrän du kör en funktion som rxImport eller rxSummary.

   Men nu när du har definierat dataobjekten kan du använda det som argument för andra funktioner.

Använda SQL Server-data i R-sammanfattningar

I det här avsnittet ska du prova flera av funktionerna i R Services (In-Database) som stöder fjärrberäkningskontexter. Genom att använda R-funktioner på datakällan kan du utforska, sammanfatta och kartlägga SQL Server-data.

1. Anropa funktionen rxGetVarInfo för att hämta en lista över variablerna i datakällan och deras datatyper.

   rxGetVarInfo är en praktisk funktion; Du kan anropa den på valfri dataram, eller på en uppsättning data i ett fjärrdataobjekt, för att hämta information som högsta och lägsta värden, datatypen och antalet nivåer i faktorkolumner.

   Överväg att köra den här funktionen efter någon form av datainmatning, funktionstransformering eller funktionsutveckling. Genom att göra det kan du se till att alla funktioner som du vill använda i din modell är av den förväntade datatypen och undviker fel.

   rxGetVarInfo(data = inDataSource)
   

   Resultat

   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. Anropa nu funktionen RevoScaleR rxSummary för att få mer detaljerad statistik om enskilda variabler.

   rxSummary baseras på R-funktionen summary , men har några ytterligare funktioner och fördelar. rxSummary fungerar i flera beräkningskontexter och stöder segmentering. Du kan också använda rxSummary för att transformera värden eller sammanfatta baserat på faktornivåer.

   I det här exemplet sammanfattar du prisbeloppet baserat på antalet passagerare.

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

   • Det första argumentet till rxSummary anger formeln eller termen som ska sammanfattas av. F() Här används funktionen för att konvertera värdena i passenger_count till faktorer innan du sammanfattar. Du måste också ange det lägsta värdet (1) och det maximala värdet (6) för passenger_count faktorvariabeln.
   • Om du inte anger vilken statistik som ska genereras, kommer rxSummary som standard att generera medelvärde, standardavvikelse, min, max och antalet giltiga och saknade observationer.
   • Det här exemplet innehåller även kod för att spåra den tid då funktionen startar och slutförs, så att du kan jämföra prestanda.

   Resultat

   Om funktionen rxSummary körs korrekt bör du se resultat som dessa, följt av en lista med statistik efter kategori.

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

Bonusövning på stordata

Prova att definiera en ny frågesträng med alla rader. Vi rekommenderar att du konfigurerar ett nytt datakällobjekt för det här experimentet. Du kan också försöka ändra parametern rowsToRead för att se hur den påverkar dataflödet.

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ästa steg