Vytváření a používání nativních externích tabulek pomocí fondů SQL ve službě Azure Synapse Analytics

Článek
10/18/2023

V této části se dozvíte, jak vytvářet a používat nativní externí tabulky ve fondech Synapse SQL. Nativní externí tabulky mají lepší výkon v porovnání s externími tabulkami v TYPE=HADOOP definici externího zdroje dat. Důvodem je to, že nativní externí tabulky používají nativní kód pro přístup k externím datům.

Externí tabulky jsou užitečné, když chcete řídit přístup k externím datům ve fondu Synapse SQL. Externí tabulky jsou užitečné také v případě, že chcete používat nástroje, jako je Power BI, ve spojení s fondem Synapse SQL. Externí tabulky mají přístup ke dvěma typům úložiště:

Veřejné úložiště, ve kterém uživatelé přistupuje k souborům veřejného úložiště.
Chráněné úložiště, kde uživatelé přistupující k souborům úložiště používají přihlašovací údaje SAS, identitu Microsoft Entra nebo spravovanou identitu pracovního prostoru Synapse.

Poznámka:

Ve vyhrazených fondech SQL můžete používat pouze nativní externí tabulky s typem souboru Parquet a tato funkce je ve verzi Public Preview. Pokud chcete používat obecně dostupné funkce čtečky Parquet ve vyhrazených fondech SQL nebo potřebujete přístup k souborům CSV nebo ORC, použijte externí tabulky Hadoop. Nativní externí tabulky jsou obecně dostupné v bezserverových fondech SQL. Přečtěte si další informace o rozdílech mezi nativními a externími tabulkami Hadoop v tématu Použití externích tabulek se službou Synapse SQL.

Následující tabulka uvádí podporované formáty dat:

Formát dat (nativní externí tabulky)	Bezserverový fond SQL	Vyhrazený fond SQL
Parquet	Ano (GA)	Ano (veřejná verze Preview)
CSV	Ano	Ne (případně použijte externí tabulky Hadoop)
Delta	Ano	No
Spark	Ano	No
Dataverse	Ano	No
Formáty dat azure Cosmos DB (JSON, BSON atd.)	Ne (případně vytvořit zobrazení)	No

Předpoklady

Prvním krokem je vytvoření databáze, ve které se tabulky vytvoří. Před vytvořením přihlašovacích údajů s vymezeným oborem databáze musí mít databáze hlavní klíč pro ochranu přihlašovacích údajů. Další informace najdete v tématu CREATE MASTER KEY (Transact-SQL). Pak vytvořte následující objekty, které se používají v této ukázce:

DATA SCOPED CREDENTIAL sqlondemand , která umožňuje přístup k účtu úložiště Azure chráněnému SAS https://sqlondemandstorage.blob.core.windows.net .

CREATE DATABASE SCOPED CREDENTIAL [sqlondemand]
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = 'sv=2018-03-28&ss=bf&srt=sco&sp=rl&st=2019-10-14T12%3A10%3A25Z&se=2061-12-31T12%3A10%3A00Z&sig=KlSU2ullCscyTS0An0nozEpo4tO5JAgGBvw%2FJX2lguw%3D'

EXTERNÍ ZDROJ sqlondemanddemo DAT, který odkazuje na ukázkový účet úložiště chráněný klíčem SAS a EXTERNÍ ZDROJ nyctlc DAT, který odkazuje na veřejně dostupný účet úložiště Azure v umístění https://azureopendatastorage.blob.core.windows.net/nyctlc/.

CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (
    LOCATION = 'https://sqlondemandstorage.blob.core.windows.net',
    CREDENTIAL = sqlondemand
);
GO
CREATE EXTERNAL DATA SOURCE nyctlc
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/')
GO
CREATE EXTERNAL DATA SOURCE DeltaLakeStorage
WITH ( location = 'https://sqlondemandstorage.blob.core.windows.net/delta-lake/' );

Formáty QuotedCSVWithHeaderFormat souborů, ParquetFormat které popisují typy souborů CSV a parquet.

