Probeer dit notitieblok in Databricks

BIJGEWERKT 11/10/2018

Pivot werd voor het eerst geïntroduceerd in Apache Spark 1.6 als een nieuwe DataFrame-functie waarmee gebruikers een expressie met tabelwaarde kunnen roteren door de unieke waarden van één kolom om te zetten in afzonderlijke kolommen.

De Apache Spark 2.4-release breidt deze krachtige functionaliteit van draaiende gegevens ook uit naar onze SQL-gebruikers. In deze blog laten we aan de hand van temperatuuropnames in Seattle zien hoe we deze algemene SQL Pivot-functie kunnen gebruiken om complexe datatransformaties te realiseren.

Zomertemperaturen onderzoeken met Pivot

Deze zomer in Seattle steeg de temperatuur gedurende negen dagen in juli tot oncomfortabele niveaus, met een piek tot hoge 80s en 90s.

Stel dat we willen onderzoeken of onderzoeken of er een historische tren d in stijgende kwikniveaus. Een intuïtieve manier om deze cijfers te onderzoeken en te presenteren, is door maanden als kolommen op te nemen en vervolgens de maandelijkse gemiddelde hoogtepunten van elk jaar in één rij. Op die manier is het gemakkelijk om de temperaturen zowel horizontaal, tussen aangrenzende maanden en verticaal, tussen verschillende jaren te vergelijken.

Nu we ondersteuning hebben voor PIVOT syntaxis in Spark SQL kunnen we dit bereiken met de volgende SQL-query.

De bovenstaande query zal een resultaat opleveren als:

Nou, het lijkt erop dat er goede en slechte jaren zijn. Het jaar 2016 lijkt een nogal energievriendelijk jaar.

Draaien in SQL

Laten we deze vraag eens nader bekijken om te begrijpen hoe het werkt. Eerst moeten we de FROM -clausule specificeren, die de invoer is van de pivot, met andere woorden, de tabel of subquery op basis waarvan de pivotering zal worden uitgevoerd. In ons geval maken we ons zorgen over de jaren, de maanden en de hoge temperaturen, dus dat zijn de velden die in de subquery verschijnen.

Ten tweede, laten we eens kijken naar een ander belangrijk onderdeel van de query, de PIVOT clausule. Het eerste argument van de PIVOT -clausule is een aggregatiefunctie en de kolom die moet worden geaggregeerd. Vervolgens specificeren we de pivot-kolom in de FOR -subclausule als het tweede argument, gevolgd door de IN -operator die de pivot-kolomwaarden bevat als het laatste argument.

De pivot-kolom is het punt waar de tabel omheen wordt geroteerd, en de pivot-kolomwaarden worden omgezet in kolommen in de uitvoertabel. Met de IN -clausule kunt u ook een alias specificeren voor elke pivotwaarde, waardoor het gemakkelijk wordt om meer betekenisvolle kolomnamen te genereren.

Een belangrijk idee over pivot is dat het voert een gegroepeerde aggregatie uit op basis van een lijst van impliciete group-by kolommen samen met de pivot-kolom. De impliciete group-by kolommen zijn kolommen uit de FROM -clausule die niet voorkomen in een verzamelfunctie of als de draaikolom.

In de bovenstaande zoekopdracht, waarbij de draaitabel de kolom maand is en de impliciete group-by kolom het jaar van de kolom, de uitdrukking avg(temp) wordt geaggregeerd op elk afzonderlijk waardepaar van (year, month), waarbij maand gelijk is aan een van de opgegeven waarden voor de draaikolom. Als gevolg hiervan wordt elk van deze geaggregeerde waarden toegewezen aan de bijbehorende cel van rij year en column maand.

Het is vermeldenswaard dat vanwege deze impliciete group-by, we ervoor moeten zorgen dat elke kolom waarvan we niet willen dat deze deel uitmaakt van de pivot-uitvoer, wordt weggelaten uit de FROM -clausule, anders zou de query ongewenste resultaten opleveren.

Meerdere geaggregeerde expressies specificeren

Het bovenstaande voorbeeld toont dat slechts één geaggregeerde expressie wordt gebruikt in de PIVOT -clausule, terwijl gebruikers in feite meerdere samengestelde expressies kunnen specificeren indien nodig. Nogmaals, met de bovenstaande weergegevens kunnen we de maximale hoge temperaturen samen met de gemiddelde hoge temperaturen tussen juni en september vermelden.

In het geval van meerdere samengevoegde uitdrukkingen, zullen de kolommen het Cartesiaans product van het draaipunt zijn kolomwaarden en de geaggregeerde expressies, met de namen als <value>_<aggExpr>.

Kolommen versus draaikolommen groeperen

Stel nu dat we lage temperaturen willen meenemen in onze verkenning van temperatuurtrends uit deze tabel met dagelijkse lage temperaturen:

Om deze tabel te combineren met de vorige tabel met dagelijkse hoge temperaturen, kunnen we deze twee tabellen samenvoegen in de kolom “Datum”. Aangezien we echter pivot gaan gebruiken, dat groepering op de datums uitvoert, kan eenvoudig de twee tabellen samenvoegen met UNION ALL. En je zult later zien dat deze aanpak ons ook meer flexibiliteit biedt:

Laten we nu onze draaiquery proberen met de nieuwe gecombineerde tabel:

Als resultaat krijgen we de gemiddelde hoogste en gemiddelde laagste waarden voor elke maand van de afgelopen 4 jaar in één tabel. Houd er rekening mee dat we de kolom flag in de pivot-zoekopdracht moeten opnemen, anders zou de uitdrukking avg(temp) gebaseerd zijn op een combinatie van hoog en laag temperaturen.

Het is je misschien opgevallen dat we nu twee rijen voor elk jaar hebben, één voor de hoge temperaturen en de andere voor lage temperaturen. Dat komt omdat we nog een kolom, flag, hebben opgenomen in de draaitabelinvoer, die op zijn beurt weer een impliciete groeperingskolom wordt naast de oorspronkelijke kolom year.

Als alternatief, in plaats van een groeperingskolom te zijn, kan de flag ook dienen als een draaikolom. Dus nu hebben we twee draaikolommen, month en flag:

Deze zoekopdracht geeft ons een andere lay-out van dezelfde gegevens, met één rij voor elk jaar, maar twee kolommen voor elke maand.

What’s Next

Bekijk de pivot SQL-voorbeelden om de queryvoorbeelden uit deze blog uit te voeren in dit begeleidende notitieboek.

Met dank aan de Apache Spark-gemeenschapsbijdragers voor hun bijdragen!