Erstellen und Ändern externer Azure Storage-Tabellen
Gilt für: ✅Microsoft Fabric✅Azure Data Explorer
Die Befehle in diesem Artikel können verwendet werden, um eine externe Azure Storage-Tabelle in der Datenbank zu erstellen oder zu ändern, aus der der Befehl ausgeführt wird. Eine externe Azure Storage-Tabelle verweist auf Daten, die sich in Azure Blob Storage, Azure Data Lake Store Gen1 oder Azure Data Lake Store Gen2 befinden.
Hinweis
Wenn die Tabelle vorhanden ist, schlägt der .create Befehl mit einem Fehler fehl. Verwenden oder .alter ändern Sie .create-or-alter vorhandene Tabellen.
Berechtigungen
Damit .create mindestens Berechtigungen für Datenbankbenutzer erforderlich sind und .alter mindestens Tabellenadministratorberechtigungen erforderlich sind.
Für .create-or-alter eine externe Tabelle mit verwalteter Identitätsauthentifizierung sind AllDatabasesAdmin-Berechtigungen erforderlich.
Syntax
(.create | | .alter.create-or-alter) external table TableName-Schema kindstorage (=) [ Partitionen) [ (by =partitionpathformat (PathFormat)]] = dataformat DataFormat (StorageConnectionString [, ...] ) [ Eigenschaft [,with (...]])
Hinweis
kind ist storage für alle externen Azure Storage-Datenspeichertypen vorgesehen. blob und adl sind veraltete Begriffe.
Erfahren Sie mehr über Syntaxkonventionen.
Parameter
| Name | Type | Erforderlich | Beschreibung |
|---|---|---|---|
| TableName | string |
✔️ | Ein externer Tabellenname, der den Entitätsnamenregeln entspricht. Eine externe Tabelle kann nicht denselben Namen wie eine reguläre Tabelle in derselben Datenbank haben. |
| Schema | string |
✔️ | Das Schema für externe Daten ist eine durch Trennzeichen getrennte Liste mit einem oder mehreren Spaltennamen und Datentypen, wobei jedes Element dem Format folgt: ColumnName : ColumnType. Wenn das Schema unbekannt ist, verwenden Sie infer_storage_schema , um das Schema basierend auf externen Dateiinhalten abzuleiten. |
| Partitionen | string |
Eine durch Trennzeichen getrennte Liste von Spalten, nach denen die externe Tabelle partitioniert wird. Partitionsspalte kann in der Datendatei selbst oder als Teil des Dateipfads vorhanden sein. Sehen Sie sich die Partitionsformatierung an, um zu erfahren, wie dieser Wert aussehen sollte. | |
| PathFormat | string |
Ein URI-Pfadformat für externe Datenordner, das mit Partitionen verwendet werden soll. Siehe Pfadformat. | |
| DataFormat | string |
✔️ | Das Datenformat, das eines der Aufnahmeformate sein kann. Es wird empfohlen, das Parquet Format für externe Tabellen zu verwenden, um die Abfrage- und Exportleistung zu verbessern, es sei denn, Sie verwenden JSON die Pfadzuordnung. Wenn Sie eine externe Tabelle für das Exportszenario verwenden, sind Sie auf die folgenden Formate beschränkt: CSV, , TSVJSON und Parquet. |
| StorageConnectionString | string |
✔️ | Mindestens ein durch Trennzeichen getrennte Pfade zu Azure Blob Storage-BLOB-Containern, Azure Data Lake Gen 2-Dateisystemen oder Azure Data Lake Gen 1-Containern, einschließlich Anmeldeinformationen. Der Speichertyp der externen Tabelle wird durch die bereitgestellten Verbindungszeichenfolge bestimmt. Siehe Speicher Verbindungszeichenfolge s. |
| Eigenschaft | string |
Ein Schlüssel-Wert-Eigenschaftspaar im Format PropertyName = PropertyValue. Weitere Informationen finden Sie unter optionalen Eigenschaften. |
Hinweis
CSV-Dateien mit nicht identischen Schemas können dazu führen, dass Daten verschoben oder fehlen. Es wird empfohlen, CSV-Dateien mit unterschiedlichen Schemas zu trennen, um Speichercontainer zu trennen und eine externe Tabelle für jeden Speichercontainer mit dem richtigen Schema zu definieren.
Tipp
Stellen Sie mehr als ein einzelnes Speicherkonto bereit, um die Speicherdrosselung beim Exportieren großer Datenmengen in die externe Tabelle zu vermeiden. Beim Export werden die Schreibvorgänge zwischen allen bereitgestellten Konten verteilt.
Authentifizierung und Autorisierung
Die Authentifizierungsmethode für den Zugriff auf eine externe Tabelle basiert auf der Verbindungszeichenfolge, die während der Erstellung bereitgestellt wurde, und die berechtigungen, die für den Zugriff auf die Tabelle erforderlich sind, variieren je nach Authentifizierungsmethode.
In der folgenden Tabelle sind die unterstützten Authentifizierungsmethoden für externe Azure Storage-Tabellen und die Zum Lesen oder Schreiben in die Tabelle erforderlichen Berechtigungen aufgeführt.
| Authentifizierungsmethode | Azure Blob Storage / Data Lake Storage Gen2 | Data Lake Storage Gen1 |
|---|---|---|
| Identitätswechsel | Leseberechtigungen: Storage Blob Data Reader Schreibberechtigungen: Storage Blob Data Contributor |
Leseberechtigungen: Leser Schreibberechtigungen: Mitwirkender |
| Verwaltete Identität | Leseberechtigungen: Storage Blob Data Reader Schreibberechtigungen: Storage Blob Data Contributor |
Leseberechtigungen: Leser Schreibberechtigungen: Mitwirkender |
| Token für gemeinsamen Zugriff (SAS) | Leseberechtigungen: Liste + Lesen Schreibberechtigungen: Schreiben |
Diese Authentifizierungsmethode wird in Gen1 nicht unterstützt. |
| Microsoft Entra-Zugriffstoken | Es sind keine zusätzlichen Berechtigungen erforderlich. | Es sind keine zusätzlichen Berechtigungen erforderlich. |
| Zugriffsschlüssel für das Speicherkonto | Es sind keine zusätzlichen Berechtigungen erforderlich. | Diese Authentifizierungsmethode wird in Gen1 nicht unterstützt. |
Partitionsformatierung
Die Partitionsliste ist eine beliebige Kombination aus Partitionsspalten, die mithilfe eines der in der folgenden Tabelle gezeigten Formulare angegeben wird.
| Partitionstyp | Syntax | Hinweise |
|---|---|---|
| Virtuelle Spalte | PartitionName : (datetime | string) |
Weitere Informationen zu virtuellen Spalten. |
| Zeichenfolgenspaltenwert | PartitionName ColumnName : string = |
|
| Zeichenfolgenspaltenwerthash () | PartitionName ColumnName=: long hash(, Number) |
Der Hash ist modulo Number. |
| Abgeschnittene Datetime-Spalte (Wert) | PartitionName (startofyear | startofmonthstartofday | | startofweek) ( ColumnName : datetime = ) |
Weitere Informationen finden Sie in der Dokumentation zu "startofyear", "startofmonth", "startofweek" oder "startofday".See documentation on startofyear, startofmonth, startofweek, or startofday functions. |
Abgeschnittener Datetime Column Value = ( bin ColumnName , TimeSpan ) |
Weitere Informationen zur Bin-Funktion . |
Pfadformat
Mit dem PathFormat-Parameter können Sie zusätzlich zu Partitionen das Format für den URI-Pfad des externen Datenordners angeben. Sie besteht aus einer Abfolge von Partitionselementen und Texttrennzeichen. Ein Partitionselement bezieht sich auf eine Partition, die in der Partitionsklausel by deklariert ist, und das Texttrennzeichen ist ein beliebiger Text, der in Anführungszeichen eingeschlossen ist. Aufeinanderfolgende Partitionselemente müssen mithilfe des Texttrennzeichens voneinander getrennt werden.
[ StringSeparator ] Partition [ StringSeparator ] [Partition [ StringSeparator ] ...]
Um das ursprüngliche Dateipfadpräfix zu erstellen, werden Partitionselemente als Zeichenfolgen gerendert und durch entsprechende Texttrennzeichen getrennt. Sie können das datetime_pattern Makro (datetime_pattern(DateTimeFormat, PartitionName)) verwenden, um das Format anzugeben, das zum Rendern eines Datetime-Partitionswerts verwendet wird. Das Makro entspricht der .NET-Formatspezifikation und ermöglicht das Einschließen von Formatbezeichnern in geschweifte Klammern. Die folgenden beiden Formate sind z. B. gleichwertig:
- 'year='yyyy'/month='MM
- year={yyyy}/month={MM}
Datetime-Werte werden standardmäßig in den folgenden Formaten gerendert:
| Partitionsfunktion | Standardformat |
|---|---|
startofyear |
yyyy |
startofmonth |
yyyy/MM |
startofweek |
yyyy/MM/dd |
startofday |
yyyy/MM/dd |
bin(Spalte, 1d) |
yyyy/MM/dd |
bin(Spalte, 1h) |
yyyy/MM/dd/HH |
bin(Spalte, 1m) |
yyyy/MM/dd/HH/mm |
Tipp
Verwenden Sie die Eigenschaft oder das Erstellen einer externen Tabelle, um partitions- und PathFormat-Definition zu überprüfen.filesPreview sampleUris
Virtuelle Spalten
Wenn Daten aus Spark exportiert werden, werden Partitionsspalten (die der Methode des partitionBy Dataframe Writer bereitgestellt werden) nicht in Datendateien geschrieben.
Bei diesem Vorgang wird eine Datenduplizierung vermieden, da die Daten bereits in den Ordnernamen vorhanden sind (z. B column1=<value>/column2=<value>/. ), und Spark kann sie beim Lesen erkennen.
Externe Tabellen unterstützen das Lesen dieser Daten in Form von virtual colums. Virtuelle Spalten können vom Typ oder datetimevom Typ string sein und werden mit der folgenden Syntax angegeben:
.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'
)
Um nach virtuellen Spalten in einer Abfrage zu filtern, geben Sie Partitionsnamen im Abfrage-Prädikat an:
external_table("ExternalTable")
| where Date between (datetime(2020-01-01) .. datetime(2020-02-01))
| where CustomerName in ("John.Doe", "Ivan.Ivanov")
Optionale Eigenschaften:
| Eigenschaft | Typ | Beschreibung |
|---|---|---|
folder |
string |
Ordner der Tabelle |
docString |
string |
Zeichenfolge zur Dokumentierung der Tabelle |
compressed |
bool |
Wenn festgelegt, gibt an, ob die Dateien als .gz Dateien komprimiert werden (nur im Exportszenario verwendet) |
includeHeaders |
string |
Bei durch Trennzeichen getrennten Textformaten (CSV, TSV, ...), gibt an, ob Dateien eine Kopfzeile enthalten. Mögliche Werte sind: All (alle Dateien enthalten eine Kopfzeile), FirstFile (erste Datei in einem Ordner enthält eine Kopfzeile), None (keine Dateien enthalten eine Kopfzeile). |
namePrefix |
string |
Wenn festgelegt, gibt das Präfix der Dateien an. Bei Schreibvorgängen werden alle Dateien mit diesem Präfix geschrieben. Bei Lesevorgängen werden nur Dateien mit diesem Präfix gelesen. |
fileExtension |
string |
Wenn festgelegt, gibt die Dateierweiterungen der Dateien an. Beim Schreiben enden die Dateinamen mit diesem Suffix. Beim Lesen werden nur Dateien mit dieser Dateierweiterung gelesen. |
encoding |
string |
Gibt an, wie der Text codiert ist: UTF8NoBOM (Standard) oder UTF8BOM. |
sampleUris |
bool |
Wenn festgelegt, enthält das Befehlsergebnis mehrere Beispiele für simulierten URI für externe Datendateien, wie sie von der definition der externen Tabelle erwartet werden. Mit dieser Option können Sie überprüfen, ob die Parameter Partitionen und PathFormat ordnungsgemäß definiert sind. |
filesPreview |
bool |
Wenn festgelegt, enthält eine der Befehlsergebnistabellen eine Vorschau des Befehls ".show external table artifacts ". Die Option hilft, sampleUridie Parameter "Partitionen " und "PathFormat " der definition der externen Tabelle zu überprüfen. |
validateNotEmpty |
bool |
Wenn festgelegt, werden die Verbindungszeichenfolge überprüft, um Inhalte darin zu haben. Der Befehl schlägt fehl, wenn der angegebene URI-Speicherort nicht vorhanden ist oder nicht genügend Berechtigungen für den Zugriff darauf vorhanden sind. |
dryRun |
bool |
Wenn dieser Wert festgelegt ist, wird die Definition der externen Tabelle nicht beibehalten. Diese Option ist nützlich zum Überprüfen der externen Tabellendefinition, insbesondere in Verbindung mit dem filesPreview Parameter.sampleUris |
Hinweis
Auf die externe Tabelle wird während der Erstellung nicht zugegriffen, nur während der Abfrage und beim Export. Verwenden Sie die optionale Eigenschaft während der validateNotEmpty Erstellung, um sicherzustellen, dass die Tabellendefinition gültig ist und auf den Speicher zugegriffen werden kann.
Tipp
Weitere Informationen zur Rolle namePrefix und fileExtension Eigenschaften der Datendateifilterung während der Abfrage finden Sie im Abschnitt zur Dateifilterlogik .
Dateifilterlogik
Beim Abfragen einer externen Tabelle wird die Leistung verbessert, indem irrelevante externe Speicherdateien herausfiltert werden. Der Prozess des Durchlaufens von Dateien und die Entscheidung, ob eine Datei verarbeitet werden soll, lautet wie folgt:
Erstellen Sie ein URI-Muster, das eine Stelle darstellt, an der Dateien gefunden werden. Zunächst entspricht das URI-Muster einer Verbindungszeichenfolge, die als Teil der externen Tabellendefinition bereitgestellt wird. Wenn Partitionen definiert sind, werden sie mithilfe von PathFormat gerendert und dann an das URI-Muster angefügt.
Überprüfen Sie für alle Dateien, die unter den erstellten URI-Mustern gefunden wurden, folgendes:
- Partitionswerte entsprechen Prädikaten, die in einer Abfrage verwendet werden.
- Blobname beginnt mit
NamePrefix, wenn eine solche Eigenschaft definiert ist. - Blobname endet mit
FileExtension, wenn eine solche Eigenschaft definiert ist.
Sobald alle Bedingungen erfüllt sind, wird die Datei abgerufen und verarbeitet.
Hinweis
Das anfängliche URI-Muster wird mithilfe von Abfrage-Prädikatwerten erstellt. Dies eignet sich am besten für einen begrenzten Satz von Zeichenfolgenwerten sowie für geschlossene Zeitbereiche.
Beispiele
Nicht partitionierte externe Tabelle
In der folgenden nicht partitionierten externen Tabelle werden die Dateien voraussichtlich direkt unter den definierten Containern platziert:
.create external table ExternalTable (x:long, s:string)
kind=storage
dataformat=csv
(
h@'https://storageaccount.blob.core.windows.net/container1;secretKey'
)
Partitioniert nach Datum
In der folgenden externen Tabelle, die nach Datum partitioniert ist, werden die Dateien voraussichtlich unter Verzeichnissen des Standarddatumsformats yyyy/MM/ddplatziert:
.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'
)
Partitioniert nach Monat
In der folgenden externen Tabelle, die nach Monat partitioniert ist, lautet year=yyyy/month=MMdas Verzeichnisformat:
.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'
)
Partitioniert nach Name und Datum
In der folgenden externen Tabelle werden die Daten zuerst nach Kundenname und dann nach Datum partitioniert, was bedeutet, dass die erwartete Verzeichnisstruktur z. B. lautet: 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'
)
Partitioniert nach Hash und Datum
Die folgende externe Tabelle wird zuerst nach Kundennamenhash (Modulo ten) und dann nach Datum partitioniert. Die erwartete Verzeichnisstruktur endet beispielsweise mit der Erweiterung, customer_id=5/dt=20190201und die Datendateinamen enden mit der .txt Erweiterung:
.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")
Filtern nach Partitionsspalten in einer Abfrage
Um nach Partitionsspalten in einer Abfrage zu filtern, geben Sie den ursprünglichen Spaltennamen im Abfrage-Prädikat an:
external_table("ExternalTable")
| where Timestamp between (datetime(2020-01-01) .. datetime(2020-02-01))
| where CustomerName in ("John.Doe", "Ivan.Ivanov")
Beispielausgabe
| TableName | TableType | Ordner | DocString | Eigenschaften | ConnectionStrings | Partitionen | 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) |