Essayez ce notebook dans Databricks

MISE À JOUR 11/10/2018

Pivot a été introduit pour la première fois dans Apache Spark 1.6 en tant que nouvelle fonctionnalité DataFrame qui permet aux utilisateurs de faire pivoter une expression table en transformant les valeurs uniques d’une colonne en colonnes individuelles.

La version Apache Spark 2.4 étend également cette puissante fonctionnalité de pivotement des données à nos utilisateurs SQL. Dans ce blog, en utilisant des enregistrements de températures à Seattle, nous montrerons comment nous pouvons utiliser cette fonctionnalité SQL Pivot commune pour réaliser des transformations de données complexes.

Examiner les températures estivales avec Pivot

Cet été à Seattle, les températures ont atteint des niveaux inconfortables, atteignant un maximum de 80 et 90 degrés pendant neuf jours en juillet.

Supposons que nous voulions explorer ou examiner s’il y avait un tren historique d dans l’augmentation des niveaux de mercure. Une façon intuitive d’examiner et de présenter ces chiffres consiste à avoir des mois comme colonnes, puis les sommets moyens mensuels de chaque année sur une seule ligne. De cette façon, il sera facile de comparer les températures à la fois horizontalement, entre des mois adjacents et verticalement, entre différentes années.

Maintenant que nous avons le support de la syntaxe PIVOT dans Spark SQL, nous pouvons y parvenir avec la requête SQL suivante.

La requête ci-dessus produira un résultat comme:

Eh bien, on dirait qu’il y a de bonnes et de mauvaises années. L’année 2016 semble plutôt économe en énergie.

Pivoter dans SQL

Examinons cette requête de plus près pour comprendre son fonctionnement. Tout d’abord, nous devons spécifier la clause FROM, qui est l’entrée du pivot, c’est-à-dire la table ou la sous-requête sur laquelle le pivotement sera effectué. Dans notre cas, nous sommes préoccupés par les années, les mois et les températures élevées, ce sont donc les champs qui apparaissent dans la sous-requête.

Deuxièmement, considérons une autre partie importante de la requête, la clause PIVOT. Le premier argument de la clause PIVOT est une fonction d’agrégation et la colonne à agréger. Nous spécifions ensuite la colonne pivot dans la sous-clause FOR comme deuxième argument, suivie de l’opérateur IN contenant les valeurs de la colonne pivot comme le dernier argument.

La colonne pivot est le point autour duquel la table sera tournée, et les valeurs de la colonne pivot seront transposées en colonnes dans la table de sortie. La clause IN vous permet également de spécifier un alias pour chaque valeur de pivot, ce qui facilite la génération de noms de colonnes plus significatifs.

Une idée importante à propos du pivot est que il effectue une agrégation groupée basée sur une liste de colonnes group-by implicites avec la colonne pivot. Les colonnes implicites group-by sont des colonnes de la clause FROM qui n’apparaissent dans aucune fonction d’agrégation ou en tant que colonne pivot.

Dans la requête ci-dessus, la colonne pivot étant le mois de la colonne et la colonne implicite group-by étant l’année de la colonne, l’expression avg(temp) sera agrégé sur chaque paire de valeurs distinctes de (year, month), où le mois est égal à l’une des valeurs de colonne pivot spécifiées. En conséquence, chacune de ces valeurs agrégées sera mappée dans sa cellule correspondante de la ligne year et column mois.

Il convient de noter qu’en raison de ce group-by implicite, nous devons nous assurer que toute colonne que nous ne souhaitons pas faire partie de la sortie du pivot doit être exclue de la FROM, sinon la requête produirait des résultats indésirables.

Spécification de plusieurs expressions agrégées

L’exemple ci-dessus montre qu’une seule expression agrégée est utilisée dans la clause PIVOT, alors qu’en fait, les utilisateurs peuvent spécifier plusieurs expressions d’agrégation si nécessaire. Encore une fois, avec les données météorologiques ci-dessus, nous pouvons lister les températures maximales élevées ainsi que les températures moyennes élevées entre juin et septembre.

En cas d’expressions agrégées multiples, les colonnes seront le produit cartésien du pivot les valeurs de colonne et les expressions agrégées, avec les noms <value>_<aggExpr>.

Regroupement des colonnes et des colonnes pivotantes

Supposons maintenant que nous souhaitons inclure les basses températures dans notre exploration des tendances de température à partir de ce tableau des basses températures quotidiennes:

Pour combiner ce tableau avec le tableau précédent des températures maximales quotidiennes, nous pourrions joindre ces deux tableaux dans la colonne « Date ». Cependant, puisque nous allons utiliser pivot, qui effectue le regroupement sur les dates, nous peut simplement concaténer les deux tables en utilisant UNION ALL. Et vous verrez plus tard, cette approche nous offre également plus de flexibilité:

Essayons maintenant notre requête pivot avec le nouveau tableau combiné:

En conséquence, nous obtenons le maximum moyen et le minimum moyen pour chaque mois des 4 dernières années dans un seul tableau. Notez que nous devons inclure la colonne flag dans la requête pivot, sinon l’expression avg(temp) serait basée sur un mélange de haut et de bas

Vous avez peut-être remarqué que nous avons maintenant deux lignes pour chaque année, une pour les températures élevées et l’autre pour les températures basses. C’est parce que nous avons inclus une colonne supplémentaire, flag, dans l’entrée pivot, qui à son tour devient une autre colonne de regroupement implicite en plus de la colonne d’origine year.

Alternativement, au lieu d’être une colonne de regroupement, la flag peut également servir de colonne pivot. Nous avons donc maintenant deux colonnes pivot, month et flag:

Cette requête nous présente une disposition différente de les mêmes données, avec une ligne pour chaque année, mais deux colonnes pour chaque mois.

Et maintenant

Pour exécuter les exemples de requêtes utilisés dans ce blog, veuillez consulter les exemples de pivot SQL dans ce carnet d’accompagnement.

Merci aux contributeurs de la communauté Apache Spark pour leurs contributions!