R-handledning: Utforska och visualisera data

Gäller för: SQL Server 2016 (13.x) och senare versioner Azure SQL Managed Instance

I del två i den här självstudieserien i fem delar utforskar du exempeldata och genererar några diagram. Senare får du lära dig hur du serialiserar grafikobjekt i Python och sedan deserialiserar dessa objekt och skapar diagram.

I del två av den här självstudieserien i fem delar granskar du exempeldata och genererar sedan några diagram med hjälp av de allmänna barplot funktionerna och hist funktionerna i bas-R.

Ett viktigt mål i den här artikeln är att visa hur du anropar R-funktioner från Transact-SQL i lagrade procedurer och sparar resultatet i programfilformat:

• Skapa en lagrad procedur med barplot för att generera ett R-diagram som varbinary-data. Använd bcp för att exportera binärströmmen till en bildfil.
• Skapa en lagrad procedur med hjälp av hist för att generera ett diagram och spara resultat som JPG- och PDF-utdata.

I den här artikeln ska du:

I del ett installerade du förhandskraven och återställde exempeldatabasen.

I del tre får du lära dig hur du skapar funktioner från rådata med hjälp av en Transact-SQL funktion. Sedan anropar du funktionen från en lagrad procedur för att skapa en tabell som innehåller funktionsvärdena.

I del fyra läser du in modulerna och anropar nödvändiga funktioner för att skapa och träna modellen med hjälp av en lagrad SQL Server-procedur.

I del fem får du lära dig hur du operationaliserar de modeller som du har tränat och sparat i del fyra.

Granska datan

Att utveckla en datavetenskapslösning omfattar vanligtvis intensiv datautforskning och datavisualisering. Så ta en minut att granska exempeldata, om du inte redan har gjort det.

I den ursprungliga offentliga datamängden tillhandahölls taxiidentifierare och reseposter i separata filer. Men för att göra exempeldata enklare att använda har de två ursprungliga datauppsättningarna kopplats till kolumnerna medallion, hack_license och pickup_datetime. Posterna samplades också för att bara få 1% av det ursprungliga antalet poster. Den ned-samplade resulterande datamängden har 1 703 957 rader och 23 kolumner.

Taxiidentifikatorer

• Kolumnen medallion representerar taxins unika ID-nummer.

• Kolumnen hack_license innehåller taxiförarens körkortsnummer (anonymiserat).

Rese- och taxeposter

• Varje reselogginlägg inkluderar tid och plats för upphämtning och avlämning samt resans avstånd.

• Varje biljettpost innehåller betalningsinformation som betalningstyp, totalt betalningsbelopp och dricksbelopp.

• De tre sista kolumnerna kan användas för olika maskininlärningsuppgifter. Kolumnen tip_amount innehåller kontinuerliga numeriska värden och kan användas som etikettkolumn för regressionsanalys. Den tippade kolumnen har endast ja/nej-värden och används för binär klassificering. Kolumnen tip_class har flera klassetiketter och kan därför användas som etikett för klassificeringsuppgifter med flera klasser.

  Den här genomgången visar endast den binära klassificeringsuppgiften. Du är välkommen att prova att skapa modeller för de andra två maskininlärningsuppgifterna, regression och klassificering i flera klasser.

• De värden som används för etikettkolumnerna baseras alla på kolumnen tip_amount med hjälp av följande affärsregler:

  Härledt kolumnnamn	Regel
  Tippad	Om tip_amount > 0, sätts tippad till 1, annars sätts tippad till 0
  tip_class	Klass 0: tip_amount = $0 Klass 1: tip_amount > $0 och tip_amount <= $5 Klass 2: tip_amount > $5 och tip_amount <= $10 Klass 3: tip_amount > $10 och tip_amount <= $20 Kategori 4: tip_amount > $20

Skapa diagram med R i T-SQL

Om du vill skapa diagrammet använder du R-funktionen barplot. Det här steget ritar ett histogram baserat på data från en Transact-SQL fråga. Du kan omsluta den här funktionen i en lagrad procedur, RPlotHistogram.

1. Högerklicka på databasen NYCTaxi_Sample i Object Explorer i SQL Server Management Studio och välj Ny fråga. Eller i Azure Data Studio väljer du Ny anteckningsbok på arkivmenyn och ansluter till databasen.

2. Klistra in följande skript för att skapa en lagrad procedur som ritar histogrammet. Det här exemplet heter RPlotHistogram.

   CREATE PROCEDURE [dbo].[RPlotHistogram]
   AS
   BEGIN
     SET NOCOUNT ON;
     DECLARE @query nvarchar(max) =  
     N'SELECT tipped FROM [dbo].[nyctaxi_sample]'  
     EXECUTE sp_execute_external_script @language = N'R',  
                                        @script = N'  
      image_file = tempfile();  
      jpeg(filename = image_file);  
      #Plot histogram  
      barplot(table(InputDataSet$tipped), main = "Tip Histogram", col="lightgreen", xlab="Tipped or not", ylab = "Counts", space=0)
      dev.off();  
      OutputDataSet <- data.frame(data=readBin(file(image_file, "rb"), what=raw(), n=1e6));  
      ',  
      @input_data_1 = @query  
      WITH RESULT SETS ((plot varbinary(max)));  
   END
   GO
   

Viktiga punkter att förstå i det här skriptet är följande:

• Variabeln @query definierar frågetexten ('SELECT tipped FROM nyctaxi_sample'), som skickas till R-skriptet som argument till skriptets indatavariabel, @input_data_1. För R-skript som körs som externa processer bör du ha en en-till-en-mappning mellan indata till skriptet och indata till den sp_execute_external_script system lagrade proceduren som startar R-sessionen på SQL Server.

• I R-skriptet definieras en variabel (image_file) för att lagra avbildningen.

• Funktionen barplot anropas för att generera diagrammet.

• R-enheten är inställd på av eftersom du kör det här kommandot som ett externt skript i SQL Server. Vanligtvis i R, när du utfärdar ett ritningskommando på hög nivå, öppnar R ett grafikfönster som kallas för en enhet. Du kan inaktivera enheten om du skriver till en fil eller hanterar utdata på något annat sätt.

• R-grafikobjektet serialiseras till en R-dataram för utdata.

Kör den lagrade proceduren och använd bcp för att exportera binära data till en bildfil

Den lagrade proceduren returnerar bilden som en ström av varbinära data, vilket du uppenbarligen inte kan visa direkt. Du kan dock använda bcp-verktyget för att hämta varbinary-data och spara dem som en avbildningsfil på en klientdator.

1. Kör följande instruktion i Management Studio:

   EXEC [dbo].[RPlotHistogram]
   

   Results

   plotta0xFFD8FFE000104A4649...

2. Öppna en PowerShell-kommandotolk och kör följande kommando och ange lämpligt instansnamn, databasnamn, användarnamn och autentiseringsuppgifter som argument. För dem som använder Windows-identiteter kan du ersätta -U och -P med -T.

   bcp "exec RPlotHistogram" queryout "plot.jpg" -S <SQL Server instance name> -d  NYCTaxi_Sample  -U <user name> -P <password> -T
   

   Anmärkning

   Kommandoväxlar för bcp är skiftlägeskänsliga.

3. Om anslutningen lyckas uppmanas du att ange mer information om det grafiska filformatet.

   Tryck på RETUR vid varje uppmaning för att acceptera standardvärdena, förutom följande ändringar:

   • För prefixlängd för fältdiagram skriver du 0.

   • Skriv Y om du vill spara utdataparametrarna för senare återanvändning.

   Enter the file storage type of field plot [varbinary(max)]: 
   Enter prefix-length of field plot [8]: 0
   Enter length of field plot [0]:
   Enter field terminator [none]:
   
   Do you want to save this format information in a file? [Y/n]
   Host filename [bcp.fmt]:
   

   Results

   Starting copy...
   1 rows copied.
   Network packet size (bytes): 4096
   Clock Time (ms.) Total     : 3922   Average : (0.25 rows per sec.)
   

   Tips/Råd

   Om du sparar formatinformationen i filen (bcp.fmt) genererar bcp-verktyget en formatdefinition som du kan använda för liknande kommandon i framtiden utan att behöva ange alternativ för grafiskt filformat. Om du vill använda formatfilen lägger du till -f bcp.fmt i slutet av en kommandorad efter lösenordsargumentet.

4. Utdatafilen skapas i samma katalog där du körde PowerShell-kommandot. Om du vill visa diagrammet öppnar du bara filen plot.jpg.

   

Skapa en lagrad procedur med hjälp av hist

Dataexperter genererar vanligtvis flera datavisualiseringar för att få insikter om data från olika perspektiv. I det här exemplet skapar du en lagrad procedur med namnet RPlotHist för att skriva histogram, punktdiagram och annan R-grafik för att .JPG och .PDF format.

Den här lagrade proceduren använder hist funktionen för att skapa histogrammet och exporterar binära data till populära format som .JPG, .PDF och .PNG.

1. Högerklicka på databasen NYCTaxi_Sample i Object Explorer i SQL Server Management Studio och välj Ny fråga.

