Esercitazione su Python: esplorare e visualizzare i dati

Articolo
05/30/2023

Si applica a: SQL Server 2017 (14.x) e versioni successive Istanza gestita di SQL di Azure

Nella seconda parte di questa serie di esercitazioni in cinque parti verranno esaminati i dati di esempio e verranno generati alcuni tracciati. Più avanti si apprenderà come serializzare gli oggetti grafici in Python e quindi deserializzare gli oggetti e creare tracciati.

Contenuto dell'articolo:

Esaminare i dati di esempio
Creare tracciati usando Python in T-SQL

Nella prima parte sono stati installati i prerequisiti ed è stato ripristinato il database di esempio.

Nella terza parte si apprenderà come creare funzionalità dai dati non elaborati tramite una funzione Transact-SQL. Tale funzione verrà quindi chiamata da una stored procedure per creare una tabella contenente i valori della funzionalità.

Nella quarta parte verranno caricati i moduli e verranno chiamate le funzioni necessarie per la creazione e il training del modello usando una stored procedure di SQL Server.

Nella quinta parte si apprenderà come rendere operativi i modelli sottoposti a training e salvati nella quarta parte.

Esaminare i dati

Dedicare prima di tutto un po' di tempo all'esame dello schema dei dati in quanto sono state apportate alcune modifiche per semplificare l'uso dei dati di NYC Taxi

Nel set di dati originale i file relativi a identificatori di taxi e record delle corse sono separati. I due set di dati originali sono stati uniti nelle colonne medallion, hack_license e pickup_datetime.
Il set di dati originale si estende su più file ed è piuttosto grande. È stato eseguito un sottocampionamento per ottenere solo l'1% del numero di record originale. La tabella dati corrente contiene 1.703.957 righe e 23 colonne.

Identificatori di taxi

La colonna medallion rappresenta l'ID univoco del taxi.

La colonna hack_license contiene il numero di patente del tassista (in forma anonima).

Record delle corse e delle tariffe

Il record di ogni corsa include il luogo e l'ora di inizio e fine della corsa e la distanza percorsa.

Il record di ogni tariffa include i dati del pagamento, ad esempio il tipo di pagamento, l'importo totale e l'importo della mancia.

Le ultime tre colonne possono essere usate per diverse attività di apprendimento automatico. La colonna tip_amount contiene valori numerici continui e può essere usata come colonna label per l'analisi della regressione. La colonna tipped include solo valori sì/no e viene usata per la classificazione binaria. La colonna tip_class include più etichette di classe e può essere quindi usata come etichetta per le attività di classificazione multiclasse.

I valori usati per le colonne etichetta sono tutti basati sulla colonna tip_amount usando le regole business seguenti:

I valori possibili per la colonna etichetta tipped sono 0 e 1

Se tip_amount> 0, tipped = 1; altrimenti tipped = 0
I valori di classe possibili per la colonna etichetta tip_class vanno da 0 a 4

Classe 0: tip_amount = $ 0

Classe 1: tip_amount> $0 e tip_amount<= $5

Classe 2: tip_amount> $5 e tip_amount<= $10

Classe 3: tip_amount> $10 e tip_amount<= $20

Classe 4: tip_amount> $20

Creare tracciati usando Python in T-SQL

Lo sviluppo di una soluzione di analisi scientifica dei dati prevede in genere frequenti esplorazioni e visualizzazioni dei dati. Poiché la visualizzazione è uno strumento efficace per comprendere la distribuzione di dati e outlier, Python offre numerosi pacchetti per la visualizzazione dei dati. Il modulo matplotlib è tra le più diffuse librerie per la visualizzazione e include numerose funzioni per la creazione di istogrammi, grafici a dispersione, box plot e altri grafici per l'esplorazione dei dati.

In questa sezione viene illustrato come lavorare con i tracciati usando stored procedure. Invece che aprire l'immagine nel server, archiviare l'oggetto Python plot come dati di tipo varbinary e quindi scriverlo in un file che può essere condiviso o visualizzato altrove.

Creare un tracciato come dati varbinary

La stored procedure restituisce un oggetto Python figure serializzato come flusso di dati varbinary. Non è possibile visualizzare direttamente i dati binari, ma è possibile usare il codice Python nel client per deserializzare e visualizzare le cifre, quindi salvare il file di immagine in un computer client.

Creare la stored procedure PyPlotMatplotlib.

Nello script seguente:

La variabile @query definisce il testo della query (SELECT tipped FROM nyctaxi_sample) che viene passato al blocco di codice Python come argomento della variabile di input dello script @input_data_1.
Lo script Python è piuttosto semplice: gli oggetti figure di matplotlib vengono usati per creare l'istogramma e il grafico a dispersione e quindi vengono serializzati usando la libreria pickle.
L'oggetto grafico Python viene serializzato in un frame di dati pandas per l'output.

DROP PROCEDURE IF EXISTS PyPlotMatplotlib;
GO

CREATE PROCEDURE [dbo].[PyPlotMatplotlib]
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'Python',
    @script = N'
import matplotlib
matplotlib.use("Agg")
import matplotlib.pyplot as plt
import pandas as pd
import pickle

fig_handle = plt.figure()
plt.hist(InputDataSet.tipped)
plt.xlabel("Tipped")
plt.ylabel("Counts")
plt.title("Histogram, Tipped")
plot0 = pd.DataFrame(data =[pickle.dumps(fig_handle)], columns =["plot"])
plt.clf()

plt.hist(InputDataSet.tip_amount)
plt.xlabel("Tip amount ($)")
plt.ylabel("Counts")
plt.title("Histogram, Tip amount")
plot1 = pd.DataFrame(data =[pickle.dumps(fig_handle)], columns =["plot"])
plt.clf()

plt.hist(InputDataSet.fare_amount)
plt.xlabel("Fare amount ($)")
plt.ylabel("Counts")
plt.title("Histogram, Fare amount")
plot2 = pd.DataFrame(data =[pickle.dumps(fig_handle)], columns =["plot"])
plt.clf()

plt.scatter( InputDataSet.fare_amount, InputDataSet.tip_amount)
plt.xlabel("Fare Amount ($)")
plt.ylabel("Tip Amount ($)")
plt.title("Tip amount by Fare amount")
plot3 = pd.DataFrame(data =[pickle.dumps(fig_handle)], columns =["plot"])
plt.clf()

OutputDataSet = plot0.append(plot1, ignore_index=True).append(plot2, ignore_index=True).append(plot3, ignore_index=True)
',
@input_data_1 = @query
WITH RESULT SETS ((plot varbinary(max)))
END
GO

A questo punto, eseguire la stored procedure senza argomenti per generare un tracciato dai dati hardcoded come query di input.
```
EXEC [dbo].[PyPlotMatplotlib]
```

I risultati saranno simili ai seguenti:

plot
0xFFD8FFE000104A4649...
 0xFFD8FFE000104A4649...
 0xFFD8FFE000104A4649...
 0xFFD8FFE000104A4649...

Da un client Python è ora possibile connettersi all'istanza di SQL Server che ha generato gli oggetti tracciato di tipo binario e visualizzare i tracciati.

A tale scopo, eseguire il codice Python seguente, sostituendo nome server, nome database e credenziali come appropriato (per l'autenticazione di Windows, sostituire i parametri UID e PWD con Trusted_Connection=True). Verificare che la versione di Python sia la stessa nel client e nel server. Assicurarsi inoltre che la versione delle librerie Python nel client (ad esempio matplotlib) sia uguale o superiore a quella delle librerie installate nel server. Per visualizzare un elenco dei pacchetti installati e delle relative versioni, vedere Ottenere informazioni sui pacchetti Python.
```
%matplotlib notebook
import pyodbc
import pickle
import os
cnxn = pyodbc.connect('DRIVER=SQL Server;SERVER={SERVER_NAME};DATABASE={DB_NAME};UID={USER_NAME};PWD={PASSWORD}')
cursor = cnxn.cursor()
cursor.execute("EXECUTE [dbo].[PyPlotMatplotlib]")
tables = cursor.fetchall()
for i in range(0, len(tables)):
    fig = pickle.loads(tables[i][0])
    fig.savefig(str(i)+'.png')
print("The plots are saved in directory: ",os.getcwd())
```
Se la connessione viene stabilita correttamente, verrà visualizzato un messaggio simile al seguente:

The plots are saved in directory: xxxx
Il file di output viene creato nella directory di lavoro di Python. Per visualizzare il tracciato, individuare la directory di lavoro di Python e aprire il file. L'immagine seguente mostra un tracciato salvato nel computer client.

Passaggi successivi

In questo articolo si apprenderà come:

Rivedere i dati di esempio
Creare tracciati usando Python in T-SQL

Esercitazione su Python: creare funzionalità dei dati con T-SQL

Condividi tramite