Sdílet prostřednictvím

Kurz jazyka R: Prozkoumání a vizualizace dat

Platí pro: SQL Server 2016 (13.x) a novější verze Azure SQL Managed Instance

Ve druhé části této pětidílné série kurzů prozkoumáte ukázková data a vygenerujete několik grafů. Později se dozvíte, jak serializovat grafické objekty v Pythonu a pak tyto objekty deserializovat a vykreslit.

Ve druhé části této pětidílné série kurzů si projdete ukázková data a pak vygenerujete některé grafy pomocí obecných funkcí barplot a hist v základu R.

Klíčovým cílem tohoto článku je ukázat, jak volat funkce R z Transact-SQL v uložených procedurách a uložit výsledky ve formátech souborů aplikace:

  • Vytvořte uloženou proceduru pomocí barplot k vygenerování grafu R jako varbinary dat. K exportu binárního streamu do souboru obrázku použijte bcp .
  • Vytvořte uloženou proceduru pomocí hist k vygenerování grafu s uložením výsledků jako JPG a PDF.

Poznámka:

Vzhledem k tomu, že vizualizace je takový výkonný nástroj pro pochopení tvaru a distribuce dat, poskytuje jazyk R celou řadu funkcí a balíčků pro generování histogramů, bodových grafů, krabicových grafů a dalších grafů pro zkoumání dat. R obvykle vytváří obrázky pomocí zařízení R pro grafický výstup, které můžete zachytit a uložit jako datový typ varbinary pro vykreslování v aplikaci. Obrázky můžete také uložit do libovolného formátu souboru podpory (.JPG, .PDF atd.).

V tomto článku:

  • Kontrola ukázkových dat
  • Vytváření grafů pomocí jazyka R v T-SQL
  • Výstupní grafy ve více formátech souborů

V první části jste nainstalovali požadavky a obnovili ukázkovou databázi.

Ve třetí části se naučíte vytvářet funkce z nezpracovaných dat pomocí funkce Transact-SQL. Potom tuto funkci zavoláte z uložené procedury a vytvoříte tabulku obsahující hodnoty funkcí.

Ve čtvrté části načtete moduly a zavoláte potřebné funkce k vytvoření a trénování modelu pomocí uložené procedury SQL Serveru.

V 5. části se dozvíte, jak zprovoznit modely, které jste natrénovali a uložili ve čtvrté části.

Kontrola dat

Vývoj řešení pro datové vědy obvykle zahrnuje intenzivní zkoumání dat a vizualizaci dat. Nejprve si na minutku projděte ukázková data, pokud jste to ještě neudělali.

V původní veřejné datové sadě byly identifikátory taxislužby a záznamy jízdy poskytnuty v samostatných souborech. Aby se ale ukázková data snadněji používala, byly dvě původní datové sady spojené se sloupci medallion, hack_license a pickup_datetime. Záznamy se také vzorkovaly, aby získaly pouze 1% původního počtu záznamů. Výsledná datová sada po snižování vzorků má 1 703 957 řádků a 23 sloupců.

Identifikátory taxislužby

  • Sloupec medallion představuje jedinečné IDENTIFIKAČNÍ číslo taxi.

  • Sloupec hack_license obsahuje číslo řidičského průkazu taxislužby (anonymizované).

Záznamy cest a jízdného

  • Každý záznam cesty zahrnuje vyzvednutí a odkládací místo a čas a vzdálenost jízdy.

  • Každý záznam o platbě zahrnuje platební údaje, jako je typ platby, celková částka platby a částka tipu.

  • Poslední tři sloupce je možné použít pro různé úlohy strojového učení. Sloupec tip_amount obsahuje souvislé číselné hodnoty a lze ho použít jako sloupec popisku pro regresní analýzu. Tipovaný sloupec obsahuje pouze hodnoty ano/ne a používá se pro binární klasifikaci. Sloupec tip_class má více popisků tříd , a proto je možné ho použít jako popisek pro úlohy klasifikace s více třídami.

    Tento názorný postup ukazuje pouze úlohu binární klasifikace; Můžete vyzkoušet vytváření modelů pro další dvě úlohy strojového učení, regresi a klasifikaci s více třídami.

  • Hodnoty použité pro sloupce popisků jsou založeny na sloupci tip_amount pomocí těchto obchodních pravidel:

    Odvozený název sloupce Pravidlo
    nakloněný Pokud tip_amount > 0, přepnuto = 1, jinak tipped = 0
    tip_class Třída 0: tip_amount = $0

    Třída 1: částka spropitného > $0 a částka spropitného <= $5

    Třída 2: částka spropitného > $5 a částka spropitného <= $10

    Třída 3: částka spropitného > $10 a částka spropitného < = $20

    Třída 4: tip_amount > $20

