Share via

Külső táblák használata a Synapse SQL-lel

  • Cikk

Egy külső tábla a Hadoopban, az Azure Storage-blobban vagy az Azure Data Lake Storage-ban található adatokra mutat. Külső táblák használatával adatokat olvashat fájlokból, vagy adatokat írhat az Azure Storage-fájlokba.

A Synapse SQL-ben külső táblák használatával olvashatja a külső adatokat dedikált SQL-készlet vagy kiszolgáló nélküli SQL-készlet használatával.

A külső adatforrás típusától függően kétféle külső táblát használhat:

  • Hadoop külső táblák , amelyekkel adatokat olvashat és exportálhat különböző adatformátumokban, például CSV, Parquet és ORC formátumban. A Hadoop külső táblák dedikált SQL-készletekben érhetők el, de kiszolgáló nélküli SQL-készletekben nem érhetők el.
  • Natív külső táblák , amelyekkel adatokat olvashat és exportálhat különböző adatformátumokban, például CSV-ben és Parquetben. A natív külső táblák kiszolgáló nélküli SQL-készletekben érhetők el, és nyilvános előzetes verzióban érhetők el a dedikált SQL-készletekben. Adatok írása/exportálása a CETAS és a natív külső táblák használatával csak a kiszolgáló nélküli SQL-készletben érhető el, a dedikált SQL-készletekben azonban nem.

A Hadoop és a natív külső táblák közötti főbb különbségek:

Külső tábla típusa Hadoop Natív
Dedikált SQL-készlet Elérhető Nyilvános előzetes verzióban csak parquet-táblák érhetők el.
Kiszolgáló nélküli SQL-készlet Nem elérhető Elérhető
Támogatott formátumok Tagolt/CSV, Parquet, ORC, Hive RC és RC Kiszolgáló nélküli SQL-készlet: Tagolt/CSV, Parquet és Delta Lake
Dedikált SQL-készlet: Parquet (előzetes verzió)
Mappapartíció eltávolítása Nem A particionálás megszüntetése csak az Apache Spark-készletekből szinkronizált Parquet- vagy CSV-formátumokon létrehozott particionált táblákban érhető el. Előfordulhat, hogy külső táblákat hoz létre a Parquet particionált mappáiban, de a particionálási oszlopok elérhetetlenek és figyelmen kívül hagyhatók, míg a partícióeltörlés nem lesz alkalmazva. Ne hozzon létre külső táblákat a Delta Lake-mappákban , mert azok nem támogatottak. Ha particionált Delta Lake-adatokat szeretne lekérdezni, használja a Delta particionált nézeteit .
Fájlelivétel (predikátum leküldése) Nem Igen a kiszolgáló nélküli SQL-készletben. A sztringleküldéshez a leküldés engedélyezéséhez az oszlopokon VARCHAR lévő rendezést kell használniaLatin1_General_100_BIN2_UTF8. A rendezésekkel kapcsolatos további információkért tekintse meg a Synapse SQL által támogatott rendezési típusokat.
Hely egyéni formátuma Nem Igen, helyettesítő karakterek használata, például /year=*/month=*/day=* Parquet- vagy CSV-formátumokhoz. Az egyéni mappa elérési útjai nem érhetők el a Delta Lake-ben. A kiszolgáló nélküli SQL-készletben rekurzív helyettesítő karakterek /logs/** használatával is hivatkozhat parquet- vagy CSV-fájlokra a hivatkozott mappa alatti almappákban.
Rekurzív mappavizsgálat Igen Igen. A kiszolgáló nélküli SQL-készletekben a hely elérési útjának végén kell megadni /** . A dedikált készletben a mappák mindig rekurzívan vannak beolvasva.
Tárhitelesítés Storage Access Key(SAK), Microsoft Entra átengedése, felügyelt identitás, egyéni alkalmazás Microsoft Entra-identitása Közös hozzáférésű jogosultságkód (SAS), Microsoft Entra átengedés, felügyelt identitás, egyéni alkalmazás Microsoft Entra-identitása.
Oszlopleképezés Ordinal – A külső tábladefiníció oszlopai pozíció szerint vannak leképezve az alapul szolgáló Parquet-fájlok oszlopaihoz. Kiszolgáló nélküli készlet: név szerint. A külső tábladefiníció oszlopai oszlopnévegyeztetés alapján vannak megfeleltetve az alapul szolgáló Parquet-fájlok oszlopaihoz.
Dedikált készlet: sorszámegyezés. A külső tábladefiníció oszlopai pozíció szerint vannak leképezve az alapul szolgáló Parquet-fájlok oszlopaihoz.
CETAS (exportálás/átalakítás) Igen A célként megadott natív táblákkal rendelkező CETAS csak a kiszolgáló nélküli SQL-készletben működik. A dedikált SQL-készletek nem használhatók az adatok natív táblákkal való exportálására.

Megjegyzés:

A natív külső táblák az általánosan elérhető készletek ajánlott megoldásai. Ha külső adatokhoz kell hozzáférnie, mindig használja a kiszolgáló nélküli készletek natív tábláinak használatát. A dedikált készletekben váltson a parquet-fájlok olvasására szolgáló natív táblákra, ha azok a GA-ban vannak. A Hadoop-táblákat csak akkor használja, ha natív külső táblákban (például - ORC, RC) nem támogatott típusokat kell elérnie, vagy ha a natív verzió nem érhető el.

Külső táblák dedikált SQL-készletben és kiszolgáló nélküli SQL-készletben

Külső táblákat a következőre használhat:

  • Az Azure Blob Storage és az Azure Data Lake Gen2 lekérdezése Transact-SQL utasításokkal.
  • A lekérdezési eredmények tárolása fájlokban az Azure Blob Storage-ban vagy az Azure Data Lake Storage-ban a CETAS használatával.
  • Importálja az adatokat az Azure Blob Storage-ból és az Azure Data Lake Storage-ból, és tárolja egy dedikált SQL-készletben (csak Hadoop-táblák dedikált készletben).

Megjegyzés:

Ha a CREATE TABLE AS Standard kiadás LECT utasítással együtt használják, a külső tábla kiválasztása adatokat importál egy táblába a dedikált SQL-készletben.

Ha a hadoop külső táblák teljesítménye a dedikált készletekben nem felel meg a teljesítménycéloknak, érdemes lehet külső adatokat betölteni a Datawarehouse-táblákba a COPY utasítással.

A betöltési oktatóanyagot a PolyBase használatával az Azure Blob Storage-ból töltheti be.

A Synapse SQL-készletekben a következő lépésekkel hozhat létre külső táblákat:

  1. KÜLSŐ ADATFORRÁS LÉTREHOZÁSA külső Azure-tárolóra való hivatkozáshoz, és adja meg a tár eléréséhez használandó hitelesítő adatokat.
  2. CREATE EXTERNAL FILE FORMAT to describe format of CSV or Parquet files.
  3. CREATE EXTERNAL TABLE on the files on the data source with the same file format.

Mappapartíció eltávolítása

A Synapse-készletek natív külső táblái figyelmen kívül hagyhatják a lekérdezésekhez nem releváns mappákban elhelyezett fájlokat. Ha a fájlok mappahierarchiában vannak tárolva (például - /year=2020/month=03/day=16) és a year, és monthday oszlopokként vannak közzétéve, a szűrőket year=2020 tartalmazó lekérdezések csak a mappában elhelyezett almappákból olvassák be a year=2020 fájlokat. A lekérdezés figyelmen kívül hagyja a más mappákban (year=2021 vagy ) elhelyezett fájlokat és year=2022mappákat. Ezt az eliminációt partícióeliminálásnak nevezzük.

A mappapartíció eltávolítása a Synapse Spark-készletekből szinkronizált natív külső táblákban érhető el. Ha particionált adatkészlettel rendelkezik, és a partíció eltávolítását a létrehozott külső táblákkal szeretné kihasználni, használja a particionált nézeteket a külső táblák helyett.

Fájlelitörlés

Egyes adatformátumok, például a Parquet és a Delta fájlstatisztikákat tartalmaznak az egyes oszlopokhoz (például az egyes oszlopok minimális/maximális értékeihez). Az adatokat szűrő lekérdezések nem olvassák be azokat a fájlokat, amelyekben a szükséges oszlopértékek nem léteznek. A lekérdezés először megvizsgálja a lekérdezési predikátumban használt oszlopok minimális/maximális értékeit, hogy megtalálja azokat a fájlokat, amelyek nem tartalmazzák a szükséges adatokat. Ezek a fájlok figyelmen kívül lesznek hagyva, és törlődnek a lekérdezési tervből. Ezt a technikát szűrő predikátum leküldéses leküldésnek is nevezik, és javíthatja a lekérdezések teljesítményét. A szűrőleküldés a Parquet- és Delta-formátumok kiszolgáló nélküli SQL-készleteiben érhető el. A sztringtípusok szűrőleküldésének használatához használja a VARCHAR típust a Latin1_General_100_BIN2_UTF8 rendezéssel. A rendezésekkel kapcsolatos további információkért tekintse meg a Synapse SQL által támogatott rendezési típusokat.

Biztonság

A felhasználónak engedéllyel kell rendelkeznie SELECT egy külső táblában az adatok olvasásához. A külső táblák az adatforrásban definiált adatbázis-hatókörű hitelesítő adatokkal férnek hozzá a mögöttes Azure Storage-hez az alábbi szabályok használatával:

  • A hitelesítő adatok nélküli adatforrás lehetővé teszi a külső táblák számára, hogy hozzáférjenek az Azure Storage nyilvánosan elérhető fájljaihoz.
  • Az adatforrás rendelkezhet olyan hitelesítő adatokkal, amelyek lehetővé teszik, hogy a külső táblák csak az Azure Storage-ban lévő fájlokat érhessék el SAS-jogkivonat vagy munkaterület felügyelt identitás használatával – Ilyen például a Tárfájlok tárterület-hozzáférés-vezérlésének fejlesztése című cikk.

Példa a CREATE EXTERNAL DATA SOURCE (KÜLSŐ ADATFORRÁS LÉTREHOZÁSA) parancsra

Az alábbi példa létrehoz egy Hadoop külső adatforrást az Azure Data Lake Gen2 dedikált SQL-készletében, amely a New York-i adatkészletre mutat:

CREATE DATABASE SCOPED CREDENTIAL [ADLS_credential]
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'
GO
CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH
  -- Please note the abfss endpoint when your account has secure transfer enabled
  ( LOCATION = 'abfss://data@newyorktaxidataset.dfs.core.windows.net' ,
    CREDENTIAL = ADLS_credential ,
    TYPE = HADOOP
  ) ;

Az alábbi példa egy külső adatforrást hoz létre az Azure Data Lake Gen2 számára, amely a nyilvánosan elérhető New York-i adatkészletre mutat:

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/',
       TYPE = HADOOP)

Példa a CREATE EXTERNAL FILE FORMAT (KÜLSŐ FÁJLFORMÁTUM LÉTREHOZÁSA) parancsra

Az alábbi példa egy külső fájlformátumot hoz létre a census fájlokhoz:

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

Példa CREATE EXTERNAL TABLE (KÜLSŐ TÁBLA LÉTREHOZÁSA)

Az alábbi példa egy külső táblát hoz létre. Az első sort adja vissza:

CREATE EXTERNAL TABLE census_external_table
(
    decennialTime varchar(20),
    stateName varchar(100),
    countyName varchar(100),
    population int,
    race varchar(50),
    sex    varchar(10),
    minAge int,
    maxAge int
)  
WITH (
    LOCATION = '/parquet/',
    DATA_SOURCE = population_ds,  
    FILE_FORMAT = census_file_format
)
GO

SELECT TOP 1 * FROM census_external_table

Külső táblák létrehozása és lekérdezése fájlból az Azure Data Lake-ben

A Synapse Studio Data Lake-felderítési képességeinek használatával mostantól létrehozhat és lekérdezhet egy külső táblát a Synapse SQL-készlettel, és egyszerűen kattintson a fájlra a jobb gombbal. Az ADLS Gen2-tárfiókból külső táblák létrehozásához használt egykattintásos kézmozdulat csak Parquet-fájlok esetében támogatott.

Előfeltételek

  • Hozzáféréssel kell rendelkeznie a munkaterülethez legalább az Storage Blob Data Contributor ADLS Gen2-fiókhoz vagy a hozzáférés-vezérlési listákhoz (ACL) való hozzáférési szerepkörrel, amelyek lehetővé teszik a fájlok lekérdezését.

  • Legalább engedéllyel kell rendelkeznie egy külső tábla létrehozásához és külső táblák lekérdezéséhez a Synapse SQL-készletben (dedikált vagy kiszolgáló nélküli).

Az Adatok panelen válassza ki azt a fájlt, amelyből létre szeretné hozni a külső táblát:

externaltable1

Ekkor megnyílik egy párbeszédpanel. Válassza ki a dedikált SQL-készletet vagy a kiszolgáló nélküli SQL-készletet, adjon nevet a táblának, és válassza a megnyitott szkriptet:

externaltable2

Az SQL-szkript automatikusan kinyeri a sémát a fájlból:

externaltable3

Run the script. A szkript automatikusan futtat egy Select Top 100 *.:

externaltable4

A külső tábla létrejött, a külső tábla tartalmának későbbi feltárásához a felhasználó közvetlenül az Adatok panelről kérdezheti le:

externaltable5

Következő lépések

A lekérdezési eredmények Azure Storage-beli külső táblába való mentéséről a CETAS-cikkben olvashat. Vagy elkezdheti az Apache Spark lekérdezését az Azure Synapse külső tábláihoz.