Einführung in SQL User-Defined Functions

Eine benutzerdefinierte Funktion (UDF) ist ein Mittel für einen Benutzer, die nativen Fähigkeiten von Apache Spark™ SQL zu erweitern. SQL auf Databricks unterstützt externe benutzerdefinierte Funktionen, die in Scala, Java, Python und R geschrieben sind, seit Version 1.3.0. Externe UDFs sind zwar sehr leistungsfähig, bergen aber auch einige Einschränkungen:

• Sicherheit. Eine in einer externen Sprache geschriebene UDF kann gefährlichen oder sogar bösartigen Code ausführen. Dies erfordert eine strenge Kontrolle darüber, wer UDFs erstellen darf.
• Leistung. UDFs sind für den Catalyst Optimizer Blackboxes. Da Catalyst die Funktionsweise einer UDF nicht kennt, kann er keine Leistungsvorteile im Kontext einer SQL-Abfrage erzielen.
• SQL-Benutzerfreundlichkeit. Für einen SQL-Benutzer kann es umständlich sein, UDFs in einer Host-Sprache zu schreiben und sie dann in Spark zu registrieren. Außerdem gibt es eine Reihe von Erweiterungen, die viele Benutzer an SQL vornehmen möchten und die recht einfach sind, sodass die Entwicklung einer externen UDF übertrieben wäre.

Um diese Einschränkungen zu überwinden, freuen wir uns, eine neue Form von UDF vorzustellen: SQL UDFs. Verfügbar in DBR 9.1 LTS, die SQL UDF wird vollständig mit der Ausdruckskraft von SQL definiert und ist für den SQL-Compiler vollständig transparent.

---

Schauen Sie sich das E-Book "Why the Data Lakehouse is Your Next Data Warehouse" an, um die Funktionsweise der Databricks Lakehouse Platform zu entdecken.

Vorteile der Verwendung von SQL UDFs

SQL UDFs sind einfache, aber leistungsstarke Erweiterungen für SQL auf Databricks. Als Funktionen bieten sie eine Abstraktionsebene zur Vereinfachung der Abfrageerstellung – wodurch SQL-Abfragen lesbarer und modularer werden. Im Gegensatz zu UDFs, die in einer Nicht-SQL-Sprache geschrieben sind, sind SQL UDFs für SQL-Benutzer einfacher zu erstellen. SQL-Funktionskörper sind für den Query Optimizer transparent und daher leistungsfähiger als externe UDFs. SQL UDFs können als temporäre oder permanente Funktionen erstellt, über mehrere Abfragen, Sitzungen und Benutzer hinweg wiederverwendet und über die Access Control Language (ACL) zugriffskontrolliert werden. In diesem Blogbeitrag werden wir einige wichtige Anwendungsfälle von SQL UDFs mit Beispielen durchgehen.

SQL UDFs als Konstanten

Beginnen wir mit der einfachsten vorstellbaren Funktion: einer Konstanten. Wir alle wissen, dass wir keine Literale in unserem Code verwenden sollten, da dies die Lesbarkeit beeinträchtigt und wer weiß, vielleicht bleibt die Konstante doch nicht konstant. Wir möchten sie also nur an einer Stelle ändern können:

Wenn Sie mit externen UDFs vertraut sind, werden Sie einige Unterschiede feststellen:

1. Eine SQL UDF muss ihre Parameterliste definieren, auch wenn sie leer ist. Eine Konstante benötigt keine Parameter.
2. Die Funktion deklariert auch den Datentyp, den sie zurückgibt. In diesem Fall ist das ein STRING.
3. Die Implementierung der Funktion ist Teil der Funktionsdefinition.
4. Sie geben LANGUAGE SQL an, um zu sagen, dass es sich um eine SQL UDF handelt. Aber das ist eigentlich nicht nötig. Die RETURN-Klausel verrät es bereits, weshalb wir uns entschieden haben, dies optional zu machen.

Abgesehen von diesen Unterschieden gibt es viele andere Dinge, die mit externen UDFs gleich sind:

• Sie können eine Funktion ersetzen. Mehr dazu später.
• Sie können einen Kommentar hinzufügen, der die Funktion beschreibt – wie oben gezeigt.
• Sie können sogar eine temporäre Funktion erstellen, die Sie nur innerhalb der aktuellen Sitzung verwenden können.

Lassen Sie uns die Funktion verwenden:

Das funktioniert erwartungsgemäß. Aber was passiert unter der Haube?

Das ist praktisch! Der SQL-Compiler hat den Funktionsaufruf durch die Konstante selbst ersetzt.
Das bedeutet, dass diese SQL UDF zumindest keine Leistungseinbußen verursacht.

Schauen wir uns nun ein weiteres gängiges Nutzungsmuster an.

SQL UDFs, die Ausdrücke kapseln

Stellen Sie sich vor, Sie mögen die Benennung einiger integrierter Funktionen nicht. Vielleicht migrieren Sie viele Abfragen aus einem anderen Produkt, das andere Funktionsnamen und -verhalten hat. Oder vielleicht können Sie es einfach nicht ertragen, einige lange Ausdrücke immer wieder in Ihren SQL-Abfragen zu kopieren und einzufügen. Sie möchten das also beheben.

Mit SQL UDF können wir einfach eine neue Funktion mit dem gewünschten Namen erstellen:

Schauen wir uns an, welche neue Syntax hier verwendet wurde:

• Diese Funktion nimmt ein Argument entgegen, und der Parameter wird durch einen Namen, einen Typ und einen optionalen Kommentar definiert.
• Die Klausel CONTAINS SQL ist optional, teilt uns aber mit, dass die Funktion keine Daten in einer Tabelle liest oder modifiziert. Dies ist die Standardeinstellung, sodass Sie sie normalerweise nicht angeben würden.
• DETERMINISTIC ist ebenfalls optional und teilt uns mit, dass die Funktion bei gleichen Argumenten immer das gleiche Ergebnis liefert. Die Klausel dient derzeit nur der Dokumentation. Zukünftig könnte sie jedoch verwendet werden, um nicht-deterministische Funktionen in bestimmten Kontexten zu blockieren.
• In der RETURN-Klausel wurde der Parameter anhand seines Namens referenziert. In komplexeren Szenarien weiter unten sehen Sie, dass der Parameter mit dem Funktionsnamen disambiguiert werden kann. Natürlich können Sie beliebig komplexe Ausdrücke als Funktionskörper verwenden.

Es funktioniert nicht nur …

... sondern es funktioniert gut:

Wir sehen, dass der physische Plan eine direkte Anwendung der Funktionen lpad, hex, least und greatest zeigt. Dies ist derselbe Plan, den Sie erhalten, wenn Sie die Funktionsreihe direkt aufrufen.

Sie können auch SQL-Funktionen aus SQL-Funktionen zusammensetzen:

SQL UDFs, die aus Tabellen lesen

Ein weiterer häufiger Anwendungsfall für SQL UDFs ist die Kodifizierung von Lookups. Ein einfacher Lookup könnte darin bestehen, RGB-Farbcodes in englische Farbnamen zu dekodieren:

Okay, aber es gibt auf der Welt viel mehr als zwei Farben. Und wir möchten diese Übersetzung in beide Richtungen haben, also sollten diese wirklich in einer Nachschlagetabelle stehen:

Hier werden mehrere neue Konzepte angewendet:

• Sie können eine SQL UDF ERSETZEN. Um dies zu dürfen, muss die neue Funktion mit der Signatur der alten Funktion übereinstimmen. Die Signatur einer Funktion ist definiert als die Anzahl ihrer Parameter und deren Typen.
• Diese Funktion sucht Informationen in einer Tabelle nach, daher können Sie dies optional mit READS SQL DATA dokumentieren. Wenn Sie nichts angeben, leitet der SQL-Compiler den korrekten Wert ab, aber Sie dürfen nicht lügen und CONTAINS SQL angeben.
• SQL SECURITY DEFINER ist eine weitere optionale Klausel, die besagt, dass die Abfrage, die auf die Tabelle colors zugreift, die Autorisierung des Funktionsbesitzers verwendet. Die Funktion könnte also von der Öffentlichkeit ausgeführt werden, ohne die Sicherheit der Tabelle zu gefährden.
• So wie die Funktion unter der Autorisierung ihres Besitzers ausgeführt wird, wird sie immer mit der aktuellen Datenbank zum Zeitpunkt der Erstellung geparst.
• `rgb` ist der Name der Spalte in numbers. Indem Sie den Parameter als `from_rgb`.`rgb` qualifizieren, verdeutlichen Sie, dass Sie die Parameterreferenz meinen und nicht die Spalte.

Wie sieht der physische Plan aus? Es ist leicht zu erkennen, dass die Verwendung einer externen UDF, die selbst eine Abfrage ausführt, die zu einem Nested-Loop-Join führen würde, eine schreckliche Methode ist, um wertvolle Ressourcen zu verbrauchen.

In diesem Fall hat Catalyst einen Broadcast Hash Join anstelle eines Nested Loop Joins gewählt. Dies ist möglich, da die Inhalte der SQL UDF verstanden werden.

Bisher haben alle besprochenen Beispiele Skalarwertfunktionen verwendet – solche, die einen einzelnen Wert zurückgeben. Dieses Ergebnis kann von beliebigem Typ sein, sogar komplexe Kombinationen von Strukturen, Arrays und Maps. Es gibt noch eine weitere Art von UDF zu besprechen – die tabellenwertige UDF.

SQL Tabellen-UDF

Stellen Sie sich vor, Ansichten könnten Argumente entgegennehmen! Sie könnten komplexe Prädikate kapseln, selbst wenn diese von benutzerdefinierten Werten abhängen. Eine SQL Tabellen-UDF ist genau das: eine Ansicht mit anderem Namen, nur eben mit Parametern.

Nehmen wir an, die Farbabstimmung oben ist nicht eindeutig. Zumindest können wir feststellen, dass sich die Farbnamen über verschiedene Sprachen hinweg unterscheiden.

Daher muss die Funktion `from_rgb` modifiziert werden, um entweder ein Array von Namen oder eine Relation zurückzugeben.

Wie Sie sehen, ist der einzige Unterschied zu einer Skalarfunktion eine komplexere RETURNS-Klausel. Im Gegensatz zu Ansichten erfordern SQL UDFs eine Deklaration der Signatur der zurückgegebenen Relation:

• TABLE gibt an, dass die Funktion eine Relation zurückgibt.
• Die TABLE-Klausel muss einen Namen für jede Rückgabespalte und den Datentyp der Spalte enthalten.
• Sie können optional einen Kommentar für jede Rückgabespalte angeben.

Benutzerdefinierte Tabellenfunktionen sind neu in DBR. Werfen wir einen Blick darauf, wie sie aufgerufen werden.

In ihrer einfachsten Form wird eine Tabellenfunktion auf die gleiche Weise und an den gleichen Stellen aufgerufen wie eine Ansicht. Der einzige Unterschied sind die obligatorischen Klammern, die die Argumente der Funktion enthalten. Diese Funktion wird mit literalen Argumenten aufgerufen, aber die Argumente können beliebige Ausdrücke sein, sogar skalare Unterabfragen.

Am leistungsfähigsten ist jedoch die Verwendung von SQL-Tabellen-UDFs in einem Join, typischerweise einem korrelierten Cross Join:

Hier beziehen sich die Argumente auf eine vorhergehende (laterale) Relation in der FROM-Klausel. Das neue LATERAL-Schlüsselwort gibt Catalyst die Erlaubnis, diese Spalten aufzulösen. Beachten Sie auch, dass Sie auf das Ergebnis der Tabellenfunktion verweisen können, indem Sie die Spaltennamen wie in der Ergebnissignatur definiert verwenden, optional qualifiziert mit dem Funktionsnamen.

Administration

Natürlich werden SQL UDFs vollständig von den bestehenden GRANT-, REVOKE-, SHOW-, DESCRIBE- und DROP-Anweisungen unterstützt.

Die Anweisung, die detaillierter betrachtet werden sollte, ist DESCRIBE.

Die einfache DESCRIBE-Anweisung gibt das erwartete Ergebnis zurück, aber die erweiterte DESCRIBE-Anweisung fügt erheblich mehr Details hinzu:

Ausblick

Was wir beschrieben haben, stellt die anfängliche Funktionalität für SQL UDFs dar. Zukünftige Erweiterungen, die wir in Erwägung ziehen, umfassen die Unterstützung für:

• SQL PATH, damit Sie eine Bibliothek von Funktionen in einer Datenbank erstellen und von einer anderen abonnieren können, genau wie Sie es in Ihrem Dateisystem tun würden.
• Überladung von UDFs.
• UDFs mit Standardwerten für Parameter.

SQL UDFs sind ein großer Schritt nach vorne in der Benutzerfreundlichkeit von SQL und können, wie in diesem Blog beschrieben, auf vielfältige Weise eingesetzt werden. Wir ermutigen Sie, sich noch kreativere Wege auszudenken, um SQL UDFs zu nutzen, sei es in Databricks SQL oder unter Verwendung von Photon für Data Engineering-Aufgaben. Probieren Sie das Notebook hier aus und lesen Sie die Dokumentation für weitere Informationen.

(Dieser Blogbeitrag wurde mit KI-gestützten Tools übersetzt.) Originalbeitrag