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

Külső táblák létrehozása és módosítása az Azure Storage-ban

  • Cikk

A cikkben szereplő parancsokkal létrehozhat vagy módosíthat egy Külső Azure Storage-táblát abban az adatbázisban, amelyből a parancsot végrehajtják. Az Azure Storage külső táblái a Azure Blob Storage, az Azure Data Lake Store Gen1 vagy az Azure Data Lake Store Gen2 szolgáltatásban található adatokra hivatkoznak.

Megjegyzés

Ha a tábla létezik, a .create parancs hiba miatt meghiúsul. Meglévő táblák használata .create-or-alter vagy .alter módosítása.

Engedélyek

Ehhez .create legalább adatbázis-felhasználói engedélyekre van szükség, és .alter legalább table Rendszergazda engedélyekre van szükség.

A felügyelt identitáshitelesítést használó külső táblákhoz .create-or-alterAllDatabasesAdmin engedély szükséges.

Syntax

(.create | | .create-or-alter.alter) externaltableTableName(séma)kindstorage= [partitionby(Partíciók) [pathformat(=PathFormat)]] dataformat=DataFormat(StorageConnectionString [, ...] ) [with(Tulajdonság [, ...])]

Megjegyzés

kind minden storage külső Azure Storage-adattártípushoz használható. blob és adl elavult kifejezések.

További információ a szintaxis konvenciókról.

Paraméterek

Név Típus Kötelező Leírás
TableName string ✔️ Az entitásnevek szabályainak megfelelő külső táblanév. A külső táblák neve nem lehet ugyanaz, mint egy normál tábla ugyanabban az adatbázisban.
Séma string ✔️ A külső adatséma egy vagy több oszlopnév és adattípus vesszővel tagolt listája, ahol minden elem a következő formátumot követi: ColumnName:ColumnType. Ha a séma ismeretlen, a infer_storage_schema használatával következtethet a sémára a külső fájltartalmak alapján.
Partíciók string A külső tábla particionálására szolgáló oszlopok vesszővel tagolt listája. A partícióoszlop létezhet magában az adatfájlban, vagy a fájl elérési útjának részeként. A partíciók formázását ismertető cikkből megtudhatja, hogyan kell kinéznie ennek az értéknek.
PathFormat string A partíciókkal használható külső adatmappák URI-elérési útvonalának formátuma. Lásd az elérési út formátumát.
DataFormat string ✔️ Az adatformátum, amely a betöltési formátumok bármelyike lehet. Javasoljuk, hogy a külső táblák formátumát használja a Parquet lekérdezési és exportálási teljesítmény javításához, kivéve, ha útvonalleképezést használ JSON . Ha külső táblát használ az exportálási forgatókönyvhöz, a következő formátumokra korlátozódik: CSV, TSV, JSON és Parquet.
StorageConnectionString string ✔️ Egy vagy több vesszővel elválasztott elérési út Azure Blob Storage blobtárolókhoz, az Azure Data Lake Gen 2 fájlrendszerekhez vagy az Azure Data Lake Gen 1-tárolókhoz, beleértve a hitelesítő adatokat is. A külső táblatároló típusát a megadott kapcsolati sztringek határozzák meg. Lásd: Tárkapcsolati sztringek.
Tulajdonság string Kulcs-érték tulajdonságpár PropertyName=TulajdonságÉrték formátumban. Tekintse meg az opcionális tulajdonságokat.

Megjegyzés

A nem azonos sémával rendelkező CSV-fájlok azt eredményezhetik, hogy az adatok eltolódnak vagy hiányoznak. Javasoljuk, hogy külön sémákkal rendelkező CSV-fájlokat különítse el a tárolótárolóktól, és definiáljon egy külső táblát az egyes tárolókhoz a megfelelő sémával.

Tipp

Adjon meg több tárfiókot, hogy elkerülje a tárterület szabályozását, miközben nagy mennyiségű adatot exportál a külső táblába. Az exportálás elosztja az írásokat az összes megadott fiók között.

Hitelesítés és engedélyezés

A külső táblák eléréséhez használt hitelesítési módszer a létrehozás során megadott kapcsolati karakterlánc alapul, és a tábla eléréséhez szükséges engedélyek a hitelesítési módszertől függően változnak.