2. Klistra in följande skript för att skapa en lagrad procedur som ritar histogrammet. Det här exemplet heter RPlotHist .

   CREATE PROCEDURE [dbo].[RPlotHist]  
   AS  
   BEGIN  
     SET NOCOUNT ON;  
     DECLARE @query nvarchar(max) =  
     N'SELECT cast(tipped as int) as tipped, tip_amount, fare_amount FROM [dbo].[nyctaxi_sample]'  
     EXECUTE sp_execute_external_script @language = N'R',  
     @script = N'  
      # Set output directory for files and check for existing files with same names   
       mainDir <- ''C:\\temp\\plots''  
       dir.create(mainDir, recursive = TRUE, showWarnings = FALSE)  
       setwd(mainDir);  
       print("Creating output plot files:", quote=FALSE)
   
       # Open a jpeg file and output histogram of tipped variable in that file.  
       dest_filename = tempfile(pattern = ''rHistogram_Tipped_'', tmpdir = mainDir)  
       dest_filename = paste(dest_filename, ''.jpg'',sep="")  
       print(dest_filename, quote=FALSE);  
       jpeg(filename=dest_filename);  
       hist(InputDataSet$tipped, col = ''lightgreen'', xlab=''Tipped'',   
           ylab = ''Counts'', main = ''Histogram, Tipped'');  
        dev.off();  
   
       # Open a pdf file and output histograms of tip amount and fare amount.   
       # Outputs two plots in one row  
       dest_filename = tempfile(pattern = ''rHistograms_Tip_and_Fare_Amount_'', tmpdir = mainDir)  
       dest_filename = paste(dest_filename, ''.pdf'',sep="")  
       print(dest_filename, quote=FALSE);  
       pdf(file=dest_filename, height=4, width=7);  
       par(mfrow=c(1,2));  
       hist(InputDataSet$tip_amount, col = ''lightgreen'',   
           xlab=''Tip amount ($)'',   
           ylab = ''Counts'',   
           main = ''Histogram, Tip amount'', xlim = c(0,40), 100);  
       hist(InputDataSet$fare_amount, col = ''lightgreen'',   
           xlab=''Fare amount ($)'',   
           ylab = ''Counts'',   
           main = ''Histogram,   
           Fare amount'',   
           xlim = c(0,100), 100);  
       dev.off();  
   
       # Open a pdf file and output an xyplot of tip amount vs. fare amount using lattice;  
       # Only 10,000 sampled observations are plotted here, otherwise file is large.  
       dest_filename = tempfile(pattern = ''rXYPlots_Tip_vs_Fare_Amount_'', tmpdir = mainDir)  
       dest_filename = paste(dest_filename, ''.pdf'',sep="")  
       print(dest_filename, quote=FALSE);  
       pdf(file=dest_filename, height=4, width=4);  
       plot(tip_amount ~ fare_amount,   
           data = InputDataSet[sample(nrow(InputDataSet), 10000), ],   
           ylim = c(0,50),   
           xlim = c(0,150),   
           cex=.5,   
           pch=19,   
           col=''darkgreen'',    
           main = ''Tip amount by Fare amount'',   
           xlab=''Fare Amount ($)'',   
           ylab = ''Tip Amount ($)'');   
       dev.off();',  
     @input_data_1 = @query  
   END
   

Viktiga punkter att förstå i det här skriptet är följande:

• Utdata från SELECT-frågan i den lagrade proceduren lagras i standard-R-dataramen, InputDataSet. Olika R-plottningsfunktioner kan sedan anropas för att generera de faktiska grafikfilerna. De flesta av de inbäddade R-skripten representerar alternativ för dessa grafikfunktioner, till exempel plot eller hist.

• R-enheten är inställd på av eftersom du kör det här kommandot som ett externt skript i SQL Server. Vanligtvis i R, när du utfärdar ett ritningskommando på hög nivå, öppnar R ett grafikfönster som kallas för en enhet. Du kan inaktivera enheten om du skriver till en fil eller hanterar utdata på något annat sätt.

• Alla filer sparas i den lokala mappen C:\temp\Plots. Målmappen definieras av argumenten som tillhandahålls till R-skriptet som en del av den lagrade proceduren. Om du vill mata ut filerna till en annan mapp ändrar du värdet för variabeln mainDir i R-skriptet som är inbäddat i den lagrade proceduren. Du kan också ändra skriptet för att mata ut olika format, fler filer och så vidare.

Kör den lagrade proceduren

Kör följande instruktion för att exportera binära diagramdata till JPEG- och PDF-filformat.

EXEC RPlotHist

Results

STDOUT message(s) from external script:
[1] Creating output plot files:[1] C:\temp\plots\rHistogram_Tipped_18887f6265d4.jpg[1] 

C:\temp\plots\rHistograms_Tip_and_Fare_Amount_1888441e542c.pdf[1]

C:\temp\plots\rXYPlots_Tip_vs_Fare_Amount_18887c9d517b.pdf

Talen i filnamnen genereras slumpmässigt för att säkerställa att du inte får något fel när du försöker skriva till en befintlig fil.

Visa utdata

Om du vill visa diagrammet öppnar du målmappen och granskar filerna som skapades av R-koden i den lagrade proceduren.

1. Gå till mappen som anges i STDOUT-meddelandet (i exemplet är detta C:\temp\plots)

2. Öppna rHistogram_Tipped.jpg för att visa antalet resor som fick ett tips jämfört med de resor som inte fick något tips (det här histogrammet liknar det du genererade i föregående steg).

3. Öppna rHistograms_Tip_and_Fare_Amount.pdf för att visa fördelningen av dricksbelopp, plottad mot resebeloppen.

   

4. Öppna rXYPlots_Tip_vs_Fare_Amount.pdf om du vill visa ett punktdiagram med prisbeloppet på x-axeln och tipsbeloppet på y-axeln.

   

Nästa steg

I den här artikeln kommer du att: