Python-självstudie: Träna och spara en Python-modell med T-SQL

Gäller för: SQL Server 2017 (14.x) och senare versioner Azure SQL Managed Instance

I del fyra i den här självstudieserien i fem delar får du lära dig hur du tränar en maskininlärningsmodell med hjälp av Python-paketen scikit-learn och revoscalepy. Dessa Python-bibliotek är redan installerade med SQL Server-maskininlärning.

Du läser in modulerna och anropar de funktioner som krävs för att skapa och träna modellen med hjälp av en lagrad SQL Server-procedur. Modellen kräver de datafunktioner som du skapade i tidigare delar av den här självstudieserien. Slutligen sparar du den tränade modellen i en SQL Server-tabell.

I den här artikeln ska du:

I del ett installerade du förhandskraven och återställde exempeldatabasen.

I del två utforskade du exempeldata och genererade några diagram.

I del tre lärde du dig att skapa funktioner från rådata med hjälp av en Transact-SQL funktion. Sedan anropade du funktionen från en lagrad procedur för att skapa en tabell som innehåller funktionsvärdena.

I del fem får du lära dig hur du operationaliserar de modeller som du har tränat och sparat i del fyra.

Dela upp exempeldata i tränings- och testuppsättningar

1. Skapa en lagrad procedur med namnet PyTrainTestSplit för att dela upp data i tabellen nyctaxi_sample i två delar: nyctaxi_sample_training och nyctaxi_sample_testing.

   Kör följande kod för att skapa den:

   DROP PROCEDURE IF EXISTS PyTrainTestSplit;
   GO
   
   CREATE PROCEDURE [dbo].[PyTrainTestSplit] (@pct int)
   AS
   
   DROP TABLE IF EXISTS dbo.nyctaxi_sample_training
   SELECT * into nyctaxi_sample_training FROM nyctaxi_sample WHERE (ABS(CAST(BINARY_CHECKSUM(medallion,hack_license)  as int)) % 100) < @pct
   
   DROP TABLE IF EXISTS dbo.nyctaxi_sample_testing
   SELECT * into nyctaxi_sample_testing FROM nyctaxi_sample
   WHERE (ABS(CAST(BINARY_CHECKSUM(medallion,hack_license)  as int)) % 100) > @pct
   GO
   

2. Om du vill dela upp dina data med en anpassad delning kör du den lagrade proceduren och anger en heltalsparameter som representerar procentandelen data som ska allokeras till träningsuppsättningen. Följande instruktion skulle till exempel allokera 60% av datan till träningsdatasettet.

   EXEC PyTrainTestSplit 60
   GO
   

Skapa en logistisk regressionsmodell

När data har förberetts kan du använda dem för att träna en modell. Du gör detta genom att anropa en lagrad procedur som kör viss Python-kod, där träningsdatatabellen används som indata. I den här självstudien skapar du två modeller, båda binära klassificeringsmodeller:

• Den lagrade proceduren PyTrainScikit skapar en tipsförutsägelsemodell med hjälp av scikit-learn-paketet .
• Den lagrade proceduren TrainTipPredictionModelRxPy skapar en förutsägelsemodell för tips med hjälp av revoscalepy-paketet .

Varje lagrad procedur använder indata som du anger för att skapa och träna en logistisk regressionsmodell. All Python-kod omsluts i den systemlagrade proceduren, sp_execute_external_script.

För att göra det enklare att träna om modellen på nya data omsluter du anropet till sp_execute_external_script i en annan lagrad procedur och skickar in nya träningsdata som en parameter. Det här avsnittet vägleder dig genom den processen.

PyTrainScikit

1. I Management Studio öppnar du ett nytt frågefönster och kör följande instruktion för att skapa den lagrade proceduren PyTrainScikit. Den lagrade proceduren innehåller en definition av indata, så du behöver inte ange någon indatafråga.

   DROP PROCEDURE IF EXISTS PyTrainScikit;
   GO
   
   CREATE PROCEDURE [dbo].[PyTrainScikit] (@trained_model varbinary(max) OUTPUT)
   AS
   BEGIN
   EXEC sp_execute_external_script
     @language = N'Python',
     @script = N'
   import numpy
   import pickle
   from sklearn.linear_model import LogisticRegression
   
   ##Create SciKit-Learn logistic regression model
   X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]
   y = numpy.ravel(InputDataSet[["tipped"]])
   
   SKLalgo = LogisticRegression()
   logitObj = SKLalgo.fit(X, y)
   
   ##Serialize model
   trained_model = pickle.dumps(logitObj)
   ',
   @input_data_1 = N'
   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_training
   ',
   @input_data_1_name = N'InputDataSet',
   @params = N'@trained_model varbinary(max) OUTPUT',
   @trained_model = @trained_model OUTPUT;
   ;
   END;
   GO
   

