Gegevens transformeren met SQL
Met de SparkSQL-bibliotheek, die de structuur van het dataframe biedt, kunt u SQL ook gebruiken als een manier om met gegevens te werken. Met deze methode kunt u gegevens in dataframes opvragen en transformeren met behulp van SQL-query's en de resultaten behouden als tabellen.
Notitie
Tabellen zijn metagegevensabstracties over bestanden. De gegevens worden niet opgeslagen in een relationele tabel, maar de tabel biedt een relationele laag over bestanden in de data lake.
Tabellen en weergaven definiëren
Tabeldefinities in Spark worden opgeslagen in de metastore, een metagegevenslaag die relationele abstracties over bestanden inkapselt. Externe tabellen zijn relationele tabellen in de metastore die verwijzen naar bestanden op een data lake-locatie die u opgeeft. U hebt toegang tot deze gegevens door een query uit te voeren op de tabel of door de bestanden rechtstreeks vanuit de data lake te lezen.
Notitie
Externe tabellen zijn 'losjes gebonden' aan de onderliggende bestanden en als u de tabel verwijdert, worden de bestanden niet verwijderd. Hierdoor kunt u Spark gebruiken om het zware werk van de transformatie uit te voeren en vervolgens de gegevens in de lake vast te houden. Nadat dit is gebeurd, kunt u de tabel verwijderen en downstreamprocessen toegang krijgen tot deze geoptimaliseerde structuren. U kunt ook beheerde tabellen definiëren waarvoor de onderliggende gegevensbestanden worden opgeslagen in een intern beheerde opslaglocatie die is gekoppeld aan de metastore. Beheerde tabellen zijn 'nauw gebonden' aan de bestanden en wanneer een beheerde tabel wordt verwijderd, worden de bijbehorende bestanden verwijderd.
In het volgende codevoorbeeld wordt een dataframe (geladen vanuit CSV-bestanden) opgeslagen als een externe tabelnaam sales_orders. De bestanden worden opgeslagen in de map /sales_orders_table in de data lake.
order_details.write.saveAsTable('sales_orders', format='parquet', mode='overwrite', path='/sales_orders_table')
SQL gebruiken om query's uit te voeren en de gegevens te transformeren
Nadat u een tabel hebt gedefinieerd, kunt u SQL gebruiken om query's uit te voeren op de gegevens en deze te transformeren. De volgende code maakt twee nieuwe afgeleide kolommen met de naam Year en Month en maakt vervolgens een nieuwe tabel transformed_orders met de nieuwe afgeleide kolommen toegevoegd.
# 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')
De gegevensbestanden voor de nieuwe tabel worden opgeslagen in een hiërarchie van mappen met de indeling Year=*NNNN* / Month=*N*, waarbij elke map een Parquet-bestand bevat voor de bijbehorende orders per jaar en maand.
Query's uitvoeren op de metastore
Omdat deze nieuwe tabel is gemaakt in de metastore, kunt u SQL gebruiken om deze rechtstreeks te doorzoeken met de magic-sleutel %%sql in de eerste regel om aan te geven dat de SQL-syntaxis wordt gebruikt, zoals wordt weergegeven in het volgende script:
%%sql
SELECT * FROM transformed_orders
WHERE Year = 2021
AND Month = 1
Tabellen neerzetten
Wanneer u met externe tabellen werkt, kunt u de DROP opdracht gebruiken om de tabeldefinities uit de metastore te verwijderen zonder dat dit van invloed is op de bestanden in de data lake. Met deze aanpak kunt u de metastore opschonen nadat u SQL hebt gebruikt om de gegevens te transformeren, terwijl u de getransformeerde gegevensbestanden beschikbaar maakt voor downstreamgegevensanalyse- en opnameprocessen.
%%sql
DROP TABLE transformed_orders;
DROP TABLE sales_orders;