Introduction aux fonctions définies par l'utilisateur SQL

Publié: 20 octobre 2021

Produit11 min de lecture

Une fonction définie par l'utilisateur (UDF) est un moyen pour un utilisateur d'étendre les capacités natives d'Apache Spark™. Le SQL sur Databricks prend en charge les UDF externes écrites en Scala, Java, Python et R depuis la version 1.3.0. Bien que les UDF externes soient très puissantes, elles présentent quelques inconvénients :

Sécurité. Une UDF écrite dans un langage externe peut exécuter du code dangereux, voire malveillant. Cela nécessite un contrôle strict sur qui peut créer des UDF.
Performance. Les UDF sont des boîtes noires pour l'Optimiseur Catalyst. Étant donné que Catalyst n'est pas au courant du fonctionnement interne d'une UDF, il ne peut rien faire pour améliorer les performances de l'UDF dans le contexte d'une requête SQL.
Utilisabilité SQL. Pour un utilisateur SQL, il peut être fastidieux d'écrire des UDF dans un langage hôte, puis de les enregistrer dans Spark. De plus, il existe un ensemble d'extensions que de nombreux utilisateurs peuvent souhaiter apporter au SQL et qui sont assez simples, pour lesquelles le développement d'une UDF externe est excessif.

Pour faire face aux limitations ci-dessus, nous sommes ravis de présenter une nouvelle forme d'UDF : les UDF SQL. Disponible dans DBR 9.1 LTS, l'UDF SQL est entièrement définie avec la puissance expressive de SQL et est également complètement transparente pour le compilateur SQL.

Consultez le livre blanc Pourquoi le Data Lakehouse est votre prochain Data Warehouse pour découvrir le fonctionnement interne de la plateforme Databricks Lakehouse.

Avantages de l'utilisation des UDF SQL

Les UDF SQL sont des extensions simples mais puissantes du SQL sur Databricks. En tant que fonctions, elles fournissent une couche d'abstraction pour simplifier la construction des requêtes, rendant les requêtes SQL plus lisibles et modulaires. Contrairement aux UDF écrites dans un langage non SQL, les UDF SQL sont plus légères à créer pour les utilisateurs SQL. Les corps des fonctions SQL sont transparents pour l'optimiseur de requêtes, ce qui les rend plus performantes que les UDF externes. Les UDF SQL peuvent être créées comme des fonctions temporaires ou permanentes, être réutilisées dans plusieurs requêtes, sessions et utilisateurs, et être contrôlées d'accès via le langage de contrôle d'accès (ACL). Dans ce blog, nous allons vous présenter quelques cas d'utilisation clés des UDF SQL avec des exemples.

UDF SQL en tant que constantes

Commençons par la fonction la plus simple imaginable : une constante. Nous savons tous que nous ne sommes pas censés utiliser de littéraux dans notre code car cela nuit à la lisibilité et, qui sait, peut-être que la constante ne reste pas constante après tout. Nous voulons donc pouvoir la modifier en un seul endroit :

Si vous êtes familier avec les UDF externes, vous pouvez constater qu'il y a quelques différences notables :

Une UDF SQL doit définir sa liste de paramètres, même si elle est vide. Une constante ne prend aucun paramètre.
La fonction déclare également le type de données qu'elle retournera. Dans ce cas, il s'agit d'une chaîne de caractères (STRING).
L'implémentation de la fonction fait partie de la définition de la fonction.
Vous spécifiez LANGUAGE SQL pour indiquer qu'il s'agit d'une UDF SQL. Mais en réalité, ce n'est pas nécessaire. La clause RETURN suffit à le faire comprendre, nous avons donc décidé de la rendre facultative.

Au-delà de ces différences, de nombreuses autres choses sont identiques aux UDF externes :

Vous pouvez remplacer une fonction. Nous en parlerons plus tard.
Vous pouvez ajouter un commentaire décrivant la fonction, comme montré ci-dessus.
Vous pouvez même créer une fonction temporaire que vous pouvez utiliser uniquement dans la session en cours.

Utilisons la fonction :

Sans surprise, cela fonctionne. Mais que se passe-t-il sous le capot ?

C'est génial ! Le compilateur SQL a remplacé l'appel de fonction par la constante elle-même.
Cela signifie qu'au moins cette UDF SQL n'a aucun coût en termes de performance.

Maintenant, examinons un autre modèle d'utilisation courant.

UDF SQL encapsulant des expressions

Imaginez que vous n'aimez pas la dénomination de certaines fonctions intégrées. Peut-être migrez-vous de nombreuses requêtes depuis un autre produit, qui a des noms et des comportements de fonctions différents. Ou peut-être ne supportez-vous pas de copier-coller des expressions longues encore et encore dans vos requêtes SQL. Vous voulez donc y remédier.

Avec les UDF SQL, nous pouvons simplement créer une nouvelle fonction avec le nom que nous aimons :

Examinons la nouvelle syntaxe utilisée ici :

Cette fonction prend un argument, et le paramètre est défini par un nom, un type et un commentaire facultatif.
La clause CONTAINS SQL est facultative, mais nous indique que la fonction ne lit ni ne modifie aucune donnée dans une table. C'est le réglage par défaut, donc vous ne le spécifieriez normalement pas.
DETERMINISTIC est également facultatif et nous indique que la fonction retournera toujours le même ensemble de résultats pour les mêmes arguments. La clause est uniquement à titre de documentation pour le moment. Mais à l'avenir, elle pourrait être utilisée pour bloquer les fonctions non déterministes dans certains contextes.
Dans la clause RETURN, le paramètre a été référencé par son nom. Dans des scénarios plus complexes ci-dessous, vous verrez que le paramètre peut être désambiguïsé avec le nom de la fonction. Naturellement, vous pouvez utiliser des expressions arbitrairement complexes comme corps de la fonction.

Non seulement cela fonctionne...

... mais cela fonctionne bien :

Nous pouvons voir que le plan physique montre une application directe des fonctions lpad, hex, least et greatest. C'est le même plan que celui obtenu en appelant directement la série de fonctions.

Vous pouvez également composer des fonctions SQL à partir d'autres fonctions SQL :

UDF SQL lisant à partir de tables

Une autre utilisation courante des UDF SQL est de codifier des recherches. Une recherche simple peut consister à décoder des codes de couleur RVB en noms de couleur anglais :

OK, mais il y a beaucoup plus que deux couleurs dans ce monde. Et nous voulons cette traduction dans les deux sens, donc cela devrait vraiment être dans une table de correspondance :

Plusieurs nouveaux concepts sont appliqués ici :

Vous pouvez REMPLACER une UDF SQL. Pour être autorisé à le faire, la nouvelle fonction doit correspondre à la signature de l'ancienne fonction. La signature d'une fonction est définie par le nombre de ses paramètres et leurs types.
Cette fonction recherche des informations dans une table, vous pouvez donc éventuellement le documenter en utilisant READS SQL DATA. Si vous ne spécifiez rien, le compilateur SQL dérivera la valeur correcte, mais vous ne devez pas mentir et spécifier CONTAINS SQL.
SQL SECURITY DEFINER est une autre clause facultative, qui stipule que la requête accédant à la table colors utilisera l'autorisation du propriétaire de la fonction. Ainsi, la fonction pourrait être exécutée par le public sans compromettre la sécurité de la table.
Tout comme la fonction opère sous l'autorisation de son propriétaire, elle sera toujours analysée en utilisant la base de données actuelle au moment de la création.
`rgb` est le nom du paramètre dans les nombres. En qualifiant le paramètre comme `from_rgb`.`rgb`, vous clarifiez que vous faites référence au paramètre, et non à la colonne.

À quoi ressemble le plan physique maintenant ? Il est facile de constater qu'utiliser une UDF externe, qui effectue elle-même une requête résultant en une jointure en boucle imbriquée, est une façon horrible de gaspiller de précieuses ressources.

