Python-zelfstudie: Voorspellingen uitvoeren met Python ingesloten in een opgeslagen procedure

Van toepassing op: SQL Server 2017 (14.x) en latere versies van Azure SQL Managed Instance

In deel vijf van deze vijfdelige zelfstudiereeks leert u hoe u de modellen kunt operationeel maken die u in het vorige deel hebt getraind en opgeslagen.

In dit scenario betekent operationeel maken het implementeren van het model in productie voor scoringsdoeleinden. De integratie met SQL Server maakt dit vrij eenvoudig, omdat u Python-code kunt insluiten in een opgeslagen procedure. Als u voorspellingen van het model wilt ophalen op basis van nieuwe invoer, roept u de opgeslagen procedure aan vanuit een toepassing en geeft u de nieuwe gegevens door.

In dit deel van de handleiding ziet u twee methoden voor het maken van voorspellingen op basis van een Python-model: batchverwerking en rij voor rij scoren.

• Batchgewijs scoren: Als u meerdere rijen invoergegevens wilt opgeven, geeft u een SELECT-query door als argument voor de opgeslagen procedure. Het resultaat is een tabel met waarnemingen die overeenkomen met de invoercases.
• Individuele score: Geef een set afzonderlijke parameterwaarden door als invoer. De opgeslagen procedure retourneert één rij of waarde.

Alle Python-code die nodig is om te scoren, wordt geleverd als onderdeel van de opgeslagen procedures.

In dit artikel gaat u het volgende doen:

In deel 1 hebt u de vereisten geïnstalleerd en de voorbeelddatabase hersteld.

In deel twee hebt u de voorbeeldgegevens verkend en enkele plots gegenereerd.

In deel drie hebt u geleerd hoe u functies maakt op basis van onbewerkte gegevens met behulp van een Transact-SQL-functie. Vervolgens hebt u die functie aangeroepen vanuit een opgeslagen procedure om een tabel te maken die de functiewaarden bevat.

In deel vier hebt u de modules geladen en de benodigde functies aangeroepen om het model te maken en te trainen met behulp van een opgeslagen SQL Server-procedure.

Batchscore

De eerste twee opgeslagen procedures die zijn gemaakt met behulp van de volgende scripts illustreren de basissyntaxis voor het verpakken van een Python-voorspellingsoproep in een opgeslagen procedure. Beide opgeslagen procedures vereisen een tabel met gegevens als invoer.

• De naam van het te gebruiken model wordt opgegeven als invoerparameter voor de opgeslagen procedure. Met de opgeslagen procedure wordt het geserialiseerde model uit de databasetabel nyc_taxi_models.table geladen met behulp van de SELECT-instructie in deze procedure.

• Het geserialiseerde model wordt opgeslagen in de Python-variabele mod voor verdere verwerking met behulp van Python.

• De nieuwe gevallen die moeten worden beoordeeld, worden verkregen uit de Transact-SQL query die is opgegeven in @input_data_1. Terwijl de querygegevens worden gelezen, worden de rijen opgeslagen in het standaardgegevensframe. InputDataSet

• Beide opgeslagen procedures gebruiken functies van sklearn om een nauwkeurigheidsmetriek te berekenen, AUC (gebied onder curve). Metrische gegevens voor nauwkeurigheid, zoals AUC, kunnen alleen worden gegenereerd als u ook het doellabel (de getypte kolom) opgeeft. Voorspellingen hebben het doellabel (variabele y) niet nodig, maar de berekening van de metrische nauwkeurigheid wel.

  Als u geen doelwitlabels hebt om de gegevens te scoren, kunt u de opgeslagen procedure aanpassen door de AUC-berekeningen te verwijderen en alleen de tipkansen van de kenmerken (variabele X in de opgeslagen procedure) te retourneren.

PredictTipSciKitPy

Voer de volgende T-SQL-instructies uit om de opgeslagen procedure PredictTipSciKitPyte maken. Voor deze opgeslagen procedure is een model vereist op basis van het scikit-learn-pakket, omdat er functies worden gebruikt die specifiek zijn voor dat pakket.

Het gegevensframe met invoer wordt doorgegeven aan de predict_proba functie van het logistieke regressiemodel. mod De predict_proba functie (probArray = mod.predict_proba(X)) retourneert een float die de kans aangeeft dat een tip (van een willekeurig bedrag) wordt gegeven.

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

