Vom Anfang des Tutorials

In-Database Python-Analyse für SQL-Entwickler

Vorheriger Schritt

Schritt 5: Trainieren und Speichern eines Modells mit T-SQL

Schritt 6: Verwenden Sie das Modell

In diesem Schritt lernen Sie, wie Sie das im vorherigen Schritt trainierte Modell verwenden. "Verwenden" bedeutet hier "Bereitstellen des Modells für die Bewertung in der Produktion". Dies ist einfach zu erweitern, da der Python-Code in der gespeicherten Prozedur enthalten ist. Rufen Sie einfach die gespeicherte Prozedur auf, um neue Beobachtungsvorhersagen aus Ihrer Anwendung zu treffen.

Es gibt zwei Möglichkeiten, das Python-Modell von einer gespeicherten Prozedur aus aufzurufen:

** Stapelbewertungsmodus **: Verwenden Sie eine SELECT-Abfrage, um mehrere Datenzeilen bereitzustellen. Die gespeicherte Prozedur gibt eine Tabelle mit Beobachtungen zurück, die der Eingabe entsprechen
** Individueller Bewertungsmodus **: Übergeben Sie eine Reihe einzelner Parameterwerte als Eingabe. Die gespeicherte Prozedur gibt einen einzelnen Datensatz oder Wert zurück.

Bewertung mit dem Scikit-Learn-Modell

Die gespeicherte Prozedur "PredictTipSciKitPy" verwendet das Scikit-Learn-Modell.

Die gespeicherte Prozedur "PredictTipSciKitPy" wird in SQL Server in Schritt 2 definiert: Importieren von Daten mit PowerShell in SQL Server (http://qiita.com/qio9o9/items/98df36982f1fbecdf5e7).

Erweitern Sie im Objekt-Explorer von Management Studio die Option Programmierung> Gespeicherte Prozeduren.

Klicken Sie mit der rechten Maustaste auf "PredictTipSciKitPy" und wählen Sie "Ändern", um das Transact-SQL-Skript in einem neuen Abfragefenster zu öffnen.

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
# import pandas
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"]])

prob_array = mod.predict_proba(X)
prob_list = [item[1] for item in prob_array]

prob_array = numpy.asarray(prob_list)
fpr, tpr, thresholds = metrics.roc_curve(y, prob_array)
auc_result = metrics.auc(fpr, tpr)
print("AUC on testing data is:", auc_result)

OutputDataSet = pandas.DataFrame(data=prob_list, 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

Geben Sie den Modellnamen an, der in den Eingabeparametern der gespeicherten Prozedur verwendet werden soll.
Holen Sie sich das serialisierte Modell aus der Tabelle nyc_taxi_models basierend auf dem angegebenen Modellnamen.
Das serialisierte Modell wird in der Python-Variablen mod gespeichert.
Der neue bewertete Fall stammt aus der Transact-SQL-Abfrage, die durch "@ input_data_1" angegeben wird. Die Ergebnisse dieser Abfrage werden im Standarddatenrahmen "InputDat aSet" gespeichert. --Dieser Datenrahmen wird an die Funktion prädict_proba des logistischen Regressionsmodells mod übergeben, das mit dem scicit-learn-Modell erstellt wurde.
Die Funktion "Predict_Proba" gibt einen Float-Wert zurück, der die Wahrscheinlichkeit angibt, einen Chip in beliebiger Höhe zu erhalten.
Berechnen Sie zusätzlich die Genauigkeitsmetrik "AUC (Fläche unter der Kurve)". Genauigkeitsmetriken wie AUC werden nur generiert, wenn Sie zusätzlich zu den erklärenden Variablen die Zielvariable (dh die getippte Spalte) angeben. Für die Vorhersage ist die Zielvariable (Variable Y) nicht erforderlich, sie wird jedoch zur Berechnung der Genauigkeitsmetriken benötigt. Wenn die zu bewertenden Daten keine objektive Variable haben, entfernen Sie daher den AUC-Berechnungsblock aus dem Quellcode und ändern Sie die gespeicherte Prozedur, um einfach die Wahrscheinlichkeit des Empfangs eines Chips durch die erklärende Variable (Variable X) zurückzugeben.

Bewertung mit dem Revoscalepy-Modell

Die gespeicherte Prozedur "PredictTipRxPy" verwendet ein Modell, das mit der revoscalepy-Bibliothek erstellt wurde. Dies funktioniert ähnlich wie die Prozedur "PredictTipSciKitPy", jedoch mit einigen Änderungen an der Revoscalepy-Funktion.

Die gespeicherte Prozedur "PredictTipRxPy" wird in SQL Server durch Schritt 2 definiert: Importieren von Daten in SQL Server mithilfe von PowerShell (http://qiita.com/qio9o9/items/98df36982f1fbecdf5e7).

Erweitern Sie im Objekt-Explorer von Management Studio die Option Programmierung> Gespeicherte Prozeduren.

Klicken Sie mit der rechten Maustaste auf "PredictTipRxPy" und wählen Sie "Ändern", um das Transact-SQL-Skript in einem neuen Abfragefenster zu öffnen.

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
# import pandas
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"]])

prob_array = rx_predict(mod, X)
prob_list = prob_array["tipped_Pred"].values

prob_array = numpy.asarray(prob_list)
fpr, tpr, thresholds = metrics.roc_curve(y, prob_array)
auc_result = metrics.auc(fpr, tpr)
print("AUC on testing data is:", auc_result)
OutputDataSet = pandas.DataFrame(data=prob_list, 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

Führen Sie eine Chargenbewertung durch

Die gespeicherten Prozeduren "PredictTipSciKitPy" und "PredictTipRxPy" erfordern zwei Eingabeparameter.

--Abfrage zum Extrahieren der zu bewertenden Daten

Trainierte Modellkennung für die Bewertung

In diesem Abschnitt erfahren Sie, wie Sie diese Argumente an eine gespeicherte Prozedur übergeben, um sowohl das Modell als auch die für die Bewertung verwendeten Daten einfach zu ändern.

Definieren Sie die Eingabedaten und rufen Sie die gespeicherte Prozedur zur Bewertung wie folgt auf: In diesem Beispiel wird die gespeicherte Prozedur "PredictTipSciKitPy" für die Bewertung verwendet und der Modellname und die Abfragezeichenfolge übergeben.

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 einen vorhergesagten Wert der Wahrscheinlichkeit des Empfangs eines Chips für jeden Operationsdatensatz zurück, der als Teil einer Eingabeabfrage übergeben wird. Die vorhergesagten Werte werden im Ergebnisbereich von Management Studio angezeigt. Zusätzlich wird die Genauigkeitsmetrik "AUC (Bereich unter der Kurve)" im Nachrichtenbereich ausgegeben.

Um das Revoscalepy-Modell für die Bewertung zu verwenden, rufen Sie die gespeicherte Prozedur PredictTipRxPy auf.

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;

Individuelle Wertung durchführen

In einigen Fällen möchten Sie anstelle der Stapelbewertung einen einzelnen Fall übergeben und ein einzelnes Ergebnis basierend auf diesem Wert erhalten. Konfigurieren Sie beispielsweise ein Excel-Arbeitsblatt, eine Webanwendung oder einen Reporting Services-Bericht so, dass eine gespeicherte Prozedur basierend auf Benutzereingaben aufgerufen wird.

In diesem Abschnitt erfahren Sie, wie Sie die gespeicherten Prozeduren "PredictTipSingleModeSciKitPy" und "PredictTipSingleModeRxPy" aufrufen, um eine einzelne Vorhersage zu erstellen.

Die gespeicherten Prozeduren "PredictTipSingleModeSciKitPy" und "PredictTipSingleModeRxPy" werden in SQL Server über Schritt 2: Daten mit PowerShell in SQL Server importieren definiert.

Erweitern Sie im Objekt-Explorer von Management Studio die Option Programmierung> Gespeicherte Prozeduren.
Klicken Sie mit der rechten Maustaste auf "PredictTipSingleModeSciKitPy" oder "PredictTipSingleModeRxPy" und wählen Sie "Ändern", um das Transact-SQL-Skript in einem neuen Abfragefenster zu öffnen.

Diese gespeicherten Prozeduren verwenden die Modelle scicit-learn und revoscalepy und führen die Bewertung wie folgt durch:

Der Modellname und mehrere Einzelwerte werden als Eingabe bereitgestellt. Diese Eingaben umfassen die Anzahl der Passagiere, die Fahrstrecke usw.
Die tabellenwertige Funktion fnEngineerFeatures verwendet Breiten- und Längengrade als Eingabe und konvertiert sie direkt in Entfernungen.
Stellen Sie beim Aufrufen einer gespeicherten Prozedur aus einer externen Anwendung sicher, dass die Eingabedaten mit den erforderlichen Eingabefunktionen des Python-Modells übereinstimmen. Dies umfasst das Umwandeln von Eingabedaten in Python-Datentypen und das Überprüfen von Datentypen und Datenlängen.
Die gespeicherte Prozedur erstellt eine Bewertung basierend auf dem gespeicherten Python-Modell.

Unten finden Sie die gespeicherte Prozedur "PredictTipSingleModeSciKitPy", die das Scoring mit dem Scikit-Learn-Modell durchführt.

```SQL:PredictTipSingleModeSciKitPy
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
# import pandas

# 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)
prob = [mod.predict_proba(X)[0][1]]

# Create output data frame
OutputDataSet = pandas.DataFrame(data=prob, 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
```

Unten finden Sie die gespeicherte Prozedur "PredictTipSingleModeRxPy", die die Bewertung mithilfe des Revoscalepy-Modells durchführt.

```SQL
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
# import pandas
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)

prob_array = rx_predict(mod, x)

prob_list = prob_array["tipped_Pred"].values

# Create output data frame
OutputDataSet = pandas.DataFrame(data=prob_list, 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
```

Öffnen Sie in Management Studio ein neues Abfragefenster und geben Sie die Spalte mit den erklärenden Variablen ein, um die gespeicherte Prozedur aufzurufen.

-- Call stored procedure PredictTipSingleModeSciKitPy to score using SciKit-Learn model
EXEC [dbo].[PredictTipSingleModeSciKitPy] 'SciKit_model', 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303
-- Call stored procedure PredictTipSingleModeRxPy to score using revoscalepy model
EXEC [dbo].[PredictTipSingleModeRxPy] 'revoscalepy_model', 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303

Die sieben erklärenden Variablenwerte sind in der folgenden Reihenfolge aufgeführt: - passenger_count - trip_distance - trip_time_in_secs - pickup_latitude - pickup_longitude - dropoff_latitude - dropoff_longitude

Infolgedessen wird die Wahrscheinlichkeit zurückgegeben, dass der Chip in der Operation mit den obigen Parametern bezahlt wird.

![sqldev-python-step6-5-gho9o9.png](https://qiita-image-store.s3.amazonaws.com/0/195839/27bc40a6-07ae-595d-fec1-4c030e6c04ab.png)

Zusammenfassung

In diesem Tutorial haben Sie gelernt, wie Sie mit Python-Code arbeiten, der in gespeicherte Prozeduren eingebettet ist. Wir haben festgestellt, dass die Integration in Transact-SQL es noch einfacher macht, Python-Modelle zur Vorhersage bereitzustellen und die Modellumschulung als Teil eines Unternehmensdaten-Workflows einzubeziehen.

Verknüpfung

[In-Database Python Analysis-Lernprogramm mit SQL Server 2017] Schritt 6: Verwenden des Modells

Vom Anfang des Tutorials

Vorheriger Schritt

Schritt 6: Verwenden Sie das Modell

Bewertung mit dem Scikit-Learn-Modell

Bewertung mit dem Revoscalepy-Modell

Führen Sie eine Chargenbewertung durch

Individuelle Wertung durchführen

Zusammenfassung

Verknüpfung

Vorheriger Schritt

Vom Anfang des Tutorials

Quelle

Verwandte Artikel