Introduzione alle Common Table Expression Ricorsive: Rendere Databricks SQL Turing Completo

Ora è supportato l'uso delle Common Table Expression (CTE) ricorsive in Databricks. Questo offre un modo nativo per esprimere cicli e attraversamenti in SQL, utile per lavorare con dati strutturati gerarchicamente e in grafo. Queste funzionalità sono allineate allo standard SQL e seguono schemi familiari utilizzati nei data warehouse legacy come Teradata e Snowflake, rendendo le migrazioni da tali data warehouse molto più semplici. Databricks ha anche contribuito al supporto delle CTE ricorsive in Apache Spark™, rendendolo completamente open source.

Databricks utilizza la sintassi SQL ANSI standard per le CTE ricorsive, inclusa la parola chiave RECURSIVE.

Questa funzionalità, apparentemente piccola, migliora significativamente le capacità espressive di SQL, rendendolo teoricamente Turing completo, il che significa che può eseguire qualsiasi calcolo un computer può fare. Le CTE ricorsive consentono soluzioni componibili che in precedenza richiedevano codice procedurale, come Python o strumenti esterni.

CTE ricorsive sono ora disponibili in anteprima pubblica DBSQL 2025.20 e Databricks Runtime 17.0 (in arrivo su Lakeflow Declarative Pipelines). In questo post esploreremo come funzionano le CTE ricorsive e come possono aiutarti a risolvere problemi del mondo reale usando SQL puro.

Funzionalità chiave del supporto CTE ricorsive

Il supporto CTE ricorsive di Databricks include:

• Attraversamento di strutture ad albero e a grafo, come organigrammi, cartelle e reti di routing
• Completamente open source e integrato in Apache Spark™
• Salvaguardie integrate per la ricorsione infinita (100 passaggi, 1 milione di righe)
• Salvaguardie personalizzabili tramite MAX RECURSION LEVEL
• Supporto per la ricorsione infinita controllata tramite LIMIT

Le CTE ricorsive funzionano bene sia con sistemi tradizionali che memorizzano dati gerarchici in tabelle normalizzate, sia con dati provenienti da applicazioni moderne che generano gerarchie JSON/XML flessibili. Vedi gli esempi seguenti per ciascuno, incluse le CTE ricorsive che sfruttano il tipo di dati Variant per le gerarchie JSON.

Inoltre, il supporto per le CTE ricorsive semplifica le migrazioni dai sistemi di database legacy. Teradata e Postgres sono due esempi di sistemi la cui sintassi è identica, mentre sistemi come Oracle, che utilizzano la sintassi CONNECT BY, sono facilmente convertibili.

Come funzionano le CTE ricorsive

Le CTE ricorsive sono common table expression definite con la parola chiave RECURSIVE. Sono composte da due parti combinate tramite UNION ALL:

1. Una sottoquery di caso base — questa viene eseguita una volta e avvia la ricorsione
2. Una sottoquery di passo ricorsivo — questa fa riferimento alla CTE stessa e viene applicata ripetutamente per costruire nuove righe.

L'esecuzione inizia con la query di base. Quindi, ad ogni iterazione, il passo ricorsivo viene eseguito utilizzando l'output del passo precedente. Questo continua finché non vengono prodotte nuove righe.

Per evitare che la ricorsione infinita consumi risorse eccessive, Databricks applica due limiti di sicurezza: una profondità massima di ricorsione di 100 passaggi e un limite di righe di 1 milione. Se una delle due soglie viene superata, la query fallisce con un errore.

Se sei sicuro che la tua ricorsione richieda più di 100 passaggi per produrre tutti i risultati, puoi sovrascrivere il livello massimo utilizzando l'hint MAX RECURSION LEVEL:

 Per maggiori dettagli, consulta la documentazione CTE.

“In bp Supply Trading and Shipping – Market Risk, la comprensione del reporting della gerarchia del portafoglio tra le unità di business è fondamentale per il buon funzionamento della nostra attività. Sostituendo il nostro codice legacy con CTE ricorsive in Databricks SQL, abbiamo ridotto un passaggio di preparazione dei dati gerarchici da circa 6 minuti a circa 30 secondi, con un miglioramento di 12 volte.” — Dharmik Prajapati, bp Staff Software Engineer

Esempi di risoluzione di attività iterative tramite CTE ricorsive

Navigazione dati ad albero e gerarchici: Ricerca dei materiali necessari tramite Distinta Base

Nel settore manifatturiero, ogni componente prodotto richiede un set di componenti per essere assemblato. Ogni componente può essere scomposto in un set più piccolo di parti individuali. Il set completo di tutte le parti è chiamato Distinta Base (BOM). 

Una BOM forma spesso una struttura ad albero, o più in generale, un grafo aciclico diretto (DAG). In questo esempio, esaminiamo le parti di una bicicletta, che semplificheremo assumendo una struttura ad albero, dove ogni componente è utilizzato in un unico elemento padre.

