Transformace datových souborů pomocí příkazu CREATE EXTERNAL TABLE AS SELECT

5 min

Jazyk SQL obsahuje mnoho funkcí a funkcí, které umožňují manipulovat s daty. Sql můžete například použít k:

Filtrování řádků a sloupců v datové sadě
Přejmenujte datová pole a převeďte mezi datovými typy.
Výpočet odvozených datových polí
Manipulace s řetězcovými hodnotami
Seskupování a agregace dat

Bezserverové fondy SQL Azure Synapse je možné použít ke spouštění příkazů SQL, které transformují data a uchovávají výsledky jako soubor v datovém jezeře pro další zpracování nebo dotazování. Pokud znáte syntaxi jazyka Transact-SQL, můžete vytvořit příkaz SELECT, který použije konkrétní transformaci, kterou vás zajímá, a uložit výsledky příkazu SELECT ve vybraném formátu souboru se schématem tabulky metadat, které je možné dotazovat pomocí SQL.

Pomocí příkazu CREATE EXTERNAL TABLE AS SELECT (CETAS) ve vyhrazeném fondu SQL nebo bezserverovém fondu SQL můžete zachovat výsledky dotazu v externí tabulce, která ukládá data do souboru v datovém jezeře.

Příkaz CETAS obsahuje příkaz SELECT, který se dotazuje a manipuluje s daty z libovolného platného zdroje dat (což může být existující tabulka nebo zobrazení v databázi, nebo funkce OPENROWSET, která čte data založená na souborech z datového jezera). Výsledky příkazu SELECT se pak zachovají v externí tabulce, což je objekt metadat v databázi, který poskytuje relační abstrakci dat uložených v souborech. Následující diagram znázorňuje koncept vizuálně:

Diagram znázorňující příkaz CREATE EXTERNAL TABLE AS SELECT, který ukládá výsledky dotazu jako soubor

Použitím této techniky můžete pomocí SQL extrahovat a transformovat data ze souborů nebo tabulek a ukládat transformované výsledky pro zpracování nebo analýzu podřízeného zpracování nebo analýzy. Následné operace s transformovanými daty lze provádět s relační tabulkou v databázi fondu SQL nebo přímo s podkladovými datovými soubory.

Vytváření externích databázových objektů pro podporu CETAS

Pokud chcete použít výrazy CETAS, musíte v databázi vytvořit následující typy objektů pro bezserverový nebo vyhrazený fond SQL. Při použití bezserverového fondu SQL vytvořte tyto objekty ve vlastní databázi (vytvořené pomocí CREATE DATABASE příkazu), nikoli v předdefinované databázi.

Externí zdroj dat

Externí zdroj dat zapouzdřuje připojení k umístění systému souborů v datovém jezeře. Toto připojení pak můžete použít k určení relativní cesty, ve které jsou uloženy datové soubory pro externí tabulku vytvořenou příkazem CETAS.

Pokud jsou zdrojová data pro příkaz CETAS v souborech ve stejné cestě data lake, můžete k dotazování použít stejný externí zdroj dat ve funkci OPENROWSET. Alternativně můžete vytvořit samostatný externí zdroj dat pro zdrojové soubory nebo použít plně kvalifikovanou cestu k souboru ve funkci OPENROWSET.

Pokud chcete vytvořit externí zdroj dat, použijte CREATE EXTERNAL DATA SOURCE příkaz, jak je znázorněno v tomto příkladu:

-- Create an external data source for the Azure storage account
CREATE EXTERNAL DATA SOURCE files
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/files/',
    TYPE = HADOOP, -- For dedicated SQL pool
    -- TYPE = BLOB_STORAGE, -- For serverless SQL pool
    CREDENTIAL = storageCred
);

Předchozí příklad předpokládá, že uživatelé, kteří spouští dotazy, které používají externí zdroj dat, budou mít dostatečná oprávnění pro přístup k souborům. Alternativním přístupem je zapouzdření přihlašovacích údajů v externím zdroji dat, aby bylo možné ho použít pro přístup k datům souborů bez udělení oprávnění ke čtení přímo všem uživatelům:

CREATE DATABASE SCOPED CREDENTIAL storagekeycred
WITH
    IDENTITY='SHARED ACCESS SIGNATURE',  
    SECRET = 'sv=xxx...';

CREATE EXTERNAL DATA SOURCE secureFiles
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/secureFiles/'
    CREDENTIAL = storagekeycred
);

Tip

Kromě ověřování SAS můžete definovat přihlašovací údaje, které používají spravovanou identitu (identitu Microsoft Entra používanou vaším pracovním prostorem Azure Synapse), konkrétní instanční objekt Microsoft Entra nebo předávací ověřování na základě identity uživatele, který spouští dotaz (což je výchozí typ ověřování). Další informace o používání přihlašovacích údajů v bezserverovém fondu SQL najdete v článku Řízení přístupu k účtu úložiště pro bezserverový fond SQL ve službě Azure Synapse Analytics v dokumentaci k Azure Synapse Analytics.

Formát externího souboru

Příkaz CETAS vytvoří tabulku s daty uloženými v souborech. Musíte zadat formát souborů, které chcete vytvořit jako formát externího souboru.

Pokud chcete vytvořit formát externího souboru, použijte CREATE EXTERNAL FILE FORMAT příkaz, jak je znázorněno v tomto příkladu:

CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
        FORMAT_TYPE = PARQUET,
        DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
    );

Tip

V tomto příkladu se soubory uloží ve formátu Parquet. Můžete také vytvořit externí formáty souborů pro jiné typy souborů. Podrobnosti najdete v tématu CREATE EXTERNAL FILE FORMAT (Transact-SQL).

Použití příkazu CETAS

Po vytvoření externího zdroje dat a formátu externího souboru můžete pomocí příkazu CETAS transformovat data a uložit výsledky do externí tabulky.

Předpokládejme například, že zdrojová data, která chcete transformovat, tvoří prodejní objednávky v textových souborech oddělených čárkami, které jsou uložené ve složce v datovém jezeře. Chcete filtrovat data tak, aby obsahovala pouze objednávky, které jsou označené jako "zvláštní objednávka", a transformovaná data uložte jako soubory Parquet do jiné složky ve stejném datovém jezeře. Stejný externí zdroj dat můžete použít pro zdrojové i cílové složky, jak je znázorněno v tomto příkladu:

CREATE EXTERNAL TABLE SpecialOrders
    WITH (
        -- details for storing results
        LOCATION = 'special_orders/',
        DATA_SOURCE = files,
        FILE_FORMAT = ParquetFormat
    )
AS
SELECT OrderID, CustomerName, OrderTotal
FROM
    OPENROWSET(
        -- details for reading source files
        BULK 'sales_orders/*.csv',
        DATA_SOURCE = 'files',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        HEADER_ROW = TRUE
    ) AS source_data
WHERE OrderType = 'Special Order';

Parametry LOCATION a BULK parametry v předchozím příkladu jsou relativní cesty pro výsledky a zdrojové soubory. Cesty jsou relativní vzhledem k umístění systému souborů, na které odkazuje externí zdroj dat.

Důležité je pochopit, že musíte použít externí zdroj dat k určení umístění, kam se mají transformovaná data pro externí tabulku uložit. Pokud jsou zdrojová data založená na souborech uložená ve stejné hierarchii složek, můžete použít stejný externí zdroj dat. V opačném případě můžete pomocí druhého zdroje dat definovat připojení ke zdrojovým datům nebo použít plně kvalifikovanou cestu, jak je znázorněno v tomto příkladu:

CREATE EXTERNAL TABLE SpecialOrders
    WITH (
        -- details for storing results
        LOCATION = 'special_orders/',
        DATA_SOURCE = files,
        FILE_FORMAT = ParquetFormat
    )
AS
SELECT OrderID, CustomerName, OrderTotal
FROM
    OPENROWSET(
        -- details for reading source files
        BULK 'https://mystorage.blob.core.windows.net/data/sales_orders/*.csv',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        HEADER_ROW = TRUE
    ) AS source_data
WHERE OrderType = 'Special Order';

Vyřazení externích tabulek

Pokud už nepotřebujete externí tabulku obsahující transformovaná data, můžete ji z databáze odstranit pomocí příkazu DROP EXTERNAL TABLE , jak je znázorněno tady:

DROP EXTERNAL TABLE SpecialOrders;