Freigeben über

Erstellen und Ändern externer Azure Storage-Tabellen

  • Artikel

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) externaltableTableName-Schemakindstorage(=) [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=:longhash(,Number) Der Hash ist modulo Number.
Abgeschnittene Datetime-Spalte (Wert) PartitionName (startofyearstartofmonthstartofdaystartofweek | | | ) (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 =(binColumnName,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.filesPreviewsampleUris

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:

  1. 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.

  2. Ü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)

Zugehöriger Inhalt