Python-Tutorial: Ausführen von Vorhersagen mithilfe eines eingebetteten Python-Skripts in einer gespeicherten Prozedur

Artikel
05/30/2023

Gilt für: SQL Server 2017 (14.x) und höher Azure SQL Managed Instance

Im fünften Teil dieser fünfteiligen Tutorialreihe erfahren Sie, wie Sie die Modelle operationalisieren, die Sie im vorherigen Teil trainiert und gespeichert haben.

In diesem Kontext bedeutet Operationalisierung, dass das Modell zur Bewertung in der Produktion bereitgestellt wird. Durch die Integration mit SQL Server gestaltet sich dieser Vorgang einfach, da Sie Python-Code in eine gespeicherte Prozedur einbetten können. Sie können die gespeicherte Prozedur in einer Anwendung aufrufen und die neuen Daten übergeben, um Vorhersagen für das Modell auf Grundlage neuer Eingaben zu erhalten.

In diesem Teil des Tutorials werden zwei Methoden für das Erstellen von Vorhersagen anhand eines Python-Modells vorgestellt: die Batchbewertung und die zeilenbasierte Bewertung.

Batchbewertung: Sie können mehrere Zeilen als Eingabedaten angeben, indem Sie eine SELECT-Abfrage als Argument an die gespeicherte Prozedur übergeben. Das Ergebnis ist eine Tabelle mit Beobachtungen, die mit den Eingabefällen übereinstimmen.
Einzelbewertung: Übergeben Sie individuelle Parameterwerte als Eingabe. Die gespeicherte Prozedur gibt eine einzelne Zeile oder einen Wert zurück.

Der gesamte Python-Code für die Bewertung ist in der gespeicherten Prozedur enthalten.

In diesem Artikel führen Sie Folgendes durch:

Erstellen und Verwenden von gespeicherten Prozeduren für die Batchbewertung
Erstellen und Verwenden von gespeicherten Prozeduren zur Bewertung einer einzelnen Zeile

In Teil 1 haben Sie die Voraussetzungen installiert und die Beispieldatenbank wiederhergestellt.

In Teil zwei haben Sie die Beispieldaten untersucht und einige Plots generiert.

In Teil drei haben Sie gelernt, wie Sie mithilfe einer Transact-SQL-Funktion aus Rohdaten Features erstellen. Anschließend haben Sie die Funktion aus einer gespeicherten Prozedur aufgerufen, um eine Tabelle zu erstellen, die die Funktionswerte enthält.

Im vierten Teil haben Sie die Module geladen und die erforderlichen Funktionen aufgerufen, um das Modell mithilfe einer gespeicherten SQL Server-Prozedur zu erstellen und zu trainieren.

Batchbewertung

Die ersten beiden gespeicherten Prozeduren, die mithilfe des folgenden Skripts erstellt wurden, beschreiben die grundlegende Syntax für das Umschließen eines Python-Vorhersageaufrufs in einer gespeicherten Prozedur. Für beide gespeicherten Prozeduren ist eine Datentabelle als Eingabe erforderlich.

Der Name des zu verwendenden Modells wird als Eingabeparameter an die gespeicherte Prozedur übergeben. Die gespeicherte Prozedur lädt das serialisierte Modell mithilfe der enthaltenen SELECT-Anweisung aus der Datenbanktabelle nyc_taxi_models.table.
Das serialisierte Modell wird in der Python-Variable mod für die weitere Verarbeitung durch Python gespeichert.
Die zu bewertenden neuen Fälle werden mit der in Transact-SQL spezifizierten Abfrage in @input_data_1 abgerufen. Wenn die Abfragedaten gelesen werden, werden die Zeilen im Standard-Datenrahmen, InputDataSet, gespeichert.
Beide gespeicherten Prozeduren verwenden Funktionen von sklearn, um eine Genauigkeitsmetrik (Area Under the Curve, AUC) zu berechnen. Genauigkeitsmetriken wie AUC können nur generiert werden, wenn Sie auch die Zielbezeichnung (die Spalte tipped) angeben. Vorhersagen benötigen keine Zielbezeichnung (Variable y), die Berechnung der Genauigkeitsmetrik jedoch schon.

Wenn also keine Zielbezeichnungen für die zu bewertenden Daten vorhanden sind, können Sie die gespeicherte Prozedur so anpassen, dass die AUC-Berechnungen entfernt werden. Dann wird nur die Trinkgeldwahrscheinlichkeit anhand der Eigenschaften (Variable X in der gespeicherten Prozedur) zurückgegeben.

PredictTipSciKitPy

Führen Sie folgenden T-SQL-Anweisungen aus, um die gespeicherte Prozedur PredictTipSciKitPy zu erstellen. Für diese gespeicherte Prozedur ist ein Modell auf Grundlage des Pakets scikit-learn erforderlich, da spezifische Funktionen dieses Pakets verwendet werden.

Der Datenrahmen mit den Eingaben wird an die predict_proba-Funktion des logistischen Regressionsmodells (mod) übergeben. Die predict_proba-Funktion (probArray = mod.predict_proba(X)) gibt einen Gleitkommawert zurück, der die Wahrscheinlichkeit angibt, dass ein Trinkgeld (beliebiger Höhe) gegeben wird.

DROP PROCEDURE IF EXISTS PredictTipSciKitPy;
GO

CREATE PROCEDURE [dbo].[PredictTipSciKitPy] (@model varchar(50), @inquery nvarchar(max))
AS
BEGIN
DECLARE @lmodel2 varbinary(max) = (select model from nyc_taxi_models where name = @model);
EXEC sp_execute_external_script
  @language = N'Python',
  @script = N'
import pickle;
import numpy;
from sklearn import metrics

mod = pickle.loads(lmodel2)
X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]
y = numpy.ravel(InputDataSet[["tipped"]])

probArray = mod.predict_proba(X)
probList = []
for i in range(len(probArray)):
  probList.append((probArray[i])[1])

probArray = numpy.asarray(probList)
fpr, tpr, thresholds = metrics.roc_curve(y, probArray)
aucResult = metrics.auc(fpr, tpr)
print ("AUC on testing data is: " + str(aucResult))

OutputDataSet = pandas.DataFrame(data = probList, columns = ["predictions"])
',	
  @input_data_1 = @inquery,
  @input_data_1_name = N'InputDataSet',
  @params = N'@lmodel2 varbinary(max)',
  @lmodel2 = @lmodel2
WITH RESULT SETS ((Score float));
END
GO

PredictTipRxPy

Führen Sie folgenden T-SQL-Anweisungen aus, um die gespeicherte Prozedur PredictTipRxPy zu erstellen. Diese gespeicherte Prozedur verwendet die gleichen Eingaben und erstellt die gleichen Bewertungen wie die vorherige gespeicherte Prozedur, verwendet jedoch Funktionen aus dem Paket revoscalepy, das im Lieferumfang von SQL Server Machine Learning Services enthalten ist.

DROP PROCEDURE IF EXISTS PredictTipRxPy;
GO

CREATE PROCEDURE [dbo].[PredictTipRxPy] (@model varchar(50), @inquery nvarchar(max))
AS
BEGIN
DECLARE @lmodel2 varbinary(max) = (select model from nyc_taxi_models where name = @model);
EXEC sp_execute_external_script 
  @language = N'Python',
  @script = N'
import pickle;
import numpy;
from sklearn import metrics
from revoscalepy.functions.RxPredict import rx_predict;

mod = pickle.loads(lmodel2)
X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]
y = numpy.ravel(InputDataSet[["tipped"]])

probArray = rx_predict(mod, X)
probList = probArray["tipped_Pred"].values 

probArray = numpy.asarray(probList)
fpr, tpr, thresholds = metrics.roc_curve(y, probArray)
aucResult = metrics.auc(fpr, tpr)
print ("AUC on testing data is: " + str(aucResult))

OutputDataSet = pandas.DataFrame(data = probList, columns = ["predictions"])
',
  @input_data_1 = @inquery,
  @input_data_1_name = N'InputDataSet',
  @params = N'@lmodel2 varbinary(max)',
  @lmodel2 = @lmodel2
WITH RESULT SETS ((Score float));
END
GO

Ausführen der Batchbewertung mithilfe einer SELECT-Abfrage

Für die gespeicherte Prozedur PredictTipSciKitPy und PredictTipRxPy sind zwei Eingabeparameter erforderlich:

die Abfrage, die die Daten für die Bewertung abruft
der Name des trainierten Modells

Indem Sie diese Argumente an die gespeicherte Prozedur übergeben, können Sie ein bestimmtes Modell auswählen oder die Daten für die Bewertung ändern.

Wenn Sie das Modell scikit-learn für die Bewertung verwenden möchten, rufen Sie die gespeicherte Prozedur PredictTipSciKitPy auf, und übergeben Sie den Modellnamen und die Abfragezeichenfolge als Eingabe.
```
DECLARE @query_string nvarchar(max) -- Specify input query
  SET @query_string='
  select tipped, fare_amount, passenger_count, trip_time_in_secs, trip_distance,
  dbo.fnCalculateDistance(pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as direct_distance
  from nyctaxi_sample_testing'
EXEC [dbo].[PredictTipSciKitPy] 'SciKit_model', @query_string;
```
Die gespeicherte Prozedur gibt die vorhergesagten Wahrscheinlichkeiten für jede Fahrt zurück, die mit der Eingabeabfrage übergeben wurde.