Az alábbi táblázat az Azure Storage külső tábláinak támogatott hitelesítési módszereit és a táblába való olvasáshoz vagy íráshoz szükséges engedélyeket sorolja fel.

Hitelesítési módszer Azure Blob Storage/Data Lake Storage Gen2 Data Lake Storage Gen1
Megszemélyesítés Olvasási engedélyek: Storage Blob Data Reader
Írási engedélyek: Storage-blobadatok közreműködője		 Olvasási engedélyek: Olvasó
Írási engedélyek: Közreműködő
Kezelt identitás Olvasási engedélyek: Storage Blob Data Reader
Írási engedélyek: Storage-blobadatok közreműködője		 Olvasási engedélyek: Olvasó
Írási engedélyek: Közreműködő
Közös hozzáférésű (SAS) jogkivonat Olvasási engedélyek: Lista + Olvasás
Írási engedélyek: Írni		 Ez a hitelesítési módszer az 1. generációs verzióban nem támogatott.
Microsoft Entra hozzáférési jogkivonat Nincs szükség további engedélyekre. Nincs szükség további engedélyekre.
Tárfiók hozzáférési kulcsa Nincs szükség további engedélyekre. Ez a hitelesítési módszer az 1. generációs verzióban nem támogatott.

Partíciók formázása

A partíciók listája a partícióoszlopok bármely kombinációja, amelyet az alábbi táblázatban látható űrlapok egyikével határoz meg.

Partíciótípus Syntax Jegyzetek
Virtuális oszlop PartitionName: (datetime | string) További információ a virtuális oszlopokról.
Sztringoszlop értéke PartitionName:string=ColumnName
Sztringoszlop értékkivonata PartitionName:long=hash(ColumnName,Szám) A kivonat modulo szám.
Csonkolt datetime oszlop (érték) PartitionName:datetime= (startofyear | startofdaystartofweekstartofmonth | | ) (ColumnName) Tekintse meg a startofyear, a startofmonth, a startofweek vagy a startofday függvény dokumentációját .
Csonkolt datetime oszlopérték (bin) PartitionName:datetime=bin(ColumnName,Időtartomány) További információ a bin függvényről.

Elérési út formátuma

A PathFormat paraméterrel a partíciók mellett megadhatja a külső adatmappa URI-útvonalának formátumát is. Partícióelemekből és szövegelválasztókból áll. A partícióelemek a partíció by záradékában deklarált partícióra utalnak, a szövegelválasztó pedig idézőjelek közé zárt szöveg. Az egymást követő partícióelemeket külön kell beállítani a szövegelválasztó használatával.

[ StringSeparator ] Partition [ StringSeparator ] [Partition [ StringSeparator ] ...]

Az eredeti fájl elérésiút-előtagjának létrehozásához a partícióelemek sztringekként jelennek meg, és a megfelelő szövegelválasztókkal vannak elválasztva. A makróval (datetime_pattern(DateTimeFormat,PartitionName)) megadhatja datetime_pattern a datetime partícióértékek megjelenítéséhez használt formátumot. A makró megfelel a .NET formátum specifikációjának, és lehetővé teszi, hogy a formátumkijelölőket szögletes zárójelek közé zárják. Az alábbi két formátum például egyenértékű:

  • 'year='yyyy'/month='MM
  • year={yyyy}/month={MM}

Alapértelmezés szerint a dátum/idő értékek a következő formátumokkal jelennek meg:

Partíciófüggvény Alapértelmezett formátum
startofyear yyyy
startofmonth yyyy/MM
startofweek yyyy/MM/dd
startofday yyyy/MM/dd
bin(Oszlop, 1d) yyyy/MM/dd
bin(Oszlop, 1h) yyyy/MM/dd/HH
bin(Oszlop, 1m) yyyy/MM/dd/HH/mm

Tipp

A Partíciók és a PathFormat definíció helyességének ellenőrzéséhez használja a tulajdonságot sampleUris vagy filesPreview egy külső tábla létrehozásakor.

Virtuális oszlopok

A Sparkból exportált adatok esetében a partícióoszlopok (amelyek az adatkeret-író metódusának partitionBy vannak megadva) nem lesznek adatfájlokba írva. Ez a folyamat elkerüli az adatok duplikálását, column1=<value>/column2=<value>/mert az adatok már szerepelnek a mappanevekben (például ), és a Spark képes felismerni őket olvasáskor.

