Déployer et utiliser le modèle de prédiction créé en Python sur SQL Server

contenu

[À propos de cet exemple](#A propos de cet exemple)

[Étape 1. Construction de l'environnement](# step-1-Construction de l'environnement)

[Étape 2. Création d'un modèle de prédiction avec Python](# step-2-Création d'un modèle de prédiction avec Python)

[Étape 3. Déploiement et utilisation du modèle prédictif sur SQL Server](# step-3-Déploiement et utilisation du modèle prédictif sur sql-server)

À propos de cet échantillon

Cet exemple exécute l'apprentissage automatique sur SQL Server 2017. Le scénario est une prévision des futures locations dans le secteur de la location de ski. Utilisez l'ensemble de données de l'historique des locations passées ci-dessous.

Year	Month	Day	WeekDay	Holiday	Snow	RentalCount
Année	Mois	journée	曜journée	祝journéeフラグ	Drapeau des chutes de neige	Nombre de locations

** Logiciels requis: **

SQL Server 2017
SQL Server Management Studio
Outils Python pour Visual Studio ou autres IDE Python (Visual Studio Code, PyCharm, etc.)

** Exemple de code: **

rental_prediction.py Un script Python qui génère un modèle prédictif et l'utilise pour prédire le nombre de locations.
rental_prediction.sql Déployez le traitement de rent_prediction.py dans SQL Server (créez des procédures stockées et des tables pour la formation, enregistrez des modèles, créez des procédures stockées pour la prédiction).
setup.sql Restaurez le fichier de sauvegarde (remplacez le chemin du fichier par le chemin téléchargé).

** Exemple de données: **

TutorialDB.bak Données d'historique de location requises pour exécuter l'exemple de code.

** La source: **

Cet article est basé sur Créer un modèle prédictif à l'aide de Python et des services SQL Server ML.

Étape 1. Construction de l'environnement

Créez l'environnement pour exécuter cet exemple.

1-1. Installation de SQL Server 2017

J'ai essayé d'utiliser SQL Server 2017 In-Database Python pour SQL Server 2017 Installez les services de moteur de base de données et les services d'apprentissage automatique (dans la base de données).

1-2. Restauration de l'exemple de base de données

Exemple de base de données [fichier de sauvegarde (TutorialDB.bak)](https://github.com/gho9o9/sql-server-samples/raw/master/samples/features/machine-learning-services/python/getting-started/rental -Téléchargez (prediction / TutorialDB.bak) et restaurez-le en exécutant Setup.sql. Setup.sql suppose que vous avez téléchargé le fichier de sauvegarde dans C: \ Program Files \ Microsoft SQL Server \ MSSQL13.MSSQLSERVER \ MSSQL \ Backup. Modifiez le chemin en fonction de votre environnement.

`Setup.sql`


USE master;
GO
RESTORE DATABASE TutorialDB
   FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\TutorialDB.bak'
   WITH
   MOVE 'TutorialDB' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TutorialDB.mdf'
   ,MOVE 'TutorialDB_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TutorialDB.ldf';
GO

Vérifiez les données créées par la restauration.

`T-SQL`


USE TutorialDB;
SELECT * FROM [dbo].[rental_data];

1-3. Activation de la fonction d'exécution de script externe

Pour exécuter Python (et R) dans SQL Server 2017, vous devez modifier le paramètre des scripts externes activés dans sp_configure. En outre, le paramètre activé des scripts externes nécessite un redémarrage de SQL Server 2017 pour que les modifications de paramètres prennent effet.

1-3-1. Activation de la fonction d'exécution de script externe

`T-SQL`


EXEC sp_configure 'external scripts enabled', 1;

1-3-2. Redémarrez SQL Server 2017

`cmd`


net stop "SQL Server Launchpad (MSSQLSERVER)"
net stop "SQL Server (MSSQLSERVER)"
net start "SQL Server (MSSQLSERVER)"
net start "SQL Server Launchpad (MSSQLSERVER)"

(*) Modifiez le nom de l'instance en fonction de l'environnement. S'il existe un service qui dépend du service SQL Server, tel que le service SQL Server Agent, redémarrez-le explicitement.

Étape 2. Création d'un modèle de prédiction à l'aide de Python

Tout d'abord, créez un modèle de prédiction par Python dans l'IDE Python.

2-1. Importation de la bibliothèque

Importez les bibliothèques requises.

`rental_prediction.py`


import os
import pandas as pd
from sklearn.linear_model import LinearRegression
from sklearn.metrics import mean_squared_error
from revoscalepy import RxSqlServerData
from revoscalepy import rx_import

Bibliothèque importée

Bibliothèque	Utilisation
scikit-learn	Utilisé pour l'apprentissage automatique
RevoScalePy	UtilisépouraccéderàSQLServer(Peutégalementêtreutilisépourl'apprentissageautomatique)

2-2. Chargement des données

Connectez-vous à SQL Server et obtenez des données et chargez-les dans le cadre de données panas.

`rental_prediction.py`


sql_server = os.getenv('PYTEST_SQL_SERVER', '.')
conn_str = 'Driver=SQL Server;Server=' + sql_server + ';Database=TutorialDB;Trusted_Connection=True;'
column_info = { 
        "Year" : { "type" : "integer" },
        "Month" : { "type" : "integer" }, 
        "Day" : { "type" : "integer" }, 
        "RentalCount" : { "type" : "integer" }, 
        "WeekDay" : { 
            "type" : "factor", 
            "levels" : ["1", "2", "3", "4", "5", "6", "7"]
        },
        "Holiday" : { 
            "type" : "factor", 
            "levels" : ["1", "0"]
        },
        "Snow" : { 
            "type" : "factor", 
            "levels" : ["1", "0"]
        }
    }

data_source = RxSqlServerData(sql_query="SELECT RentalCount, Year, Month, Day, WeekDay, Snow, Holiday FROM dbo.rental_data",
                              connection_string=conn_str, column_info=column_info)
    
# import data source and convert to pandas dataframe
df = pd.DataFrame(rx_import(data_source))
print("Data frame:", df)
# Get all the columns from the dataframe.
columns = df.columns.tolist()
# Filter the columns to remove ones we don't want.
columns = [c for c in columns if c not in ["Year"]]

(*) Modifiez le serveur de destination de la connexion (variable: sql_server) en fonction de l'environnement.

`Results`


Rows Processed: 453 
Data frame:      RentalCount  Year  Month  Day WeekDay Snow Holiday
0            445  2014      1   20       2    0       1
1             40  2014      2   13       5    0       0
2            456  2013      3   10       1    0       0
...
450           29  2015      3   24       3    1       0
451           50  2014      3   26       4    1       0
452          377  2015     12    6       1    1       0
[453 rows x 7 columns]

2-3. Formation sur les modèles

Cet exemple utilise un algorithme de régression linéaire pour l'apprentissage du modèle. Cet apprentissage du modèle consiste à trouver la fonction (modèle) qui décrit le mieux la corrélation des variables dans l'ensemble de données.

`rental_prediction.py`


# Store the variable we'll be predicting on.
target = "RentalCount"
# Generate the training set.  Set random_state to be able to replicate results.
train = df.sample(frac=0.8, random_state=1)
# Select anything not in the training set and put it in the testing set.
test = df.loc[~df.index.isin(train.index)]
# Print the shapes of both sets.
print("Training set shape:", train.shape)
print("Testing set shape:", test.shape)
# Initialize the model class.
lin_model = LinearRegression()
# Fit the model to the training data.
lin_model.fit(train[columns], train[target])

`Results`


Training set shape: (362, 7)
Testing set shape: (91, 7)

2-4. Prévision

Prédisez avec le modèle créé précédemment à l'aide des données de test.

`rental_prediction.py`


# Generate our predictions for the test set.
lin_predictions = lin_model.predict(test[columns])
print("Predictions:", end="")
print(['{:.15f}'.format(n) for n in lin_predictions])
# Compute error between our test predictions and the actual values.
lin_mse = mean_squared_error(lin_predictions, test[target])
print("Computed error:", lin_mse)

`Results`


Predictions:['40.000000000000007', '38.000000000000007', '240.000000000000000', '39.000000000000000', '514.000000000000000', '48.000000000000007', '297.000000000000000', '24.999999999999993',
...
'432.000000000000000', '24.999999999999993', '39.000000000000007', '28.000000000000004', '325.000000000000000', '46.000000000000014', '36.000000000000014', '50.000000000000007', '63.000000000000007']
Computed error: 6.85182043392e-29

Étape 3. Déployez et utilisez le modèle prédictif sur SQL Server

SQL Server Machine Learning Services vous permet de former et de tester des modèles prédictifs dans le contexte de SQL Server. Créez un programme T-SQL qui contient un script Python intégré, que le moteur de base de données SQL Server gère. Ces codes Python s'exécutent sur SQL Server, ce qui simplifie l'interaction avec les données stockées dans la base de données.

3-1. Définition du tableau

Définissez une table pour stocker le modèle et les résultats de la prédiction.

`rental_prediction.sql`


-- 3-1.définition de table
--Setup model table
DROP TABLE IF EXISTS rental_py_models;
GO
CREATE TABLE rental_py_models (
                model_name VARCHAR(30) NOT NULL DEFAULT('default model') PRIMARY KEY,
                model VARBINARY(MAX) NOT NULL
);
GO

--Create a table to store the predictions in
DROP TABLE IF EXISTS [dbo].[py_rental_predictions];
GO
CREATE TABLE [dbo].[py_rental_predictions](
	[RentalCount_Predicted] [int] NULL,
	[RentalCount_Actual] [int] NULL,
	[Month] [int] NULL,
	[Day] [int] NULL,
	[WeekDay] [int] NULL,
	[Snow] [int] NULL,
	[Holiday] [int] NULL,
	[Year] [int] NULL
) ON [PRIMARY]
GO

3-2. Modélisation de la définition de procédure stockée

Le code de création de modèle créé en Python est utilisé et implémenté dans SQL Server. Cette procédure stockée utilise les données de SQL Server pour créer un modèle de régression linéaire.

`rental_prediction.sql`


-- 3-2.Modélisation de la définition de procédure stockée
-- Stored procedure that trains and generates an R model using the rental_data and a decision tree algorithm
DROP PROCEDURE IF EXISTS generate_rental_py_model;
go
CREATE PROCEDURE generate_rental_py_model (@trained_model varbinary(max) OUTPUT)
AS
BEGIN
    EXECUTE sp_execute_external_script
      @language = N'Python'
    , @script = N'

df = rental_train_data

# Get all the columns from the dataframe.
columns = df.columns.tolist()

# Store the variable well be predicting on.
target = "RentalCount"

from sklearn.linear_model import LinearRegression

# Initialize the model class.
lin_model = LinearRegression()

# Fit the model to the training data.
lin_model.fit(df[columns], df[target])

import pickle
#Before saving the model to the DB table, we need to convert it to a binary object
trained_model = pickle.dumps(lin_model)
'
    , @input_data_1 = N'select "RentalCount", "Year", "Month", "Day", "WeekDay", "Snow", "Holiday" from dbo.rental_data where Year < 2015'
    , @input_data_1_name = N'rental_train_data'
    , @params = N'@trained_model varbinary(max) OUTPUT'
    , @trained_model = @trained_model OUTPUT;
END;
GO

3-3. Exécution de la création du modèle

Création de modèle Enregistre le modèle créé en exécutant la procédure stockée en tant que données VARBINARY dans SQL Server.

`rental_prediction.sql`


-- 3-3.Exécution de la création du modèle
TRUNCATE TABLE rental_py_models;

DECLARE @model VARBINARY(MAX);
EXEC generate_rental_py_model @model OUTPUT;

INSERT INTO rental_py_models (model_name, model) VALUES('linear_model', @model);

SELECT * FROM rental_py_models;

`Results`


model_name                     model
------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
linear_model                   0x800363736B6C6561726E2E6C696E6561725F6D6F64656C2E626173650A4C696E65617252656772657373696F6E0A7100298171017D71022858060000006E5F6A6F627371034B01580500000072616E6B5F71044B0758100000005F736B6C6561726E5F76657273696F6E71055806000000302E31382E31710658090000006E

(Une ligne traitée)

3-4. Définition de procédure stockée prédictive

Prédisez les données de test enregistrées dans SQL Server avec le modèle enregistré dans SQL Server.

`rental_prediction.sql`


-- 3-4.Définition de procédure stockée prédictive
DROP PROCEDURE IF EXISTS py_predict_rentalcount;
GO
CREATE PROCEDURE py_predict_rentalcount (@model varchar(100))
AS
BEGIN
	DECLARE @py_model varbinary(max) = (select model from rental_py_models where model_name = @model);

	EXEC sp_execute_external_script 
					@language = N'Python'
				  , @script = N'

import pickle
rental_model = pickle.loads(py_model)

df = rental_score_data

# Get all the columns from the dataframe.
columns = df.columns.tolist()

# Store the variable well be predicting on.
target = "RentalCount"

# Generate our predictions for the test set.
lin_predictions = rental_model.predict(df[columns])

# Import the scikit-learn function to compute error.
from sklearn.metrics import mean_squared_error
# Compute error between our test predictions and the actual values.
lin_mse = mean_squared_error(lin_predictions, df[target])

import pandas as pd
predictions_df = pd.DataFrame(lin_predictions)  
OutputDataSet = pd.concat([predictions_df, df["RentalCount"], df["Month"], df["Day"], df["WeekDay"], df["Snow"], df["Holiday"], df["Year"]], axis=1)
'
	, @input_data_1 = N'Select "RentalCount", "Year" ,"Month", "Day", "WeekDay", "Snow", "Holiday"  from rental_data where Year = 2015'
	, @input_data_1_name = N'rental_score_data'
	, @params = N'@py_model varbinary(max)'
	, @py_model = @py_model
	with result sets (("RentalCount_Predicted" float, "RentalCount" float, "Month" float,"Day" float,"WeekDay" float,"Snow" float,"Holiday" float, "Year" float));
END;
GO

3-5. Exécution de la prédiction

Exécutez une procédure stockée de prédiction pour effectuer une prédiction et enregistrer le résultat dans SQL Server.

`rental_prediction.sql`


--3-5.Exécution prédictive
TRUNCATE TABLE py_rental_predictions;
--Insert the results of the predictions for test set into a table
INSERT INTO py_rental_predictions
EXEC py_predict_rentalcount 'linear_model';
-- Select contents of the table
SELECT * FROM py_rental_predictions;

`Results`


RentalCount_Predicted RentalCount_Actual Month       Day         WeekDay     Snow        Holiday     Year
--------------------- ------------------ ----------- ----------- ----------- ----------- ----------- -----------
41                    42                 2           11          4           0           0           2015
360                   360                3           29          1           0           0           2015
19                    20                 4           22          4           0           0           2015
...
25                    26                 3           18          4           0           0           2015
28                    29                 3           24          3           1           0           2015
377                   377                12          6           1           1           0           2015

(151 lignes traitées)

Visualisez le résultat enregistré dans SQL Server avec PowerBI etc.