Wenn Sie SSMS (SQL Server Management Studio) für die Ausführung von Abfragen verwenden, werden die Wahrscheinlichkeiten als Tabelle im Bereich Ergebnisse angezeigt. Im Bereich Meldungen wird die Genauigkeitsmetrik (AUC) mit einem Wert von etwa 0,56 angezeigt.

Wenn Sie das Modell revoscalepy für die Bewertung verwenden möchten, rufen Sie die gespeicherte Prozedur PredictTipRxPy auf, und übergeben den Modellnamen und die Abfragezeichenfolge als Eingabe.

DECLARE @query_string nvarchar(max) -- Specify input query
  SET @query_string='
  select tipped, fare_amount, passenger_count, trip_time_in_secs, trip_distance,
  dbo.fnCalculateDistance(pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as direct_distance
  from nyctaxi_sample_testing'
EXEC [dbo].[PredictTipRxPy] 'revoscalepy_model', @query_string;

Zeilenbasierte Bewertung

Es kann vorkommen, dass Sie anstelle einer Batchbewertung nur einen einzelnen Fall zur Bewertung übergeben müssen, sodass Werte von einer Anwendung abgerufen und ein einzelnes anhand dieser Werte berechnetes Ergebnis zurückgegeben wird. Beispielsweise könnten Sie ein Excel-Arbeitsblatt, eine Webanwendung oder einen Bericht einrichten, um die gespeicherte Prozedur aufzurufen und Eingaben zu übergeben, die von Benutzern getätigt oder ausgewählt wurden.

In diesem Abschnitt erfahren Sie, wie Sie einzelne Vorhersagen durch Abrufen von zwei gespeicherten Prozeduren erstellen können.

PredictTipSingleModeSciKitPy wurde für die zeilenbasierte Bewertung mithilfe des Modells scikit-learn entworfen.
PredictTipSingleModeRxPy wurde für die zeilenbasierte Bewertung mithilfe des Modells revoscalepy entworfen.
Wenn Sie noch kein Modell trainiert haben, sollten Sie zum fünften Teil zurückkehren.

Beide Modelle akzeptieren einzelne Werte als Eingabe, z. B. die Anzahl der Fahrgäste oder die Fahrtstrecke. fnEngineerFeatures wird als Tabellenwertfunktion verwendet, um die Werte für Längen- und Breitengrad aus den Eingaben in eine neue Eigenschaft zu konvertieren: die direkte Entfernung. Im vierten Teil wird diese Tabellenwertfunktion näher erläutert.

Beide gespeicherten Prozeduren erstellen eine Bewertung anhand des Python-Modells.

Hinweis

Es ist wichtig, alle Eingabeeigenschaften anzugeben, die das Python-Modell benötigt, wenn Sie die gespeicherte Prozedur von einer externen Anwendung aus aufrufen. Sie können Fehler vermeiden, indem Sie die Eingabedaten in einen Python-Datentyp umwandeln oder konvertieren. Zudem können Sie den Datentyp und die Datenlänge überprüfen.

PredictTipSingleModeSciKitPy

Die folgende gespeicherte Prozedur PredictTipSingleModeSciKitPy führt die Bewertung mithilfe des Modells scikit-learn durch.

DROP PROCEDURE IF EXISTS PredictTipSingleModeSciKitPy;
GO

CREATE PROCEDURE [dbo].[PredictTipSingleModeSciKitPy] (@model varchar(50), @passenger_count int = 0,
  @trip_distance float = 0,
  @trip_time_in_secs int = 0,
  @pickup_latitude float = 0,
  @pickup_longitude float = 0,
  @dropoff_latitude float = 0,
  @dropoff_longitude float = 0)
AS
BEGIN
  DECLARE @inquery nvarchar(max) = N'
  SELECT * FROM [dbo].[fnEngineerFeatures]( 
    @passenger_count,
    @trip_distance,
    @trip_time_in_secs,
    @pickup_latitude,
    @pickup_longitude,
    @dropoff_latitude,
    @dropoff_longitude)
    '
DECLARE @lmodel2 varbinary(max) = (select model from nyc_taxi_models where name = @model);
EXEC sp_execute_external_script 
  @language = N'Python',
  @script = N'
import pickle;
import numpy;

# Load model and unserialize
mod = pickle.loads(model)

# Get features for scoring from input data
X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]

# Score data to get tip prediction probability as a list (of float)
probList = []
probList.append((mod.predict_proba(X)[0])[1])

