Présentation de la prise en charge des alias de colonnes latéraux

Nous sommes ravis de présenter la prise en charge d'une nouvelle fonctionnalité SQL dans Apache Spark et Databricks : l'alias de colonne latérale (LCA). Cette fonctionnalité simplifie les requêtes SQL complexes en permettant aux utilisateurs de réutiliser une expression spécifiée précédemment dans la même liste SELECT, éliminant ainsi le besoin d'utiliser des sous-requêtes imbriquées et des expressions de table communes (CTE) dans de nombreux cas. Ce billet de blogue aborde les cas d'utilisation de la fonctionnalité et les avantages qu'elle apporte aux utilisateurs de Spark et de Databricks.

Qu'est-ce que la prise en charge des alias de colonnes latérales ?

L'alias de colonne latérale (LCA) offre aux utilisateurs la possibilité de réutiliser une expression spécifiée précédemment dans la même liste SELECT.
Cette fonctionnalité peut être mieux comprise à travers l'exemple fourni ci-dessous. Voici une requête simple :

En l'absence de prise en charge LCA, les utilisateurs recevront une erreur pour cette requête, indiquant que le `a` ultérieur dans la liste SELECT ne peut pas être résolu :

Heureusement, avec la fonctionnalité LCA, ce second `a` dans la requête est maintenant correctement identifié comme l'alias précédemment défini dans la même liste SELECT : 1 AS a. Les utilisateurs ne sont plus confrontés à une erreur, mais reçoivent plutôt les résultats suivants :

Éliminez les sous-requêtes et les CTE complexes avec le chaînage LCA

Bien que les exemples précédents illustrent le concept de base du LCA, la véritable puissance de cette fonctionnalité réside dans sa capacité à éliminer les sous-requêtes et les CTE complexes.

Avant l'introduction du LCA, les utilisateurs devaient gérer plusieurs sous-requêtes et CTE lorsqu'ils essayaient de référencer un attribut défini par un alias précédent. Cela augmentait la complexité et la verbosité des requêtes SQL, les rendant difficiles à lire, à écrire et à maintenir. En revanche, la prise en charge LCA simplifie fondamentalement ces requêtes, les rendant plus conviviales et gérables.

Prenons un exemple. Supposons qu'il existe une table `products` stockant des informations sur les produits tels que le nom, la catégorie, le prix et la note client. Notre objectif est de calculer un prix ajusté en fonction de plusieurs facteurs influents. Le scénario décrira clairement comment le LCA peut transformer une requête compliquée en une version considérablement simplifiée.

Voici la structure de la table :

Nous aimerions calculer le prix ajusté pour chaque produit en fonction de la valeur la plus élevée de deux facteurs : le pourcentage d'augmentation du prix basé sur la note des utilisateurs du produit et basé sur le rang du produit dans sa catégorie. Sans prise en charge LCA, la requête ressemble à ceci :

La logique contient de nombreuses opérations de chaînage où un calcul ultérieur dépend des résultats précédemment calculés. Par conséquent, elle nécessite plusieurs CTE pour stocker chaque calcul intermédiaire d'une manière adaptée aux références ultérieures dans les étapes suivantes de la requête.

Cependant, avec le LCA, il est possible d'exprimer la requête en une seule instruction SELECT :

Les LCA peuvent également être chaînés ! Cela signifie que l'expression d'alias actuelle, qui peut être référencée par des expressions ultérieures, peut référencer un alias latéral précédemment défini. Par exemple, la définition de `final_increase_percentage` dépend de deux alias de colonnes latérales : `increase_percentage_based_on_rating` et `increase_percentage_based_on_rank`. Le calcul suivant de `adjusted_price` fait ensuite référence à `final_increase_percentage`. Cette puissance de chaînage du LCA permet aux utilisateurs de créer une série de calculs dépendants, où les résultats d'un calcul sont utilisés comme entrées pour le suivant.

Comme nous pouvons le voir dans l'exemple ci-dessus, le LCA simplifie grandement la requête, éliminant les calculs répétés ou le besoin de plusieurs CTE, la rendant plus facile à comprendre, à maintenir et à déboguer. Il améliore également la lisibilité car la définition du calcul et son utilisation sont proches dans la requête.

LCA pour tout

Expressions simples, d'agrégation ou de fenêtre

Presque toutes les expressions peuvent résider dans un alias de colonne latérale. Les exemples de la dernière section montrent que les expressions CASE-WHEN complexes, ainsi que les expressions GREATEST ou même les fonctions de fenêtre, peuvent être contenues dans un alias de colonne latérale pour une utilisation ultérieure dans des expressions subséquentes.

De même, nous pouvons également imbriquer des expressions d'agrégation de cette manière. Voici un exemple sur la même table `products` :

Types de données complexes

Le LCA fonctionne également bien avec des types de données complexes comme les structs, les tableaux et les maps. Par exemple,

Expressions non déterministes

Le LCA garantit que les expressions non déterministes ne sont évaluées qu'une seule fois, reflétant la sémantique « une seule exécution » offerte par les CTE. Cela garantit des résultats cohérents lors de l'utilisation d'expressions non déterministes dans la requête.

Par exemple, considérons un scénario où il existe un `member_price` pour chaque produit dans la table `products` ci-dessus. Nous aimerions appliquer un pourcentage de remise aléatoire entre 0 % et 5 % à chaque produit, puis calculer le prix réduit à la fois pour le `price` et le `member_price`. Cet exercice devrait garantir que le pourcentage de remise appliqué aux deux prix reste le même.

Avec le LCA, nous pouvons écrire :

Dans cet exemple, Databricks calcule la discounted_rate une seule fois, et cette valeur reste la même pour toutes les références ultérieures, y compris le calcul de adjusted_price et adjusted_member_price.

D'un autre côté, si nous copions simplement des expressions non déterministes, ce comportement ne s'applique pas car chaque expression serait évaluée séparément, entraînant des taux de remise incohérents pour les deux prix :

Essayez LCA !

En résumé, Lateral Column Alias (LCA) est une fonctionnalité puissante qui simplifie considérablement les requêtes SQL en permettant aux utilisateurs de définir un alias nommé sur un arbre d'expressions, puis de référencer cet alias plus tard dans la même clause SELECT.

• Cela évite de répéter les mêmes expressions plusieurs fois ou le besoin de sous-requêtes ou de CTE, générant à la place des requêtes SELECT concises et lisibles.
• Il est compatible avec toutes sortes d'expressions et de types de données complexes. La syntaxe SQL prend en charge l'enchaînement de ces alias pour une plus grande flexibilité.
• Il garantit que chaque expression non déterministe n'est évaluée qu'une seule fois, permettant ainsi d'obtenir des résultats cohérents sur plusieurs références.

LCA est entièrement disponible et activé par défaut dans Databricks Runtime 12.2 LTS et versions ultérieures, dans Databricks SQL 2023.20 et versions ultérieures, et Apache Spark 3.4.

En savoir plus

• Ordre de résolution
  Les lecteurs curieux pourraient s'intéresser à l'ordre de résolution des noms dans les requêtes SQL avec l'introduction de LCA. Ce document Databricks sur la résolution des noms définit un ensemble clair de règles ordonnées et des exemples concrets pour résoudre les références, y compris le rôle de LCA dans ce processus.

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