Dela via

Skapa och ändra externa Azure Storage-tabeller

  • Artikel

Kommandona i den här artikeln kan användas för att skapa eller ändra en extern Azure Storage-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.

Anteckning

Om tabellen finns .create misslyckas kommandot med ett fel. Använd .create-or-alter eller .alter för att ändra befintliga tabeller.

Behörigheter

För att .create kräver minst behörigheter för databasanvändare och för att .alter minst behöva behörigheter för table Admin.

För .create-or-alter en extern tabell som använder hanterad identitetsautentisering krävs Behörigheter för AllDatabasesAdmin .

Syntax

(.create | .create-or-alter | .alter) externaltableTableName Schema()=kindstorage [partitionby(Partitioner) [pathformat(=PathFormat)]] dataformat=DataFormat(StorageConnectionString [, ...] ) [with(Egenskap [, ...])]

Anteckning

kind är storage för alla typer av externa Azure Storage-datalager. blob och adl är inaktuella termer.

Läs mer om syntaxkonventioner.

Parametrar

Namn Typ Obligatorisk Beskrivning
TableName string ✔️ Ett externt tabellnamn som följer reglerna för entitetsnamn . En extern tabell får 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 är partitionerad 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 inmatningsformaten. Vi rekommenderar att du använder Parquet formatet 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 exportscenariot är du begränsad till följande format: CSV, TSVJSON 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.

Anteckning

CSV-filer med icke-identiska scheman kan resultera i att data visas skiftade eller saknas. Vi rekommenderar att du separerar CSV-filer med distinkta scheman för att avgränsa lagringscontainrar och definiera en extern tabell för varje lagringscontainer med rätt schema.

Tips

Ange mer än ett enda lagringskonto för att undvika lagringsbegränsning vid export av 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å de anslutningssträng 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örighet: Storage Blob Data-deltagare		 Läsbehörigheter: Läsare
Skrivbehörighet: Bidragsgivare
Hanterade identiteter Läsbehörigheter: Storage Blob Data Reader
Skrivbehörighet: Storage Blob Data-deltagare		 Läsbehörigheter: Läsare
Skrivbehörighet: Bidragsgivare
Sas-token (delad åtkomst) Läsbehörigheter: Lista + läsa
Skrivbehörighet: Skriva		 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

Partitionslistan är valfri kombination av partitionskolumner, som anges med något av de formulär som visas i följande tabell.

Partitionstyp Syntax Kommentarer
Virtuell kolumn PartitionName: (datetime | string) Läs mer om virtuella kolumner.
Strängkolumnvärde PartitionName:string=Columnname
Hash för strängkolumnvärde PartitionName:long=hash(Columnname,Nummer) Hashen är modulo Number.
Trunkerad datetime-kolumn (värde) PartitionName:datetime= (startofyear | startofdaystartofweekstartofmonth | | ) (ColumnName) Se dokumentationen om startofyear-, startofmonth-, startofweek- eller startofday-funktioner .
Trunkerat datetime-kolumnvärde (lagerplats) PartitionName:datetime=bin(Columnname,Gått) Läs mer om bin-funktionen .

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 partitionssatsen by , 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 skapa det ursprungliga filsökvägsprefixet renderas 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='yyyy'/month='MM
  • year={yyyy}/month={MM}

Som standard renderas 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

Tips

Om du vill kontrollera korrekthet för partitioner och PathFormat-definitioner 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 dataramskrivarens 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 datetime, och 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 Description
folder string Tabellens mapp
docString string Sträng som dokumenterar tabellen
compressed bool Om det anges anger om filerna komprimeras som .gz filer (endast används i exportscenariot )
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 (den första filen i en mapp innehåller ett huvud), None (inga filer innehåller ett huvud).
namePrefix string Om det 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 filnamnstillägg för filerna. 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 är kodad: 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 den externa tabelldefinitionen. Det här alternativet hjälper dig att kontrollera om parametrarna Partitioner och PathFormat har definierats korrekt.
filesPreview bool Om den anges innehåller en av kommandoresultattabellerna en förhandsgranskning av kommandot .show external table artifacts . Precis som sampleUrihjälper alternativet till att verifiera parametrarna Partitioner och PathFormat för den externa tabelldefinitionen.
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 .

Anteckning

Den externa tabellen används inte när den skapas, bara under frågor 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.

Tips

Mer information om rollen namePrefix och fileExtension egenskaperna i datafilfiltrering under frågan finns i avsnittet om filfiltreringslogik .

Filfiltreringslogik

När du kör frågor mot en extern tabell förbättras prestandan genom att filtrera bort irrelevanta externa lagringsfiler. Processen att iterera filer och bestämma om en fil ska bearbetas är följande:

  1. 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 renderas de med PathFormat och läggs sedan till i URI-mönstret.

  2. Kontrollera följande för alla filer som finns under URI-mönstret 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.
    • Blobnamnet slutar med FileExtension, om en sådan egenskap har definierats.

När alla villkor är uppfyllda hämtas och bearbetas filen.

Anteckning

Det initiala 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 externa tabell som inte är partitionerad förväntas filerna placeras direkt under de/de definierade containern:

.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 partitionerad efter datum förväntas filerna placeras under kataloger i standardformatet yyyy/MM/dddatetime :

.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'
)

Partitionerat efter månad

I följande externa tabell partitionerad efter månad är year=yyyy/month=MMkatalogformatet :

.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 customer_name=Softworks/2019/02/01är :

.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=20190201, och datafilnamn slutar .txt med 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")

Exempelutdata

TableName TableType Mapp DocString Egenskaper ConnectionStrings Partitioner PathFormat
ExternalTable Blob 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",Date)

Relaterat innehåll