Voer de volgende T-SQL-instructies uit om de opgeslagen procedure PredictTipRxPyte maken. Deze opgeslagen procedure maakt gebruik van dezelfde invoer en maakt hetzelfde type scores als de vorige opgeslagen procedure, maar maakt gebruik van functies uit het revoscalepy-pakket dat is geleverd met SQL Server Machine Learning.

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

Batchgewijs scoren uitvoeren met behulp van een SELECT-query

Voor de opgeslagen procedures PredictTipSciKitPy en PredictTipRxPy zijn twee invoerparameters vereist:

• De query waarmee de gegevens worden opgehaald voor scoren
• De naam van een getraind model

Door deze argumenten door te geven aan de opgeslagen procedure, kunt u een bepaald model selecteren of de gegevens wijzigen die worden gebruikt voor scoren.

1. Als u het scikit-learn-model wilt gebruiken voor scoren, roept u de opgeslagen procedure PredictTipSciKitPy aan en geeft u de modelnaam en querytekenreeks door als invoer.

   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;
   

   De opgeslagen procedure retourneert voorspelde waarschijnlijkheden voor elke reis die is doorgegeven als onderdeel van de invoerquery.

   Als u SSMS (SQL Server Management Studio) gebruikt voor het uitvoeren van query's, worden de waarschijnlijkheden weergegeven als een tabel in het deelvenster Resultaten . In het deelvenster Berichten wordt de metrische waarde voor nauwkeurigheid (AUC of gebied onder curve) uitgevoerd met een waarde van ongeveer 0,56.

2. Als u het revoscalepy-model wilt gebruiken voor scoren, roept u de opgeslagen procedure PredictTipRxPy aan en geeft u de modelnaam en querytekenreeks door als invoer.

   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;
   

Scoren met één rij

Soms wilt u in plaats van batchverwerking een enkel geval doorgeven, waarden ophalen uit een applicatie, en één resultaat retourneren op basis van deze waarden. U kunt bijvoorbeeld een Excel-werkblad, een webapplicatie of een rapport instellen om de stored procedure aan te roepen en de procedure te voorzien van invoer die door gebruikers is getypt of geselecteerd.

In deze sectie leert u hoe u enkele voorspellingen maakt door twee opgeslagen procedures aan te roepen:

• PredictTipSingleModeSciKitPy is ontworpen voor scoren met één rij met behulp van het scikit-learn-model.
• PredictTipSingleModeRxPy is ontworpen voor enkelrij-scoreberekening met behulp van het revoscalepy-model.
• Als u nog geen model hebt getraind, gaat u terug naar deel vijf.

Beide modellen nemen als invoer een reeks enkele waarden op, zoals het aantal passagiers, reisafstand, enzovoort. Een tabelwaardefunctie, fnEngineerFeatureswordt gebruikt om waarden voor breedtegraad en lengtegraad van de invoer te converteren naar een nieuwe functie, directe afstand. Deel vier bevat een beschrijving van deze tabelwaardefunctie.

Beide opgeslagen procedures maken een score op basis van het Python-model.

PredictTipSingleModeSciKitPy

De volgende opgeslagen procedure PredictTipSingleModeSciKitPy voert scoren uit met behulp van het scikit-learn-model .

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

De volgende opgeslagen procedure PredictTipSingleModeRxPy voert scoren uit met behulp van het revoscalepy-model .

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

Scores genereren op modellen

Nadat de opgeslagen procedures zijn gemaakt, is het eenvoudig om een score te genereren op basis van een van beide modellen. Open een nieuw queryvenster en geef parameters op voor elk van de functiekolommen.

De zeven vereiste waarden voor deze functiekolommen zijn in volgorde:

• passenger_count
• trip_distance
• reis_tijd_in_seconden
• pickup_latitude
• pickup_longitude
• dropoff_latitude
• dropoff_longitude

Voorbeeld:

• Als u een voorspelling wilt genereren met behulp van het revoscalepy-model , voert u deze instructie uit:

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

• Als u een score wilt genereren met behulp van het scikit-learn-model , voert u deze instructie uit:

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

De uitvoer van beide procedures is een kans dat een tip wordt betaald voor de taxirit met de opgegeven parameters of kenmerken.

Conclusion

In deze reeks zelfstudies hebt u geleerd hoe u kunt werken met Python-code die is ingesloten in opgeslagen procedures. De integratie met Transact-SQL maakt het veel eenvoudiger om Python-modellen te implementeren voor voorspellingen en om het opnieuw trainen van modellen op te nemen als onderdeel van een werkstroom voor zakelijke gegevens.

Volgende stappen

In dit artikel gaat u als volgt te werk:

Zie de Python-extensie in SQL Server voor meer informatie over Python.