Esercitazione su Python: esplorare e visualizzare i dati
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 1Se
tip_amount
> 0,tipped
= 1; altrimentitipped
= 0I valori di classe possibili per la colonna etichetta
tip_class
vanno da 0 a 4Classe 0:
tip_amount
= $ 0Classe 1:
tip_amount
> $0 etip_amount
<= $5Classe 2:
tip_amount
> $5 etip_amount
<= $10Classe 3:
tip_amount
> $10 etip_amount
<= $20Classe 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 libreriapickle
. - 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
- La variabile
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
ePWD
conTrusted_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