R-zelfstudie: Gegevens verkennen en visualiseren

Van toepassing op: SQL Server 2016 (13.x) en latere versies van Azure SQL Managed Instance

In deel twee van deze vijfdelige reeks zelfstudies verkent u de voorbeeldgegevens en genereert u enkele plots. Later leert u hoe u grafische objecten in Python serialiseert en deze objecten vervolgens deserialiseert en plots maakt.

In deel twee van deze vijfdelige reeks zelfstudies bekijkt u de voorbeeldgegevens en genereert u vervolgens enkele plots met behulp van de algemene barplot en hist functies in basis R.

Een belangrijk doel van dit artikel is het aanroepen van R-functies vanuit Transact-SQL in opgeslagen procedures en het opslaan van de resultaten in bestandsindelingen van toepassingen:

• Maak een opgeslagen procedure met behulp van barplot om een R-plot als varbinaire data te genereren. Gebruik bcp om de binaire stroom te exporteren naar een afbeeldingsbestand.
• Maak een opgeslagen procedure met hist om een plot te genereren en sla de resultaten op als JPG- en PDF-uitvoer.

In dit artikel gaat u het volgende doen:

In deel 1 hebt u de vereisten geïnstalleerd en de voorbeelddatabase hersteld.

In deel drie leert u hoe u functies maakt op basis van onbewerkte gegevens met behulp van een Transact-SQL-functie. Vervolgens roept u die functie aan vanuit een opgeslagen procedure om een tabel te maken die de functiewaarden bevat.

In deel vier laadt u de modules en roept u de benodigde functies aan om het model te maken en te trainen met behulp van een opgeslagen SQL Server-procedure.

In deel vijf leert u hoe u de modellen kunt operationeel maken die u hebt getraind en opgeslagen in deel vier.

De gegevens controleren

Het ontwikkelen van een data science-oplossing omvat meestal intensieve gegevensverkenning en gegevensvisualisatie. Neem dus eerst even de tijd om de voorbeeldgegevens te bekijken, als u dat nog niet hebt gedaan.

In de oorspronkelijke openbare gegevensset zijn de taxi-id's en reisrecords opgegeven in afzonderlijke bestanden. Om de voorbeeldgegevens echter gemakkelijker te kunnen gebruiken, zijn de twee oorspronkelijke gegevenssets samengevoegd op de kolommen medallion, hack_license en pickup_datetime. De records zijn ook gesampeld om slechts 1% van het oorspronkelijke aantal records te verkrijgen. De resulterende gegevensset met down-samples heeft 1.703.957 rijen en 23 kolommen.

Taxi-identificatoren

• De medaillonkolom vertegenwoordigt het unieke ID-nummer van de taxi.

• De kolom hack_license bevat het rijbewijsnummer van de taxichauffeur (geanonimiseerd).

Rit- en tariefrecords

• Elke reisrecord omvat de locatie en tijd voor ophalen en afzetten, en de reisafstand.

• Elke tariefrecord bevat betalingsgegevens, zoals het betalingstype, het totale bedrag van de betaling en het fooibedrag.

• De laatste drie kolommen kunnen worden gebruikt voor verschillende machine learning-taken. De kolom tip_amount bevat doorlopende numerieke waarden en kan worden gebruikt als labelkolom voor regressieanalyse. De getipte kolom heeft alleen ja/nee-waarden en wordt gebruikt voor binaire classificatie. De kolom tip_class heeft meerdere klasselabels en kan daarom worden gebruikt als label voor classificatietaken met meerdere klassen.

  In dit scenario ziet u alleen de binaire classificatietaak; U kunt modellen bouwen voor de andere twee machine learning-taken, regressie en classificatie met meerdere klassen.

