Python-handledning: Genomföra förutsägelser med Python som är inbäddad i en lagrad procedur

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

I del fem i den här självstudieserien i fem delar får du lära dig hur du operationaliserar de modeller som du har tränat och sparat i föregående del.

I det här scenariot innebär operationalisering att modellen distribueras till produktion för bedömning. Integreringen med SQL Server gör detta ganska enkelt eftersom du kan bädda in Python-kod i en lagrad procedur. Om du vill hämta förutsägelser från modellen baserat på nya indata anropar du bara den lagrade proceduren från ett program och skickar de nya data.

Den här delen av handledningen demonstrerar två metoder för att skapa prognoser baserat på en Python-modell: batch-poängsättning och poängsättning rad för rad.

• Batchberäkning: Om du vill ange flera rader med indata skickar du en SELECT-fråga som ett argument till den lagrade proceduren. Resultatet är en tabell med observationer som motsvarar indatafallen.
• Individuell bedömning: Skicka en uppsättning enskilda parametervärden som indata. Den lagrade proceduren returnerar en enskild rad eller ett värde.

All Python-kod som behövs för bedömning tillhandahålls som en del av de lagrade procedurerna.

I den här artikeln ska du:

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

I del två utforskade du exempeldata och genererade några diagram.

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

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

Batchbedömning

De första två lagrade procedurerna som skapas med hjälp av följande skript illustrerar den grundläggande syntaxen för att omsluta ett Python-förutsägelseanrop i en lagrad procedur. Båda lagrade procedurer kräver en tabell med data som indata.

• Namnet på den modell som ska användas anges som indataparameter i den lagrade proceduren. Den lagrade proceduren läser in den serialiserade modellen från databastabellen nyc_taxi_models.table med hjälp av SELECT-instruktionen i den lagrade proceduren.

• Den serialiserade modellen lagras i Python-variabeln mod för vidare bearbetning med Python.

• De nya fallen som måste poängsättas hämtas från den Transact-SQL-fråga som specificeras i @input_data_1. När frågedatan läses sparas raderna i standarddataramen, InputDataSet.

• Båda lagrade procedurer använder funktioner från sklearn för att beräkna ett noggrannhetsmått, AUC (område under kurva). Noggrannhetsmått som AUC kan bara genereras om du även anger mål-etiketten (den märkta kolumnen). Förutsägelser behöver inte måletiketten (variabeln y), men beräkningen av noggrannhetsmåttet gör det.

  Om du inte har måletiketter för de data som ska poängsätts kan du därför ändra den lagrade proceduren för att ta bort AUC-beräkningarna och endast returnera tipsannolikheterna från funktionerna (variabel X i den lagrade proceduren).

PredictTipSciKitPy

Kör följande T-SQL-instruktioner för att skapa den lagrade proceduren PredictTipSciKitPy. Den här lagrade proceduren kräver en modell baserad på scikit-learn-paketet, eftersom den använder funktioner som är specifika för paketet.

Dataramen som innehåller indata skickas till predict_proba funktionen för den logistiska regressionsmodellen, mod. Funktionen predict_proba (probArray = mod.predict_proba(X)) returnerar ett flyttal som representerar sannolikheten att ett tips (oavsett belopp) ges.

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

Kör följande T-SQL-instruktioner för att skapa den lagrade proceduren PredictTipRxPy. Den här lagrade proceduren använder samma indata och skapar samma typ av poäng som den tidigare lagrade proceduren, men den använder funktioner från revoscalepy-paketet som medföljer SQL Server-maskininlärning.

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

Köra batchbedömning med hjälp av en SELECT-fråga

De lagrade procedurerna PredictTipSciKitPy och PredictTipRxPy kräver två indataparametrar:

• Frågan som hämtar data för bedömning
• Namnet på en tränad modell

Genom att skicka dessa argument till den lagrade proceduren kan du välja en viss modell eller ändra de data som används för bedömning.

1. Om du vill använda scikit-learn-modellen för bedömning anropar du den lagrade proceduren PredictTipSciKitPy och skickar modellnamnet och frågesträngen som indata.

   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;
   

   Den lagrade proceduren returnerar förutsagda sannolikheter för varje resa som skickades som en del av indatafrågan.

   Om du använder SSMS (SQL Server Management Studio) för att köra frågor visas sannolikheterna som en tabell i fönstret Resultat . Fönstret Meddelanden matar ut noggrannhetsmåttet (AUC eller området under kurvan) med ett värde på cirka 0,56.

2. Om du vill använda revoscalepy-modellen för bedömning anropar du den lagrade proceduren PredictTipRxPy och skickar modellnamnet och frågesträngen som indata.

   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;
   

Poängsättning med en rad

Du kanske ibland, istället för batchbearbetning, vill skicka in ett enda ärende där du hämtar värden från en applikation och returnerar ett enda resultat baserat på dessa värden. Du kan till exempel konfigurera ett Excel-kalkylblad, ett webbprogram eller en rapport för att anropa den lagrade proceduren och skicka indata som har skrivits eller valts av användarna.

I det här avsnittet får du lära dig hur du skapar enkla förutsägelser genom att anropa två lagrade procedurer:

• PredictTipSingleModeSciKitPy är utformad för att poängsätta enskilda rader med en scikit-learn-modell.
• PredictTipSingleModeRxPy är utformat för enradsbedömning med hjälp av revoscalepy-modellen.
• Om du inte har tränat en modell än kan du gå tillbaka till del fem!

Båda modellerna tar som indata en serie med enskilda värden, till exempel antal passagerare, reseavstånd och så vidare. En tabellvärdesfunktion, fnEngineerFeatures, används för att konvertera latitud- och longitudvärden från indata till en ny funktion, direkt avstånd. Del fyra innehåller en beskrivning av den här tabellvärdesfunktionen.

Båda lagrade procedurerna skapar en poäng baserat på Python-modellen.

PredictTipSingleModeSciKitPy

Följande lagrade procedur PredictTipSingleModeSciKitPy utför bedömning med hjälp av scikit-learn-modellen .

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

Följande lagrade procedur PredictTipSingleModeRxPy utför bedömning med hjälp av revoscalepy-modellen .

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

Generera poäng från modeller

När de lagrade procedurerna har skapats är det enkelt att generera en poäng baserat på någon av dessa modeller. Öppna ett nytt frågefönster och ange parametrar för var och en av funktionskolumnerna.

De sju obligatoriska värdena för dessa funktionskolumner är i ordning:

• passenger_count
• trip_distance
• restid_i_sek
• pickup_latitude
• pickup_longitude
• dropoff_latitude
• dropoff_longitude

Till exempel:

• Om du vill generera en förutsägelse med hjälp av revoscalepy-modellen kör du den här instruktionen:

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

• Om du vill generera en poäng med hjälp av scikit-learn-modellen kör du den här instruktionen:

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

Utdata från båda procedurerna är sannolikheten att ett tips betalas för taxiresan med de angivna parametrarna eller funktionerna.

Conclusion

I den här självstudieserien har du lärt dig hur du arbetar med Python-kod inbäddad i lagrade procedurer. Integreringen med Transact-SQL gör det mycket enklare att distribuera Python-modeller för förutsägelse och att införliva modellomskolning som en del av ett företagsdataarbetsflöde.

Nästa steg

I den här artikeln kommer du att:

Mer information om Python finns i Python-tillägget i SQL Server.