Kurz Pythonu: Spouštění předpovědí pomocí Pythonu vloženého do uložené procedury

Platí pro: SQL Server 2017 (14.x) a novější verze Azure SQL Managed Instance

V pětidílné sérii kurzů se dozvíte, jak zprovoznit modely, které jste natrénovali a uložili v předchozí části.

V tomto scénáři zprovoznění znamená nasazení modelu do produkčního prostředí pro vyhodnocování. Díky integraci s SQL Serverem je to poměrně snadné, protože kód Pythonu můžete vložit do uložené procedury. Pokud chcete získat předpovědi z modelu na základě nových vstupů, stačí volat uloženou proceduru z aplikace a předat nová data.

Tato část kurzu ukazuje dvě metody pro vytváření předpovědí založených na modelu Pythonu: dávkové bodování a bodování řádku po řádcích.

Dávkové vyhodnocování: Pokud chcete zadat více řádků vstupních dat, předávejte dotaz SELECT jako argument uložené procedury. Výsledkem je tabulka pozorování odpovídající vstupním případům.
Individuální bodování: Předejte sadu hodnot jednotlivých parametrů jako vstup. Uložená procedura vrátí jeden řádek nebo hodnotu.

Veškerý kód Pythonu potřebný pro bodování se poskytuje jako součást uložených procedur.

V tomto článku:

Vytvoření a použití uložených procedur pro dávkové vyhodnocování
Vytvoření a použití uložených procedur pro bodování jednoho řádku

V první části jste nainstalovali požadavky a obnovili ukázkovou databázi.

Ve druhé části jste prozkoumali ukázková data a vygenerovali několik grafů.

Ve třetí části jste se naučili vytvářet funkce z nezpracovaných dat pomocí funkce Transact-SQL. Potom jste tuto funkci volali z uložené procedury a vytvořili tabulku obsahující hodnoty funkcí.

Ve čtvrté části jste načetli moduly a volali potřebné funkce k vytvoření a trénování modelu pomocí uložené procedury SQL Serveru.

Dávkové vyhodnocování

První dvě uložené procedury vytvořené pomocí následujících skriptů ilustrují základní syntaxi pro zabalení volání předpovědi Pythonu do uložené procedury. Obě uložené procedury vyžadují tabulku dat jako vstupy.

Název modelu, který se má použít, se poskytuje jako vstupní parametr uložené procedury. Uložená procedura načte serializovaný model z tabulky nyc_taxi_modelsdatabáze .table pomocí příkazu SELECT v uložené proceduře.
Serializovaný model je uložen v proměnné mod Pythonu pro další zpracování pomocí Pythonu.
Nové případy, které musí být vyhodnoceny, jsou získány z Transact-SQL dotazu zadaného v @input_data_1. Při čtení dat dotazu se řádky ukládají do výchozího datového rámce InputDataSet.
Obě uložené procedury používají funkce z sklearn k výpočtu metriky přesnosti, AUC (oblast pod křivkou). Metriky přesnosti, jako je AUC, se dají vygenerovat jenom v případě, že zadáte také cílové označení (tipped sloupec). Předpovědi nepotřebují cílový popisek (proměnnou y), ale výpočet metriky přesnosti ano.

Pokud tedy nemáte cílové popisky pro data, která mají být vyhodnocena, můžete upravit uloženou proceduru tak, aby se odebraly výpočty AUC, a vrátit pouze pravděpodobnosti tipu z funkcí (proměnná X v uložené proceduře).

PredictTipSciKitPy

Spuštěním následujících příkazů T-SQL vytvořte uloženou proceduru PredictTipSciKitPy. Tato uložená procedura vyžaduje model založený na balíčku scikit-learn, protože používá funkce specifické pro tento balíček.

Datový rámec obsahující vstupy se předává funkci predict_proba logistického regresního modelu mod. Funkce predict_proba (probArray = mod.predict_proba(X)) vrátí hodnotu float , která představuje pravděpodobnost, že bude udělen tip (libovolné částky).

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

Spuštěním následujících příkazů T-SQL vytvořte uloženou proceduru PredictTipRxPy. Tato uložená procedura používá stejné vstupy a vytvoří stejný typ skóre jako předchozí uložená procedura, ale používá funkce z balíčku revoscalepy , který je součástí strojového učení SQL Serveru.

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

