Introduzione alle funzioni definite dall'utente SQL

Una funzione definita dall'utente (UDF) è un modo per un utente di estendere le capacità native di Apache Spark™ SQL. SQL su Databricks supporta funzioni definite dall'utente esterne scritte nei linguaggi di programmazione Scala, Java, Python e R fin dalla versione 1.3.0. Sebbene le UDF esterne siano molto potenti, presentano anche alcuni avvertimenti:

• Sicurezza. Una UDF scritta in un linguaggio esterno può eseguire codice pericoloso o addirittura dannoso. Ciò richiede un controllo rigoroso su chi può creare UDF.
• Prestazioni. Le UDF sono scatole nere per il Catalyst Optimizer. Dato che Catalyst non è a conoscenza del funzionamento interno di una UDF, non può fare nulla per migliorarne le prestazioni nel contesto di una query SQL.
• Usabilità SQL. Per un utente SQL, può essere macchinoso scrivere UDF in un linguaggio host e poi registrarle in Spark. Inoltre, ci sono una serie di estensioni che molti utenti potrebbero voler apportare a SQL che sono piuttosto semplici, dove lo sviluppo di una UDF esterna è eccessivo.

Per far fronte alle limitazioni di cui sopra, siamo entusiasti di introdurre una nuova forma di UDF: le UDF SQL. Disponibile in DBR 9.1 LTS, la UDF SQL è completamente definita con la potenza espressiva di SQL ed è anche completamente trasparente al compilatore SQL.

---

Dai un'occhiata all'ebook Why the Data Lakehouse is Your Next Data Warehouse per scoprire il funzionamento interno della Databricks Lakehouse Platform.

Vantaggi dell'utilizzo delle UDF SQL

Le UDF SQL sono estensioni semplici ma potenti di SQL su Databricks. Come funzioni, forniscono un livello di astrazione per semplificare la costruzione delle query, rendendo le query SQL più leggibili e modularizzate. A differenza delle UDF scritte in un linguaggio non SQL, le UDF SQL sono più leggere da creare per gli utenti SQL. I corpi delle funzioni SQL sono trasparenti all'ottimizzatore di query, rendendole quindi più performanti delle UDF esterne. Le UDF SQL possono essere create come funzioni temporanee o permanenti, riutilizzate in più query, sessioni e utenti, e controllate tramite Access Control Language (ACL). In questo blog, ti guideremo attraverso alcuni casi d'uso chiave delle UDF SQL con esempi.

UDF SQL come costanti

Iniziamo con la funzione più semplice immaginabile: una costante. Sappiamo tutti che non dovremmo usare letterali nel nostro codice perché danneggia la leggibilità e, chi lo sa, forse la costante non rimane tale. Quindi vogliamo poterla cambiare in un solo posto:

Se hai familiarità con le UDF esterne, puoi vedere che ci sono alcune differenze che spiccano:

1. Una UDF SQL deve definire la sua lista di parametri, anche se è vuota. Una costante non accetta parametri.
2. La funzione dichiara anche il tipo di dati che restituirà. In questo caso è una STRINGA.
3. L'implementazione della funzione fa parte della definizione della funzione.
4. Specifichi LANGUAGE SQL per indicare che si tratta di una UDF SQL. Ma in realtà, non è necessario. La clausola RETURN è sufficiente a far capire che abbiamo deciso di renderla opzionale.

Oltre a queste differenze, ci sono molte altre cose che sono uguali alle UDF esterne:

• Puoi sostituire una funzione. Maggiori informazioni più avanti.
• Puoi aggiungere un commento che descrive la funzione, come mostrato sopra.
• Puoi anche creare una funzione temporanea che puoi usare solo all'interno della sessione corrente.

Usiamo la funzione:

Senza sorpresa, questo funziona. Ma cosa sta succedendo sotto il cofano?

Questo è fantastico! Il compilatore SQL ha sostituito l'invocazione della funzione con la costante stessa.
Ciò significa che almeno questa UDF SQL non ha alcun costo in termini di prestazioni.

Ora, diamo un'occhiata a un altro modello di utilizzo comune.

UDF SQL che incapsulano espressioni

Immagina di non gradire la denominazione di alcune funzioni predefinite. Forse stai migrando molte query da un altro prodotto, che ha nomi e comportamenti di funzione diversi. O forse semplicemente non sopporti di copiare e incollare espressioni lunghe più e più volte nelle tue query SQL. Quindi, vuoi risolvere questo problema.

Con le UDF SQL, possiamo semplicemente creare una nuova funzione con il nome che preferiamo:

Diamo un'occhiata a quale nuova sintassi è stata utilizzata qui:

• Questa funzione accetta un argomento e il parametro è definito da un nome, un tipo e un commento opzionale.
• La clausola CONTAINS SQL è opzionale, ma ci dice che la funzione non legge né modifica alcun dato in una tabella. È l'impostazione predefinita, quindi normalmente non la specificheresti.
• DETERMINISTIC è anche opzionale e ci dice che la funzione restituirà sempre lo stesso set di risultati dati gli stessi argomenti. La clausola è solo a scopo documentale al momento. Ma in futuro potrebbe essere utilizzata per bloccare funzioni non deterministiche in determinati contesti.
• Nella clausola RETURN, il parametro è stato fatto riferimento per nome. In scenari più complessi di seguito vedrai che il parametro può essere disambiguato con il nome della funzione. Naturalmente puoi usare espressioni arbitrariamente complesse come corpo della funzione.

Non solo funziona…

...ma funziona bene:

Possiamo vedere che il piano fisico mostra un'applicazione diretta delle funzioni lpad, hex, least e greatest. Questo è lo stesso piano che ottieni invocando direttamente la serie di funzioni.

Puoi anche comporre funzioni SQL da funzioni SQL:

UDF SQL che leggono da tabelle

Un altro uso comune delle UDF SQL è quello di codificare i lookup. Un semplice lookup può essere quello di decodificare i codici colore RGB in nomi di colore in inglese:

OK, ma ci sono molti più di due colori in questo mondo. E vogliamo questa traduzione in entrambi i sensi, quindi questi dovrebbero essere in una tabella di lookup:

Ci sono diversi nuovi concetti applicati qui:

• Puoi SOSTITUIRE una UDF SQL. Per poterlo fare, la nuova funzione deve corrispondere alla firma della vecchia funzione. La firma di una funzione è definita dal numero dei suoi parametri e dai loro tipi.
• Questa funzione cerca informazioni in una tabella, quindi puoi opzionalmente documentarlo usando READS SQL DATA. Se non dichiari nulla, il compilatore SQL deriverà il valore corretto, ma non devi mentire e dichiarare CONTAINS SQL.
• SQL SECURITY DEFINER è un'altra clausola opzionale, che afferma che la query che accede alla tabella dei colori utilizzerà l'autorizzazione del proprietario della funzione. Quindi la funzione potrebbe essere eseguita dal pubblico senza compromettere la sicurezza della tabella.
• Proprio come la funzione opera sotto l'autorizzazione del suo proprietario, verrà sempre analizzata utilizzando il database corrente al momento della creazione.
• `rgb` è il nome della colonna nei numeri. Qualificando il parametro come `from_rgb`.`rgb` chiarisci che intendi il riferimento al parametro, e non la colonna.

Come appare il piano fisico ora? È facile vedere che l'utilizzo di una UDF esterna, che a sua volta esegue una query che risulterebbe in un join a ciclo annidato, è un modo terribile per bruciare preziose risorse.

In questo caso, Catalyst ha scelto un broadcast hash join invece di un nested loop join. Può farlo perché comprende il contenuto della SQL UDF.

Finora, tutti gli esempi discussi hanno utilizzato funzioni a valore scalare – quelle che restituiscono un singolo valore. Quel risultato può essere di qualsiasi tipo, anche combinazioni complesse di struct, array e mappe. Esiste anche un altro tipo di UDF da discutere: la UDF a valore di tabella.

SQL Table UDF

Immagina se le viste accettassero argomenti! Potresti incapsulare predicati complessi anche se si basano su valori forniti dall'utente. Una SQL Table UDF è proprio questo: una vista con un altro nome, tranne che con i parametri.

Supponiamo che la mappatura dei colori sopra non sia univoca. Almeno, possiamo affermare che i nomi dei colori differiscono tra le lingue.

Pertanto, la funzione `from_rgb` deve essere modificata per restituire un array di nomi o una relazione.

Come puoi vedere, l'unica differenza rispetto a una funzione scalare è una clausola RETURNS più complessa. A differenza delle viste, le SQL UDF richiedono una dichiarazione della firma della relazione restituita:

• TABLE specifica che la funzione restituisce una relazione.
• La clausola TABLE deve includere un nome per ogni colonna restituita e il tipo di dati della colonna.
• Puoi facoltativamente specificare un commento per qualsiasi colonna restituita.

Le funzioni definite dall'utente a tabella sono una novità di DBR. Diamo un'occhiata a come invocarle.

Nella sua forma più semplice, una funzione a tabella viene invocata nello stesso modo e negli stessi luoghi in cui viene referenziata una vista. L'unica differenza sono le parentesi graffe obbligatorie, che includono gli argomenti della funzione. Questa funzione viene invocata con argomenti letterali, ma gli argomenti possono essere qualsiasi espressione, anche sottoquery scalari.

Tuttavia, l'uso più potente è l'utilizzo di SQL table UDF in un join, tipicamente un correlated cross join:

Qui gli argomenti fanno riferimento (correlano) a una relazione precedente (laterale) nella clausola FROM. La nuova keyword LATERAL dà a Catalyst il permesso di risolvere queste colonne. Nota anche che puoi fare riferimento al risultato della funzione a tabella nominando le colonne come definite nella firma del risultato e opzionalmente qualificate dal nome della funzione.

Administration

Naturalmente, le SQL UDF sono completamente supportate dalle istruzioni esistenti GRANT, REVOKE, SHOW, DESCRIBE e DROP.

L'istruzione che merita un approfondimento è DESCRIBE.

La describe di base restituisce ciò che ti aspetteresti, ma la DESCRIBE estesa aggiunge dettagli significativamente maggiori:

Outlook

Ciò che abbiamo descritto rappresenta la funzionalità iniziale per le SQL UDF. Le future estensioni che stiamo valutando includono il supporto per:

• SQL PATH, in modo da poter creare una libreria di funzioni in un database e sottoscriverle da un altro, proprio come faresti nel tuo file system.
• Overloading delle UDF.
• UDF con valori predefiniti per i parametri.

Le SQL UDF rappresentano un grande passo avanti nell'usabilità di SQL e possono essere utilizzate in molti modi diversi come delineato in questo blog. Ti incoraggiamo a pensare a modi ancora più creativi per sfruttare le SQL UDF, sia in Databricks SQL che utilizzando Photon per i lavori di Data Engineering. Prova il notebook qui e consulta la documentazione per maggiori informazioni.

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