Stellen Sie das in Python unter SQL Server erstellte Vorhersagemodell bereit und verwenden Sie es

Inhalt

[Über dieses Beispiel](#Über dieses Beispiel)

[Schritt 1. Umgebungskonstruktion](# Schritt-1-Umgebungskonstruktion)

[Schritt 2. Erstellen eines Vorhersagemodells mit Python](# Schritt 2 - Erstellen eines Vorhersagemodells mit Python)

[Schritt 3. Bereitstellen und Verwenden des Vorhersagemodells auf SQL Server](# Schritt 3 - Bereitstellen und Verwenden des Vorhersagemodells auf SQL Server)

Über dieses Beispiel

In diesem Beispiel wird maschinelles Lernen unter SQL Server 2017 ausgeführt. Das Szenario ist eine Prognose zukünftiger Vermietungen im Skiverleihgeschäft. Verwenden Sie den unten gezeigten Datensatz für die vergangene Miethistorie.

Year	Month	Day	WeekDay	Holiday	Snow	RentalCount
Jahr	Mond	Tag	曜Tag	祝Tagフラグ	Schneefall Flagge	Anzahl der Anmietungen

** Software Anforderungen: **

SQL Server 2017
SQL Server Management Studio
Python-Tools für Visual Studio oder andere Python-IDEs (Visual Studio-Code, PyCharm usw.)

** Beispielcode: **

rental_prediction.py Ein Python-Skript, das ein Vorhersagemodell generiert und es zur Vorhersage der Anzahl der Anmietungen verwendet.
rental_prediction.sql Stellen Sie die Verarbeitung von rent_prediction.py in SQL Server bereit (erstellen Sie gespeicherte Prozeduren und Tabellen für das Training, speichern Sie Modelle, erstellen Sie gespeicherte Prozeduren für die Vorhersage).
setup.sql Stellen Sie die Sicherungsdatei wieder her (ersetzen Sie den Dateipfad durch den heruntergeladenen Pfad).

** Beispieldaten: **

TutorialDB.bak Mietverlaufsdaten erforderlich, um den Beispielcode auszuführen.

** Quelle: **

Dieser Artikel basiert auf Erstellen eines Vorhersagemodells mit Python- und SQL Server ML-Diensten.

Schritt 1. Umgebungskonstruktion

Erstellen Sie die Umgebung, um dieses Beispiel auszuführen.

1-1. Installation von SQL Server 2017

Ich habe versucht, SQL Server 2017 In-Database Python zu verwenden für SQL Server 2017 Installieren Sie Database Engine Services und Machine Learning Services (In-Database).

1-2. Wiederherstellen der Beispiel-DB

Beispiel-DB [Sicherungsdatei (TutorialDB.bak)](https://github.com/gho9o9/sql-server-samples/raw/master/samples/features/machine-learning-services/python/getting-started/rental -Download (Vorhersage / TutorialDB.bak) und Wiederherstellung durch Ausführen von Setup.sql. Setup.sql setzt voraus, dass Sie die Sicherungsdatei unter C: \ Programme \ Microsoft SQL Server \ MSSQL13.MSSQLSERVER \ MSSQL \ Backup heruntergeladen haben. Ändern Sie den Pfad entsprechend Ihrer Umgebung.

`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

Überprüfen Sie die durch die Wiederherstellung erstellten Daten.

`T-SQL`


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

1-3 Aktivieren der externen Skriptausführungsfunktion

Um Python (und R) in SQL Server 2017 auszuführen, müssen Sie die Einstellung der in sp_configure aktivierten externen Skripts ändern. Außerdem erfordert der Parameter für die Aktivierung externer Skripts einen Neustart von SQL Server 2017, damit die Einstellungsänderungen wirksam werden.

1-3-1. Aktivieren der externen Skriptausführungsfunktion

`T-SQL`


EXEC sp_configure 'external scripts enabled', 1;

1-3-2. Starten Sie SQL Server 2017 neu

`cmd`


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

(*) Ändern Sie den Instanznamen entsprechend der Umgebung. Wenn es einen Dienst gibt, der vom SQL Server-Dienst abhängt, z. B. den SQL Server-Agent-Dienst, starten Sie ihn explizit neu.

Schritt 2. Erstellen eines Vorhersagemodells mit Python

Erstellen Sie zunächst ein Vorhersagemodell von Python in der Python-IDE.

2-1. Importieren der Bibliothek

Importieren Sie die erforderlichen Bibliotheken.

`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

Importierte Bibliothek

Bibliothek	Verwenden
scikit-learn	Wird für maschinelles Lernen verwendet
RevoScalePy	Wirdverwendet,umaufSQLServerzuzugreifen(KannauchzummaschinellenLernenverwendetwerden)

2-2. Laden von Daten

Stellen Sie eine Verbindung zu SQL Server her, rufen Sie Daten ab und laden Sie sie in den Panas-Datenrahmen.

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

(*) Ändern Sie den Verbindungszielserver (Variable: sql_server) entsprechend der Umgebung.

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

In diesem Beispiel wird ein linearer Regressionsalgorithmus für das Modelltraining verwendet. In diesem Modelltraining wird die Funktion (Modell) gefunden, die die Korrelation der Variablen im Datensatz am besten beschreibt.

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

Vorhersage mit dem zuvor erstellten Modell unter Verwendung der Testdaten.

`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

Schritt 3. Stellen Sie das Vorhersagemodell auf SQL Server bereit und verwenden Sie es

Mit SQL Server Machine Learning Services können Sie Vorhersagemodelle im Kontext von SQL Server trainieren und testen. Erstellen Sie ein T-SQL-Programm, das ein eingebettetes Python-Skript enthält, das vom SQL Server-Datenbankmodul verarbeitet wird. Diese Python-Codes werden auf SQL Server ausgeführt, was die Interaktion mit den in der Datenbank gespeicherten Daten vereinfacht.

3-1. Tabellendefinition

Definieren Sie eine Tabelle zum Speichern der Modell- und Vorhersageergebnisse.

`rental_prediction.sql`


-- 3-1.Tabellendefinition
--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. Modellierung der Definition gespeicherter Prozeduren

Der in Python erstellte Modellerstellungscode wird in SQL Server verwendet und implementiert. Diese gespeicherte Prozedur verwendet die Daten in SQL Server, um ein lineares Regressionsmodell zu erstellen.

`rental_prediction.sql`


-- 3-2.Modellierung der Definition gespeicherter Prozeduren
-- 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. Ausführung der Modellerstellung

Modellerstellung Registriert das Modell, das durch Ausführen der gespeicherten Prozedur erstellt wurde, als VARBINARY-Daten in SQL Server.

`rental_prediction.sql`


-- 3-3.Ausführung der Modellerstellung
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

(Eine Zeile verarbeitet)

3-4. Definition der prädiktiven gespeicherten Prozedur

Prognostizieren Sie die in SQL Server registrierten Testdaten mit dem in SQL Server registrierten Modell.

`rental_prediction.sql`


-- 3-4.Predictive Definition gespeicherter Prozeduren
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. Ausführung der Vorhersage

Führen Sie eine vorausgesagte gespeicherte Prozedur aus, um eine Vorhersage zu erstellen und das Ergebnis in SQL Server zu registrieren.

`rental_prediction.sql`


--3-5.Vorausschauende Ausführung
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 Zeilen verarbeitet)

Visualisieren Sie das in SQL Server mit PowerBI usw. registrierte Ergebnis.