A külső táblák támogatják az adatok olvasását a következő virtual columsformában: . A virtuális oszlopok típusa string vagy datetime, és a következő szintaxissal adható meg:

.create external table ExternalTable (EventName:string, Revenue:double)  
kind=storage  
partition by (CustomerName:string, Date:datetime)  
pathformat=("customer=" CustomerName "/date=" datetime_pattern("yyyyMMdd", Date))  
dataformat=parquet
( 
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey'
)

Ha egy lekérdezés virtuális oszlopai alapján szeretne szűrni, adja meg a partícióneveket a lekérdezési predikátumban:

external_table("ExternalTable")
 | where Date between (datetime(2020-01-01) .. datetime(2020-02-01))
 | where CustomerName in ("John.Doe", "Ivan.Ivanov")

Választható tulajdonságok

Tulajdonság Típus Description
folder string Tábla mappája
docString string A táblázatot dokumentáló sztring
compressed bool Ha be van állítva, azt jelzi, hogy a fájlok fájlokként .gz vannak-e tömörítve (csak exportálási forgatókönyvben használják)
includeHeaders string A tagolt szövegformátumok (CSV, TSV, ...) esetében azt jelzi, hogy a fájlok tartalmaznak-e fejlécet. Lehetséges értékek: All (minden fájl tartalmaz fejlécet), FirstFile (a mappa első fájlja fejlécet tartalmaz), None (egyetlen fájl sem tartalmaz fejlécet).
namePrefix string Ha be van állítva, a fájlok előtagját jelzi. Írási műveletek esetén az összes fájl ezzel az előtaggal lesz megírva. Olvasási műveletek esetén csak az ezzel az előtaggal rendelkező fájlok olvashatók.
fileExtension string Ha be van állítva, a fájlok fájlkiterjesztéseit jelzi. Írás esetén a fájlok neve ezzel az utótaggal végződik. Olvasás esetén csak az ezzel a fájlkiterjesztéssel rendelkező fájlok lesznek olvashatók.
encoding string Azt jelzi, hogy a szöveg hogyan van kódolva: UTF8NoBOM (alapértelmezett) vagy UTF8BOM.
sampleUris bool Ha be van állítva, a parancs eredménye több példát is tartalmaz a szimulált külső adatfájlok URI-jára, ahogy azt a külső tábladefiníció elvárja. Ez a beállítás segít ellenőrizni, hogy a Partíciók és a PathFormat paraméterek megfelelően vannak-e definiálva.
filesPreview bool Ha be van állítva, az egyik parancs eredménytáblája a .show external table artifacts parancs előnézetét tartalmazza. Például sampleUria beállítás segít ellenőrizni a külső tábladefiníció Partíciók és PathFormat paramétereit .
validateNotEmpty bool Ha be van állítva, a kapcsolati sztringek ellenőrzik, hogy van-e bennük tartalom. A parancs sikertelen lesz, ha a megadott URI-hely nem létezik, vagy ha nincs megfelelő engedély a hozzáféréshez.
dryRun bool Ha be van állítva, a külső tábladefiníció nem marad meg. Ez a beállítás hasznos a külső tábladefiníció érvényesítéséhez, különösen a vagy sampleUris paraméterrel filesPreview együtt.

Megjegyzés

A külső tábla nem érhető el a létrehozás során, csak lekérdezés és exportálás közben. A létrehozás során használja az validateNotEmpty opcionális tulajdonságot, hogy ellenőrizze, hogy a tábladefiníció érvényes-e, és hogy a tároló elérhető-e.

Tipp

Ha többet szeretne megtudni arról, hogy a szerepkör és fileExtension a tulajdonságok milyen szerepet namePrefix játszanak az adatfájl-szűrésben a lekérdezés során, tekintse meg a fájlszűrési logika szakaszt.

Fájlszűrési logika

Külső tábla lekérdezésekor a teljesítmény javul az irreleváns külső tárolófájlok kiszűrésével. A fájlok iterálásának folyamata és annak eldöntése, hogy egy fájlt fel kell-e dolgozni, a következő:

  1. Hozzon létre egy URI-mintát, amely egy olyan helyet jelöl, ahol a fájlok találhatók. Kezdetben az URI-minta megegyezik a külső tábladefiníció részeként megadott kapcsolati karakterlánc. Ha vannak definiált partíciók, azok a PathFormat használatával lesznek renderelve, majd hozzáfűzve az URI-mintához.

  2. A létrehozott URI-minta(ok) alatt található összes fájl esetében ellenőrizze a következőt:

    • A partícióértékek egyeznek a lekérdezésekben használt predikátumokkal.
    • A blobnév a következővel NamePrefixkezdődik: , ha egy ilyen tulajdonság definiálva van.
    • Ha egy ilyen tulajdonság definiálva van, a blobnév a -vel FileExtensionvégződik.

