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

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.

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

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.

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.

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.

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.

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í .

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.

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.