Pruebe este cuaderno en Databricks

ACTUALIZADO 10/11/2018

Pivot se introdujo por primera vez en Apache Spark 1.6 como una nueva función de DataFrame que permite a los usuarios rotar una expresión con valores de tabla convirtiendo los valores únicos de una columna en columnas individuales.

La versión de Apache Spark 2.4 extiende esta poderosa funcionalidad de pivotar datos a nuestros usuarios de SQL también. En este blog, usando registros de temperaturas en Seattle, mostraremos cómo podemos usar esta característica común de SQL Pivot para lograr transformaciones de datos complejas.

Examinar las temperaturas de verano con Pivot

Este verano en Seattle, las temperaturas subieron a niveles incómodos, alcanzando un máximo de 80 y 90 grados, durante nueve días en julio.

Fecha	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

Supongamos que queremos explorar o examinar si hubo un tren histórico d en niveles crecientes de mercurio. Una forma intuitiva de examinar y presentar estos números es tener meses como columnas y luego los máximos promedio mensuales de cada año en una sola fila. De esa manera, será fácil comparar las temperaturas tanto horizontalmente, entre meses adyacentes, como verticalmente, entre diferentes años.

Ahora que tenemos soporte para la sintaxis PIVOT en Spark SQL, podemos lograr esto con la siguiente consulta SQL.

La consulta anterior producirá un resultado como:

Bueno, parece que hay años buenos y malos. El año 2016 parece un año bastante favorable a la energía.

Pivotando en SQL

Analicemos más de cerca esta consulta para entender cómo funciona. Primero, necesitamos especificar la cláusula FROM, que es la entrada del pivote, en otras palabras, la tabla o subconsulta basada en la cual se realizará el pivote. En nuestro caso, nos preocupan los años, los meses y las altas temperaturas, por lo que esos son los campos que aparecen en la subconsulta.

En segundo lugar, consideremos otra parte importante de la consulta, la cláusula PIVOT. El primer argumento de la cláusula PIVOT es una función agregada y la columna que se agregará. A continuación, especificamos la columna dinámica en la subcláusula FOR como segundo argumento, seguida del operador IN que contiene los valores de la columna dinámica como el último argumento.

La columna pivote es el punto alrededor del cual se rotará la tabla, y los valores de la columna pivote se trasladarán a columnas en la tabla de salida. La cláusula IN también le permite especificar un alias para cada valor pivote, lo que facilita la generación de nombres de columna más significativos.

Una idea importante sobre pivote es que realiza una agregación agrupada basada en una lista de columnas group-by implícitas junto con la columna dinámica. Las columnas group-by implícitas son columnas de la cláusula FROM que no aparecen en ninguna función agregada o como columna dinámica.

En la consulta anterior, donde la columna dinámica es el mes de la columna y la columna group-by implícita es el año de la columna, la expresión avg(temp) se agregará en cada par de valores distintos de (year, month), donde el mes equivale a uno de los valores de la columna dinámica especificados. Como resultado, cada uno de estos valores agregados se asignará a su celda correspondiente de la fila year y column mes.

Vale la pena señalar que debido a este group-by implícito, debemos asegurarnos de que cualquier columna que no deseamos que forme parte de la salida dinámica debe quedar fuera del FROM cláusula; de lo contrario, la consulta produciría resultados no deseados.

Especificación de múltiples expresiones agregadas

El ejemplo anterior muestra solo una expresión agregada en uso en la cláusula PIVOT, mientras que de hecho, los usuarios pueden especificar múltiples expresiones agregadas si es necesario. Nuevamente, con los datos meteorológicos anteriores, podemos enumerar las temperaturas máximas altas junto con las temperaturas máximas promedio entre junio y septiembre.

En caso de múltiples expresiones agregadas, las columnas serán el producto cartesiano del pivote valores de columna y las expresiones agregadas, con los nombres como <value>_<aggExpr>.

Agrupar columnas frente a columnas dinámicas

Ahora suponga que queremos incluir las bajas temperaturas en nuestra exploración de las tendencias de temperatura de esta tabla de bajas temperaturas diarias:

Fecha	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
…	…

Para combinar esta tabla con la tabla anterior de temperaturas máximas diarias, podríamos unir estas dos tablas en la columna «Fecha». Sin embargo, como vamos a utilizar pivot, que realiza agrupaciones en las fechas, simplemente puede concatenar las dos tablas usando UNION ALL. Y verá más adelante, este enfoque también nos brinda más flexibilidad:

Ahora probemos nuestra consulta dinámica con la nueva tabla combinada:

Como resultado, obtenemos el promedio máximo y mínimo promedio de cada mes de los últimos 4 años en una tabla. Tenga en cuenta que debemos incluir la columna flag en la consulta dinámica; de lo contrario, la expresión avg(temp) se basaría en una combinación de alto y bajo temperaturas.

Puede que hayas notado que ahora tenemos dos filas para cada año, una para las temperaturas altas y la otra para las bajas. Esto se debe a que hemos incluido una columna más, flag, en la entrada dinámica, que a su vez se convierte en otra columna de agrupación implícita además de la columna original year.

Alternativamente, en lugar de ser una columna de agrupación, flag también puede servir como columna dinámica. Así que ahora tenemos dos columnas dinámicas, month y flag:

Esta consulta nos presenta un diseño diferente de los mismos datos, con una fila para cada año, pero dos columnas para cada mes.

Qué sigue

Para ejecutar los ejemplos de consulta utilizados en este blog, consulte los ejemplos de SQL dinámico en este cuaderno adjunto.

¡Gracias a los colaboradores de la comunidad de Apache Spark por sus contribuciones!