Az összes feltétel teljesülése után a rendszer lekéri és feldolgozza a fájlt.

Megjegyzés

A kezdeti URI-minta lekérdezési predikátumértékek használatával jön létre. Ez csak korlátozott sztringértékek és zárt időtartományok esetén működik a legjobban.

Példák

Nem particionált külső tábla

A következő, nem particionált külső táblában a fájloknak közvetlenül a definiált tároló(k) alá kell kerülnie:

.create external table ExternalTable (x:long, s:string)  
kind=storage 
dataformat=csv 
( 
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey' 
)

Particionált dátum szerint

A következő, dátum szerint particionált külső táblában a fájlok az alapértelmezett datetime formátumú yyyy/MM/ddkönyvtárakba kerülnek:

.create external table ExternalTable (Timestamp:datetime, x:long, s:string) 
kind=storage
partition by (Date:datetime = bin(Timestamp, 1d)) 
dataformat=csv 
( 
   h@'abfss://filesystem@storageaccount.dfs.core.windows.net/path;secretKey'
)

Particionált hónap szerint

A következő, hónap szerint particionált külső táblában a címtár formátuma a következő year=yyyy/month=MM:

.create external table ExternalTable (Timestamp:datetime, x:long, s:string) 
kind=storage 
partition by (Month:datetime = startofmonth(Timestamp)) 
pathformat=(datetime_pattern("'year='yyyy'/month='MM", Month)) 
dataformat=csv 
( 
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey' 
)

Név és dátum szerint particionált

A következő külső táblában az adatok particionálása először ügyfélnév, majd dátum szerint történik, ami azt jelenti, hogy a várt könyvtárstruktúra például customer_name=Softworks/2019/02/01:

.create external table ExternalTable (Timestamp:datetime, CustomerName:string) 
kind=storage 
partition by (CustomerNamePart:string = CustomerName, Date:datetime = startofday(Timestamp)) 
pathformat=("customer_name=" CustomerNamePart "/" Date)
dataformat=csv 
(  
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey' 
)

Kivonat és dátum szerint particionált

A következő külső tábla particionálása először ügyfélnév-kivonat (modulo tíz), majd dátum szerint történik. A várt könyvtárstruktúra például a customer_id=5/dt=20190201, és az adatfájlnevek a .txt kiterjesztéssel végződnek:

.create external table ExternalTable (Timestamp:datetime, CustomerName:string) 
kind=storage 
partition by (CustomerId:long = hash(CustomerName, 10), Date:datetime = startofday(Timestamp)) 
pathformat=("customer_id=" CustomerId "/dt=" datetime_pattern("yyyyMMdd", Date)) 
dataformat=csv 
( 
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey'
)
with (fileExtension = ".txt")

Szűrés partícióoszlopok szerint egy lekérdezésben

Ha partícióoszlopok alapján szeretne szűrni egy lekérdezésben, adja meg az eredeti oszlop nevét a lekérdezési predikátumban:

external_table("ExternalTable")
 | where Timestamp between (datetime(2020-01-01) .. datetime(2020-02-01))
 | where CustomerName in ("John.Doe", "Ivan.Ivanov")

Példakimenet

TableName TableType Mappa DocString Tulajdonságok ConnectionStrings Partíciók PathFormat
ExternalTable Blob ExternalTables Docs {"Format":"Csv","Compressed":false,"CompressionType":null,"FileExtension":null,"IncludeHeaders":"None","Encoding":null,"NamePrefix":null} ["https://storageaccount.blob.core.windows.net/container1;*******"] [{"Mod":10,"Name":"CustomerId","ColumnName":"CustomerName","Ordinal":0},{"Function":"StartOfDay","Name":"Date","ColumnName":"Timestamp","Ordinal":1}] "customer_id=" CustomerId "/dt=" datetime_pattern("yyyyMMdd",Date)

Kapcsolódó tartalom