Introduzione alle stored procedure SQL in Databricks

Gestire attività SQL ripetitive, come la pulizia dei dati, l'aggiornamento delle regole di business o l'esecuzione di logiche batch, può essere noioso e soggetto a errori se si copiano e incollano codice.

Ora, le Stored Procedure SQL in Databricks (ora generalmente disponibili) consentono di memorizzare quella logica una sola volta, eseguirla ogni volta che è necessario e mantenerla sotto il controllo di Unity Catalog.

Sia che si tratti di pulire i dati prima dell'analisi, aggiornare tabelle in base a criteri di business o spostare carichi di lavoro da un data warehouse aziendale legacy, le stored procedure rendono il processo più semplice, coerente e facile da mantenere.

Databricks supporta standard aperti e interoperabilità, evitando implementazioni proprietarie o specifiche del vendor. Le Stored Procedure SQL seguono lo standard ANSI/PSM SQL e verranno contribuite a Apache Spark™ open source.

Le procedure sono ampiamente utilizzate in attività amministrative, gestione dei dati e flussi di lavoro ETL, specialmente nei data warehouse aziendali (EDW). Per i clienti che passano dagli EDW a Databricks, le stored procedure esistenti possono essere migrate senza doverle riscrivere, rendendo la transizione più semplice. E come sempre, il miglior data warehouse è un lakehouse.

Per uno dei nostri casi d'uso critici relativi alla segmentazione dei clienti, abbiamo sfruttato le Stored Procedure SQL con DBSQL per ottenere migliori prestazioni, scalabilità ed efficienza dei costi. La familiarità con SQL ci ha aiutato a implementare e distribuire la soluzione in produzione in tempi molto brevi. L'utilizzo delle Stored Procedure ci ha permesso di gestire logiche complesse in modo più efficace, mantenendo l'architettura generale snella e manutenibile. —SambaSiva Rao, Sr. Data Engineer/Architect, ClicTechnologies

Le Stored Procedure SQL sono ora generalmente disponibili.

Panoramica delle Stored Procedure SQL

Cosa sono le Stored Procedure?

Nei flussi di lavoro di elaborazione dei dati, i clienti possono avere difficoltà a mantenere la coerenza e le prestazioni di attività ripetitive e logiche complesse. Le stored procedure sono un ottimo approccio in questi casi, garantendo che i dati vengano elaborati in modo coerente e standardizzato, e che le prestazioni siano ottimali.

Per le attività di pulizia dei dati, le procedure possono applicare trasformazioni come la conversione di formati di data incoerenti in una struttura standardizzata, la rimozione degli spazi bianchi iniziali e finali dai campi di testo e la sostituzione o correzione di valori errati. Ciò garantisce che i dati siano preparati per l'analisi downstream. Vedere l'esempio ETL dettagliato di seguito.

Sul lato della gestione dei dati, le stored procedure possono aggiornare in modo efficiente i valori delle tabelle in base a regole di business definite, come la segnalazione di record obsoleti, il ricalcolo di campi o la sincronizzazione dei dati tra tabelle correlate. Incapsulando queste operazioni in procedure, i team possono garantire un'esecuzione coerente, ridurre l'intervento manuale e migliorare la qualità dei dati su larga scala. Vedere l'esempio di gestione dei dati dettagliato di seguito, utilizzando le stored procedure per aggiornare un programma fedeltà/adesione.

Quindi, cosa sono le Procedure? Sono raccolte precompilate di istruzioni SQL che consentono a un utente di gestire la propria logica SQL in un'unica unità riutilizzabile. Le procedure sono memorizzate in Unity Catalog, il che significa che sono governate e incapsulano completamente i permessi. Quando una stored procedure viene chiamata, il database esegue queste operazioni predefinite, offrendo il vantaggio di una maggiore sicurezza, una manutenzione semplificata di carichi di lavoro complessi e il potenziale per prestazioni migliorate.

Cosa è supportato?

Ci sono 5 comandi principali che supportano le procedure: CREATE, CALL, DESCRIBE, SHOW e DROP.

