Dès le début du tutoriel

Analyse Python en base de données pour les développeurs SQL

Étape précédente

Étape 5: Formation et enregistrement d'un modèle à l'aide de T-SQL

Étape 6: utiliser le modèle

Dans cette étape, vous apprendrez à utiliser le modèle formé à l'étape précédente. «Utiliser» signifie ici «déployer le modèle en production pour le scoring». Cela est facile à développer car le code Python est contenu dans la procédure stockée. Appelez simplement la procédure stockée pour effectuer de nouvelles prédictions d'observation à partir de votre application.

Il existe deux façons d'appeler le modèle Python à partir d'une procédure stockée:

** Mode de scoring par lots **: utilisez une requête SELECT pour diffuser plusieurs lignes de données. La procédure stockée renvoie une table d'observations correspondant à l'entrée
** Mode de notation individuelle **: transmettez un ensemble de valeurs de paramètres individuels en entrée. La procédure stockée renvoie un seul enregistrement ou valeur.

Notation à l'aide du modèle scikit-learn

La procédure stockée PredictTipSciKitPy utilise le modèle scikit-learn.

La procédure stockée PredictTipSciKitPy est définie dans SQL Server à l'étape 2: Importer des données vers SQL Server à l'aide de PowerShell (http://qiita.com/qio9o9/items/98df36982f1fbecdf5e7).

Dans l'Explorateur d'objets de Management Studio, développez Programmation> Procédures stockées.

Cliquez avec le bouton droit sur PredictTipSciKitPy et sélectionnez Modifier pour ouvrir le script Transact-SQL dans une nouvelle fenêtre de requête.

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

Spécifiez le nom du modèle à utiliser dans les paramètres d'entrée de la procédure stockée.
Récupérez le modèle sérialisé dans la table nyc_taxi_models en fonction du nom de modèle spécifié.
Le modèle sérialisé est stocké dans la variable Python mod.
Le nouveau cas noté est extrait de la requête Transact-SQL spécifiée par @ input_data_1. Le résultat de cette requête est enregistré dans le bloc de données par défaut ʻInputDat a Set. --Cette trame de données est transmise à la fonction predict_proba du modèle de régression logistique mod` créée à l'aide du modèle scicit-learn.
La fonction predict_proba renvoie une valeur flottante qui indique la probabilité de recevoir un jeton de n'importe quel montant.
De plus, calculez la métrique de précision ʻAUC (aire sous la courbe) `. Les métriques de précision telles que AUC ne sont générées que si vous spécifiez la variable objectif (c'est-à-dire la colonne tapée) en plus des variables explicatives. La prédiction ne nécessite pas la variable objective (variable Y), mais elle est nécessaire pour calculer les métriques d'exactitude. Par conséquent, si les données à noter n'ont pas de variable objective, supprimez le bloc de calcul AUC du code source et modifiez la procédure stockée pour renvoyer simplement la probabilité de recevoir une puce par la variable explicative (variable X).

Scoring à l'aide du modèle Revoscalepy

La procédure stockée PredictTipRxPy utilise un modèle créé à l'aide de la bibliothèque revoscalepy. Cela fonctionne un peu comme la procédure PredictTipSciKitPy, mais avec quelques modifications de la fonction revoscalepy.

La procédure stockée PredictTipRxPy est définie dans SQL Server à l'étape 2: Importer des données vers SQL Server à l'aide de PowerShell (http://qiita.com/qio9o9/items/98df36982f1fbecdf5e7).

Dans l'Explorateur d'objets de Management Studio, développez Programmation> Procédures stockées.

Cliquez avec le bouton droit de la souris sur PredictTipRxPy et sélectionnez Modifier pour ouvrir le script Transact-SQL dans une nouvelle fenêtre de requête.

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

Effectuer une notation par lots

Les procédures stockées «PredictTipSciKitPy» et «PredictTipRxPy» nécessitent deux paramètres d'entrée.

Requête pour extraire les données à noter
Identifiant du modèle formé utilisé pour la notation

Dans cette section, vous apprendrez à transmettre ces arguments à une procédure stockée pour modifier facilement à la fois le modèle et les données utilisées pour le scoring.

Définissez les données d'entrée et appelez la procédure stockée pour l'évaluation comme suit: Cet exemple utilise la procédure stockée PredictTipSciKitPy pour le scoring et transmet le nom du modèle et la chaîne de requête.

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;

La procédure stockée renvoie une valeur prédite de la probabilité de recevoir une puce pour chaque enregistrement d'opération transmis dans le cadre d'une requête d'entrée. Les valeurs prévues sont affichées dans le volet des résultats de Management Studio. De plus, la métrique de précision ʻAUC (aire sous la courbe) `est sortie dans le volet des messages.

Pour utiliser le modèle revoscalepy pour le scoring, appelez la procédure stockée PredictTipRxPy.

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;

Effectuer une notation individuelle

Dans certains cas, au lieu du scoring par lots, vous souhaitez transmettre un seul cas et obtenir un seul résultat basé sur cette valeur. Par exemple, configurez une feuille de calcul Excel, une application Web ou un rapport Reporting Services pour appeler une procédure stockée en fonction de l'entrée utilisateur.

Dans cette section, vous apprendrez à appeler les procédures stockées «PredictTipSingleModeSciKitPy» et «PredictTipSingleModeRxPy» pour créer une seule prédiction.

Les procédures stockées «PredictTipSingleModeSciKitPy» et «PredictTipSingleModeRxPy» sont définies dans SQL Server via Étape 2: Importer des données dans SQL Server à l'aide de PowerShell.

Dans l'Explorateur d'objets de Management Studio, développez Programmation> Procédures stockées.
Cliquez avec le bouton droit sur "PredictTipSingleModeSciKitPy" ou "PredictTipSingleModeRxPy" et sélectionnez Modifier pour ouvrir le script Transact-SQL dans une nouvelle fenêtre de requête.

Ces procédures stockées utilisent les modèles scicit-learn et revoscalepy et effectuent le scoring comme suit:

Le nom du modèle et plusieurs valeurs uniques sont fournis en entrée. Ces entrées incluent le nombre de passagers, la distance parcourue, etc.
La fonction table fnEngineerFeatures prend la latitude et la longitude en entrée et les convertit directement en distance.
Lors de l'appel d'une procédure stockée à partir d'une application externe, assurez-vous que les données d'entrée correspondent à la fonctionnalité d'entrée requise du modèle Python. Cela inclut la conversion des données d'entrée en types de données Python et la validation des types de données et des longueurs de données.
La procédure stockée crée un score basé sur le modèle Python stocké.

Vous trouverez ci-dessous la procédure stockée PredictTipSingleModeSciKitPy qui effectue une évaluation à l'aide du modèle scikit-learn.

```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
```

Vous trouverez ci-dessous la procédure stockée PredictTipSingleModeRxPy qui effectue la notation à l'aide du modèle revoscalepy.

```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
```

Ouvrez une nouvelle fenêtre de requête dans Management Studio et entrez la colonne de variable explicative pour appeler la procédure stockée.

-- 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

Les sept valeurs des variables explicatives sont dans l'ordre suivant: - passenger_count - trip_distance - trip_time_in_secs - pickup_latitude - pickup_longitude - dropoff_latitude - dropoff_longitude

En conséquence, la probabilité que la puce soit payée dans l'opération avec les paramètres ci-dessus est renvoyée.

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

Résumé

Dans ce didacticiel, vous avez appris à utiliser du code Python intégré dans des procédures stockées. Nous avons constaté que l'intégration avec Transact-SQL rend encore plus facile le déploiement de modèles Python pour la prédiction et l'intégration de la reconversion des modèles dans le cadre d'un flux de travail de données d'entreprise.

Lien

[Didacticiel d'analyse Python dans la base de données avec SQL Server 2017] Étape 6: Utilisation du modèle

Dès le début du tutoriel

Étape précédente

Étape 6: utiliser le modèle

Notation à l'aide du modèle scikit-learn

Scoring à l'aide du modèle Revoscalepy

Effectuer une notation par lots

Effectuer une notation individuelle

Résumé

Lien

Étape précédente

Dès le début du tutoriel

La source

Article connexe