CREATE EXTERNAL FILE FORMAT QuotedCsvWithHeaderFormat
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS ( FIELD_TERMINATOR = ',', STRING_DELIMITER = '"', FIRST_ROW = 2   )
);
GO
CREATE EXTERNAL FILE FORMAT ParquetFormat WITH (  FORMAT_TYPE = PARQUET );
GO
CREATE EXTERNAL FILE FORMAT DeltaLakeFormat WITH (  FORMAT_TYPE = DELTA );
GO

Dotazy v tomto článku se spustí ve vaší ukázkové databázi a použijí se tyto objekty.

Externí tabulka v souboru

Můžete vytvořit externí tabulky, které přistupuje k datům v účtu úložiště Azure, které umožňují přístup uživatelům s určitou identitou Microsoft Entra nebo klíčem SAS. Externí tabulky můžete vytvářet stejným způsobem jako běžné externí tabulky SQL Serveru.

Následující dotaz vytvoří externí tabulku, která načte soubor population.csv z ukázkového účtu úložiště Azure SynapseSQL, na který odkazuje zdroj sqlondemanddemo dat a chráněný přihlašovacími údaji sqlondemands vymezeným oborem databáze.

Přihlašovací údaje s vymezeným zdrojem dat a databází se vytvářejí ve skriptu instalace.

Poznámka:

Změňte první řádek v dotazu, tj. [mydbname], abyste používali databázi, kterou jste vytvořili.

USE [mydbname];
GO
CREATE EXTERNAL TABLE populationExternalTable
(
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat
);

Nativní tabulky CSV jsou aktuálně k dispozici pouze v bezserverových fondech SQL.

Externí tabulka v sadě souborů

Můžete vytvořit externí tabulky, které čtou data ze sady souborů umístěných v úložišti Azure:

CREATE EXTERNAL TABLE Taxi (
     vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
     pickup_datetime DATETIME2, 
     dropoff_datetime DATETIME2,
     passenger_count INT,
     trip_distance FLOAT,
     fare_amount FLOAT,
     tip_amount FLOAT,
     tolls_amount FLOAT,
     total_amount FLOAT
) WITH (
         LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
         DATA_SOURCE = nyctlc,
         FILE_FORMAT = ParquetFormat
);

Můžete určit vzor, který musí soubory splňovat, aby byly odkazovány externí tabulkou. Vzor se vyžaduje jenom pro tabulky Parquet a CSV. Pokud používáte formát Delta Lake, musíte zadat jenom kořenovou složku a externí tabulka automaticky najde vzor.

Poznámka:

Tabulka se vytvoří ve struktuře složek rozdělené na oddíly, ale nemůžete využít odstranění oddílu. Pokud chcete dosáhnout lepšího výkonu přeskočením souborů, které nesplňují určité kritérium (například konkrétní rok nebo měsíc v tomto případě), použijte zobrazení externích dat.

Externí tabulka u připojitelných souborů

Soubory, na které odkazuje externí tabulka, by se neměly měnit, když je dotaz spuštěný. V dlouhotrvajícím dotazu může fond SQL opakovat čtení, číst části souborů nebo dokonce číst soubor několikrát. Změny obsahu souboru by způsobily nesprávné výsledky. Fond SQL proto selže dotaz, pokud zjistí, že se během provádění dotazu změní čas změny libovolného souboru. V některých scénářích můžete chtít vytvořit tabulku u souborů, které jsou neustále připojené. Pokud se chcete vyhnout selháním dotazů kvůli neustále připojeným souborům, můžete určit, že externí tabulka by měla pomocí TABLE_OPTIONS nastavení ignorovat potenciálně nekonzistentní čtení.

CREATE EXTERNAL TABLE populationExternalTable
(
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat,
    TABLE_OPTIONS = N'{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}'
);

Možnost ALLOW_INCONSISTENT_READS čtení zakáže kontrolu času úprav souborů během životního cyklu dotazu a přečte, co je k dispozici v souborech odkazovaných externí tabulkou. V doplňovatelných souborech se stávající obsah neaktualizuje a přidají se jenom nové řádky. Proto je pravděpodobnost nesprávných výsledků minimalizovaná ve srovnání s aktualizovatelnými soubory. Tato možnost vám může umožnit čtení často připojených souborů bez zpracování chyb.