• CREATE PROCEDURE: Definisce e memorizza una nuova stored procedure all'interno di Unity Catalog. Specifica il nome della procedura, i parametri (se presenti) e le istruzioni SQL da eseguire quando la procedura viene chiamata.
• CALL PROCEDURE: Esegue una stored procedure creata in precedenza, passando eventuali parametri richiesti.
• DESCRIBE PROCEDURE: Restituisce i metadati di base su una procedura esistente, come il suo nome e i parametri. Con EXTENDED, la descrizione includerà metadati aggiuntivi, tra cui il proprietario, la data/ora di creazione, il tipo di sicurezza, ecc.
• SHOW PROCEDURES: Elenca tutte le stored procedure disponibili nello schema del catalogo corrente.
• DROP PROCEDURE: Elimina una procedura esistente dall'archivio.

Quando si crea una procedura, è possibile utilizzare diversi tipi di parametri per controllare l'input e l'output.

• Parametro `IN`: Utilizzato per passare valori a una procedura come input. Ad esempio, potresti passare un ID cliente per recuperare o elaborare solo i dati di quel cliente. La procedura può leggere ma non modificare questi valori.
• Parametro `OUT`: Utilizzato per restituire valori da una procedura, dopo essere stati assegnati. Ad esempio, potresti passare un ID cliente e restituire il suo stato dell'account o il totale delle vendite calcolato, per un'ulteriore elaborazione al di fuori della procedura.
• Parametro `INOUT`: Ha un duplice scopo, consentendo a un valore di essere passato a una procedura, modificato al suo interno e il valore aggiornato restituito. Funziona sia come input che come output.

Questi parametri possono essere assegnati a:

• Variabili locali, dichiarate all'interno di uno script/procedura
• Variabili di sessione, dichiarate all'interno della sessione, e anche al di fuori dello script/procedura

La logica incapsulata all'interno di una Stored Procedure SQL è costruita su SQL Scripting. Una stored procedure può essere considerata come uno script riutilizzabile con parametri, governato da Unity Catalog. Puoi leggere di Scripting in questi due blog introduttivi:

• Introduzione a SQL Scripting Parte 1
• Introduzione a SQL Scripting Parte 2
• Rivedi la documentazione di SQL Scripting

Sono supportate chiamate di procedure nidificate e ricorsive, il che significa che i clienti possono organizzare le proprie unità di lavoro o logiche di business in modo conveniente in procedure separate, rendendo l'intero flusso di esecuzione SQL più modulare. Ciò migliora la leggibilità e la manutenibilità.

In che modo le Procedure differiscono dalle Funzioni?

Le procedure sono raggruppate con le Funzioni in Unity Catalog nell'interfaccia utente. Tuttavia, procedure e funzioni, pur consentendo di riutilizzare la logica SQL, servono scopi diversi.

Una funzione viene utilizzata per restituire un valore o una tabella. Deve essere utilizzata all'interno di una query SQL e non può includere SQL dinamico o logica procedurale. Una procedura, al contrario, viene utilizzata per eseguire una sequenza di istruzioni SQL. Può includere flussi di controllo, variabili, cicli e SQL dinamico utilizzando IDENTIFIER ed EXECUTE IMMEDIATE. Si chiama una procedura come comando autonomo, tipicamente per eseguire un'attività o un flusso di lavoro.

Esempi di utilizzo delle Stored Procedure SQL

Ora che abbiamo trattato le capacità delle Stored Procedure SQL, esploriamo alcuni esempi per dimostrare il loro valore e i problemi che aiutano a risolvere.

Puoi usare questo notebook per seguire, contiene tutti gli esempi di questo post, oltre ai comandi di preparazione dei dati.

ETL – Pulizia dei dati: Preparazione delle tabelle Silver o Gold

Se segui la tipica architettura medallion, sai che spostare i dati da Bronze a Silver (o da Silver a Gold) può richiedere la pulizia, la trasformazione, l'aggregazione e la formattazione dei dati. Le Stored Procedure sono ottime per gestire processi ripetitivi come questi all'interno di un flusso di lavoro ETL. 

In questo scenario ETL, una Procedura viene utilizzata per:

• Caricare i dati da raw in una tabella di fatti
• Selezionare i dati in base all'intervallo di date e all'origine della vendita (web, mobile, punto vendita, fornitori terzi)
• Pulire e formattare i dati convertendo le date in un formato specifico e rimuovendo gli spazi bianchi
• Una volta puliti, caricare i dati in una tabella 'pulita'
• Aggiungere un record di log basato su timestamp, date da-a e origine della vendita
• Utilizzando la Procedura, cercare le vendite da app mobile per giugno 2025

