Avvio rapido: Creare e assegnare i punteggi a un modello predittivo in R con Machine Learning in SQL

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

Creerai ed eseguirai due stored procedure utilizzando SQL. La prima usa il set di dati mtcars incluso in R e genera un modello lineare generalizzato (GLM, Generalized Linear Model) semplice che stima la probabilità che un veicolo sia dotato di trasmissione manuale. La seconda procedura, dedicata all'assegnazione dei punteggi, chiama il modello generato nella prima procedura per restituire un set di previsioni basate sui nuovi dati. Inserendo il codice R in una stored procedure SQL, le operazioni sono contenute in SQL, sono riutilizzabili e possono essere chiamate da altre stored procedure e applicazioni client.

Completando questa guida rapida, imparerai:

Prerequisiti

Per eseguire questo avvio rapido, è necessario avere i prerequisiti seguenti.

• Uno strumento per l'esecuzione di query SQL che contengono script R. Questa guida rapida utilizza l'estensione MSSQL per Visual Studio Code.

Creare il modello

Per creare il modello, è necessario creare i dati di origine per il training, creare il modello ed eseguirne il training usando i dati e quindi archiviare il modello in un database dove può essere usato per generare stime con nuovi dati.

Creare i dati di origine

1. Aprire Visual Studio Code, connettersi all'istanza e aprire una nuova finestra di query.

2. Creare una tabella in cui salvare i dati di training.

   CREATE TABLE dbo.MTCars(
       mpg decimal(10, 1) NOT NULL,
       cyl int NOT NULL,
       disp decimal(10, 1) NOT NULL,
       hp int NOT NULL,
       drat decimal(10, 2) NOT NULL,
       wt decimal(10, 3) NOT NULL,
       qsec decimal(10, 2) NOT NULL,
       vs int NOT NULL,
       am int NOT NULL,
       gear int NOT NULL,
       carb int NOT NULL
   );
   

3. Inserire i dati dal set di dati predefinito mtcars.

   INSERT INTO dbo.MTCars
   EXEC sp_execute_external_script @language = N'R'
       , @script = N'MTCars <- mtcars;'
       , @input_data_1 = N''
       , @output_data_1_name = N'MTCars';
   

   Suggerimento

   Molti set di dati di piccole e grandi dimensioni sono inclusi con il runtime di R. Per ottenere un elenco dei set di dati installati con R, digitare library(help="datasets") da un prompt dei comandi R.

Creare il modello ed eseguirne il training

I dati sulla velocità delle auto contengono due colonne, entrambe numeriche, relative a potenza in cavalli vapore (hp) e peso (wt). Da questi dati si creerà un modello lineare generalizzato (GLM, Generalized Linear Model) che stima la probabilità che un veicolo sia dotato di trasmissione manuale.

Per creare il modello, si definisce la formula nel codice R e si passano i dati come parametro di input.

DROP PROCEDURE IF EXISTS generate_GLM;
GO
CREATE PROCEDURE generate_GLM
AS
BEGIN
    EXEC sp_execute_external_script
    @language = N'R'
    , @script = N'carsModel <- glm(formula = am ~ hp + wt, data = MTCarsData, family = binomial);
        trained_model <- data.frame(payload = as.raw(serialize(carsModel, connection=NULL)));'
    , @input_data_1 = N'SELECT hp, wt, am FROM MTCars'
    , @input_data_1_name = N'MTCarsData'
    , @output_data_1_name = N'trained_model'
    WITH RESULT SETS ((model VARBINARY(max)));
END;
GO

• Il primo argomento di glm è il parametro formula, che definisce am come dipendente da hp + wt.
• I dati di input vengono archiviati nella variabile MTCarsData, che viene popolata dalla query SQL. Se non si assegna un nome specifico ai dati di input, il nome predefinito della variabile è InputDataSet.

Archiviare il modello nel database

Archiviare quindi il modello in un database per poterlo usare per la stima o per poter eseguire di nuovo il training.

1. Creare una tabella per archiviare il modello.

   L'output di un pacchetto R che crea un modello è in genere un oggetto binario. La tabella in cui si archivia il modello deve quindi contenere una colonna di tipo varbinary(max).

   CREATE TABLE GLM_models (
       model_name varchar(30) not null default('default model') primary key,
       model varbinary(max) not null
   );
   

2. Eseguire l'istruzione Transact-SQL seguente per chiamare la stored procedure, generare il modello e salvarlo nella tabella creata.

   INSERT INTO GLM_models(model)
   EXEC generate_GLM;
   

   Suggerimento

   Se si esegue questo codice una seconda volta, viene visualizzato questo errore: "Violazione del vincolo PRIMARY KEY... Impossibile inserire la chiave duplicata nell'oggetto dbo.stopping_distance_models". Un modo per evitare questo errore consiste nell'aggiornare il nome per ogni nuovo modello. Ad esempio, si può modificare il nome scegliendone uno più descrittivo e includere il tipo di modello, il giorno di creazione e così via.

   UPDATE GLM_models
   SET model_name = 'GLM_' + format(getdate(), 'yyyy.MM.HH.mm', 'en-gb')
   WHERE model_name = 'default model'
   

Assegnare punteggi ai nuovi dati utilizzando il modello addestrato.

L'assegnazione dei punteggi è un concetto di data science che indica la generazione di stime, probabilità o altri valori in base ai nuovi dati inseriti in un modello sottoposto a training. Si userà il modello creato nella sezione precedente per assegnare punteggi alle stime in base ai nuovi dati.

Creare una tabella di nuovi dati

Creare prima di tutto una tabella con nuovi dati.

CREATE TABLE dbo.NewMTCars(
	hp INT NOT NULL
	, wt DECIMAL(10,3) NOT NULL
	, am INT NULL
)
GO

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (110, 2.634)

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (72, 3.435)

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (220, 5.220)

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (120, 2.800)
GO

Prevedere la trasmissione manuale

Per ottenere stime basate su un modello specifico, scrivere uno script SQL che esegue le operazioni seguenti:

1. Ottiene il modello desiderato
2. Ottiene i nuovi dati di input
3. Chiama una funzione di stima R compatibile con tale modello

Col tempo, la tabella potrebbe contenere più modelli R, tutti creati usando parametri o algoritmi diversi o sottoposti a training su subset di dati diversi. In questo esempio viene usato il modello denominato default model.

DECLARE @glmmodel varbinary(max) = 
    (SELECT model FROM dbo.GLM_models WHERE model_name = 'default model');

EXEC sp_execute_external_script
    @language = N'R'
    , @script = N'
            current_model <- unserialize(as.raw(glmmodel));
            new <- data.frame(NewMTCars);
            predicted.am <- predict(current_model, new, type = "response");
            str(predicted.am);
            OutputDataSet <- cbind(new, predicted.am);
            '
    , @input_data_1 = N'SELECT hp, wt FROM dbo.NewMTCars'
    , @input_data_1_name = N'NewMTCars'
    , @params = N'@glmmodel varbinary(max)'
    , @glmmodel = @glmmodel
WITH RESULT SETS ((new_hp INT, new_wt DECIMAL(10,3), predicted_am DECIMAL(10,3)));

Lo script precedente esegue i passaggi seguenti:

• Usa un'istruzione SELECT per ottenere un singolo modello dalla tabella e passarlo come parametro di input.

• Dopo aver recuperato il modello dalla tabella, chiama la funzione unserialize sul modello.

• Applica la funzione predict con gli argomenti appropriati al modello e fornisce i nuovi dati di input.

Risultati

È anche possibile usare l'istruzione PREDICT (Transact-SQL) per generare un punteggio o un valore stimato in base a un modello archiviato.

Passaggi successivi

Per altre informazioni sulle esercitazioni per R con Machine Learning in SQL, vedere:

• Esercitazioni di R