Prova questo taccuino in Databricks

AGGIORNATO 11/10/2018

Pivot è stato introdotto per la prima volta in Apache Spark 1.6 come una nuova funzionalità DataFrame che consente agli utenti di ruotare un’espressione con valori di tabella trasformando i valori univoci di una colonna in singole colonne.

La versione di Apache Spark 2.4 estende questa potente funzionalità di rotazione dei dati anche ai nostri utenti SQL. In questo blog, utilizzando le registrazioni delle temperature a Seattle, mostreremo come possiamo utilizzare questa comune funzione SQL Pivot per ottenere complesse trasformazioni dei dati.

Esame delle temperature estive con Pivot

Quest’estate a Seattle le temperature sono aumentate a livelli sgradevoli, con picchi fino a 80 e 90, per nove giorni a luglio.

Data	Temp (° F)
22/07/2018	86
23/07/2018	90
24/07/2018	91
25/07/2018	92
26/07/2018	92
27/07/2018	88
28/07/2018	85
29/07/2018	94
30/07/2018	89

Supponiamo di voler esplorare o esaminare se ci fosse un tren storico d in aumento dei livelli di mercurio. Un modo intuitivo per esaminare e presentare questi numeri è avere i mesi come colonne e quindi i massimi medi mensili di ogni anno in una singola riga. In questo modo sarà facile confrontare le temperature sia orizzontalmente, tra mesi adiacenti, sia verticalmente, tra anni diversi.

Ora che abbiamo il supporto per la sintassi PIVOT in Spark SQL, possiamo ottenere questo risultato con la seguente query SQL.

La query precedente produrrà un risultato come:

Bene, sembra che ci siano anni buoni e anni cattivi. L’anno 2016 sembra un anno piuttosto favorevole all’energia.

Pivot in SQL

Diamo un’occhiata più da vicino a questa query per capire come funziona. Innanzitutto, dobbiamo specificare la clausola FROM, che è l’input del pivot, in altre parole, la tabella o la sottoquery in base alla quale verrà eseguito il pivot. Nel nostro caso, siamo preoccupati per gli anni, i mesi e le alte temperature, quindi questi sono i campi che compaiono nella sottoquery.

In secondo luogo, consideriamo un’altra parte importante della query, la clausola PIVOT. Il primo argomento della clausola PIVOT è una funzione aggregata e la colonna da aggregare. Specifichiamo quindi la colonna pivot nella sottoclausola FOR come secondo argomento, seguita dall’operatore IN contenente i valori della colonna pivot come l’ultimo argomento.

La colonna pivot è il punto attorno al quale verrà ruotata la tabella e i valori della colonna pivot verranno trasposti in colonne nella tabella di output. La clausola IN consente inoltre di specificare un alias per ciascun valore pivot, semplificando la generazione di nomi di colonna più significativi.

Un’idea importante del pivot è che esegue un’aggregazione raggruppata basata su un elenco di colonne group-by implicite insieme alla colonna pivot. Le colonne group-by implicite sono colonne della clausola FROM che non vengono visualizzate in alcuna funzione di aggregazione o come colonna pivot.

Nella query precedente, con la colonna pivot che rappresenta il mese della colonna e la colonna group-by implicita è l’anno della colonna, l’espressione avg(temp) verrà aggregato su ciascuna coppia di valori distinti di (year, month), dove il mese è uguale a uno dei valori della colonna pivot specificati. Di conseguenza, ciascuno di questi valori aggregati verrà mappato nella cella corrispondente della riga year e column mese.

Vale la pena notare che a causa di questo group-by implicito, dobbiamo assicurarci che qualsiasi colonna che non desideriamo far parte dell’output del pivot debba essere esclusa dal FROM, altrimenti la query produrrebbe risultati indesiderati.

Specifica di più espressioni aggregate

L’esempio precedente mostra solo un’espressione aggregata utilizzata nella clausola PIVOT, mentre in realtà gli utenti possono specificare più espressioni aggregate, se necessario. Anche in questo caso, con i dati meteorologici sopra, possiamo elencare le temperature massime alte insieme alle temperature medie alte tra giugno e settembre.

In caso di più espressioni aggregate, le colonne saranno il prodotto cartesiano del pivot i valori delle colonne e le espressioni aggregate, con i nomi <value>_<aggExpr>.

Raggruppamento di colonne e colonne pivot

Supponiamo ora di voler includere le basse temperature nella nostra esplorazione delle tendenze di temperatura da questa tabella delle basse temperature giornaliere:

Data	Temp (° F)
…	…
08-01-2018	59
08-02-2018	58
08-03-2018	59
08-04-2018	58
08-05-2018	59
08-06-2018	59
…	…

Per combinare questa tabella con la precedente tabella delle alte temperature giornaliere, potremmo unire queste due tabelle nella colonna “Data”. Tuttavia, poiché utilizzeremo il pivot, che esegue il raggruppamento sulle date, può semplicemente concatenare le due tabelle utilizzando UNION ALL. E vedrai più avanti, questo approccio ci fornisce anche maggiore flessibilità:

Ora proviamo la nostra query pivot con la nuova tabella combinata:

Di conseguenza, otteniamo la media massima e minima per ogni mese degli ultimi 4 anni in una tabella. Tieni presente che dobbiamo includere la colonna flag nella query pivot, altrimenti l’espressione avg(temp) sarebbe basata su una combinazione di valori alti e bassi temperature.

Avrai notato che ora abbiamo due file per ogni anno, una per le alte temperature e l’altra per le basse temperature. Questo perché abbiamo incluso un’altra colonna, flag, nell’input pivot, che a sua volta diventa un’altra colonna di raggruppamento implicito oltre alla colonna originale year.

In alternativa, invece di essere una colonna di raggruppamento, flag può anche fungere da colonna pivot. Quindi ora abbiamo due colonne pivot, month e flag:

Questa query ci presenta un layout diverso di gli stessi dati, con una riga per ogni anno, ma due colonne per ogni mese.

Passaggi successivi

Per eseguire gli esempi di query utilizzati in questo blog, controllare gli esempi SQL pivot in questo taccuino di accompagnamento.

Grazie ai collaboratori della comunità di Apache Spark per i loro contributi!