# Create output data frame
OutputDataSet = pandas.DataFrame(data = probList, columns = ["predictions"])
',
  @input_data_1 = @inquery,
  @params = N'@model varbinary(max),@passenger_count int,@trip_distance float,
    @trip_time_in_secs int ,
    @pickup_latitude float ,
    @pickup_longitude float ,
    @dropoff_latitude float ,
    @dropoff_longitude float',
    @model = @lmodel2,
    @passenger_count =@passenger_count ,
    @trip_distance=@trip_distance,
    @trip_time_in_secs=@trip_time_in_secs,
    @pickup_latitude=@pickup_latitude,
    @pickup_longitude=@pickup_longitude,
    @dropoff_latitude=@dropoff_latitude,
    @dropoff_longitude=@dropoff_longitude
WITH RESULT SETS ((Score float));
END
GO

PredictTipSingleModeRxPy

Die folgende gespeicherte Prozedur PredictTipSingleModeRxPy führt die Bewertung mithilfe des Modells revoscalepy durch.

DROP PROCEDURE IF EXISTS PredictTipSingleModeRxPy;
GO

CREATE PROCEDURE [dbo].[PredictTipSingleModeRxPy] (@model varchar(50), @passenger_count int = 0,
  @trip_distance float = 0,
  @trip_time_in_secs int = 0,
  @pickup_latitude float = 0,
  @pickup_longitude float = 0,
  @dropoff_latitude float = 0,
  @dropoff_longitude float = 0)
AS
BEGIN
DECLARE @inquery nvarchar(max) = N'
  SELECT * FROM [dbo].[fnEngineerFeatures]( 
    @passenger_count,
    @trip_distance,
    @trip_time_in_secs,
    @pickup_latitude,
    @pickup_longitude,
    @dropoff_latitude,
    @dropoff_longitude)
  '
DECLARE @lmodel2 varbinary(max) = (select model from nyc_taxi_models where name = @model);
EXEC sp_execute_external_script 
  @language = N'Python',
  @script = N'
import pickle;
import numpy;
from revoscalepy.functions.RxPredict import rx_predict;

# Load model and unserialize
mod = pickle.loads(model)

# Get features for scoring from input data
X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]

# Score data to get tip prediction probability as a list (of float)

probArray = rx_predict(mod, X)

probList = []
probList = probArray["tipped_Pred"].values

# Create output data frame
OutputDataSet = pandas.DataFrame(data = probList, columns = ["predictions"])
',
  @input_data_1 = @inquery,
  @params = N'@model varbinary(max),@passenger_count int,@trip_distance float,
    @trip_time_in_secs int ,
    @pickup_latitude float ,
    @pickup_longitude float ,
    @dropoff_latitude float ,
    @dropoff_longitude float',
    @model = @lmodel2,
    @passenger_count =@passenger_count ,
    @trip_distance=@trip_distance,
    @trip_time_in_secs=@trip_time_in_secs,
    @pickup_latitude=@pickup_latitude,
    @pickup_longitude=@pickup_longitude,
    @dropoff_latitude=@dropoff_latitude,
    @dropoff_longitude=@dropoff_longitude
WITH RESULT SETS ((Score float));
END
GO

Generieren von Bewertungen aus Modellen

Nachdem die gespeicherte Prozedur erstellt wurde, ist es einfach, eine Bewertung auf der Grundlage eines der beiden Modelle zu erstellen. Öffnen Sie ein neues Abfragefenster, und geben Sie für jede Featurespalte Parameter an.

Für diese Featurespalten sind die folgenden sieben Werte erforderlich:

passenger_count
trip_distance
trip_time_in_secs
pickup_latitude
pickup_longitude
dropoff_latitude
dropoff_longitude

Beispiel:

Führen Sie diese Anweisung aus, um eine Vorhersage mithilfe des Modells revoscalepy zu generieren:

EXEC [dbo].[PredictTipSingleModeRxPy] 'revoscalepy_model', 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303

Führen Sie diese Anweisung aus, um eine Bewertung mithilfe des Modells scikit-learn zu generieren:

EXEC [dbo].[PredictTipSingleModeSciKitPy] 'SciKit_model', 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303

Die Ausgabe beider Prozeduren entspricht der Wahrscheinlichkeit, dass ein Trinkgeld für die Taxifahrt mit den angegebenen Parametern bzw. Eigenschaften gegeben wird.

Zusammenfassung

In dieser Tutorialreihe haben Sie erfahren, wie Sie mit Python-Code arbeiten, der in gespeicherten Prozeduren eingebettet ist. Die Integration mit Transact-SQL macht es einfacher, Python-Modelle für Vorhersagen bereitzustellen und das erneute Training von Modellen im Rahmen eines Unternehmensdaten-Workflows zu integrieren.

Nächste Schritte

In diesem Artikel führen Sie folgende Schritte aus:

Sie haben gespeicherte Prozeduren für die Batchbewertung erstellt und verwendet.
Sie haben gespeicherte Prozeduren zur Bewertung einer einzelnen Zeile erstellt und verwendet.

Weitere Informationen über Python finden Sie unter Python-Erweiterung in SQL Server.