Experimente este bloco de notas no Databricks

ATUALIZADO em 10/11/2018

O Pivot foi introduzido pela primeira vez no Apache Spark 1.6 como um novo recurso DataFrame que permite aos usuários girar uma expressão com valor de tabela transformando os valores exclusivos de uma coluna em colunas individuais.

A versão 2.4 do Apache Spark estende essa funcionalidade poderosa de dados dinâmicos para nossos usuários SQL também. Neste blog, usando registros de temperatura em Seattle, mostraremos como podemos usar esse recurso SQL Pivot comum para obter transformações de dados complexas.

Examinando temperaturas de verão com Pivot

Neste verão em Seattle, as temperaturas atingiram níveis desconfortáveis, chegando a altos 80 e 90, durante nove dias em julho.

Suponha que queiramos explorar ou examinar se houve um trem histórico d nos níveis crescentes de mercúrio. Uma maneira intuitiva de examinar e apresentar esses números é ter os meses como colunas e, em seguida, os máximos médios mensais de cada ano em uma única linha. Dessa forma, será fácil comparar as temperaturas horizontalmente, entre meses adjacentes, e verticalmente, entre anos diferentes.

Agora que temos suporte para a sintaxe PIVOT no Spark SQL, podemos fazer isso com a seguinte consulta SQL.

A consulta acima produzirá um resultado como:

Bem, parece que há anos bons e anos ruins. O ano de 2016 parece um ano bastante favorável à energia.

Pivoting in SQL

Vamos dar uma olhada nesta consulta para entender como ela funciona. Primeiro, precisamos especificar a cláusula FROM, que é a entrada do pivô, em outras palavras, a tabela ou subconsulta com base na qual o pivô será executado. No nosso caso, estamos preocupados com os anos, os meses e as altas temperaturas, então esses são os campos que aparecem na subconsulta.

Em segundo lugar, vamos considerar outra parte importante da consulta, a cláusula PIVOT. O primeiro argumento da cláusula PIVOT é uma função de agregação e a coluna a ser agregada. Em seguida, especificamos a coluna dinâmica na FOR subcláusula como o segundo argumento, seguida pelo operador IN contendo os valores da coluna dinâmica como o último argumento.

A coluna dinâmica é o ponto em torno do qual a tabela será girada e os valores da coluna dinâmica serão transpostos em colunas na tabela de saída. A cláusula IN também permite que você especifique um alias para cada valor pivô, facilitando a geração de nomes de coluna mais significativos.

Uma ideia importante sobre pivô é que ele executa uma agregação agrupada com base em uma lista de colunas group-by implícitas junto com a coluna dinâmica. As colunas group-by implícitas são colunas da cláusula FROM que não aparecem em nenhuma função agregada ou como a coluna dinâmica.

Na consulta acima, com a coluna dinâmica sendo a coluna mês e a coluna group-by implícita sendo a coluna ano, a expressão avg(temp) será agregado em cada par de valores distintos de (year, month), onde mês é igual a um dos valores da coluna dinâmica especificada. Como resultado, cada um desses valores agregados será mapeado em sua célula correspondente da linha year e column mês.

É importante notar que, devido a este group-by implícito, precisamos nos certificar de que qualquer coluna que não desejamos que faça parte da saída dinâmica deve ser deixada de fora do FROM cláusula, caso contrário, a consulta produziria resultados indesejados.

Especificando várias expressões agregadas

O exemplo acima mostra apenas uma expressão agregada sendo usada na cláusula PIVOT, embora, na verdade, os usuários possam especificar várias expressões de agregação, se necessário. Novamente, com os dados meteorológicos acima, podemos listar as altas temperaturas máximas juntamente com as altas temperaturas médias entre junho e setembro.

No caso de múltiplas expressões de agregação, as colunas serão o produto cartesiano do pivô valores de coluna e as expressões agregadas, com os nomes como <value>_<aggExpr>.

Colunas de agrupamento vs. colunas pivotantes

Agora, suponha que queremos incluir baixas temperaturas em nossa exploração das tendências de temperatura desta tabela de baixas temperaturas diárias:

Para combinar esta tabela com a tabela anterior de altas temperaturas diárias, poderíamos juntar essas duas tabelas na coluna “Data”. No entanto, como vamos usar o pivot, que realiza agrupamento nas datas, pode simplesmente concatenar as duas tabelas usando UNION ALL. E você verá mais tarde, essa abordagem também nos fornece mais flexibilidade:

Agora, vamos tentar nossa consulta dinâmica com a nova tabela combinada:

Como resultado, obtemos a média máxima e a média mínima para cada mês dos últimos 4 anos em uma tabela. Observe que precisamos incluir a coluna flag na consulta dinâmica, caso contrário, a expressão avg(temp) seria baseada em uma mistura de alto e baixo temperaturas.

Você deve ter notado que agora temos duas linhas para cada ano, uma para altas temperaturas e outra para baixas temperaturas. Isso porque incluímos mais uma coluna, flag, na entrada dinâmica, que por sua vez se torna outra coluna de agrupamento implícita além da coluna original year.

Alternativamente, em vez de ser uma coluna de agrupamento, o flag também pode servir como uma coluna dinâmica. Portanto, agora temos duas colunas pivô, month e flag:

Esta consulta nos apresenta um layout diferente de os mesmos dados, com uma linha para cada ano, mas duas colunas para cada mês.

O que vem a seguir

Para executar os exemplos de consulta usados neste blog, verifique os exemplos de SQL pivô neste bloco de notas que acompanha.

Obrigado aos contribuidores da comunidade Apache Spark por suas contribuições!