Transformer des données avec SQL
La bibliothèque SparkSQL, qui fournit la structure de trame de données vous permet également d’utiliser SQL comme moyen d’utiliser des données. Avec cette approche, vous pouvez interroger et transformer des données dans des trames de données à l’aide de requêtes SQL et conserver les résultats sous forme de tables.
Remarque
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 des fichiers dans le lac de données.
Définir 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 des abstractions relationnelles sur les fichiers. tables de externes sont des tables relationnelles dans le metastore qui référence les fichiers dans un emplacement data lake 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.
Remarque
Les tables externes sont « faiblement liées » aux fichiers sous-jacents et la suppression de la table ne pas supprimer les fichiers. Cela vous permet d’utiliser Spark pour effectuer le gros travail de transformation, puis de conserver 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 également définir tables de managées, pour lesquelles les fichiers de données sous-jacents sont stockés dans 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é à partir de fichiers CSV) en tant que nom de table externe sales_orders. Les fichiers sont stockés dans le dossier /sales_orders_table dans le 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*, avec chaque dossier contenant un fichier Parquet pour les commandes correspondantes par année et par mois.
Interroger le metastore
Étant donné que cette nouvelle table a été créée dans le metastore, vous pouvez utiliser SQL pour l’interroger directement avec la clé magique %%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
Lorsque vous utilisez 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 en 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;