Python-självstudie: Utforska och visualisera data

Gäller för: SQL Server 2017 (14.x) och senare versioner Azure SQL Managed Instance

I del två i den här självstudieserien i fem delar utforskar du exempeldata och genererar några diagram. Senare får du lära dig hur du serialiserar grafikobjekt i Python och sedan deserialiserar dessa objekt och skapar diagram.

I den här artikeln ska du:

I del ett installerade du förhandskraven och återställde exempeldatabasen.

I del tre får du lära dig hur du skapar funktioner från rådata med hjälp av en Transact-SQL funktion. Sedan anropar du funktionen från en lagrad procedur för att skapa en tabell som innehåller funktionsvärdena.

I del fyra läser du in modulerna och anropar nödvändiga funktioner för att skapa och träna modellen med hjälp av en lagrad SQL Server-procedur.

I del fem får du lära dig hur du operationaliserar de modeller som du har tränat och sparat i del fyra.

Granska datan

Först tar det en minut att bläddra i dataschemat eftersom vi har gjort vissa ändringar för att göra det enklare att använda NYC Taxi-data

• Den ursprungliga datauppsättningen använde separata filer för taxiidentifierarna och reseposterna. Vi har anslutit de två ursprungliga datauppsättningarna på kolumnerna medallion, hack_license och pickup_datetime.
• Den ursprungliga datamängden sträckte sig över många filer och var ganska stor. Vi har reducerat för att endast erhålla 1% av det ursprungliga antalet poster. Den aktuella datatabellen har 1 703 957 rader och 23 kolumner.

Taxiidentifikatorer

Kolumnen medallion representerar taxins unika ID-nummer.

Kolumnen hack_license innehåller taxiförarens körkortsnummer (anonymiserat).

Rese- och taxeposter

Varje reselogginlägg inkluderar tid och plats för upphämtning och avlämning samt resans avstånd.

Varje biljettpost innehåller betalningsinformation som betalningstyp, totalt betalningsbelopp och dricksbelopp.

De tre sista kolumnerna kan användas för olika maskininlärningsuppgifter. Kolumnen tip_amount innehåller kontinuerliga numeriska värden och kan användas som etikettkolumn för regressionsanalys. Den tippade kolumnen har endast ja/nej-värden och används för binär klassificering. Kolumnen tip_class har flera klassetiketter och kan därför användas som etikett för klassificeringsuppgifter med flera klasser.

De värden som används för etikettkolumnerna baseras alla på tip_amount kolumnen med hjälp av följande affärsregler:

• Etikettkolumnen tipped har möjliga värden 0 och 1

  Om tip_amount> 0, tipped = 1; annars tipped = 0

• Etikettkolumnen tip_class har möjliga klassvärden 0–4

  Klass 0: tip_amount = $0

  Klass 1: tip_amount> $0 och tip_amount<= $5

  Klass 2: tip_amount> $5 och tip_amount<= $10

  Klass 3: tip_amount> $10 och tip_amount<= $20

  Klass 4: tip_amount> $20

Skapa diagram med Python i T-SQL

Att utveckla en datavetenskapslösning omfattar vanligtvis intensiv datautforskning och datavisualisering. Eftersom visualisering är ett så kraftfullt verktyg för att förstå fördelningen av data och extremvärden, tillhandahåller Python många paket för visualisering av data. Modulen matplotlib är ett av de mer populära biblioteken för visualisering och innehåller många funktioner för att skapa histogram, punktdiagram, rutor och andra datautforskningsdiagram.

I det här avsnittet får du lära dig hur du arbetar med diagram med hjälp av lagrade procedurer. I stället för att öppna avbildningen på servern lagrar du Python-objektet plot som varbinära data och skriver sedan det till en fil som kan delas eller visas någon annanstans.

Skapa en graf som varbinary-data

Den lagrade proceduren returnerar ett serialiserat Python-objekt figure som en ström av varbinära data. Du kan inte visa binära data direkt, men du kan använda Python-kod på klienten för att deserialisera och visa siffrorna och sedan spara bildfilen på en klientdator.

1. Skapa den lagrade proceduren PyPlotMatplotlib.

   I följande skript:

   • Variabeln @query definierar frågetexten SELECT tipped FROM nyctaxi_sample, som skickas till Python-kodblocket som argument till skriptets indatavariabel, @input_data_1.
   • Python-skriptet är ganska enkelt: matplotlib-objektfigure används för att göra histogrammet och punktdiagrammet, och dessa objekt serialiseras sedan med hjälp av pickle biblioteket.
   • Python-grafikobjektet serialiseras till en Pandas DataFrame för utdata.

   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. Kör nu den lagrade proceduren utan argument för att generera ett diagram från data som hårdkodas som indatafråga.

   EXEC [dbo].[PyPlotMatplotlib]
   

3. Resultatet bör se ut ungefär så här:

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

4. Från en Python-klient kan du nu ansluta till SQL Server-instansen som genererade de binära diagramobjekten och visa diagram.

   Det gör du genom att köra följande Python-kod och ersätta servernamnet, databasnamnet och autentiseringsuppgifterna efter behov (för Windows-autentisering ersätter du parametrarna UID och PWD med Trusted_Connection=True). Kontrollera att Python-versionen är densamma på klienten och servern. Kontrollera också att Python-biblioteken på klienten (till exempel matplotlib) är samma eller högre version i förhållande till biblioteken som är installerade på servern. En lista över installerade paket och deras versioner finns i Hämta Information om Python-paket.

   %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. Om anslutningen lyckas bör du se ett meddelande som liknar följande:

   Diagrammet sparas i katalogen: xxxx

6. Utdatafilen skapas i Python-arbetskatalogen. Om du vill visa diagrammet letar du upp Python-arbetskatalogen och öppnar filen. Följande bild visar ett diagram som sparats på klientdatorn.

   

Nästa steg

I den här artikeln kommer du att: