Avvio rapido: Creare un modello predittivo in Python e assegnare i punteggi con l’apprendimento automatico in SQL

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

Questo argomento di avvio rapido illustra come creare ed eseguire il training di un modello predittivo con Python. Il modello verrà salvato in una tabella nell'istanza di SQL Server in uso, quindi si userà il modello per stimare i valori dei nuovi dati tramite Machine Learning Services per SQL Server, Machine Learning Services per Istanza gestita di SQL di Azure o cluster Big Data di SQL Server.

Creerai ed eseguirai due stored procedure utilizzando SQL. La prima usa il classico set di dati Iris e genera un modello Naïve Bayes per stimare una specie di iris in base alle caratteristiche del fiore. 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 Python 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:

Come incorporare codice Python in una procedura memorizzata
Come passare gli input al codice tramite una procedura memorizzata
Come usare le stored procedure per rendere operativi i modelli

Prerequisiti

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

Un database SQL in una di queste piattaforme:
- Machine Learning Services per SQL Server. Per eseguire l'installazione, vedere la guida all'installazione in Windows o la guida all'installazione in Linux.
- Cluster di Big Data di SQL Server. Vedi come abilitare Machine Learning Services nei cluster Big Data di SQL Server.
- Servizi di Machine Learning sull’istanza gestita di SQL di Azure. Per informazioni, vedere Panoramica dei Servizi di Machine Learning per Istanza Gestita SQL di Azure.
Uno strumento per l'esecuzione di query SQL che contengono script Python. Questa guida rapida utilizza l'estensione MSSQL per Visual Studio Code.
In questo esercizio vengono usati i dati di esempio del set di dati Iris. Seguire le istruzioni nei dati demo del set di dati Iris per creare il database di esempio irissql.

Creare una stored procedure che genera modelli

In questo passaggio si creerà una stored procedure che genera un modello per la stima dei risultati.

Connettersi all'istanza di SQL con l'estensione MSSQL per Visual Studio Code e aprire una nuova finestra di query.
Stabilire la connessione al database irissql.
```
USE irissql
GO
```
Copia il codice seguente per creare una nuova procedura memorizzata.

Quando viene eseguita, questa procedura chiama sp_execute_external_script per avviare una sessione di Python.

Gli input necessari per il tuo codice Python vengono passati come parametri di input in questa procedura memorizzata. L'output sarà un modello addestrato basato sulla libreria Python scikit-learn per l'algoritmo di apprendimento automatico.

Questo codice usa pickle per serializzare il modello. Il training del modello verrà eseguito usando i dati delle colonne da 0 a 4 della tabella iris_data.

I parametri che vedi nella seconda parte della procedura articolano gli input dei dati e gli output del modello. Per quanto possibile, il codice Python in esecuzione in una stored procedure dovrebbe disporre di input e output chiaramente definiti che mappano agli input e output della stored procedure forniti durante l'esecuzione.
```
CREATE PROCEDURE generate_iris_model (@trained_model VARBINARY(max) OUTPUT)
AS
BEGIN
    EXECUTE sp_execute_external_script @language = N'Python'
        , @script = N'
import pickle
from sklearn.naive_bayes import GaussianNB
GNB = GaussianNB()
trained_model = pickle.dumps(GNB.fit(iris_data[["Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width"]], iris_data[["SpeciesId"]].values.ravel()))
'
        , @input_data_1 = N'select "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "SpeciesId" from iris_data'
        , @input_data_1_name = N'iris_data'
        , @params = N'@trained_model varbinary(max) OUTPUT'
        , @trained_model = @trained_model OUTPUT;
END;
GO
```
Verificare che la procedura memorizzata esista.

Se lo script T-SQL del passaggio precedente è stato eseguito senza errori, una nuova stored procedure denominata generate_iris_model viene creata e aggiunta al database irissql. È possibile trovare le stored procedure in Esplora oggetti di Visual Studio Code, sotto Programmabilità.

Eseguire la procedura per creare e addestrare modelli

In questo passaggio viene eseguita la procedura per eseguire il codice incorporato, per creare un modello addestrato e serializzato come output.

I modelli archiviati per il riutilizzo nel database vengono serializzati come flusso di byte e archiviati in una colonna VARBINARY(MAX) in una tabella di database. Dopo la creazione, l'addestramento, la serializzazione e il salvataggio in un database, il modello può essere chiamato da altre procedure o dalla funzione PREDICT T-SQL nei carichi di lavoro di verifica dei punteggi.

Esegui il seguente script per eseguire la procedura. L'istruzione specifica per l'esecuzione di una stored procedure è EXECUTE nella quarta riga.

Questo particolare script elimina un modello esistente con lo stesso nome ("Naive Bayes") per creare spazio per quelli nuovi creati rieseguendo la stessa procedura. Se non si elimina il modello, si verifica un errore che indica che l'oggetto esiste già. Il modello viene archiviato in una tabella denominata iris_models, di cui è stato effettuato il provisioning al momento della creazione del database irissql.
```
DECLARE @model varbinary(max);
DECLARE @new_model_name varchar(50)
SET @new_model_name = 'Naive Bayes'
EXECUTE generate_iris_model @model OUTPUT;
DELETE iris_models WHERE model_name = @new_model_name;
INSERT INTO iris_models (model_name, model) values(@new_model_name, @model);
GO
```
Verificare che il modello sia stato inserito.
```
SELECT * FROM dbo.iris_models
```
Risultati

model_name modello

Bayes ingenuo 0x800363736B6C6561726E2E6E616976655F62617965730A...

model_name	modello
Bayes ingenuo	0x800363736B6C6561726E2E6E616976655F62617965730A...

Creare ed eseguire una stored procedure per generare le stime

Ora che un modello è stato creato, sottoposto a training e salvato, procedere con il passaggio successivo, ossia la creazione di una stored procedure che genera stime. A questo scopo occorre chiamare sp_execute_external_script per eseguire uno script Python che carica il modello serializzato e gli passa nuovi input di dati a cui assegnare i punteggi.

Eseguire il codice seguente per creare la stored procedure che esegue l'assegnazione dei punteggi. In fase di esecuzione, questa stored procedure caricherà un modello binario, userà le colonne [1,2,3,4] come input e specificherà le colonne [0,5,6] come output.

CREATE PROCEDURE predict_species (@model VARCHAR(100))
AS
BEGIN
    DECLARE @nb_model VARBINARY(max) = (
            SELECT model
            FROM iris_models
            WHERE model_name = @model
            );

    EXECUTE sp_execute_external_script @language = N'Python'
        , @script = N'
import pickle
irismodel = pickle.loads(nb_model)
species_pred = irismodel.predict(iris_data[["Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width"]])
iris_data["PredictedSpecies"] = species_pred
OutputDataSet = iris_data[["id","SpeciesId","PredictedSpecies"]] 
print(OutputDataSet)
'
        , @input_data_1 = N'select id, "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "SpeciesId" from iris_data'
        , @input_data_1_name = N'iris_data'
        , @params = N'@nb_model varbinary(max)'
        , @nb_model = @nb_model
    WITH RESULT SETS((
                "id" INT
              , "SpeciesId" INT
              , "SpeciesId.Predicted" INT
                ));
END;
GO

Eseguire la procedura memorizzata specificando il nome del modello "Naive Bayes" così che la procedura sappia quale modello utilizzare.
```
EXECUTE predict_species 'Naive Bayes';
GO
```
Quando viene eseguita, la stored procedure restituisce un data.frame Python. Questa riga di T-SQL specifica lo schema dei risultati restituiti: WITH RESULT SETS ( ("id" int, "SpeciesId" int, "SpeciesId.Predicted" int));. È possibile inserire i risultati in una nuova tabella o restituirli a un'applicazione.

I risultati sono 150 stime sulle specie usando caratteristiche floreali come input. Per la maggior parte delle osservazioni, le specie stimate corrispondono alle specie effettive.

Questo esempio è stato reso semplice usando il set di dati Iris di Python sia per il training che per l'assegnazione dei punteggi. Un approccio più comune consiste nell'eseguire una query SQL per ottenere i nuovi dati e quindi passarli in Python come InputDataSet.

Conclusione

In questo esercizio si è appreso come creare stored procedure dedicate a diverse attività, in cui ognuna ha usato la stored procedure di sistema sp_execute_external_script per avviare un processo Python. Gli input del processo Python vengono passati a sp_execute_external come parametri. Sia lo script Python che le variabili di dati in un database vengono passati come input.

In genere, è consigliabile pianificare solo l'uso di Visual Studio Code con codice Python lucido o codice Python semplice che restituisce un output basato su righe. Come strumento, Visual Studio Code supporta linguaggi di query come T-SQL e restituisce set di righe bidimensionali. Se il codice genera un output visivo come un grafico a dispersione o un istogramma, sono necessari uno strumento o un'applicazione per utenti finali separati in grado di eseguire il rendering dell'immagine all'esterno della stored procedure.

Per alcuni sviluppatori Python abituati a scrivere script onnicomprensivi che gestiscono una serie di operazioni, l'organizzazione delle attività in procedure separate potrebbe sembrare superflua. Il training e l'assegnazione dei punteggi hanno però casi d'uso diversi. Separando queste attività, è possibile inserirle in pianificazioni diverse e assegnare le autorizzazioni in base a ogni operazione.

Un vantaggio finale è che i processi possono essere modificati usando parametri. In questo esercizio, il codice Python che ha creato il modello di esempio denominato "Naive Bayes" è stato passato come input a una seconda stored procedure che chiama il modello in un processo di assegnazione dei punteggi. Questo esercizio usa solo un modello, ma si può immaginare come la parametrizzazione del modello in un'attività di assegnazione dei punteggi possa rendere lo script più utile.

Passaggi successivi

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

Esercitazioni di Python

Commenti e suggerimenti

Questa pagina è stata utile?

Last updated on 2026-02-18