Transformación de datos con SQL
La biblioteca SparkSQL, que proporciona la estructura de trama de datos, también permite usar SQL como una forma de trabajar con datos. Con este enfoque, puede consultar y transformar datos en tramas de datos mediante consultas SQL y conservar los resultados como tablas.
Nota:
Las tablas son abstracciones de metadatos sobre archivos. Los datos no se almacenan en una tabla relacional, pero la tabla proporciona una capa relacional sobre los archivos del lago de datos.
Definición de tablas y vistas
Las definiciones de tabla de Spark se almacenan en metastore, una capa de metadatos que encapsula abstracciones relacionales sobre archivos. tablas de externas son tablas relacionales en el metastore que hacen referencia a archivos en una ubicación de lago de datos que especifique. Puede acceder a estos datos consultando la tabla o leyendo los archivos directamente desde el lago de datos.
Nota:
Las tablas externas se "enlazan de forma flexible" a los archivos subyacentes y la eliminación de la tabla no eliminar los archivos. Esto le permite usar Spark para realizar el trabajo pesado de la transformación y, a continuación, conservar los datos en el lago. Una vez hecho esto, puede quitar la tabla y los procesos de bajada pueden acceder a estas estructuras optimizadas. También puede definir tablas de administradas, para las que los archivos de datos subyacentes se almacenan en una ubicación de almacenamiento administrada internamente asociada al metastore. Las tablas administradas están "estrechamente enlazadas" a los archivos y la eliminación de una tabla administrada elimina los archivos asociados.
En el ejemplo de código siguiente se guarda un dataframe (cargado desde archivos CSV) como un nombre de tabla externo sales_orders. Los archivos se almacenan en la carpeta /sales_orders_table del lago de datos.
order_details.write.saveAsTable('sales_orders', format='parquet', mode='overwrite', path='/sales_orders_table')
Uso de SQL para consultar y transformar los datos
Después de definir una tabla, puede usar SQL para consultar y transformar sus datos. El código siguiente crea dos nuevas columnas derivadas denominadas Year y Month y, a continuación, crea una nueva tabla transformed_orders con las nuevas columnas derivadas agregadas.
# Create derived columns
sql_transform = spark.sql("SELECT *, YEAR(OrderDate) AS Year, MONTH(OrderDate) AS Month FROM sales_orders")
# Save the results
sql_transform.write.partitionBy("Year","Month").saveAsTable('transformed_orders', format='parquet', mode='overwrite', path='/transformed_orders_table')
Los archivos de datos de la nueva tabla se almacenan en una jerarquía de carpetas con el formato de Year=*NNNN* / Month=*N*, con cada carpeta que contiene un archivo parquet para los pedidos correspondientes por año y mes.
Consulta del metastore
Dado que esta nueva tabla se creó en el metastore, puede usar SQL para consultarla directamente con la clave mágica %%sql en la primera línea para indicar que la sintaxis SQL se usará como se muestra en el siguiente script:
%%sql
SELECT * FROM transformed_orders
WHERE Year = 2021
AND Month = 1
Quitar tablas
Al trabajar con tablas externas, puede usar el comando DROP para eliminar las definiciones de tabla de la metastore sin afectar a los archivos del lago de datos. Este enfoque permite limpiar el metastore después de usar SQL para transformar los datos, al tiempo que hace que los archivos de datos transformados estén disponibles para los procesos de análisis y ingesta de datos de bajada.
%%sql
DROP TABLE transformed_orders;
DROP TABLE sales_orders;