Vytváření grafů pomocí jazyka R v T-SQL

Důležité

Počínaje SQL Serverem 2019 vyžaduje mechanismus izolace, abyste adresáři, ve kterém je uložený soubor grafu, dali příslušná oprávnění. Další informace o tom, jak tato oprávnění nastavit, najdete v části Oprávnění k souborům v SYSTÉMU SQL Server 2019 ve Windows: Změny izolace pro službu Machine Learning Services.

K vytvoření grafu použijte funkci barplotR . Tento krok vykreslí histogram na základě dat z dotazu Transact-SQL. Tuto funkci můžete zabalit do uložené procedury RPlotHistogram.

  1. V aplikaci SQL Server Management Studio klikněte v Průzkumníku objektů pravým tlačítkem na databázi NYCTaxi_Sample a vyberte Nový dotaz. Nebo v Nástroji Azure Data Studio vyberte v nabídce Soubornový poznámkový blok a připojte se k databázi.

  2. Vložte následující skript a vytvořte uloženou proceduru, která vykreslí histogram. Tento příklad má název 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

Mezi klíčové body, které je potřeba pochopit v tomto skriptu, patří:

  • Proměnná @query definuje text dotazu ('SELECT tipped FROM nyctaxi_sample'), který se předá skriptu R jako argument vstupní proměnné skriptu. @input_data_1 U skriptů R, které běží jako externí procesy, byste měli mít mapování 1:1 mezi vstupy do skriptu a vstupy do uložené procedury sp_execute_external_script systému, která spouští relaci jazyka R na SQL Serveru.

  • Ve skriptu jazyka R je definována proměnná (image_file) pro uložení image.

  • Funkce barplot se volá k vygenerování grafu.

  • Zařízení R je vypnuté , protože tento příkaz spouštíte jako externí skript na SQL Serveru. Když v jazyce R vydáte příkaz pro vykreslení vysoké úrovně, otevře R grafické okno označované jako zařízení. Zařízení můžete vypnout, pokud píšete do souboru nebo zpracováváte výstup jiným způsobem.

  • Grafický objekt R je serializován do R data.frame pro výstup.

Spusťte uloženou proceduru a pomocí nástroje bcp exportujte binární data do souboru obrázku.

Uložená procedura vrátí obrázek jako datový proud varbinárních dat, který samozřejmě nemůžete zobrazit přímo. Nástroj bcp však můžete použít k získání varbinárních dat a jeho uložení jako souboru obrázku na klientském počítači.

  1. V sadě Management Studio spusťte následující příkaz:

    EXEC [dbo].[RPlotHistogram]

    Results

    plot0xFFD8FFE000104A4649...

  2. Otevřete příkazový řádek PowerShellu a spusťte následující příkaz a jako argumenty zadejte odpovídající název instance, název databáze, uživatelské jméno a přihlašovací údaje. Pro ty, kteří používají identity Systému Windows, můžete nahradit -U a -Pparametrem -T.

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

    Poznámka:

    Přepínače příkazů pro bcp rozlišují malá a velká písmena.

  3. Pokud je připojení úspěšné, zobrazí se výzva k zadání dalších informací o formátu grafického souboru.

    Stisknutím klávesy ENTER v každé výzvě přijměte výchozí hodnoty s výjimkou těchto změn:

    • Pro délku předpony polí zadejte 0.

    • Pokud chcete uložit výstupní parametry pro pozdější použití, zadejte Y .

    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.)

    Návod

    Pokud uložíte informace o formátu do souboru (bcp.fmt), nástroj bcp vygeneruje definici formátu, kterou můžete použít u podobných příkazů v budoucnu, aniž by se zobrazila výzva k zadání možností formátu grafického souboru. Chcete-li použít formátovací soubor, přidejte -f bcp.fmt za argument heslo na konec libovolného příkazového řádku.

  4. Výstupní soubor se vytvoří ve stejném adresáři, ve kterém jste spustili příkaz PowerShellu. Pokud chcete zobrazit graf, stačí otevřít soubor plot.jpg.

    taxi výlety s a bez tipů

