Megosztás a következőn keresztül:

Natív külső táblák létrehozása és használata SQL-készletek használatával az Azure Synapse Analyticsben

Ebben a szakaszban megtanulhatja, hogyan hozhat létre és használhat natív külső táblákat a Synapse SQL-készletekben. A natív külső tábla jobb teljesítményt biztosít azokhoz a külső táblákhoz képest, amelyek TYPE=HADOOP-t tartalmaznak a külső adatforrás definíciójában. Ennek az az oka, hogy a natív külső táblák natív kódot használnak a külső adatok eléréséhez.

A külső táblák akkor hasznosak, ha a Synapse SQL-készletben szeretné szabályozni a külső adatokhoz való hozzáférést. A külső táblák akkor is hasznosak, ha olyan eszközöket szeretne használni, mint a Power BI, a Synapse SQL-készlettel együtt. A külső táblák kétféle tárolóhoz férhetnek hozzá:

  • Nyilvános tárterület, ahol a felhasználók hozzáférnek a nyilvános tárfájlokhoz.
  • Védett tárterület, ahol a felhasználók SAS-hitelesítő adatok, Microsoft Entra-identitás vagy Synapse-munkaterület felügyelt identitása használatával férnek hozzá a tárolófájlokhoz.

Megjegyzés

Dedikált SQL-készletekben csak natív külső táblák használhatók Parquet-fájltípussal, és ez a funkció nyilvános előzetes verzióban érhető el. Ha általánosan elérhető Parquet-olvasó funkciót szeretne használni dedikált SQL-készletekben, vagy CSV- vagy ORC-fájlokat szeretne elérni, használja a Hadoop külső táblázatokat. A natív külső táblák általánosan elérhetők a kiszolgáló nélküli SQL-készletekben. További információ a natív és a Hadoop külső táblák közötti különbségekről a Külső táblák használata a Synapse SQL-ben.

Az alábbi táblázat a támogatott adatformátumokat sorolja fel:

Adatformátum (natív külső táblák) Kiszolgáló nélküli SQL-készlet Dedikált SQL-készlet
parketta Igen (GA) Igen (nyilvános előzetes verzió)
CSV fájlformátum Igen Nem (Másik lehetőségként használja a Hadoop külső táblázatokat)
delta Igen Nem
szikra Igen Nem
Dataverse Igen Nem
Azure Cosmos DB-adatformátumok (JSON, BSON stb.) Nem (Másik lehetőségként nézetek létrehozása) Nem

Előfeltételek

Első lépésként hozzon létre egy adatbázist, amelyben a táblák létre lesznek hozva. Az adatbázisnak rendelkeznie kell egy fő kulccsal a hitelesítő adatok védelméhez. Erről további információt a CREATE MASTER KEY (Transact-SQL) című témakörben talál. Ezután hozza létre a példában használt alábbi objektumokat:

  • KÜLSŐ ADATFORRÁS sqlondemanddemo , amely nyilvános demó tárfiókra hivatkozik, és külső adatforrás nyctlc , amely a nyilvánosan elérhető Azure Storage-fiókra hivatkozik a helyszínen https://azureopendatastorage.blob.core.windows.net/nyctlc/.

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

  • Fájlformátumok QuotedCSVWithHeaderFormatParquetFormat , amelyek a CSV és a parquet fájltípusokat írják le.

    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

A cikkben szereplő lekérdezések a mintaadatbázison lesznek végrehajtva, és ezeket az objektumokat használják.

Külső tábla fájlban

Létrehozhat olyan külső táblákat, amelyek egy Azure Storage-fiók adataihoz férnek hozzá, amelyek lehetővé teszik a Microsoft Entra-identitással vagy SAS-kulccsal rendelkező felhasználók hozzáférését. A külső táblákat ugyanúgy hozhatja létre, mint a hagyományos KÜLSŐ SQL Server-táblákat.

Az alábbi lekérdezés létrehoz egy külső táblát, amely a SynapseSQL demó Azure Storage-fiókjában található population.csv fájlt olvassa be, amelyre egy sqlondemanddemo adatforrással hivatkozunk.

Megjegyzés

Módosítsa a lekérdezés első sorát, azaz a [mydbname] nevet, így a létrehozott adatbázist használja.

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
);

A natív CSV-táblák jelenleg csak a kiszolgáló nélküli SQL-készletekben érhetők el.

Külső tábla egy fájlkészleten

Létrehozhat olyan külső táblákat, amelyek adatokat olvasnak az Azure Storage-ban elhelyezett fájlokból:

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
);

Megadhatja azt a mintát, amelyet a fájloknak teljesíteniük kell ahhoz, hogy a külső tábla hivatkozzon rá. A minta csak Parquet- és CSV-táblákhoz szükséges. Ha a Delta Lake formátumot használod, csak egy gyökérmappát kell megadnod, és a külső táblázat automatikusan megtalálja a mintát.

Megjegyzés

A tábla particionált mappastruktúrában jön létre, de nem használhatja a partíciók eltávolítását. Ha jobb teljesítményt szeretne elérni azáltal, hogy kihagyja azokat a fájlokat, amelyek nem felelnek meg bizonyos feltételeknek (például adott év vagy hónap ebben az esetben), használja a külső adatok nézeteit.

Külső táblázat a hozzáfűzhető fájlokról

A külső tábla által hivatkozott fájlokat nem szabad módosítani a lekérdezés futtatása közben. A hosszú ideig futó lekérdezésben az SQL-készlet újra megpróbálhatja az olvasást, a fájlok egyes részeinek olvasását, vagy akár többször is elolvashatja a fájlt. A fájltartalom módosítása rossz eredményt eredményezne. Ezért az SQL-készlet nem tudja végrehajtani a lekérdezést, ha azt észleli, hogy bármely fájl módosítási ideje módosul a lekérdezés végrehajtása során. Egyes esetekben érdemes lehet létrehozni egy táblát a folyamatosan hozzáfűzött fájlokon. A folyamatosan hozzáfűzött fájlok lekérdezési hibáinak elkerülése érdekében megadhatja, hogy a külső tábla figyelmen kívül hagyja a TABLE_OPTIONS beállítással esetlegesen inkonzisztens olvasásokat.

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"]}'
);

Az ALLOW_INCONSISTENT_READS olvasási beállítás letiltja a fájlmódosítási idő ellenőrzését a lekérdezés életciklusa során, és beolvassa a külső tábla által hivatkozott fájlokban elérhető adatokat. A hozzáfűzhető fájlokban a meglévő tartalom nem frissül, és csak új sorokat ad hozzá. Ezért a hibás eredmények valószínűsége a frissíthető fájlokhoz képest minimálisra csökken. Ez a beállítás lehetővé teheti a gyakran hozzáfűzött fájlok olvasását a hibák kezelése nélkül.

Ez a beállítás csak a CSV fájlformátumban létrehozott külső táblákban érhető el.

Megjegyzés

Ahogy a beállítás neve is mutatja, a tábla létrehozója elfogadja annak kockázatát, hogy az eredmények nem lesznek konzisztensek. A hozzáfűzhető fájlokban helytelen eredményeket kaphat, ha a tábla önillesztésével előidézi a mögöttes fájlok többszöri olvasását. A legtöbb "klasszikus" lekérdezésben a külső tábla figyelmen kívül hagy néhány sort, amelyeket a lekérdezés futtatása közben fűz hozzá.

Külső Delta Lake tábla

Külső táblák hozhatók létre egy Delta Lake-mappa tetején. Az egyetlen különbség az egyetlen fájlon vagy fájlkészleten létrehozott külső táblák és a Delta Lake-formátumban létrehozott külső táblák között az, hogy a Delta Lake külső táblában egy Delta Lake-struktúrát tartalmazó mappára kell hivatkoznia.

ECDC COVID-19 Delta Lake mappa

Egy Delta Lake-mappában létrehozott tábladefinícióra példa a következő:

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
);

A külső táblák nem hozhatók létre particionált mappában. Tekintse át a Synapse kiszolgáló nélküli SQL-készlet önsegítő oldalán található egyéb ismert problémákat.

Delta-táblák particionált mappákon

A kiszolgáló nélküli SQL-készletek külső táblái nem támogatják a Delta Lake formátumú particionálást. A Delta particionált nézeteit táblák helyett használja, ha delta Lake-adatkészleteket particionált.

Fontos

Ne hozzon létre külső táblákat particionált Delta Lake-mappákon, még akkor sem, ha azt látja, hogy bizonyos esetekben működni fognak. Nem támogatott funkciók, például a particionált deltamappák külső tábláinak használata problémákat vagy instabilitást okozhat a serverless poolban. Azure-támogatás nem tudja megoldani a problémát, ha particionált mappák tábláit használja. A rendszer megkéri, hogy váltson Delta-particionált nézetekre, és alakítsa át a kódját, hogy csak a támogatott funkciót használja a probléma megoldása előtt.

Használjon külső táblát

A lekérdezésekben ugyanúgy használhat külső táblákat , mint az SQL Server-lekérdezésekben.

Az alábbi lekérdezés ezt az előző szakaszban létrehozott külső sokaságtáblával szemlélteti. 2019-ben csökkenő sorrendben adja vissza az ország-/régióneveket a lakosságszámukkal.

Megjegyzés

Módosítsa a lekérdezés első sorát, azaz a [mydbname] nevet, így a létrehozott adatbázist használja.

USE [mydbname];
GO

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

A lekérdezés teljesítménye régiótól függően változhat. Előfordulhat, hogy a munkaterület nem ugyanabban a régióban található, mint az ezekben a mintákban használt Azure Storage-fiókok. Éles számítási feladatokhoz helyezze a Synapse-munkaterületet és az Azure Storage-t ugyanabban a régióban.

Következő lépés

Lekérdezési eredmények tárolása a tárolóban