Tato možnost je k dispozici pouze v externích tabulkách vytvořených ve formátu souboru CSV.

Poznámka:

Jak název možnosti napovídá, tvůrce tabulky přijímá riziko, že výsledky nemusí být konzistentní. V připojených souborech se můžou zobrazit nesprávné výsledky, pokud vynutíte více čtení podkladových souborů tím, že tabulku připojíte sama. Ve většině klasických dotazů externí tabulka jenom ignoruje některé řádky, které se připojují během spuštění dotazu.

Externí tabulka Delta Lake

Externí tabulky je možné vytvořit nad složkou Delta Lake. Jediným rozdílem mezi externími tabulkami vytvořenými v jednom souboru nebo sadě souborů a externími tabulkami vytvořenými ve formátu Delta Lake je, že v externí tabulce Delta Lake musíte odkazovat na složku obsahující strukturu Delta Lake.

ECDC COVID-19 Delta Lake folder

Příkladem definice tabulky vytvořené ve složce Delta Lake je:

CREATE EXTERNAL TABLE Covid (
     date_rep date,
     cases int,
     geo_id varchar(6)
) WITH (
        LOCATION = 'covid', --> the root folder containing the Delta Lake files
        data_source = DeltaLakeStorage,
        FILE_FORMAT = DeltaLakeFormat
);

Externí tabulky nelze vytvořit v dělené složce. Projděte si další známé problémy na samoobslužné stránce samoobslužné podpory fondu SQL synapse.

Tabulky Delta v dělených složkách

Externí tabulky v bezserverových fondech SQL nepodporují dělení ve formátu Delta Lake. Pokud máte dělené datové sady Delta Lake, použijte místo tabulek rozdílová zobrazení .

Důležité

V rozdělených složkách Delta Lake nevytvádejte externí tabulky, i když zjistíte, že v některých případech můžou fungovat. Použití nepodporovaných funkcí, jako jsou externí tabulky v dělených rozdílových složkách, můžou způsobit problémy nebo nestabilitu bezserverového fondu. podpora Azure nebude možné vyřešit žádný problém, pokud používá tabulky v dělených složkách. Před pokračováním v řešení problémů byste byli požádáni, abyste přešli na rozdílová rozdělená zobrazení a přepsali kód tak, aby používal jenom podporovanou funkci.

Použití externí tabulky

Externí tabulky můžete ve svých dotazech používat stejným způsobem jako v dotazech SQL Serveru.

Následující dotaz ukazuje použití externí tabulky základního souboru, kterou jsme vytvořili v předchozí části. V roce 2019 vrací názvy zemí a oblastí sestupným počtem obyvatel.

Poznámka:

Změňte první řádek v dotazu, tj. [mydbname], abyste používali databázi, kterou jste vytvořili.

USE [mydbname];
GO

SELECT
    country_name, population
FROM populationExternalTable
WHERE
    [year] = 2019
ORDER BY
    [population] DESC;

Výkon tohoto dotazu se může lišit v závislosti na oblasti. Váš pracovní prostor nemusí být umístěný ve stejné oblasti jako účty úložiště Azure používané v těchto ukázkách. V případě produkčních úloh umístěte pracovní prostor Synapse a úložiště Azure do stejné oblasti.

Další kroky

Informace o tom, jak ukládat výsledky dotazu do úložiště, najdete v článku o ukládání výsledků dotazu.

Sdílet prostřednictvím

Vytváření a používání nativních externích tabulek pomocí fondů SQL ve službě Azure Synapse Analytics

Předpoklady

Externí tabulka v souboru

Externí tabulka v sadě souborů

Externí tabulka u připojitelných souborů

Externí tabulka Delta Lake

Tabulky Delta v dělených složkách

Použití externí tabulky

Další kroky

Váš názor

Váš názor

Další materiály