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

Cikk
10/18/2023

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áblák jobb teljesítményt biztosítanak, ha összehasonlítják a külső táblákat a külső adatforrás definíciójával TYPE=HADOOP . 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
Parquet	Igen (GA)	Igen (nyilvános előzetes verzió)
CSV	Igen	Nem (Másik lehetőségként használja a Hadoop külső táblázatokat)
delta	Igen	Nem
Spark	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ázis hatókörébe tartozó hitelesítő adatok létrehozása előtt 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:

DATABA Standard kiadás SCOPED CREDENTIALsqlondemand, amely lehetővé teszi az SAS által védett https://sqlondemandstorage.blob.core.windows.net Azure Storage-fiókhoz való hozzáférést.

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'

KÜLSŐ ADATFORRÁS sqlondemanddemo , amely SAS-kulccsal védett 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://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/' );

Fájlformátumok QuotedCSVWithHeaderFormat ParquetFormat , 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 egy fájlon

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.

A következő lekérdezés létrehoz egy külső táblát, amely a SynapseSQL demo Azure Storage-fiókból olvassa be a population.csv fájlt, amely adatforrással sqlondemanddemo van hivatkozva, és amelyet az adatbázis hatókörébe tartozó hitelesítő adatokkal véd sqlondemand.

Az adatforrás és az adatbázis hatókörébe tartozó hitelesítő adatok a beállítási szkriptben jönnek létre.

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 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 Delta Lake-formátumot használ, csak egy gyökérmappát kell megadnia, és a külső tábla 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ábla hozzáfűzhető fájlokon

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 előfordulhat, hogy az SQL-készlet újra megkísérli az olvasást, beolvassa a fájlok részeit, vagy akár többször is felolvassa 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. Bizonyos 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 öncsatlakoztatásával kényszeríti 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á.

Delta Lake külső 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 folder

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 Particionálást Delta Lake formátumban. 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. A 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 kiszolgáló nélküli készletben. Azure-támogatás nem tudja megoldani a problémát, ha particionált mappákban lévő táblákat használ. A rendszer megkéri, hogy váltson át a Delta particionált nézeteire , és írja át a kódot, hogy csak a támogatott funkciót használja a probléma megoldása előtt.

Külső tábla használata

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.

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ések

Ha tudni szeretné, hogyan tárolhatja a lekérdezések eredményeit a tárterületen, tekintse meg a Storage-lekérdezés eredményeit ismertető cikket.

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

Előfeltételek

Külső tábla egy fájlon

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

Külső tábla hozzáfűzhető fájlokon

Delta Lake külső tábla

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

Külső tábla használata

Következő lépések

Visszajelzés

További források