Vytvoření uložené procedury pomocí hist

Datoví vědci obvykle generují několik vizualizací dat, aby získali přehled o datech z různých perspektiv. V tomto příkladu vytvoříte uloženou proceduru s názvem RPlotHist pro zápis histogramů, bodových grafů a dalších grafických objektů R pro .JPG a .PDF formát.

Tato uložená procedura hist používá funkci k vytvoření histogramu, exportu binárních dat do oblíbených formátů, jako jsou .JPG, .PDF a .PNG.

  1. V aplikaci SQL Server Management Studio klikněte v Průzkumníku objektů pravým tlačítkem na databázi NYCTaxi_Sample a vyberte Nový dotaz.

  2. Vložte následující skript a vytvořte uloženou proceduru, která vykreslí histogram. Tento příklad má název 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

Mezi klíčové body, které je potřeba pochopit v tomto skriptu, patří:

  • Výstup dotazu SELECT v rámci uložené procedury je uložen ve výchozím datovém rámci jazyka R. InputDataSet K vygenerování skutečných grafických souborů je pak možné volat různé funkce vykreslování jazyka R. Většina vloženého skriptu jazyka R představuje možnosti pro tyto grafické funkce, například plot .hist

  • Zařízení R je vypnuté , protože tento příkaz spouštíte jako externí skript na SQL Serveru. Když v jazyce R vydáte příkaz pro vykreslení vysoké úrovně, otevře R grafické okno označované jako zařízení. Zařízení můžete vypnout, pokud píšete do souboru nebo zpracováváte výstup jiným způsobem.

  • Všechny soubory se ukládají do místní složky C:\temp\Plots. Cílová složka je definována argumenty zadanými skriptem jazyka R jako součást uložené procedury. Pokud chcete výstupní soubory do jiné složky, změňte hodnotu mainDir proměnné ve skriptu R vloženém do uložené procedury. Můžete také upravit skript tak, aby vytvářel různé formáty, více souborů atd.

Spusťte uloženou proceduru.

Spuštěním následujícího příkazu exportujte binární data grafu do formátů souborů JPEG a PDF.

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

Čísla v názvech souborů se náhodně vygenerují, abyste se ujistili, že při pokusu o zápis do existujícího souboru se nezobrazí chyba.

Zobrazení výstupu

Pokud chcete zobrazit graf, otevřete cílovou složku a zkontrolujte soubory vytvořené kódem R v uložené proceduře.

  1. Přejděte do složky uvedené ve zprávě STDOUT (v příkladu je to C:\temp\plots).

  2. Otevřete rHistogram_Tipped.jpg, k zobrazení počtu jízd, které dostaly spropitné vs. jízd, které nedostaly žádné spropitné (tento histogram je podobný tomu, který jste vytvořili v předchozím kroku).

  3. Otevřete rHistograms_Tip_and_Fare_Amount.pdf, abyste zobrazili rozdělení částek spropitného vykreslené proti částkám jízdného.

    histogram zobrazující tip_amount a fare_amount

  4. Otevřete rXYPlots_Tip_vs_Fare_Amount.pdf, abyste zobrazili rozptylový diagram s jízdným na ose x a výší spropitného na ose y.

    částka tipu vynesená přes částku jízdného

Další kroky

V tomto článku:

  • Kontrola ukázkových dat
  • Vytvoření grafů pomocí jazyka R v T-SQL
  • Výstupní grafy ve více formátech souborů

Kurz jazyka R: Vytváření datových funkcí

  • Last updated on