Procedure come questa aiutano a standardizzare i prodotti di dati. Qualsiasi utente di questa procedura produrrà dati nella stessa struttura, indipendentemente dall'intervallo di date o dal punto vendita. Questo è un vantaggio primario del riutilizzo del codice. Il riutilizzo del codice sarà naturalmente meno soggetto a errori poiché la stessa logica viene eseguita ogni volta.

Gestione dei dati: Aggiornare una tabella in base ai criteri di business

La gestione dei dati è la pratica di garantire che i tuoi dati siano accurati, coerenti e accessibili in modo efficiente, qualità essenziali per qualsiasi organizzazione che miri a prendere decisioni basate sui dati. Senza una solida gestione dei dati, anche gli sforzi di analisi o reporting più avanzati possono essere compromessi da informazioni inaffidabili o incoerenti. 

Esaminiamo un esempio comune nell'industria commerciale in cui un'azienda stabilisce un programma fedeltà per fornire ai clienti vantaggi basati sul loro livello. Le compagnie aeree hanno programmi frequent flyer e la maggior parte delle catene di negozi al dettaglio ha programmi di ricompense, ecc. Man mano che i clienti volano di più con la stessa compagnia aerea o acquistano più articoli dalla stessa catena, i clienti guadagnano più vantaggi.

Ecco un esempio di come le Stored Procedure possono essere utilizzate per gestire e aggiornare un programma fedeltà standard per la vendita al dettaglio. Ci sono due procedure utilizzate per gestire i livelli fedeltà dei clienti: una per aggiornare il livello fedeltà di un cliente specifico per il customerID fornito e l'altra che aggiorna il livello fedeltà dei clienti per tutti i clienti di un paese fornito.

Ora utilizziamo la procedura creata per aggiornare i livelli fedeltà dei clienti per i clienti della Serbia, Germania e Canada, e poi controlliamo i record aggiornati:

La query precedente produce il seguente risultato:

Incapsulando la logica di aggiornamento del livello nei rispettivi procedure, evitiamo la duplicazione del codice e rimuoviamo la complessità dal chiamante, che deve solo invocare la procedura con i parametri appropriati.

Cosa fare dopo?

Con le Stored Procedure SQL ora in DBSQL, i clienti possono continuare a migrare i carichi di lavoro dei data warehouse aziendali legacy nel lakehouse. Sulla base del feedback dei clienti, ci sono diverse funzionalità chiave che vogliamo affrontare mentre ci muoviamo verso la GA:

• Stored Procedure SQL in Apache Spark™: Supporto per tutte le Stored Procedure SQL nell'open source
• Supporto per SQL SECURITY DEFINER: Consentire ai clienti di eseguire la logica della procedura con le autorizzazioni del creatore della procedura (definer)

I clienti che desiderano condividere feedback o richieste relative a SQL Scripting e Procedure possono farlo tramite questo modulo.

Altri due importanti costrutti SQL sono necessari per migrare le Stored Procedure dai sistemi legacy: le Tabelle Temporanee e le Transazioni. Le Tabelle Temporanee sono ora generalmente disponibili su DBSQL, mentre il supporto per le Transazioni (multi-statement e multi-tabella) sarà presto disponibile. 

Sia che tu sia un utente Databricks esistente o che tu stia migrando da un altro Data Warehouse, le Stored Procedure SQL sono una funzionalità che dovresti usare per semplificare la gestione di flussi di lavoro SQL complessi. Inizia con le Stored Procedure SQL leggendo la documentazione di Databricks documentazione.

Per saperne di più su Databricks SQL, visita il nostro sito web o leggi la documentazione. Puoi anche dare un'occhiata al tour del prodotto per Databricks SQL. Se vuoi migrare il tuo data warehouse esistente a un data warehouse serverless ad alte prestazioni con un'ottima esperienza utente e un costo totale inferiore, allora Databricks SQL è la soluzione — provalo gratuitamente.

(Questo post sul blog è stato tradotto utilizzando strumenti basati sull'intelligenza artificiale) Post originale