Samouczek języka Python: uruchamianie przewidywań przy użyciu języka Python osadzonego w procedurze składowanej

Dotyczy: SQL Server 2017 (14.x) i nowsze wersje usługi Azure SQL Managed Instance

W piątej części tej pięcioczęściowej serii samouczków dowiesz się, jak operacjonalizować modele, które zostały wytrenowane i zapisane w poprzedniej części.

W tym scenariuszu operacjonalizacja oznacza wdrożenie modelu w środowisku produkcyjnym na potrzeby oceniania. Integracja z programem SQL Server sprawia, że jest to dość proste, ponieważ można osadzić kod języka Python w procedurze składowanej. Aby uzyskać przewidywania z modelu na podstawie nowych danych wejściowych, wystarczy wywołać procedurę składowaną z aplikacji i przekazać nowe dane.

W tej części samouczka przedstawiono dwie metody tworzenia przewidywań na podstawie modelu języka Python: ocenianie wsadowe i ocenianie wierszy według wierszy.

Ocenianie zbiorcze: Aby podać wiele wierszy danych wejściowych, przekaż zapytanie SELECT jako argument do procedury składowanej. Wynikiem jest tabela obserwacji odpowiadających przypadkom wejściowym.
Indywidualne ocenianie: Przekaż zestaw pojedynczych wartości parametrów jako dane wejściowe. Procedura składowana zwraca pojedynczy wiersz lub wartość.

Cały kod języka Python potrzebny do oceniania jest dostarczany w ramach procedur składowanych.

W tym artykule dowiesz się:

Tworzenie i używanie procedur składowanych na potrzeby oceniania wsadowego
Tworzenie i używanie procedur składowanych do oceniania pojedynczego wiersza

W części 1 zainstalowano wymagania wstępne i przywrócono przykładową bazę danych.

W drugiej części zapoznaliśmy się z przykładowymi danymi i wygenerowaliśmy niektóre wykresy.

W trzeciej części przedstawiono sposób tworzenia funkcji na podstawie danych pierwotnych przy użyciu funkcji Transact-SQL. Następnie wywołaliśmy tę funkcję z procedury składowanej, aby utworzyć tabelę zawierającą wartości funkcji.

W czwartej części załadowano moduły i wywołano niezbędne funkcje do utworzenia i wytrenowania modelu przy użyciu procedury składowanej programu SQL Server.

Wsadowe ocenianie

Pierwsze dwie procedury składowane utworzone przy użyciu poniższych skryptów ilustrują podstawową składnię zawijania wywołania przewidywania języka Python w procedurze składowanej. Obie procedury składowane wymagają tabeli danych jako danych wejściowych.

Nazwa modelu, którego należy użyć, jest podawana jako parametr wejściowy do procedury składowanej. Procedura składowana ładuje serializowany model z tabeli nyc_taxi_modelsbazy danych .table przy użyciu instrukcji SELECT w procedurze składowanej.
Serializowany model jest przechowywany w zmiennej mod języka Python do dalszego przetwarzania przy użyciu języka Python.
Nowe przypadki, które należy ocenić, są uzyskiwane z zapytania Transact-SQL określonego w pliku @input_data_1. Podczas odczytywania danych zapytania wiersze są zapisywane w domyślnej ramce danych . InputDataSet
Obie procedury składowane używają funkcji z sklearn , aby obliczyć metrykę dokładności, AUC (obszar pod krzywą). Metryki dokładności, takie jak AUC, można wygenerować tylko wtedy, gdy podasz również etykietę docelową (kolumnę przechylona ). Przewidywania nie wymagają etykiety docelowej (zmiennej y), ale obliczenia metryki dokładności jej potrzebują.

W związku z tym, jeśli nie masz etykiet docelowych dla danych do oceny, możesz zmodyfikować procedurę składowaną, aby usunąć obliczenia AUC i zwrócić tylko prawdopodobieństwa z funkcji (zmienna X w procedurze składowanej).

PredictTipSciKitPy

Uruchom następujące instrukcje języka T-SQL, aby utworzyć procedurę składowaną PredictTipSciKitPy. Ta procedura składowana wymaga modelu opartego na pakiecie scikit-learn, ponieważ używa funkcji specyficznych dla tego pakietu.

Ramka danych zawierająca dane wejściowe jest przekazywana do predict_proba funkcji modelu regresji logistycznej, mod. Funkcja predict_proba (probArray = mod.predict_proba(X)) zwraca float reprezentujący prawdopodobieństwo, że zostanie dany napiwek (dowolnej wysokości).

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

Uruchom następujące instrukcje języka T-SQL, aby utworzyć procedurę składowaną PredictTipRxPy. Ta procedura składowana używa tych samych danych wejściowych i tworzy ten sam typ wyników co poprzednia procedura składowana, ale używa funkcji z pakietu revoscalepy dostarczonego z uczeniem maszynowym programu SQL Server.

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