2. Kör följande SQL-instruktioner för att infoga den tränade modellen i tabell nyc_taxi_models.

   DECLARE @model VARBINARY(MAX);
   EXEC PyTrainScikit @model OUTPUT;
   INSERT INTO nyc_taxi_models (name, model) VALUES('SciKit_model', @model);
   

   Det kan ta några minuter att bearbeta data och anpassa modellen. Meddelanden som skickas till Pythons stdout-ström visas i fönstret Meddelanden i Management Studio. Till exempel:

   STDOUT message(s) from external script:
   C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy
   

3. Öppna tabellen nyc_taxi_models. Du kan se att en ny rad har lagts till, som innehåller den serialiserade modellen i kolumnmodellen.

   SciKit_model
   0x800363736B6C6561726E2E6C696E6561....
   

TrainTipPredictionModelRxPy

Den här lagrade proceduren använder python-paketet revoscalepy . Den innehåller objekt, transformering och algoritmer som liknar dem som tillhandahålls för R-språkets RevoScaleR-paket .

Med revoscalepy kan du skapa fjärrberäkningskontexter, flytta data mellan beräkningskontexter, transformera data och träna förutsägelsemodeller med hjälp av populära algoritmer som logistisk och linjär regression, beslutsträd med mera. Mer information finns i revoscalepy-modulen i SQL Server - och revoscalepy-funktionsreferensen.

1. I Management Studio öppnar du ett nytt frågefönster och kör följande instruktion för att skapa den lagrade proceduren TrainTipPredictionModelRxPy. Eftersom den lagrade proceduren redan innehåller en definition av indata behöver du inte ange någon indatafråga.

   DROP PROCEDURE IF EXISTS TrainTipPredictionModelRxPy;
   GO
   
   CREATE PROCEDURE [dbo].[TrainTipPredictionModelRxPy] (@trained_model varbinary(max) OUTPUT)
   AS
   BEGIN
   EXEC sp_execute_external_script 
     @language = N'Python',
     @script = N'
   import numpy
   import pickle
   from revoscalepy.functions.RxLogit import rx_logit
   
   ## Create a logistic regression model using rx_logit function from revoscalepy package
   logitObj = rx_logit("tipped ~ passenger_count + trip_distance + trip_time_in_secs + direct_distance", data = InputDataSet);
   
   ## Serialize model
   trained_model = pickle.dumps(logitObj)
   ',
   @input_data_1 = N'
   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_training
   ',
   @input_data_1_name = N'InputDataSet',
   @params = N'@trained_model varbinary(max) OUTPUT',
   @trained_model = @trained_model OUTPUT;
   ;
   END;
   GO
   

   Den här lagrade proceduren utför följande steg som en del av modellträningen:

   • SELECT-frågan tillämpar den anpassade skalärfunktionen fnCalculateDistance för att beräkna det direkta avståndet mellan upphämtnings- och avlämningsplatserna. Resultatet av frågan lagras i standardvariabeln för Python-indata, InputDataset.
   • Den binära variabeln tipped används som etikett eller utfallskolumn, och modellen anpassas med hjälp av dessa egenskapskolumner: passenger_count, trip_distance, trip_time_in_secs och direct_distance.
   • Den tränade modellen serialiseras och lagras i Python-variabeln logitObj. Genom att lägga till T-SQL-nyckelordet OUTPUT kan du lägga till variabeln som utdata från den lagrade proceduren. I nästa steg används variabeln för att infoga modellens binära kod i en databastabell nyc_taxi_models. Den här mekanismen gör det enkelt att lagra och återanvända modeller.

2. Kör den lagrade proceduren så här för att infoga den tränade revoscalepy-modellen i tabellen nyc_taxi_models.

   DECLARE @model VARBINARY(MAX);
   EXEC TrainTipPredictionModelRxPy @model OUTPUT;
   INSERT INTO nyc_taxi_models (name, model) VALUES('revoscalepy_model', @model);
   

   Det kan ta en stund att bearbeta data och anpassa modellen. Meddelanden som skickas till Pythons stdout-ström visas i fönstret Meddelanden i Management Studio. Till exempel:

   STDOUT message(s) from external script:
   C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy
   

3. Öppna tabellen nyc_taxi_models. Du kan se att en ny rad har lagts till, som innehåller den serialiserade modellen i kolumnmodellen.

   revoscalepy_model
   0x8003637265766F7363616c....
   

I nästa del av den här självstudien använder du de tränade modellerna för att skapa förutsägelser.

Nästa steg

I den här artikeln kommer du att: