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:

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.

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

2. A questo punto, eseguire la stored procedure senza argomenti per generare un tracciato dai dati hardcoded come query di input.

   EXEC [dbo].[PyPlotMatplotlib]
   

3. I risultati saranno simili ai seguenti:

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

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

5. Se la connessione viene stabilita correttamente, verrà visualizzato un messaggio simile al seguente:

   The plots are saved in directory: xxxx

6. 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: