Externe Azure Storage-tabellen maken en wijzigen
Van toepassing op: ✅Microsoft Fabric✅Azure Data Explorer-
De opdrachten in dit artikel kunnen worden gebruikt om een azure Storage-externe tabel te maken of te wijzigen in de database waaruit de opdracht wordt uitgevoerd. Een externe Azure Storage-tabel verwijst naar gegevens die zich bevinden in Azure Blob Storage, Azure Data Lake Store Gen1 of Azure Data Lake Store Gen2.
Notitie
Als de tabel bestaat, mislukt de opdracht .create met een fout. Gebruik .create-or-alter of .alter om bestaande tabellen te wijzigen.
Machtigingen
Als u .create ten minste machtigingen voor databasegebruiker nodig hebt en .alter ten minste machtigingen voor tabelbeheerder vereist.
Als u een externe tabel wilt .create-or-alter met behulp van verificatie voor beheerde identiteiten, zijn machtigingen voor AllDatabasesAdmin vereist.
Syntaxis
(.create | .alter | .create-or-alter) externaltableTableName(Schema)kind=storage [partitionby(Partitions) [pathformat=(PathFormat)]] dataformat=DataFormat(StorageConnectionString [, ...] ) [with(Eigenschap [, ...])]
Notitie
kind is storage voor alle externe gegevensopslagtypen van Azure Storage.
blob en adl worden afgeschaft.
Meer informatie over syntaxisconventies.
Parameters
| Naam | Type | Vereist | Beschrijving |
|---|---|---|---|
| TableName- | string |
✔️ | Een externe tabelnaam die voldoet aan de namen van de entiteiten regels. Een externe tabel kan niet dezelfde naam hebben als een gewone tabel in dezelfde database. |
| schema | string |
✔️ | Het externe gegevensschema is een door komma's gescheiden lijst met een of meer kolomnamen en gegevenstypen, waarbij elk item de indeling volgt: ColumnName:ColumnType. Als het schema onbekend is, gebruikt u infer_storage_schema om het schema af te leiden op basis van de inhoud van het externe bestand. |
| partities | string |
Een door komma's gescheiden lijst met kolommen waarmee de externe tabel wordt gepartitioneerd. Partitiekolom kan bestaan in het gegevensbestand zelf of als onderdeel van het bestandspad. Zie partities opmaken om te zien hoe deze waarde eruit moet zien. | |
| PathFormat- | string |
Een URI-padindeling voor externe gegevensmappen voor gebruik met partities. Zie padindeling. | |
| DataFormat- | string |
✔️ | De gegevensindeling, die een van de opname-indelingen kan zijn. U wordt aangeraden de Parquet-indeling voor externe tabellen te gebruiken om de prestaties van query's en export te verbeteren, tenzij u JSON padentoewijzing gebruikt. Wanneer u een externe tabel gebruikt voor exportscenario, bent u beperkt tot de volgende indelingen: CSV, TSV, JSON en Parquet. |
| StorageConnectionString- | string |
✔️ | Een of meer door komma's gescheiden paden naar Azure Blob Storage-blobcontainers, Azure Data Lake Gen 2-bestandssystemen of Azure Data Lake Gen 1-containers, inclusief referenties. Het type externe tabelopslag wordt bepaald door de opgegeven verbindingsreeksen. Zie opslagverbindingsreeksen. |
| eigenschap | string |
Een sleutel-waarde-eigenschapspaar in de notatie PropertyName=PropertyValue. Zie optionele eigenschappen. |
Notitie
CSV-bestanden met een niet-identiek schema kunnen ertoe leiden dat gegevens worden verplaatst of ontbreken. U wordt aangeraden CSV-bestanden met afzonderlijke schema's te scheiden om opslagcontainers te scheiden en een externe tabel te definiëren voor elke opslagcontainer met het juiste schema.
Fooi
Geef meer dan één opslagaccount op om opslagbeperking te voorkomen tijdens het exporteren van grote hoeveelheden gegevens naar de externe tabel. Exporteren verdeelt de schrijfbewerkingen tussen alle opgegeven accounts.
Verificatie en autorisatie
De verificatiemethode voor toegang tot een externe tabel is gebaseerd op de verbindingsreeks die is opgegeven tijdens het maken en de vereiste machtigingen voor toegang tot de tabel variëren, afhankelijk van de verificatiemethode.
De volgende tabel bevat de ondersteunde verificatiemethoden voor externe Azure Storage-tabellen en de machtigingen die nodig zijn om naar de tabel te lezen of schrijven.
| Verificatiemethode | Azure Blob Storage/Data Lake Storage Gen2 | Data Lake Storage Gen1 |
|---|---|---|
| imitatie |
leesmachtigingen: Storage Blob-gegevenslezer Schrijfmachtigingen: Inzender voor opslagblobgegevens |
leesmachtigingen : Lezer Schrijfmachtigingen: Inzender |
| beheerde identiteit |
leesmachtigingen: Storage Blob-gegevenslezer Schrijfmachtigingen: Inzender voor opslagblobgegevens |
leesmachtigingen : Lezer Schrijfmachtigingen: Inzender |
| SAS-token (Shared Access) |
Leesmachtigingen: Lijst + Lezen schrijfmachtigingen: Schrijven |
Deze verificatiemethode wordt niet ondersteund in Gen1. |
| Microsoft Entra-toegangstoken | Er zijn geen extra machtigingen vereist. | Er zijn geen extra machtigingen vereist. |
| toegangssleutel voor opslagaccounts | Er zijn geen extra machtigingen vereist. | Deze verificatiemethode wordt niet ondersteund in Gen1. |
Opmaak van partities
De lijst met partities is een combinatie van partitiekolommen, die zijn opgegeven met behulp van een van de formulieren die in de volgende tabel worden weergegeven.
| Partitietype | Syntaxis | Notities |
|---|---|---|
| Virtuele kolom |
PartitionName: (datetime | string) |
Lees meer over virtuele kolommen. |
| Kolomwaarde tekenreeks |
PartitionName:string=ColumnName |
|
| Kolomwaarde tekenreeks hash() |
PartitionName:long=hash(ColumnName,Number) |
De hash is modulo Number. |
| Afgekapte datum/tijd-kolom (waarde) |
PartitionName:datetime= (startofyear | startofmonth | startofweek | startofday) (ColumnName) |
Zie de documentatie over startofyear, startofmonth, startofweekof startofday functies. |
Afgekapte datum/tijd-kolomwaarde =bin(ColumnName,TimeSpan-) |
Lees meer over de functie bin. |
Padindeling
Met de parameter PathFormat kunt u de indeling voor het URI-pad van de externe gegevensmap opgeven naast partities. Het bestaat uit een reeks partitie-elementen en tekstscheidingstekens. Een partitieelement verwijst naar een partitie die is gedeclareerd in de partitie by component en het tekstscheidingsteken is eventuele tekst tussen aanhalingstekens. Opeenvolgende partitie-elementen moeten worden gescheiden met behulp van het tekstscheidingsteken.
[ StringSeparator ] Partition [ StringSeparator ] [Partition [ StringSeparator ] ...]
Als u het oorspronkelijke bestandspadvoorvoegsel wilt maken, worden partitie-elementen weergegeven als tekenreeksen en gescheiden door bijbehorende tekstscheidingstekens. U kunt de datetime_pattern macro (datetime_pattern(DateTimeFormat,PartitionName)) gebruiken om de indeling op te geven die wordt gebruikt voor het weergeven van een datum/tijd-partitiewaarde. De macro voldoet aan de specificatie van de .NET-indeling en staat toe dat opmaakaanduidingen tussen accolades worden geplaatst. De volgende twee indelingen zijn bijvoorbeeld equivalent:
- 'jaar='jjjj'/maand='MM
- jaar={jjjj}/maand={MM}
Datum/tijd-waarden worden standaard weergegeven met behulp van de volgende notaties:
| Partitiefunctie | Standaardindeling |
|---|---|
startofyear |
yyyy |
startofmonth |
yyyy/MM |
startofweek |
yyyy/MM/dd |
startofday |
yyyy/MM/dd |
bin(
kolom, 1d) |
yyyy/MM/dd |
bin(
kolom, 1h) |
yyyy/MM/dd/HH |
bin(
kolom, 1m) |
yyyy/MM/dd/HH/mm |
Fooi
Gebruik de eigenschap sampleUris of filesPreview bij het maken van een externe tabel als u partities en PathFormat wilt controleren.
Virtuele kolommen
Wanneer gegevens worden geëxporteerd uit Spark, worden partitiekolommen (die worden verstrekt aan de partitionBy methode van dataframe writer) niet naar gegevensbestanden geschreven.
Dit proces voorkomt duplicatie van gegevens omdat de gegevens al aanwezig zijn in de mapnamen (bijvoorbeeld column1=<value>/column2=<value>/) en Spark deze kan herkennen wanneer ze worden gelezen.
Externe tabellen ondersteunen het lezen van deze gegevens in de vorm van virtual colums. Virtuele kolommen kunnen van het type string of datetimezijn en worden opgegeven met behulp van de volgende syntaxis:
.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'
)
Als u wilt filteren op virtuele kolommen in een query, geeft u partitienamen op in het querypredicaat:
external_table("ExternalTable")
| where Date between (datetime(2020-01-01) .. datetime(2020-02-01))
| where CustomerName in ("John.Doe", "Ivan.Ivanov")
Optionele eigenschappen
| Eigenschap | Type | Beschrijving |
|---|---|---|
folder |
string |
De map van de tabel |
docString |
string |
Tekenreeks die de tabel documenteert |
compressed |
bool |
Alleen relevant voor het exportscenario. Als deze optie is ingesteld op waar, worden de gegevens geëxporteerd in de indeling die is opgegeven door de eigenschap compressionType. Voor het leespad wordt compressie automatisch gedetecteerd. |
compressionType |
string |
Alleen relevant voor het exportscenario. Het compressietype van geëxporteerde bestanden. Voor niet-Parquet-bestanden is alleen gzip toegestaan. Voor Parquet-bestanden zijn mogelijke waarden gzip, snappy, lz4_raw, brotlien zstd. De standaardwaarde is gzip. Voor het leespad wordt het compressietype automatisch gedetecteerd. |
includeHeaders |
string |
Voor tekstindelingen met scheidingstekens (CSV, TSV, ...), geeft u aan of bestanden een koptekst bevatten. Mogelijke waarden zijn: All (alle bestanden bevatten een koptekst), FirstFile (eerste bestand in een map bevat een koptekst), None (geen bestanden bevatten een koptekst). |
namePrefix |
string |
Als deze optie is ingesteld, geeft u het voorvoegsel van de bestanden op. Bij schrijfbewerkingen worden alle bestanden met dit voorvoegsel geschreven. Bij leesbewerkingen worden alleen bestanden met dit voorvoegsel gelezen. |
fileExtension |
string |
Als deze optie is ingesteld, geeft u de extensie van de bestanden op. Bij schrijven eindigen bestandsnamen met dit achtervoegsel. Bij lezen worden alleen bestanden met deze bestandsextensie gelezen. |
encoding |
string |
Hiermee geeft u op hoe de tekst wordt gecodeerd: UTF8NoBOM (standaard) of UTF8BOM. |
sampleUris |
bool |
Als dit is ingesteld, biedt het opdrachtresultaat verschillende voorbeelden van de gesimuleerde URI voor externe gegevensbestanden zoals ze worden verwacht door de definitie van de externe tabel. Met deze optie kunt u controleren of de parameters Partitions en PathFormat juist zijn gedefinieerd. |
filesPreview |
bool |
Als dit is ingesteld, bevat een van de opdrachtresultaattabellen een voorbeeld van .show external table artifacts command. Net als sampleUrihelpt de optie bij het valideren van de Partities en PathFormat parameters van de definitie van de externe tabel. |
validateNotEmpty |
bool |
Als deze optie is ingesteld, worden de verbindingsreeksen gevalideerd voor het bevatten van inhoud. De opdracht mislukt als de opgegeven URI-locatie niet bestaat of als er onvoldoende machtigingen zijn om er toegang toe te krijgen. |
dryRun |
bool |
Als deze optie is ingesteld, blijft de definitie van de externe tabel niet behouden. Deze optie is handig voor het valideren van de definitie van de externe tabel, met name in combinatie met de parameter filesPreview of sampleUris. |
Notitie
De externe tabel wordt niet geopend tijdens het maken, alleen tijdens het uitvoeren van query's en exporteren. Gebruik de validateNotEmpty optionele eigenschap tijdens het maken om ervoor te zorgen dat de tabeldefinitie geldig is en de opslag toegankelijk is.
Fooi
Zie sectie over logica voor bestandsfilters voor meer informatie over de rol namePrefix en fileExtension eigenschappen van het filteren van gegevensbestanden.
Logica voor bestandsfiltering
Bij het uitvoeren van query's op een externe tabel worden de prestaties verbeterd door irrelevante externe opslagbestanden te filteren. Het proces van het herhalen van bestanden en het bepalen of een bestand moet worden verwerkt, is als volgt:
Bouw een URI-patroon dat een plaats vertegenwoordigt waar bestanden worden gevonden. In eerste instantie is het URI-patroon gelijk aan een verbindingsreeks die is opgegeven als onderdeel van de definitie van de externe tabel. Als er partities zijn gedefinieerd, worden deze weergegeven met behulp van PathFormaten vervolgens toegevoegd aan het URI-patroon.
Controleer of voor alle bestanden die zijn gevonden onder de gemaakte URI-patronen:
- Partitiewaarden komen overeen met predicaten die worden gebruikt in een query.
- De naam van de blob begint met
NamePrefix, als een dergelijke eigenschap is gedefinieerd. - De blobnaam eindigt met
FileExtension, als een dergelijke eigenschap is gedefinieerd.
Zodra aan alle voorwaarden is voldaan, wordt het bestand opgehaald en verwerkt.
Notitie
Het oorspronkelijke URI-patroon wordt gebouwd met behulp van querypredicaatwaarden. Dit werkt het beste voor een beperkte set tekenreekswaarden en voor een gesloten tijdsbereik.
Voorbeelden
Niet-gepartitioneerde externe tabel
In de volgende niet-gepartitioneerde externe tabel worden de bestanden naar verwachting rechtstreeks onder de gedefinieerde container(s) geplaatst:
.create external table ExternalTable (x:long, s:string)
kind=storage
dataformat=csv
(
h@'https://storageaccount.blob.core.windows.net/container1;secretKey'
)
Gepartitioneerd op datum
In de volgende externe tabel die is gepartitioneerd op datum, worden de bestanden naar verwachting geplaatst onder mappen met de standaardnotatie voor datum/tijd 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'
)
Gepartitioneerd op maand
In de volgende externe tabel die per maand is gepartitioneerd, wordt de mapindeling 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'
)
Gepartitioneerd op naam en datum
In de volgende externe tabel worden de gegevens eerst gepartitioneerd op klantnaam en vervolgens op datum, wat betekent dat de verwachte mapstructuur bijvoorbeeld is 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'
)
Gepartitioneerd op hash en datum
De volgende externe tabel wordt eerst gepartitioneerd op klantnaam-hash (modulo tien) en vervolgens op datum. De verwachte mapstructuur is bijvoorbeeld customer_id=5/dt=20190201en de namen van gegevensbestanden eindigen op de .txt-extensie:
.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")
Filteren op partitiekolommen in een query
Als u wilt filteren op partitiekolommen in een query, geeft u de oorspronkelijke kolomnaam op in het querypredicaat:
external_table("ExternalTable")
| where Timestamp between (datetime(2020-01-01) .. datetime(2020-02-01))
| where CustomerName in ("John.Doe", "Ivan.Ivanov")
voorbeelduitvoer
| TableName | TableType | Map | DocString | Eigenschappen | ConnectionStrings | Partities | 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) |