Share via

Esercitazione su R: Eseguire il training e il salvataggio del modello

  • Articolo

Si applica a: SQL Server 2016 (13.x) e versioni successive Istanza gestita di SQL di Azure

Nella quarta parte di questa serie di esercitazioni in cinque parti si apprenderà come eseguire il training di un modello di Machine Learning usando R. Si eseguirà il training del modello usando le funzionalità dei dati create nella parte precedente e quindi si salverà il modello sottoposto a training in una tabella di SQL Server. In questo caso, i pacchetti R sono già installati con R Services (In-Database), quindi è possibile eseguire tutte le operazioni da SQL.

Contenuto dell'articolo:

  • Creare ed eseguire il training di un modello usando una stored procedure SQL
  • Salvare il modello sottoposto a training in una tabella SQL

Nella prima parte sono stati installati i prerequisiti ed è stato ripristinato il database di esempio.

Nella seconda parte sono stati esaminati i dati di esempio e sono stati generati alcuni tracciati.

Nella terza parte si è appreso come creare funzionalità dai dati non elaborati tramite una funzione Transact-SQL. La funzione è stata quindi chiamata da una stored procedure per creare una tabella contenente i valori della funzionalità.

Nella quinta parte si apprenderà come rendere operativi i modelli sottoposti a training e salvati nella quarta parte.

Creare la stored procedure

Quando si chiama R da T-SQL, si usa la stored procedure di sistema sp_execute_external_script. Per i processi che vengono ripetuti spesso, come la ripetizione del training di un modello, è tuttavia più semplice incapsulare la chiamata a sp_execute_external_script in un'altra stored procedure.

  1. In Management Studio aprire una nuova finestra di query.

  2. Eseguire l'istruzione seguente per creare la stored procedure RTrainLogitModel. Questa stored procedure definisce i dati di input e usa glm per creare un modello di regressione logistica.

    CREATE PROCEDURE [dbo].[RTrainLogitModel] (@trained_model varbinary(max) OUTPUT)

AS
BEGIN
  DECLARE @inquery nvarchar(max) = N'
    select tipped, fare_amount, passenger_count,trip_time_in_secs,trip_distance,
    pickup_datetime, dropoff_datetime,
    dbo.fnCalculateDistance(pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as direct_distance
    from nyctaxi_sample
    tablesample (70 percent) repeatable (98052)
'

  EXEC sp_execute_external_script @language = N'R',
                                  @script = N'
## Create model
logitObj <- glm(tipped ~ passenger_count + trip_distance + trip_time_in_secs + direct_distance, data = InputDataSet, family = binomial)
summary(logitObj)

## Serialize model 
trained_model <- as.raw(serialize(logitObj, NULL));
',
  @input_data_1 = @inquery,
  @params = N'@trained_model varbinary(max) OUTPUT',
  @trained_model = @trained_model OUTPUT; 
END
GO

    • Per assicurarsi che rimangano dati disponibili per testare il modello, viene selezionato in modo casuale il 70% dei dati della tabella relativa ai dati dei taxi da usare per il training.

    • La query SELECT usa la funzione scalare personalizzata fnCalculateDistance per calcolare la distanza diretta tra la posizione di salita e discesa dei passeggeri. I risultati della query vengono archiviati nella variabile di input R predefinita InputDataset.

    • Lo script R chiama la funzione R glm per creare il modello di regressione logistica.

      La variabile binaria tipped viene usata come colonna dei risultati o etichetta. Il modello viene adattato usando le colonne delle caratteristiche seguenti: passenger_count, trip_distance, trip_time_in_secs e direct_distance.

    • Il modello sottoposto a training salvato nella variabile R logitObj viene serializzato e restituito come parametro di output.

Eseguire il training e la distribuzione del modello R usando la stored procedure

Poiché la stored procedure include già una definizione dei dati di input, non è necessario specificare una query di input.

  1. Per eseguire il training e la distribuzione del modello R, chiamare la stored procedure e inserirla nella tabella di database nyc_taxi_models, in modo che sia possibile farne uso per stime future:

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

  2. Esaminare la finestra Messaggi di Management Studio per visualizzare i messaggi che verrebbero inoltrati al flusso stdout di R, come il seguente:

    “STDOUT message(s) from external script: Rows Read: 1193025, Total Rows Processed: 1193025, Total Chunk Time: 0.093 seconds”

  3. Dopo il completamento dell'istruzione, aprire la tabella nyc_taxi_models. L'elaborazione dei dati e l'adattamento del modello possono richiedere un po' di tempo.

    È possibile notare che è stata aggiunta una nuova riga nuova contenente il modello serializzato nella colonna model e il nome del modello RTrainLogit_model nella colonna name.

    model                        name
---------------------------- ------------------
0x580A00000002000302020....  RTrainLogit_model

Nella parte successiva di questa esercitazione si userà il modello sottoposto a training per generare previsioni.

Passaggi successivi

In questo articolo si apprenderà come:

  • Creare ed eseguire il training di un modello usando una stored procedure SQL
  • Salvare il modello sottoposto a training in una tabella SQL

Esercitazione su R: Eseguire le previsioni nelle stored procedure SQL