Spuštění dávkového bodování pomocí dotazu SELECT

Uložené procedury PredictTipSciKitPy a PredictTipRxPy vyžadují dva vstupní parametry:

Dotaz, který načte data pro bodování
Název natrénovaného modelu

Předáním těchto argumentů uložené proceduře můžete vybrat konkrétní model nebo změnit data použitá k bodování.

Pokud chcete použít model scikit-learn pro bodování, zavolejte uloženou proceduru PredictTipSciKitPy a předejte název modelu a řetězec dotazu jako vstupy.
```
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;
```
Uložená procedura vrátí predikované pravděpodobnosti pro každou cestu, která byla předána jako součást vstupního dotazu.

Pokud pro spouštění dotazů používáte SSMS (SQL Server Management Studio), pravděpodobnosti se zobrazí jako tabulka v podokně Výsledky . Podokno Zprávy vypíše metriku přesnosti (AUC nebo oblast pod křivkou) s hodnotou přibližně 0,56.

Pokud chcete použít model revoscalepy pro bodování, zavolejte uloženou proceduru PredictTipRxPy a předejte název modelu a řetězec dotazu jako vstupy.

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;

Bodování s jedním řádkem

Někdy místo hromadného vyhodnocení můžete chtít zpracovat jeden případ, získat hodnoty z aplikace a vrátit jednotlivý výsledek na základě těchto hodnot. Můžete například nastavit excelový list, webovou aplikaci nebo sestavu tak, aby volal uloženou proceduru a předával do ní vstupy zadané nebo vybrané uživateli.

V této části se dozvíte, jak vytvořit jednoduché předpovědi voláním dvou uložených procedur:

PredictTipSingleModeSciKitPy je navržený pro skórování jednotlivých řádků pomocí modelu scikit-learn.
PredictTipSingleModeRxPy je navržený pro jednorázové vyhodnocení pomocí modelu revoscalepy.
Pokud jste model ještě nenatrénovali, vraťte se do pěti částí.

Oba modely berou jako vstup řadu jednoduchých hodnot, jako je počet cestujících, vzdálenost jízdy atd. Funkce fnEngineerFeaturess hodnotou tabulky slouží k převodu hodnot zeměpisné šířky a délky ze vstupů na novou funkci, přímou vzdálenost. Část 4 obsahuje popis této tabulkové funkce.

Obě uložené procedury vytvářejí skóre založené na modelu Pythonu.

Poznámka:

Při volání uložené procedury z externí aplikace je důležité zadat všechny vstupní funkce vyžadované modelem Pythonu. Abyste se vyhnuli chybám, možná budete muset přetypovat nebo převést vstupní data na datový typ Pythonu, a to kromě ověřování datového typu a délky dat.

PredictTipSingleModeSciKitPy

Následující uložená procedura PredictTipSingleModeSciKitPy provádí bodování pomocí modelu scikit-learn .

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

Následující uložená procedura PredictTipSingleModeRxPy provádí bodování pomocí modelu revoscalepy .

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

Generování skóre z modelů

Po vytvoření uložených procedur je snadné vygenerovat skóre na základě některého modelu. Otevřete nové okno dotazu a zadejte parametry pro každý sloupec funkce.

Sedm požadovaných hodnot pro tyto sloupce funkcí je v pořadí:

passenger_count
trip_distance
trip_time_in_secs (čas výletu v sekundách)
pickup_latitude
pickup_longitude
dropoff_latitude
dropoff_longitude

Například:

Pokud chcete vygenerovat předpověď pomocí modelu revoscalepy , spusťte tento příkaz:

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

Pokud chcete vygenerovat skóre pomocí modelu scikit-learn , spusťte tento příkaz:

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

Výstupem obou postupů je pravděpodobnost placení tipu za jízdu taxíkem se zadanými parametry nebo funkcemi.

Conclusion

V této sérii kurzů jste se naučili pracovat s kódem Pythonu vloženým do uložených procedur. Integrace s Transact-SQL usnadňuje nasazení modelů Pythonu pro predikci a začlenění opětovného trénování modelu jako součásti pracovního postupu podnikových dat.

Další kroky