Wykonywanie oceniania wsadowego przy użyciu zapytania SELECT

Procedury składowane PredictTipSciKitPy i PredictTipRxPy wymagają dwóch parametrów wejściowych:

Zapytanie, które pobiera dane na potrzeby oceniania
Nazwa wytrenowanego modelu

Przekazując te argumenty do procedury składowanej, można wybrać określony model lub zmienić dane używane do oceniania.

Aby użyć modelu scikit-learn do oceniania, wywołaj procedurę składowaną PredictTipSciKitPy, przekazując nazwę modelu i ciąg zapytania jako dane wejściowe.
```
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;
```
Procedura składowana zwraca przewidywane prawdopodobieństwa dla każdej podróży przekazanej w ramach zapytania wejściowego.

Jeśli używasz programu SSMS (SQL Server Management Studio) do uruchamiania zapytań, prawdopodobieństwo będzie wyświetlane jako tabela w okienku Wyniki . Okienko Komunikaty zwraca metrykę dokładności (AUC lub obszar pod krzywą) z wartością około 0,56.

Aby użyć modelu revoscalepy do oceniania, wywołaj procedurę składowaną PredictTipRxPy, przekazując nazwę modelu i ciąg zapytania jako dane wejściowe.

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;

Ocenianie pojedynczego wiersza

Czasami zamiast oceniania wsadowego warto przekazać pojedynczy przypadek, uzyskać wartości z aplikacji i zwrócić pojedynczy wynik na podstawie tych wartości. Można na przykład skonfigurować arkusz programu Excel, aplikację internetową lub raport w celu wywołania procedury składowanej i przekazania do niego danych wejściowych wpisanych lub wybranych przez użytkowników.

W tej sekcji dowiesz się, jak utworzyć pojedyncze przewidywania, wywołując dwie procedury składowane:

PredictTipSingleModeSciKitPy jest przeznaczony do jednowierszowego oceniania przy użyciu modelu scikit-learn.
Element PredictTipSingleModeRxPy jest przeznaczony do oceniania w jednym wierszu przy użyciu modelu revoscalepy.
Jeśli jeszcze nie wytrenujesz modelu, wróć do części pięć!

Oba modele przyjmują jako dane wejściowe serii pojedynczych wartości, takich jak liczba pasażerów, odległość podróży itd. Funkcja o wartości tabeli, fnEngineerFeatures, służy do konwertowania wartości szerokości geograficznej i długości geograficznej z danych wejściowych na nową funkcję, bezpośrednią odległość. Część czwarta zawiera opis tej funkcji wartości tabeli.

Obie procedury składowane tworzą ocenę na podstawie modelu języka Python.

Uwaga / Notatka

Podczas wywoływania procedury składowanej z aplikacji zewnętrznej należy podać wszystkie funkcje wejściowe wymagane przez model języka Python. Aby uniknąć błędów, może być konieczne rzutowanie lub konwertowanie danych wejściowych na typ danych języka Python oprócz sprawdzania poprawności typu danych i długości danych.

PredictTipSingleModeSciKitPy

Poniższa procedura przechowywana PredictTipSingleModeSciKitPy wykonuje ocenianie przy użyciu 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

Poniższa procedura przechowywana PredictTipSingleModeRxPy wykonuje ocenianie przy użyciu 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

Generowanie wyników na podstawie modeli

Po utworzeniu przechowywanych procedur łatwo jest wygenerować wynik na podstawie każdego modelu. Otwórz nowe okno Zapytanie i podaj parametry dla każdej z kolumn funkcji.

Siedem wymaganych wartości dla tych kolumn funkcji jest w następującej kolejności:

passenger_count
trip_distance
czas_przejazdu_w_sekundach
pickup_latitude
pickup_longitude
dropoff_latitude
dropoff_longitude

Przykład:

Aby wygenerować przewidywanie przy użyciu modelu revoscalepy , uruchom następującą instrukcję:

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

Aby wygenerować wynik przy użyciu modelu scikit-learn , uruchom następującą instrukcję:

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

Dane wyjściowe obu procedur to prawdopodobieństwo, że napiwek zostanie zapłacony za przejazd taksówką z określonymi parametrami lub cechami.

Podsumowanie

W tej serii samouczków nauczyłeś się, jak pracować z kodem języka Python osadzonym w procedurach składowanych. Integracja z Transact-SQL znacznie ułatwia wdrażanie modeli języka Python na potrzeby przewidywania i dołączanie ponownego trenowania modelu w ramach przepływu pracy danych przedsiębiorstwa.

Dalsze kroki