Transformace dat pomocí SQL

Dokončeno

Knihovna SparkSQL, která poskytuje strukturu datového rámce, umožňuje také používat SQL jako způsob práce s daty. Pomocí tohoto přístupu můžete dotazovat a transformovat data v datových rámcích pomocí dotazů SQL a zachovat výsledky jako tabulky.

Poznámka:

Tabulky jsou abstrakce metadat u souborů. Data nejsou uložená v relační tabulce, ale tabulka poskytuje relační vrstvu nad soubory v datovém jezeře.

Definování tabulek a zobrazení

Definice tabulek ve Sparku jsou uloženy v metastoru, což je vrstva metadat, která zapouzdřuje relační abstrakce nad soubory. Externí tabulky jsou relační tabulky v metastoru, které odkazují na soubory v zadaném umístění datového jezera. K datům můžete přistupovat dotazováním tabulky nebo čtením souborů přímo z datového jezera.

Poznámka:

Externí tabulky jsou "volně svázané" s podkladovými soubory a odstraněním tabulky se soubory neodstraní . Díky tomu můžete pomocí Sparku provádět náročné transformace a pak zachovat data v jezeře. Až to uděláte, můžete tabulku a podřízené procesy dostat k těmto optimalizovaným strukturám. Můžete také definovat spravované tabulky, pro které jsou podkladové datové soubory uložené v interně spravovaném úložišti přidruženém k metastoru. Spravované tabulky jsou "úzce vázané" na soubory a odstranění spravované tabulky odstraní přidružené soubory.

Následující příklad kódu uloží datový rámec (načtený ze souborů CSV) jako název externí tabulky sales_orders. Soubory jsou uloženy ve složce /sales_orders_table v datovém jezeře.

order_details.write.saveAsTable('sales_orders', format='parquet', mode='overwrite', path='/sales_orders_table')

Použití SQL k dotazování a transformaci dat

Po definování tabulky můžete pomocí jazyka SQL dotazovat a transformovat data. Následující kód vytvoří dva nové odvozené sloupce s názvem Year a Month a pak vytvoří novou tabulku transformed_orders s přidanými novými odvozenými sloupci.

# 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')

Datové soubory pro novou tabulku jsou uloženy v hierarchii složek ve formátu Year=*NNNN* / Month=*N*, přičemž každá složka obsahuje soubor parquet pro odpovídající objednávky podle roku a měsíce.

Dotazování metastoru

Vzhledem k tomu, že tato nová tabulka byla vytvořena v metastoru, můžete ji pomocí SQL dotazovat přímo pomocí magického klíče %%sql na prvním řádku, aby bylo možné označit, že se syntaxe SQL použije, jak je znázorněno v následujícím skriptu:

%%sql

SELECT * FROM transformed_orders
WHERE Year = 2021
    AND Month = 1

Přetažení tabulek

Při práci s externími tabulkami můžete pomocí DROP příkazu odstranit definice tabulek z metastoru, aniž by to ovlivnilo soubory v datovém jezeře. Tento přístup umožňuje vyčistit metastore po použití SQL k transformaci dat a zároveň zpřístupnit transformované datové soubory pro následné analýzy a příjem dat.

%%sql

DROP TABLE transformed_orders;
DROP TABLE sales_orders;