Încercați acest blocnotes în Databricks

ACTUALIZAT 11/10/2018

Pivotul a fost introdus pentru prima dată în Apache Spark 1.6 ca o nouă caracteristică DataFrame care permite utilizatorilor să rotească o expresie cu valoare de tabel transformând valorile unice dintr-o coloană în coloane individuale.

Versiunea Apache Spark 2.4 extinde această funcționalitate puternică a datelor pivotante și la utilizatorii noștri SQL. În acest blog, folosind înregistrările de temperatură din Seattle, vom arăta cum putem folosi această caracteristică comună SQL Pivot pentru a realiza transformări complexe de date.

Examinarea temperaturilor de vară cu pivot

În această vară în Seattle, temperaturile au crescut până la niveluri incomode, atingând valori maxime de 80 și 90, timp de nouă zile în iulie.

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

Să presupunem că dorim să explorăm sau să examinăm dacă a existat un tren istoric d în creșterea nivelului de mercur. Un mod intuitiv de a examina și prezenta aceste numere este să aveți luni ca coloane și apoi maximele medii lunare ale fiecărui an într-un singur rând. În acest fel, va fi ușor să comparăm temperaturile atât pe orizontală, între lunile adiacente, cât și pe verticală, între ani diferiți.

Acum că avem suport pentru sintaxa PIVOT în Spark SQL, putem realiza acest lucru cu următoarea interogare SQL.

Interogarea de mai sus va produce un rezultat de genul:

Ei bine, se pare că există ani buni și ani răi. Anul 2016 pare un an destul de ecologic.

Pivotarea în SQL

Să aruncăm o privire mai atentă la această interogare pentru a înțelege cum funcționează. În primul rând, trebuie să specificăm clauza FROM, care este intrarea pivotului, cu alte cuvinte, tabelul sau subinterogarea pe baza căruia va fi efectuată pivotarea. În cazul nostru, suntem îngrijorați de ani, luni și temperaturi ridicate, așa că acestea sunt câmpurile care apar în interogare.

În al doilea rând, să luăm în considerare o altă parte importantă a interogării, clauza PIVOT. Primul argument al clauzei PIVOT este o funcție agregată și coloana care trebuie agregată. Apoi specificăm coloana pivot în sub-clauza FOR ca al doilea argument, urmată de operatorul IN care conține valorile coloanei pivot ca ultimul argument.

Coloana pivot este punctul în jurul căruia va fi rotită tabela, iar valorile coloanei pivot vor fi transpuse în coloane în tabelul de ieșire. Clauza IN vă permite, de asemenea, să specificați un alias pentru fiecare valoare pivot, facilitând generarea de nume de coloane mai semnificative.

O idee importantă despre pivot este că efectuează o agregare grupată pe baza unei liste de coloane implicite group-by împreună cu coloana pivot. Coloanele implicite group-by sunt coloane din clauza FROM care nu apar în nicio funcție agregată sau ca coloană pivot.

În interogarea de mai sus, coloana pivot fiind luna coloanei și coloana implicită group-by fiind anul coloanei, expresia avg(temp) va fi agregat pe fiecare pereche de valori distincte de (year, month), unde luna este egală cu una dintre valorile specificate ale coloanei pivot. Ca urmare, fiecare dintre aceste valori agregate va fi mapată în celula corespunzătoare a rândului year și column lună.

Este demn de remarcat faptul că, din cauza acestui implicit group-by, trebuie să ne asigurăm că orice coloană pe care nu dorim să o facă parte din ieșirea pivot ar trebui lăsată în afara FROM clauză, altfel interogarea ar produce rezultate nedorite.

Specificarea expresiilor agregate multiple

Exemplul de mai sus arată o singură expresie agregată utilizată în clauza PIVOT, deși utilizatorii pot specifica mai multe expresii cumulate dacă este necesar. Din nou, cu datele meteo de mai sus, putem enumera temperaturile maxime maxime, împreună cu temperaturile medii ridicate între iunie și septembrie.

În cazul expresiilor agregate multiple, coloanele vor fi produsul cartezian al pivotului valorile coloanei și expresiile agregate, cu numele ca <value>_<aggExpr>.

Gruparea coloanelor față de coloanele pivot

Să presupunem că dorim să includem temperaturi scăzute în explorarea tendințelor de temperatură din acest tabel cu temperaturi scăzute zilnice:

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

Pentru a combina acest tabel cu tabelul anterior al temperaturilor zilnice ridicate, am putea uni aceste două tabele în coloana „Data”. Cu toate acestea, deoarece vom folosi pivot, care efectuează gruparea la date, vom poate pur și simplu să concateneze cele două tabele folosind UNION ALL. Și veți vedea mai târziu, această abordare ne oferă și mai multă flexibilitate:

Acum să încercăm interogarea noastră pivot cu noul tabel combinat:

Ca rezultat, obținem media maximă și medie medie pentru fiecare lună din ultimii 4 ani într-un singur tabel. Rețineți că trebuie să includem coloana flag în interogarea pivot, altfel expresia avg(temp) s-ar baza pe un mix de mare și scăzut temperaturi.

Este posibil să fi observat că acum avem două rânduri pentru fiecare an, unul pentru temperaturile ridicate și celălalt pentru temperaturile scăzute. Asta pentru că am inclus încă o coloană, flag, în intrarea pivot, care la rândul său devine o altă coloană de grupare implicită în plus față de coloana originală year.

Alternativ, în loc să fie o coloană de grupare, flag poate servi și ca coloană pivot. Deci, acum avem două coloane pivot, month și flag:

Această interogare ne prezintă un aspect diferit de aceleași date, cu un rând pentru fiecare an, dar două coloane pentru fiecare lună.

Ce urmează

Pentru a rula exemplele de interogare utilizate în acest blog, vă rugăm să verificați exemplele SQL pivot în acest caiet de însoțire.

Mulțumim colaboratorilor comunității Apache Spark pentru contribuțiile lor!