Supponiamo di voler calcolare quanti materiali grezzi sono necessari per costruire una bicicletta. Consideriamo la seguente BOM:

Ogni riga descrive un componente, la parte più grande a cui appartiene e quanti componenti sono necessari per assemblare un'unità dell'elemento padre.

La CTE ricorsiva inizia con un obiettivo: costruire una bicicletta. Questo è il caso base. In ogni passaggio ricorsivo, scomponiamo i componenti nei loro sottocomponenti. Ad esempio, una bicicletta include un telaio, una trasmissione e due ruote. Ogni ruota, a sua volta, è composta da uno pneumatico e 32 raggi. La struttura ricorsiva diventa chiara man mano che scomponiamo le parti in pezzi più piccoli.

Una volta espansa completamente la gerarchia, filtriamo i componenti intermedi (genitori) per conservare solo le materie prime necessarie per l'assemblaggio.

Questa query calcola la quantità totale di ogni materiale di base necessario per costruire una bicicletta:

Ricerca di percorsi basata sulla traversata del grafo: Trovare tutti i percorsi aerei da una città

Esaminiamo un problema utilizzando una struttura dati a grafo. Un grafo è costituito da un insieme di nodi collegati da archi. Viene utilizzato per rappresentare relazioni o connessioni tra coppie di elementi. Risolvere un problema di grafo richiedeva in precedenza Python, logica di scripting complessa o una libreria esterna. Ora, le query ricorsive lo rendono semplice. 

Un tipico problema di struttura a grafo è il viaggio in aereo: quali aeroporti posso raggiungere utilizzando una serie di voli? Supponiamo di avere il seguente insieme di voli che esistono in un giorno:

Ogni volo è identificato dai codici IATA della sua origine e destinazione, insieme agli orari di partenza e arrivo.

Supponiamo che una persona arrivi all'aeroporto BEG alle 8 del mattino e voglia trovare tutti i possibili itinerari di viaggio che può fare quel giorno.

Questo è naturalmente posto come un problema iterativo. Ogni volta che scopriamo una nuova città raggiungibile, troviamo tutti i voli che partono da lì dopo il nostro orario di arrivo. Per questo motivo, nella CTE ricorsiva, teniamo traccia dell'orario di arrivo in ogni aeroporto.  

Questo produce l'insieme di tutti gli aeroporti raggiungibili, insieme al numero e all'insieme di voli richiesti.

Questa query può aiutare gli utenti a esplorare tutte le destinazioni raggiungibili dati i vincoli di orario, supportando applicazioni come la pianificazione di viaggi, il routing di pacchi o la logistica dei trasporti.

Nell'esempio precedente, abbiamo definito i nomi delle colonne nella clausola WITH RECURSIVE ... AS (...). Qui, li definiamo invece nella query di ancoraggio. Entrambi gli approcci sono validi nelle CTE ricorsive su Databricks.

Analisi di dati semi-strutturati e non strutturati: Trovare tutti gli impiegati archiviati in un file JSON

I sistemi tradizionali spesso archiviano dati gerarchici in tabelle rigide e normalizzate. Nel frattempo, le applicazioni moderne generano frequentemente gerarchie JSON/XML flessibili. La combinazione di CTE ricorsive di Databricks con il tipo VARIANT consente di migrare questi modelli di dati in modo fluido, permettendo di interrogare sia dati normalizzati tradizionali che strutture JSON/XML flessibili in un unico sistema.

In questo esempio, ci viene data una gerarchia aziendale (relativamente piccola). Ma invece di una tabella completamente strutturata, ci viene data sotto forma di JSON:

Supponiamo di volere un elenco di tutti gli impiegati e i loro titoli in una tabella. I campi delle persone nell'azienda non seguono lo stesso schema: alcuni hanno subordinati diretti, altri no; alcuni hanno la loro posizione, altri no! Con l'uso del datatype VARIANT in Databricks, tutte le loro necessità comuni possono essere utilizzate all'interno di una CTE ricorsiva per esplorare completamente la struttura annidata del JSON, mentre le loro differenze possono essere ignorate.

Il caso base della ricorsione è il JSON completo dei dati del dipendente principale, che include un elenco dei suoi subordinati. In ogni passaggio ricorsivo, la query elabora i dati di ciascun subordinato e ripete il processo fino a raggiungere un dipendente senza subordinati.

Ecco la query ricorsiva per questo esempio:

Anche se tutte le CTE qui sono sotto un blocco WITH RECURSIVE, solo quella con ricorsione effettiva viene trattata come ricorsiva. Databricks è abbastanza intelligente da rilevare quali necessitano di ricorsione, anche se le marchi tutte!

L'output della query:

Come iniziare

Inizia con le CTE ricorsive leggendo la documentazione di Databricks. 

Il miglior data warehouse è un lakehouse. 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 costi totali inferiori, allora Databricks SQL è la soluzione — provalo gratuitamente.

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