Dans ce cas, Catalyst a choisi un jointure par hachage diffusée (broadcast hash join) au lieu d'une jointure en boucle imbriquée (nested loop join). Il peut le faire car il comprend le contenu de l'UDF SQL.

Jusqu'à présent, tous les exemples abordés utilisaient des fonctions à valeur scalaire – celles qui retournent une seule valeur. Ce résultat peut être de n'importe quel type, même des combinaisons complexes de structures, de tableaux et de cartes. Il existe un autre type d'UDF à aborder – l'UDF à valeur de table.

UDF de table SQL

Imaginez si les vues prenaient des arguments ! Vous pourriez encapsuler des prédicats complexes même s'ils dépendent de valeurs fournies par l'utilisateur. Une UDF de table SQL est exactement cela : une vue par un autre nom, sauf qu'elle a des paramètres.

Supposons que le mappage de couleurs ci-dessus ne soit pas unique. Au minimum, nous pouvons affirmer que les noms de couleurs diffèrent selon les langues.

Par conséquent, la fonction `from_rgb` doit être modifiée pour retourner soit un tableau de noms, soit une relation.

Comme vous pouvez le voir, la seule différence par rapport à une fonction scalaire est une clause RETURNS plus complexe. Contrairement aux vues, les UDF SQL exigent une déclaration de la signature de la relation retournée :

TABLE spécifie que la fonction retourne une relation.
La clause TABLE doit inclure un nom pour chaque colonne de retour et le type de données de la colonne.
Vous pouvez éventuellement spécifier un commentaire pour toute colonne de retour.

Les fonctions de table définies par l'utilisateur sont nouvelles dans DBR. Jetons un coup d'œil à la façon de les invoquer.

Dans sa forme la plus simple, une fonction de table est invoquée de la même manière et aux mêmes endroits qu'une vue est référencée. La seule différence réside dans les parenthèses obligatoires, qui incluent les arguments de la fonction. Cette fonction est invoquée avec des arguments littéraux, mais les arguments peuvent être n'importe quelle expression, même des sous-requêtes scalaires.

Le plus puissant, cependant, est l'utilisation d'une UDF de table SQL dans une jointure, typiquement une jointure croisée corrélée :

Ici, les arguments font référence (corrèlent) à une relation précédente (latérale) dans la clause FROM. Le nouveau mot-clé LATERAL donne à Catalyst la permission de résoudre ces colonnes. Notez également que vous pouvez faire référence au résultat de la fonction de table en nommant les colonnes telles que définies dans la signature du résultat et éventuellement qualifiées par le nom de la fonction.

Administration

Naturellement, les UDF SQL sont entièrement prises en charge par les instructions GRANT, REVOKE, SHOW, DESCRIBE et DROP existantes.

L'instruction qui mérite d'être soulignée plus en détail est DESCRIBE.

La description de base renvoie ce à quoi vous pourriez vous attendre, mais la commande DESCRIBE étendue ajoute beaucoup plus de détails :

Perspectives

Ce que nous avons décrit représente la fonctionnalité initiale pour les UDF SQL. Les futures extensions que nous envisageons incluent la prise en charge de :

SQL PATH, afin que vous puissiez créer une bibliothèque de fonctions dans une base de données et vous y abonner depuis une autre, tout comme vous le feriez dans votre système de fichiers.
La surcharge des UDF.
Les UDF avec des valeurs par défaut pour les paramètres.

Les UDF SQL représentent une grande avancée en matière d'utilisabilité de SQL et peuvent être utilisées de nombreuses manières différentes, comme décrit dans ce blog. Nous vous encourageons à penser à des moyens encore plus créatifs d'exploiter les UDF SQL, que ce soit dans Databricks SQL ou en utilisant Photon pour les tâches d'ingénierie de données. Essayez le notebook ici et consultez la documentation pour plus d'informations.

(Cet article de blog a été traduit à l'aide d'outils basés sur l'intelligence artificielle) Article original