Transformer des données avec SQL
La bibliothèque SparkSQL, qui fournit la structure de dataframe, vous permet aussi d’utiliser SQL comme méthode pour travailler avec des données. Avec cette approche, vous pouvez interroger et transformer des données dans des dataframes en utilisant des requêtes SQL, et enregistrer les résultats sous forme de tables.
Notes
Les tables sont des abstractions de métadonnées sur des fichiers. Les données ne sont pas stockées dans une table relationnelle, mais la table fournit une couche relationnelle sur les fichiers du lac de données.
Créer des tables et des vues
Les définitions de table dans Spark sont stockées dans le metastore, une couche de métadonnées qui encapsule les abstractions relationnelles sur les fichiers. Les tables externes sont des tables relationnelles du metastore qui référencent des fichiers à un emplacement du lac de données que vous spécifiez. Vous pouvez accéder à ces données en interrogeant la table ou en lisant les fichiers directement à partir du lac de données.
Notes
Les tables externes sont « faiblement liées » aux fichiers sous-jacents et la suppression de la table ne supprime pas les fichiers. Ceci vous permet d’utiliser Spark pour effectuer le gros travail de transformation, puis d’enregistrer les données dans le lac. Une fois cette opération effectuée, vous pouvez supprimer la table, et les processus en aval peuvent accéder à ces structures optimisées. Vous pouvez aussi définir des tables managées, pour lesquelles les fichiers de données sous-jacents sont stockés à un emplacement de stockage géré en interne associé au metastore. Les tables managées sont « étroitement liées » aux fichiers et la suppression d’une table managée supprime les fichiers associés.
L’exemple de code suivant enregistre un dataframe (chargé depuis des fichiers CSV) sous la forme d’une table externe nommée sales_orders. Les fichiers sont stockés dans le dossier /sales_orders_table du lac de données.
order_details.write.saveAsTable('sales_orders', format='parquet', mode='overwrite', path='/sales_orders_table')
Utiliser SQL pour interroger et transformer les données
Après avoir défini une table, vous pouvez utiliser SQL pour interroger et transformer ses données. Le code suivant crée deux nouvelles colonnes dérivées nommées Year et Month, puis crée une table transformed_orders avec les nouvelles colonnes dérivées ajoutées.
# 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')
Les fichiers de données de la nouvelle table sont stockés dans une hiérarchie de dossiers au format Year=*NNNN* / Month=*N*, chaque dossier contenant un fichier Parquet pour les commandes correspondantes par année et par mois.
Interroger le metastore
Comme cette nouvelle table a été créée dans le metastore, vous pouvez utiliser SQL pour l’interroger directement avec la clé magic %%sql dans la première ligne pour indiquer que la syntaxe SQL sera utilisée, comme indiqué dans le script suivant :
%%sql
SELECT * FROM transformed_orders
WHERE Year = 2021
AND Month = 1
Supprimer des tables
Quand vous travaillez avec des tables externes, vous pouvez utiliser la commande DROP pour supprimer les définitions de table du metastore sans affecter les fichiers dans le lac de données. Cette approche vous permet de nettoyer le metastore après avoir utilisé SQL pour transformer les données, tout rendant les fichiers de données transformés disponibles pour les processus d’analyse et d’ingestion des données en aval.
%%sql
DROP TABLE transformed_orders;
DROP TABLE sales_orders;