• De waarden die worden gebruikt voor de labelkolommen zijn allemaal gebaseerd op de kolom tip_amount met behulp van deze bedrijfsregels:

  Naam van afgeleide kolom	Regel
  Getipt	Als tip_amount > 0, getipt = 1, anders getipt = 0
  tip_klasse	Klasse 0: tip_amount = $ 0 Klasse 1: tip_amount > $ 0 en tip_amount <= $ 5 Klasse 2: tip_amount > $5 en tip_amount <= $10 Klasse 3: tip_amount > $10 en tip_amount <= $20 Klasse 4: tip_amount > $ 20

Plots maken met behulp van R in T-SQL

Gebruik de functie barplotR om de plot te maken. Met deze stap wordt een histogram gemaakt op basis van gegevens uit een Transact-SQL query. U kunt deze functie verpakken in een opgeslagen procedure, RPlotHistogram.

1. Klik in SQL Server Management Studio in Objectverkenner met de rechtermuisknop op de NYCTaxi_Sample-database en selecteer Nieuwe query. Of selecteer in Azure Data Studio New Notebook in het menu Bestand en maak verbinding met de database.

2. Plak het volgende script in om een opgeslagen procedure te maken die het histogram plot. Dit voorbeeld heeft de naam 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
   

Belangrijke punten die u in dit script moet begrijpen, zijn onder andere:

• De variabele @query definieert de querytekst ('SELECT tipped FROM nyctaxi_sample'), die als argument wordt doorgegeven aan het R-script als argument voor de scriptinvoervariabele. @input_data_1 Voor R-scripts die als externe processen worden uitgevoerd, moet u een een-op-een-toewijzing hebben tussen invoer voor uw script en invoer voor de sp_execute_external_script systeem opgeslagen procedure waarmee de R-sessie op SQL Server wordt gestart.

• In het R-script wordt een variabele (image_file) gedefinieerd om de afbeelding op te slaan.

• De barplot functie wordt aangeroepen om de plot te genereren.

• Het R-apparaat is uitgeschakeld omdat u deze opdracht uitvoert als een extern script in SQL Server. Normaal gesproken opent R een grafisch venster, een zogenaamde device, wanneer u in R een geavanceerd tekencommando geeft. U kunt het apparaat uitschakelen als u naar een bestand schrijft of de uitvoer op een andere manier verwerkt.

• Het R-grafische object wordt geserialiseerd naar een R data.frame voor uitvoer.

Voer de opgeslagen procedure uit en gebruik bcp om binaire gegevens te exporteren naar een afbeeldingsbestand

De opgeslagen procedure retourneert de afbeelding als een stroom varbinaire gegevens, die u uiteraard niet rechtstreeks kunt bekijken. U kunt echter het hulpprogramma bcp gebruiken om de varbinaire gegevens op te halen en op te slaan als een afbeeldingsbestand op een clientcomputer.

1. Voer in Management Studio de volgende instructie uit:

   EXEC [dbo].[RPlotHistogram]
   

   resultaten

   plot0xFFD8FFE000104A4649...

2. Open een PowerShell-opdrachtprompt en voer de volgende opdracht uit en geef de juiste exemplaarnaam, databasenaam, gebruikersnaam en referenties op als argumenten. Voor degenen die Windows-identiteiten gebruiken, kunt u -U en -P vervangen door -T.

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

   Opmerking

   Opdrachtswitches voor bcp zijn hoofdlettergevoelig.

3. Als de verbinding is geslaagd, wordt u gevraagd om meer informatie over de bestandsindeling voor afbeeldingen in te voeren.

   Druk op Enter bij elke prompt om de standaardinstellingen te accepteren, met uitzondering van deze wijzigingen:

   • Typ 0 voor prefixlengte van veldplot.

   • Typ Y als u de uitvoerparameters wilt opslaan voor later hergebruik.

   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]:
   

   resultaten

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

   Aanbeveling

   Als u de indelingsgegevens opslaat in het bestand (bcp.fmt), genereert het hulpprogramma bcp een indelingsdefinitie die u in de toekomst kunt toepassen op vergelijkbare opdrachten zonder dat u wordt gevraagd om opties voor grafische bestandsindelingen. Als u het indelingsbestand wilt gebruiken, voegt u dit toe -f bcp.fmt aan het einde van een opdrachtregel, na het wachtwoordargument.

