Python-zelfstudie: Een Python-model trainen en opslaan met behulp van T-SQL

Van toepassing op: SQL Server 2017 (14.x) en latere versies van Azure SQL Managed Instance

In deel vier van deze vijfdelige zelfstudiereeks leert u hoe u een machine learning-model traint met behulp van de Python-pakketten scikit-learn en revoscalepy. Deze Python-bibliotheken zijn al geïnstalleerd met SQL Server Machine Learning.

U laadt de modules en roept de benodigde functies aan om het model te maken en te trainen met behulp van een opgeslagen SQL Server-procedure. Voor het model zijn de gegevensfuncties vereist die u in eerdere delen van deze reeks zelfstudies hebt ontworpen. Ten slotte slaat u het getrainde model op in een SQL Server-tabel.

In dit artikel gaat u het volgende doen:

In deel 1 hebt u de vereisten geïnstalleerd en de voorbeelddatabase hersteld.

In deel twee hebt u de voorbeeldgegevens verkend en enkele plots gegenereerd.

In deel drie hebt u geleerd hoe u functies maakt op basis van onbewerkte gegevens met behulp van een Transact-SQL-functie. Vervolgens hebt u die functie aangeroepen vanuit een opgeslagen procedure om een tabel te maken die de functiewaarden bevat.

In deel vijf leert u hoe u de modellen kunt operationeel maken die u hebt getraind en opgeslagen in deel vier.

De voorbeeldgegevens splitsen in een training- en een testset

1. Maak een opgeslagen procedure met de naam PyTrainTestSplit om de gegevens in de nyctaxi_sample tabel in twee delen te verdelen: nyctaxi_sample_training en nyctaxi_sample_testing.

   Voer de volgende code uit om deze te maken:

   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. Als u uw gegevens wilt verdelen met behulp van een aangepaste splitsing, voert u de opgeslagen procedure uit en geeft u een geheel getal op dat het percentage gegevens vertegenwoordigt dat moet worden toegewezen aan de trainingsset. Met de volgende instructie worden bijvoorbeeld 60% gegevens toegewezen aan de trainingsset.

   EXEC PyTrainTestSplit 60
   GO
   

Een logistiek regressiemodel bouwen

Nadat de gegevens zijn voorbereid, kunt u deze gebruiken om een model te trainen. U doet dit door een opgeslagen procedure aan te roepen waarmee python-code wordt uitgevoerd, waarbij de trainingsgegevenstabel wordt ingevoerd. Voor deze zelfstudie maakt u twee modellen, beide binaire classificatiemodellen:

• Met de opgeslagen procedure maakt PyTrainScikit een tipvoorspellingsmodel met behulp van het scikit-learn-pakket .
• Met de opgeslagen procedure TrainTipPredictionModelRxPy wordt een tipvoorspellingsmodel gemaakt met behulp van het revoscalepy-pakket .

Elke opgeslagen procedure maakt gebruik van de invoergegevens die u opgeeft om een logistiek regressiemodel te maken en te trainen. Alle Python-code wordt verpakt in de opgeslagen procedure van het systeem. sp_execute_external_script

Om het model gemakkelijker opnieuw te trainen op nieuwe gegevens, verpakt u de aanroep in sp_execute_external_script een andere opgeslagen procedure en geeft u de nieuwe trainingsgegevens door als parameter. In deze sectie wordt u begeleid bij dat proces.

PyTrainScikit

1. Open in Management Studio een nieuw queryvenster en voer de volgende instructie uit om de opgeslagen procedure PyTrainScikit te maken. De opgeslagen procedure bevat een definitie van de invoergegevens, dus u hoeft geen invoerquery op te geven.

   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. Voer de volgende SQL-instructies uit om het getrainde model in te voegen in tabel nyc_taxi_models.

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

   Het verwerken van de gegevens en het aanpassen van het model kan enkele minuten duren. Berichten die worden doorgesluisd naar de stdoutstream van Python, worden weergegeven in het venster Berichten van Management Studio. Voorbeeld:

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

3. Open de tabel nyc_taxi_models. U kunt zien dat er één nieuwe rij is toegevoegd, die het geserialiseerde model in het kolommodel bevat.

   SciKit_model
   0x800363736B6C6561726E2E6C696E6561....
   

TrainTipPredictionModelRxPy

Deze opgeslagen procedure maakt gebruik van het Python-pakket revoscalepy . Het bevat objecten, transformatie en algoritmen die vergelijkbaar zijn met de algoritmen die zijn opgegeven voor het RevoScaleR-pakket van de R-taal .

Met behulp van revoscalepy kunt u externe rekencontexten maken, gegevens verplaatsen tussen rekencontexten, gegevens transformeren en voorspellende modellen trainen met behulp van populaire algoritmen zoals logistieke en lineaire regressie, beslissingsstructuren en meer. Zie de revoscalepy-module in SQL Server en naslaginformatie over revoscalepy-functies voor meer informatie.

1. Open in Management Studio een nieuw queryvenster en voer de volgende instructie uit om de opgeslagen procedure TrainTipPredictionModelRxPy te maken. Omdat de opgeslagen procedure al een definitie van de invoergegevens bevat, hoeft u geen invoerquery op te geven.

   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
   

   Deze opgeslagen procedure voert de volgende stappen uit als onderdeel van modeltraining:

   • De SELECT-query past de aangepaste scalaire functie fnCalculateDistance toe om de directe afstand tussen de ophaal- en afgiftelocaties te berekenen. De resultaten van de query worden opgeslagen in de standaard-Python-invoervariabele. InputDataset
   • De binaire variabele die wordt getypt , wordt gebruikt als de kolom met labels of resultaten en het model past bij de volgende functiekolommen: passenger_count, trip_distance, trip_time_in_secs en direct_distance.
   • Het getrainde model wordt geserialiseerd en opgeslagen in de Python-variabele logitObj. Door de T-SQL-trefwoordUITVOER toe te voegen, kunt u de variabele toevoegen als uitvoer van de opgeslagen procedure. In de volgende stap wordt die variabele gebruikt om de binaire code van het model in te voegen in een databasetabel nyc_taxi_models. Dit mechanisme maakt het eenvoudig om modellen op te slaan en opnieuw te gebruiken.

2. Voer de opgeslagen procedure als volgt uit om het getrainde revoscalepy-model in te voegen in de tabel nyc_taxi_models.

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

   Het verwerken van de gegevens en het aanpassen van het model kan enige tijd duren. Berichten die worden doorgesluisd naar de stdoutstream van Python, worden weergegeven in het venster Berichten van Management Studio. Voorbeeld:

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

3. Open de tabel nyc_taxi_models. U kunt zien dat er één nieuwe rij is toegevoegd, die het geserialiseerde model in het kolommodel bevat.

   revoscalepy_model
   0x8003637265766F7363616c....
   

In het volgende deel van deze zelfstudie gebruikt u de getrainde modellen om voorspellingen te maken.

Volgende stappen

In dit artikel gaat u als volgt te werk: