Modificare il codice R/Python per l'esecuzione in istanze (in database) di SQL Server

  • Articolo

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

In questo articolo sono disponibili indicazioni generali su come modificare il codice R o Python in modo che venga eseguito come stored procedure di SQL Server e sia pertanto possibile migliorare le prestazioni durante l'accesso ai dati SQL.

Quando si sposta il codice R/Python da un IDE locale o da un altro ambiente in SQL Server, in genere il codice funziona senza che siano necessarie ulteriori modifiche. Ciò vale soprattutto per il codice semplice, ad esempio una funzione che accetta alcuni input e restituisce un valore. Con ancora maggiore facilità, è possibile eseguire il trasferimento di soluzioni che usano i pacchetti RevoScaleR/revoscalepy, che supportano l'esecuzione in contesti di esecuzione diversi con modifiche minime. Tenere presente che MicrosoftML si applica a SQL Server 2016 (13.x), SQL Server 2017 (14.x) e SQL Server 2019 (15.x) e non è disponibile in SQL Server 2022 (16.x).

Il codice potrebbe tuttavia richiedere modifiche sostanziali se si verifica una delle condizioni seguenti:

  • Si usano librerie R che accedono alla rete o che non è possibile installare in SQL Server.
  • Il codice effettua chiamate separate alle origini dati all'esterno di SQL Server, ad esempio fogli di lavoro di Excel, file in condivisioni e altri database.
  • Si desidera assegnare parametri alla stored procedure ed eseguire il codice nel parametro @script di sp_execute_external_script.
  • La soluzione originale include più passaggi che potrebbero essere più efficienti in un ambiente di produzione se eseguiti in modo indipendente, ad esempio la preparazione dei dati o la progettazione delle caratteristiche rispetto al training del modello, all'assegnazione dei punteggi o alla creazione di report.
  • Si vogliono ottimizzare le prestazioni modificando le librerie, usando l'esecuzione parallela o eseguendo l'offload di alcuni processi in SQL Server.

Passaggio 1: Pianificare risorse e requisiti

Pacchetti

  • Determinare quali pacchetti sono necessari e verificare che funzionino in SQL Server.

  • Installare i pacchetti in anticipo, nella libreria di pacchetti predefinita usata da Machine Learning Services. Le librerie degli utenti non sono supportate.

Origini dati

  • Se si desidera incorporare il codice R in sp_execute_external_script, identificare le origini dati primarie e secondarie.

    • Le origini dati primarie sono set di dati di grandi dimensioni, ad esempio dati di training del modello o dati di input per le stime. Pianificare il mapping dei set di dati di maggiori dimensioni al parametro di input di sp_execute_external_script.

    • Le origini dati secondarie sono in genere set di dati di dimensioni minori, ad esempio elenchi di fattori o variabili di raggruppamento aggiuntive.

    Attualmente sp_execute_external_script supporta solo un singolo set di dati come input per la stored procedure. È tuttavia possibile aggiungere più input scalari o binari.

    Le chiamate di stored procedure precedute da EXECUTE non possono essere usate come input per sp_execute_external_script. È possibile usare query, viste o qualsiasi altra istruzione SELECT valida.

  • Determinare gli output necessari. Se si esegue il codice usando sp_execute_external_script, la stored procedure può restituire un unico frame di dati come risultato. Tuttavia, è anche possibile generare diversi output scalari, inclusi tracciati e modelli in formato binario, nonché altri valori scalari derivati dal codice R o dai parametri SQL.

Tipo di dati

Per un'analisi dettagliata dei mapping dei tipi di dati tra R/Python e SQL Server, vedere gli articoli seguenti:

Esaminare i tipi di dati usati nel codice R/Python ed effettuare le operazioni seguenti:

  • Creare un elenco di controllo dei possibili problemi con i tipi di dati.

    Tutti i tipi di dati R/Python sono supportati da Machine Learning Services per SQL Server. Tuttavia, SQL Server supporta una gamma di tipi di dati più ampia rispetto a R o Python. Di conseguenza, durante i trasferimenti dei dati di SQL Server in e da il codice, vengono eseguite alcune conversioni implicite dei tipi dei dati. Potrebbe essere necessario eseguire il cast esplicito di alcuni dati o convertirli.

    I valori NULL sono supportati. R usa tuttavia il costrutto di dati na per rappresentare un valore mancante, che è simile a un valore null.

  • Provare a eliminare la dipendenza dai dati che non possono essere usati da R. I tipi di dati rowid e GUID di SQL Server, ad esempio, non possono essere utilizzati da R e generano errori.

Passaggio 2. Convertire o creare un nuovo pacchetto di codice

L'entità delle modifiche da apportare al codice varia a seconda che si pianifichi di inviare il codice R da un client remoto per l'esecuzione nel contesto di calcolo di SQL Server o che si pianifichi di distribuire il codice come parte di una stored procedure. Quest'ultima opzione può offrire un livello superiore di prestazioni e sicurezza dei dati, ma impone alcuni requisiti aggiuntivi.

  • Quando possibile, definire i dati di input primari come query SQL per evitare spostamenti di dati.

  • Quando si esegue il codice in una stored procedure, è possibile eseguire il pass-through di più input scalari. Per i parametri che si vuole usare nell'output, aggiungere la parola chiave OUTPUT.

    Ad esempio, l'input scalare @model_name seguente contiene il nome del modello, che viene successivamente anche modificato dallo script R, e viene restituito nella relativa colonna nei risultati:

    -- declare a local scalar variable which will be passed into the R script
DECLARE @local_model_name AS NVARCHAR (50) = 'DefaultModel';

-- The below defines an OUTPUT variable in the scope of the R script, called model_name
-- Syntactically, it is defined by using the @model_name name. Be aware that the sequence
-- of these parameters is very important. Mandatory parameters to sp_execute_external_script
-- must appear first, followed by the additional parameter definitions like @params, etc.
EXECUTE sp_execute_external_script @language = N'R', @script = N'
  model_name <- "Model name from R script"
  OutputDataSet <- data.frame(InputDataSet$c1, model_name)'
  , @input_data_1 = N'SELECT 1 AS c1'
  , @params = N'@model_name nvarchar(50) OUTPUT'
  , @model_name = @local_model_name OUTPUT;

-- optionally, examine the new value for the local variable:
SELECT @local_model_name;

  • È necessario eseguire il mapping di qualsiasi variabile passata come parametro della stored procedure sp_execute_external_script alle variabili presenti nel codice. Per impostazione predefinita, il mapping delle variabili viene eseguito in base al nome. È anche necessario eseguire il mapping di tutte le colonne del set di dati di input alle variabili presenti nello script R.

    Si supponga, ad esempio, che lo script R contenga una formula come la seguente:

    formula <- ArrDelay ~ CRSDepTime + DayOfWeek + CRSDepHour:DayOfWeek

    Se il set di dati di input non contiene colonne con i nomi ArrDelay, CRSDepTime, DayOfWeek, CRSDepHour e DayOfWeek corrispondenti, viene generato un errore.

  • In alcuni casi, è necessario definire in anticipo uno schema di output per i risultati.

    Per inserire i dati in una tabella, ad esempio, è necessario usare la clausola WITH RESULT SET per specificare lo schema.

    Se lo script R usa l'argomento @parallel=1, è anche necessario lo schema di output, perché SQL Server potrebbe creare più processi per eseguire la query in parallelo, raccogliendo i risultati alla fine. Prima che possano essere creati processi paralleli, deve quindi essere preparato lo schema di output.

    In altri casi, è possibile omettere lo schema dei risultati usando l'opzione WITH RESULT SETS UNDEFINED. Questa istruzione restituisce il set di dati dallo script R senza denominare le colonne o specificare i tipi di dati SQL.

  • Provare a generare dati temporali o di rilevamento usando T-SQL anziché R/Python.

    È ad esempio possibile passare l'ora di sistema o altre informazioni usate per il controllo e l'archiviazione aggiungendo una chiamata T-SQL che viene passata ai risultati, anziché generare dati simili nello script.

Migliorare le prestazioni e la sicurezza

  • Evitare di scrivere stime o risultati intermedi in un file. È preferibile scrivere le stime in una tabella per evitare lo spostamento dei dati.

  • Eseguire tutte le query in anticipo ed esaminare i piani di query di SQL Server per identificare le attività che possono essere eseguite in parallelo.

    Se la query di input può essere eseguita in parallelo, impostare @parallel=1 come parte degli argomenti su sp_execute_external_script.

    L'elaborazione parallela con questo flag è in genere possibile ogni volta che SQL Server può usare le tabelle partizionate o distribuire una query tra più processi e aggregare i risultati alla fine. In genere, l'elaborazione parallela con questo flag non è possibile se si esegue il training dei modelli usando algoritmi che richiedono la lettura di tutti i dati o se è necessario creare aggregati.

  • Riesaminare il codice per determinare se sono presenti passaggi che possono essere eseguiti in modo indipendente o più efficiente, usando una chiamata alla stored procedure separata. È ad esempio possibile migliorare le prestazioni eseguendo la progettazione o l'estrazione di caratteristiche separatamente e salvando i valori in una tabella.

  • Cercare di individuare metodi che consentano di usare T-SQL invece del codice R/Python per i calcoli basati su set.

    Questa soluzione R, ad esempio, mostra come le funzioni T-SQL definite dall'utente e R possono eseguire la stessa attività di ingegneria delle funzionalità: Procedura dettagliata end-to-end per l'analisi scientifica dei dati.

  • Consultare uno sviluppatore di database per individuare i metodi che consentono di migliorare le prestazioni usando le funzionalità di SQL Server, ad esempio le tabelle ottimizzate per la memoria o, se si usa Enterprise Edition, Resource Governor.

  • Se si usa R, è possibile sostituire le funzioni R convenzionali con funzioni RevoScaleR che supportano l'esecuzione distribuita. Per altre informazioni, vedere Confronto di funzioni R di base e RevoScaleR.

Passaggio 3. Preparare la distribuzione

  • Inviare una notifica all'amministratore in modo che i pacchetti possano essere installati e testati prima di distribuire il codice.

    In un ambiente di sviluppo è possibile installare i pacchetti come parte del codice, ma è meglio evitare di farlo in un ambiente di produzione.

    Le librerie utente non sono supportate, anche se si usa una stored procedure o si esegue il codice R/Python nel contesto di calcolo di SQL Server.

Creare il pacchetto del codice R/Python in una stored procedure

  • Creare una funzione T-SQL definita dall'utente, incorporando il codice tramite l'istruzione sp-execute-external-script.

  • Se il codice R è complesso, usare il pacchetto R sqlrutils per convertirlo. Questo pacchetto è stato progettato per consentire agli utenti esperti di R di scrivere codice di stored procedure valido. È possibile riscrivere il codice R come funzione singola con input e output chiaramente definiti, quindi usare il pacchetto sqlrutils per generare l'input e gli output nel formato corretto. Il pacchetto sqlrutils genera automaticamente il codice completo della stored procedure e può anche registrare la stored procedure nel database.

    Per altre informazioni ed esempi, vedere sqlrutils (SQL).

Eseguire l'integrazione con altri flussi di lavoro

  • Sfruttare gli strumenti T-SQL e i processi ETL. Eseguire la progettazione di caratteristiche, l'estrazione di caratteristiche e la pulizia dei dati in anticipo come parte dei flussi di lavoro dei dati.

    Quando si lavora in un ambiente di sviluppo dedicato, è possibile eseguire il pull dei dati nel computer, analizzare i dati in modo iterativo e quindi scrivere o visualizzare i risultati. Ad ogni modo, quando si esegue la migrazione di codice autonomo a SQL Server, gran parte di questo processo può essere semplificato o delegato ad altri strumenti di SQL Server.

  • Usare strategie di visualizzazione asincrona sicura.

    Gli utenti di SQL Server spesso non possono accedere ai file che si trovano nel server e, in genere, gli strumenti client di SQL non supportano i dispositivi per la grafica R/Python. Se si generano tracciati o altri elementi grafici come parte della soluzione, provare a esportare i tracciati come dati binari e a salvarli in una tabella oppure a scriverli.

  • Eseguire il wrapping delle funzioni di stima e di assegnazione dei punteggi nelle stored procedure per l'accesso diretto con le applicazioni.

Passaggi successivi

Per alcuni esempi sul modo in cui le soluzioni R e Python possono essere distribuite in SQL Server, fare riferimento alle esercitazioni seguenti:

Esercitazioni di R

Esercitazioni di Python