Skapa och ändra externa Azure Storage-tabeller
Gäller för: ✅Microsoft Fabric✅Azure Data Explorer
Kommandona i den här artikeln kan användas för att skapa eller ändra en extern Azure Storage-extern tabell i databasen som kommandot körs från. En extern Azure Storage-tabell refererar till data som finns i Azure Blob Storage, Azure Data Lake Store Gen1 eller Azure Data Lake Store Gen2.
Not
Om tabellen finns misslyckas kommandot .create med ett fel. Använd .create-or-alter eller .alter för att ändra befintliga tabeller.
Behörigheter
För att .create krävs minst behörigheter för databasanvändare och för att .alter krävs minst behörigheter för tabelladministratör.
För att .create-or-alter en extern tabell med hanterad identitetsautentisering krävs AllDatabasesAdmin behörigheter.
Syntax
(.create | .alter | .create-or-alter) externaltableTableName(Schema)kind=storage [partitionby(Partitioner) [pathformat=(PathFormat)]] dataformat=DataFormat(StorageConnectionString [, ...] ) [with(Property [, ...])]
Not
kind är storage för alla typer av externa datalager i Azure Storage.
blob och adl är inaktuella termer.
Läs mer om syntaxkonventioner.
Parametrar
| Namn | Typ | Krävs | Beskrivning |
|---|---|---|---|
| TableName | string |
✔️ | Ett externt tabellnamn som följer entitetsnamn regler. En extern tabell kan inte ha samma namn som en vanlig tabell i samma databas. |
| Schema | string |
✔️ | Det externa dataschemat är en kommaavgränsad lista med ett eller flera kolumnnamn och datatyper, där varje objekt följer formatet: ColumnName:ColumnType. Om schemat är okänt använder du infer_storage_schema för att härleda schemat baserat på externt filinnehåll. |
| partitioner | string |
En kommaavgränsad lista med kolumner som den externa tabellen partitioneras med. Partitionskolumnen kan finnas i själva datafilen eller som en del av filsökvägen. Se partitionsformatering för att lära dig hur det här värdet ska se ut. | |
| PathFormat | string |
Ett URI-sökvägsformat för externa datamappar som ska användas med partitioner. Se sökvägsformat. | |
| DataFormat | string |
✔️ | Dataformatet, som kan vara något av de inmatningsformaten. Vi rekommenderar att du använder Parquet format för externa tabeller för att förbättra fråge- och exportprestanda, såvida du inte använder JSON sökvägsmappning. När du använder en extern tabell för exportscenarioär du begränsad till följande format: CSV, TSV, JSON och Parquet. |
| StorageConnectionString | string |
✔️ | En eller flera kommaavgränsade sökvägar till Azure Blob Storage-blobcontainrar, Azure Data Lake Gen 2-filsystem eller Azure Data Lake Gen 1-containrar, inklusive autentiseringsuppgifter. Den externa tabelllagringstypen bestäms av de angivna anslutningssträngarna. Se anslutningssträngar för lagring. |
| egenskap | string |
Ett nyckel/värde-egenskapspar i formatet PropertyName=PropertyValue. Se valfria egenskaper. |
Not
CSV-filer med ett icke-identiskt schema kan leda till att data visas skiftade eller saknas. Vi rekommenderar att du separerar CSV-filer med distinkta scheman för att separera lagringscontainrar och definierar en extern tabell för varje lagringscontainer med rätt schema.
Dricks
Ange mer än ett enda lagringskonto för att undvika lagringsbegränsning när exporterar stora mängder data till den externa tabellen. Export distribuerar skrivningar mellan alla konton som tillhandahålls.
Autentisering och auktorisering
Autentiseringsmetoden för åtkomst till en extern tabell baseras på anslutningssträngen som angavs när den skapades, och de behörigheter som krävs för att komma åt tabellen varierar beroende på autentiseringsmetoden.
I följande tabell visas de autentiseringsmetoder som stöds för externa Azure Storage-tabeller och de behörigheter som krävs för att läsa eller skriva till tabellen.
| Autentiseringsmetod | Azure Blob Storage/Data Lake Storage Gen2 | Data Lake Storage Gen1 |
|---|---|---|
| personifiering |
Läsbehörigheter: Storage Blob Data Reader Skrivbehörigheter: Storage Blob Data-deltagare |
Läsbehörigheter: Läsare skrivbehörigheter: deltagare |
| hanterad identitet |
Läsbehörigheter: Storage Blob Data Reader Skrivbehörigheter: Storage Blob Data-deltagare |
Läsbehörigheter: Läsare skrivbehörigheter: deltagare |
| SAS-token (Shared Access) |
Läsbehörigheter: Lista + Läs Skrivbehörigheter: Skriv |
Den här autentiseringsmetoden stöds inte i Gen1. |
| Microsoft Entra-åtkomsttoken | Inga ytterligare behörigheter krävs. | Inga ytterligare behörigheter krävs. |
| Åtkomstnyckel för lagringskonto | Inga ytterligare behörigheter krävs. | Den här autentiseringsmetoden stöds inte i Gen1. |
Partitionsformatering
Listan partitioner är en kombination av partitionskolumner som anges med något av de formulär som visas i följande tabell.
| Partitionstyp | Syntax | Anteckningar |
|---|---|---|
| Virtuell kolumn |
PartitionName: (datetime | string) |
Läs mer om virtuella kolumner. |
| Strängkolumnvärde |
PartitionName:string=ColumnName |
|
| Strängkolumnvärde hash() |
PartitionName:long=hash(ColumnName,Number) |
Hashen är modulo Number. |
| Trunkerad datetime-kolumn (värde) |
PartitionName:datetime= (startofyear | startofmonth | startofweek | startofday) (ColumnName) |
Se dokumentationen om startofyear, startofmonth, startofweekeller startofday functions. |
Trunkerat datetime-kolumnvärde =bin(ColumnName,TimeSpan) |
Läs mer om funktionen bin. |
Sökvägsformat
Med parametern PathFormat kan du ange formatet för URI-sökvägen för den externa datamappen utöver partitioner. Den består av en sekvens med partitionselement och textavgränsare. Ett partitionselement refererar till en partition som deklareras i partitionen by-satsen, och textavgränsaren är all text som omges av citattecken. Partitionselement i följd måste vara åtskilda med hjälp av textavgränsaren.
[ StringSeparator ] Partition [ StringSeparator ] [Partition [ StringSeparator ] ...]
Om du vill konstruera det ursprungliga filsökvägsprefixet återges partitionselement som strängar och avgränsas med motsvarande textavgränsare. Du kan använda makrot datetime_pattern (datetime_pattern(DateTimeFormat,PartitionName)) för att ange det format som används för att återge ett datetime-partitionsvärde. Makrot följer formatspecifikationen för .NET och tillåter att formatspecificerare omges av klammerparenteser. Följande två format är till exempel likvärdiga:
- 'year='åååå'/month='MM
- year={åååå}/month={MM}
Som standard återges datetime-värden med följande format:
| Partitionsfunktion | Standardformat |
|---|---|
startofyear |
yyyy |
startofmonth |
yyyy/MM |
startofweek |
yyyy/MM/dd |
startofday |
yyyy/MM/dd |
bin(
kolumn, 1d) |
yyyy/MM/dd |
bin(
kolumn, 1h) |
yyyy/MM/dd/HH |
bin(
kolumn, 1m) |
yyyy/MM/dd/HH/mm |
Dricks
Om du vill kontrollera partitioner och PathFormat definitionskorrigering använder du egenskapen sampleUris eller filesPreview när du skapar en extern tabell.
Virtuella kolumner
När data exporteras från Spark skrivs inte partitionskolumner (som tillhandahålls till dataramsskrivarens partitionBy-metod) till datafiler.
Den här processen undviker dataduplicering eftersom data redan finns i mappnamnen (till exempel column1=<value>/column2=<value>/), och Spark kan känna igen dem vid läsning.
Externa tabeller stöder läsning av dessa data i form av virtual colums. Virtuella kolumner kan vara av antingen typ string eller datetimeoch anges med hjälp av följande syntax:
.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'
)
Om du vill filtrera efter virtuella kolumner i en fråga anger du partitionsnamn i frågepredikat:
external_table("ExternalTable")
| where Date between (datetime(2020-01-01) .. datetime(2020-02-01))
| where CustomerName in ("John.Doe", "Ivan.Ivanov")
Valfria egenskaper
| Egenskap | Typ | Beskrivning |
|---|---|---|
folder |
string |
Tabellens mapp |
docString |
string |
Sträng som dokumenterar tabellen |
compressed |
bool |
Endast relevant för exportscenariot. Om värdet är true exporteras data i det format som anges av egenskapen compressionType. För lässökvägen identifieras komprimering automatiskt. |
compressionType |
string |
Endast relevant för exportscenariot. Komprimeringstypen för exporterade filer. För icke-Parquet-filer tillåts endast gzip. För Parquet-filer inkluderar möjliga värden gzip, snappy, lz4_raw, brotlioch zstd. Standardvärdet är gzip. För lässökvägen identifieras komprimeringstypen automatiskt. |
includeHeaders |
string |
För avgränsade textformat (CSV, TSV, ...) anger om filer innehåller ett huvud. Möjliga värden är: All (alla filer innehåller ett huvud), FirstFile (första filen i en mapp innehåller ett huvud), None (inga filer innehåller ett huvud). |
namePrefix |
string |
Om värdet anges anger du prefixet för filerna. Vid skrivåtgärder skrivs alla filer med det här prefixet. Vid läsåtgärder skrivs endast filer med det här prefixet. |
fileExtension |
string |
Om det anges anger du filnamnstillägget. Vid skrivning slutar filnamnen med det här suffixet. Vid läsning kommer endast filer med det här filtillägget att läsas. |
encoding |
string |
Anger hur texten kodas: UTF8NoBOM (standard) eller UTF8BOM. |
sampleUris |
bool |
Om det anges innehåller kommandoresultatet flera exempel på simulerade externa datafilers URI som de förväntas av definitionen för den externa tabellen. Det här alternativet hjälper dig att kontrollera om parametrarna Partitioner och PathFormat har definierats korrekt. |
filesPreview |
bool |
Om det anges innehåller en av kommandoresultattabellerna en förhandsgranskning av .show external table artifacts command. Precis som sampleUrihjälper alternativet till att verifiera parametrarna Partitioner och PathFormat för extern tabelldefinition. |
validateNotEmpty |
bool |
Om det anges verifieras anslutningssträngarna för att ha innehåll i dem. Kommandot misslyckas om den angivna URI-platsen inte finns eller om det inte finns tillräckliga behörigheter för att komma åt den. |
dryRun |
bool |
Om den anges sparas inte den externa tabelldefinitionen. Det här alternativet är användbart för att verifiera den externa tabelldefinitionen, särskilt tillsammans med parametern filesPreview eller sampleUris. |
Not
Den externa tabellen nås inte när den skapas, bara under fråga och export. Använd den validateNotEmpty valfria egenskapen när du skapar den för att kontrollera att tabelldefinitionen är giltig och att lagringen är tillgänglig.
Dricks
Mer information om vilken roll namePrefix och fileExtension egenskaper spelar i datafilfiltrering under frågan finns i avsnittet filfiltreringslogik.
Filfiltreringslogik
När du kör frågor mot en extern tabell förbättras prestandan genom att irrelevanta externa lagringsfiler filtreras bort. Processen att iterera filer och bestämma om en fil ska bearbetas är följande:
Skapa ett URI-mönster som representerar en plats där filer hittas. Inledningsvis är URI-mönstret lika med en anslutningssträng som tillhandahålls som en del av den externa tabelldefinitionen. Om det finns några definierade partitioner återges de med hjälp av PathFormatoch läggs sedan till i URI-mönstret.
Kontrollera följande för alla filer som finns under de URI-mönster som skapats:
- Partitionsvärden matchar predikat som används i en fråga.
- Blobnamnet börjar med
NamePrefix, om en sådan egenskap har definierats. - Blobnamn slutar med
FileExtension, om en sådan egenskap har definierats.
När alla villkor är uppfyllda hämtas och bearbetas filen.
Not
Det inledande URI-mönstret skapas med hjälp av frågepredikatvärden. Detta fungerar bäst för en begränsad uppsättning strängvärden samt för ett stängt tidsintervall.
Exempel
Extern tabell som inte är partitionerad
I följande icke-partitionerade externa tabell förväntas filerna placeras direkt under de container(er) som definierats:
.create external table ExternalTable (x:long, s:string)
kind=storage
dataformat=csv
(
h@'https://storageaccount.blob.core.windows.net/container1;secretKey'
)
Partitionerat efter datum
I följande externa tabell som partitioneras efter datum förväntas filerna placeras under kataloger i standardformatet datetime yyyy/MM/dd:
.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'
)
Partitionerad per månad
I följande externa tabell partitionerad efter månad är katalogformatet 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'
)
Partitionerat efter namn och datum
I följande externa tabell partitioneras data först efter kundnamn och sedan efter datum, vilket innebär att den förväntade katalogstrukturen till exempel är 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'
)
Partitionerad efter hash och datum
Följande externa tabell partitioneras först efter kundnamnshash (modulo tio) och sedan efter datum. Den förväntade katalogstrukturen är till exempel customer_id=5/dt=20190201och datafilnamn slutar med .txt-tillägget:
.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")
Filtrera efter partitionskolumner i en fråga
Om du vill filtrera efter partitionskolumner i en fråga anger du det ursprungliga kolumnnamnet i frågepredikatet:
external_table("ExternalTable")
| where Timestamp between (datetime(2020-01-01) .. datetime(2020-02-01))
| where CustomerName in ("John.Doe", "Ivan.Ivanov")
Exempel på utdata
| TableName | TableType | Mapp | DocString | Egenskaper | ConnectionStrings | Partitioner | PathFormat |
|---|---|---|---|---|---|---|---|
| ExternalTable | Klick | ExternalTables | Docs | {"Format":"Csv","Komprimerad":false,"CompressionType":null,"FileExtension":null,"IncludeHeaders":"None","Kodning":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",Datum) |