4. Het uitvoerbestand wordt gemaakt in dezelfde map waarin u de PowerShell-opdracht hebt uitgevoerd. Als u de plot wilt bekijken, opent u het bestand plot.jpg.

   

Een opgeslagen procedure maken met hist

Gegevenswetenschappers genereren doorgaans meerdere gegevensvisualisaties om inzicht te krijgen in de gegevens vanuit verschillende perspectieven. In dit voorbeeld maakt u een opgeslagen procedure met de naam RPlotHist om histogrammen, spreidingsdiagrammen en andere R-afbeeldingen te schrijven naar .JPG- en .PDF-indeling.

In deze opgeslagen procedure wordt de hist functie gebruikt om het histogram te maken, de binaire gegevens te exporteren naar populaire indelingen zoals .JPG, .PDF en .PNG.

1. Klik in SQL Server Management Studio in Objectverkenner met de rechtermuisknop op de NYCTaxi_Sample-database en selecteer Nieuwe query.

2. Voeg het volgende script in om een opgeslagen procedure te maken waarmee het histogram wordt geplot. Dit voorbeeld heeft de naam 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
   

Belangrijke punten die u in dit script moet begrijpen, zijn onder andere:

• De uitvoer van de SELECT-query binnen de opgeslagen procedure wordt opgeslagen in het standaard R-gegevensframe. InputDataSet Verschillende R-plottingfuncties kunnen vervolgens worden aangeroepen om de werkelijke grafische bestanden te genereren. Het grootste deel van het ingesloten R-script vertegenwoordigt opties voor deze grafische functies, zoals plot of hist.

• Het R-apparaat is uitgeschakeld omdat u deze opdracht uitvoert als een extern script in SQL Server. Normaal gesproken opent R, wanneer u een hoog-niveau plotopdracht geeft, een grafisch venster dat een apparaat wordt genoemd. U kunt het apparaat uitschakelen als u naar een bestand schrijft of de uitvoer op een andere manier verwerkt.

• Alle bestanden worden opgeslagen in de lokale map C:\temp\Plots. De doelmap wordt gedefinieerd door de argumenten die aan het R-script zijn opgegeven als onderdeel van de opgeslagen procedure. Als u de bestanden naar een andere map wilt uitvoeren, wijzigt u de waarde van de mainDir variabele in het R-script dat is ingesloten in de opgeslagen procedure. U kunt het script ook wijzigen om verschillende indelingen, meer bestanden enzovoort uit te voeren.

De opgeslagen procedure uitvoeren

Voer de volgende instructie uit om binaire plotgegevens te exporteren naar JPEG- en PDF-bestandsindelingen.

EXEC RPlotHist

resultaten

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

De getallen in de bestandsnamen worden willekeurig gegenereerd om ervoor te zorgen dat u geen fout krijgt bij het schrijven naar een bestaand bestand.

Uitvoer weergeven

Als u de plot wilt weergeven, opent u de doelmap en controleert u de bestanden die zijn gemaakt door de R-code in de opgeslagen procedure.

1. Ga naar de map die wordt aangegeven in het STDOUT-bericht (in het voorbeeld is dit C:\temp\plots)

2. Open rHistogram_Tipped.jpg om het aantal reizen weer te geven dat een tip heeft versus de reizen die geen tip hebben (dit histogram is vergelijkbaar met het aantal reizen dat u in de vorige stap hebt gegenereerd).

3. Open rHistograms_Tip_and_Fare_Amount.pdf om de verdeling van tipbedragen weer te geven, weergegeven op basis van de tariefbedragen.

   

4. Open rXYPlots_Tip_vs_Fare_Amount.pdf om een spreidingsdiagram weer te geven met de tariefhoeveelheid op de x-as en de tiphoeveelheid op de y-as.

   

Volgende stappen

In dit artikel gaat u als volgt te werk: