Share via

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 in Azure Blob Storage, Azure Data Lake Store Gen1 oder Azure Data Lake Store Gen2.

Hinweis

Wenn die Tabelle vorhanden ist, schlägt der .create Befehl mit einem Fehler fehl. Verwenden oder .create-or-alter.alter ändern Sie vorhandene Tabellen.

Berechtigungen

Erfordert .create mindestens Datenbankbenutzerberechtigungen und .alter mindestens Tabellen-Admin-Berechtigungen.

Für .create-or-alter eine externe Tabelle mit der Authentifizierung mit verwalteter Identität sind AllDatabasesAdmin-Berechtigungen erforderlich.

Syntax

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

Hinweis

kind gilt storage für alle externen Azure Storage-Datenspeichertypen. blob und adl sind veraltete Begriffe.

Erfahren Sie mehr über Syntaxkonventionen.

Parameter

Name Typ Erforderlich BESCHREIBUNG
TableName string ✔️ Ein externer Tabellenname, der den Entitätsnamensregeln entspricht. Eine externe Tabelle kann nicht den gleichen Namen wie eine reguläre Tabelle in derselben Datenbank haben.
Schema string ✔️ Das externe Datenschema ist eine durch Trennzeichen getrennte Liste mit einem oder mehreren Spaltennamen und Datentypen, wobei jedes Element dem Format ColumnName:ColumnType folgt. 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, durch die die externe Tabelle partitioniert ist. Partitionsspalte kann in der Datendatei selbst oder als Teil des Dateipfads vorhanden sein. Unter Partitionsformatierung erfahren Sie, wie dieser Wert aussehen sollte.
PathFormat string Ein externes Datenordner-URI-Pfadformat, das mit Partitionen verwendet werden soll. Siehe Pfadformat.
DataFormat string ✔️ Das Datenformat, bei dem es sich um ein beliebiges Erfassungsformat sein kann. Es wird empfohlen, das Format für externe Tabellen zu verwenden, um die Parquet 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, TSVund ParquetJSON .
StorageConnectionString string ✔️ Mindestens ein durch Trennzeichen getrennte Pfade zu Azure Blob Storage Blobcontainern, Azure Data Lake Gen2-Dateisystemen oder Azure Data Lake Gen 1-Containern, einschließlich Anmeldeinformationen. Der externe Tabellenspeichertyp wird durch die bereitgestellten Verbindungszeichenfolgen bestimmt. Weitere Informationen finden Sie unter Speicherverbindungszeichenfolgen.
Eigenschaft string Ein Schlüssel-Wert-Eigenschaftspaar im Format PropertyName=PropertyValue. Siehe optionale Eigenschaften.

Hinweis

CSV-Dateien mit nicht identischem Schema 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 eine Speicherdrosselung beim Exportieren großer Datenmengen in die externe Tabelle zu vermeiden. Beim Export werden die Schreibvorgänge auf alle bereitgestellten Konten verteilt.

Authentifizierung und Autorisierung

Die Authentifizierungsmethode für den Zugriff auf eine externe Tabelle basiert auf dem Verbindungszeichenfolge, der bei 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 Berechtigungen aufgeführt, die zum Lesen oder Schreiben in die Tabelle erforderlich sind.

Authentifizierungsmethode Azure Blob Storage/Data Lake Storage Gen2 Data Lake Storage Gen1
Identitätswechsel Leseberechtigungen: Speicherblobdatenleser
Schreibberechtigungen: Mitwirkender für Speicherblobdaten		 Leseberechtigungen: Leser
Schreibberechtigungen: Beitrag
Verwaltete Identität Leseberechtigungen: Speicherblobdatenleser
Schreibberechtigungen: Mitwirkender für Speicherblobdaten		 Leseberechtigungen: Leser
Schreibberechtigungen: Beitrag
SAS-Token (Shared Access) 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.
Speicherkonto-Zugriffsschlüssel Es sind keine zusätzlichen Berechtigungen erforderlich. Diese Authentifizierungsmethode wird in Gen1 nicht unterstützt.

Partitionsformatierung

Die Partitionsliste ist eine beliebige Kombination von Partitionsspalten, die mithilfe eines der in der folgenden Tabelle dargestellten Formulare angegeben wird.

Partitionstyp Syntax Hinweise
Virtuelle Spalte Partitionname: (datetime | string) Erfahren Sie mehr über virtuelle Spalten.
Wert der Zeichenfolgenspalte Partitionname:string=Columnname
Zeichenfolgenspaltenwerthash Partitionname:long=hash(Columnname,Anzahl) Der Hash ist modulo Number.
Abgeschnittene datetime-Spalte (Wert) Partitionname:datetime= (startofyear | startofdaystartofweekstartofmonth | | ) (ColumnName) Weitere Informationen finden Sie in der Dokumentation zu den Funktionen "startofyear", "startofmonth", "startofweek" oder "startofday".
Abgeschnittener Datetime-Spaltenwert (bin) Partitionname:datetime=bin(Columnname,Timespan) Weitere Informationen zur bin-Funktion finden Sie hier.

Pfadformat

Mit dem PathFormat-Parameter können Sie zusätzlich zu Partitionen das Format für den URI-Pfad des externen Datenordners angeben. Es besteht aus einer Sequenz 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 getrennt werden.

[ StringSeparator ] Partition [ StringSeparator ] [Partition [ StringSeparator ] ...]

Zum Erstellen des ursprünglichen Dateipfadpräfixes 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. Beispielsweise sind die folgenden beiden Formate gleichwertig:

  • 'year='jjjj'/month='MM
  • year={JJJJ}/month={MM}

Standardmäßig werden datetime-Werte mit 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 sampleUris oder beim Erstellen einer externen Tabelle, um die Richtigkeit der Partitions- und filesPreviewPathFormat-Definition zu überprüfen.

Virtuelle Spalten

Wenn Daten aus Spark exportiert werden, werden Partitionsspalten (die für die Methode des partitionBy Dataframe writer bereitgestellt werden) nicht in Datendateien geschrieben. Bei diesem Prozess werden Datenduplizierungen vermieden, column1=<value>/column2=<value>/da die Daten bereits in den Ordnernamen vorhanden sind (z. B. ), und Spark sie beim Lesen erkennen kann.

Externe Tabellen unterstützen das Lesen dieser Daten in Form von virtual colums. Virtuelle Spalten können entweder vom Typ string oder datetimesein 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 Textformaten mit Trennzeichen (CSV, TSV, ...) gibt an, ob Dateien einen Header enthalten. Mögliche Werte sind: All (alle Dateien enthalten einen Header), FirstFile (die erste Datei in einem Ordner enthält einen Header), None (keine Dateien enthalten einen Header).
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 dateierweiterungen der Dateien an. Beim Schreiben enden 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 simulierte externe Datendateien-URI, wie sie von der definition der externen Tabelle erwartet werden. Mit dieser Option wird überprüft, ob die Parameter Partitions und PathFormat ordnungsgemäß definiert sind.
filesPreview bool Wenn festgelegt, enthält eine der Befehlsergebnistabellen eine Vorschau des Befehls ".show external table artifacts ". Wie sampleUrihilft die Option, die Partitions- und PathFormat-Parameter der externen Tabellendefinition zu überprüfen.
validateNotEmpty bool Wenn festgelegt, werden die Verbindungszeichenfolgen auf Inhalt überprüft. Der Befehl schlägt fehl, wenn der angegebene URI-Speicherort nicht vorhanden ist oder nicht genügend Berechtigungen für den Zugriff vorhanden sind.
dryRun bool Wenn festgelegt, wird die definition der externen Tabelle nicht beibehalten. Diese Option ist nützlich, um die externe Tabellendefinition zu überprüfen, insbesondere in Verbindung mit dem filesPreview Parameter oder sampleUris .

Hinweis

Auf die externe Tabelle wird während der Erstellung nicht zugegriffen, nur während der Abfrage und beim Export. Verwenden Sie die validateNotEmpty optionale Eigenschaft während der Erstellung, um sicherzustellen, dass die Tabellendefinition gültig ist und auf den Speicher zugegriffen werden kann.

Tipp

Weitere Informationen zur Rolle namePrefix und fileExtension den Eigenschaften, die bei der Datendateifilterung während der Abfrage spielen, finden Sie im Abschnitt Dateifilterlogik .

Logik der Dateifilterung

Beim Abfragen einer externen Tabelle wird die Leistung verbessert, indem irrelevante externe Speicherdateien herausfiltert. Der Prozess des Durchlaufens von Dateien und der Entscheidung, ob eine Datei verarbeitet werden soll, sieht wie folgt aus:

  1. Erstellen Sie ein URI-Muster, das einen Ort darstellt, an dem Dateien gefunden werden. Zunächst entspricht das URI-Muster einem Verbindungszeichenfolge, der als Teil der externen Tabellendefinition bereitgestellt wird. Wenn Partitionen definiert sind, werden sie mit 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:

    • 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 funktioniert 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 wird erwartet, dass die Dateien direkt unter den definierten Containern platziert werden:

.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 Verzeichnisse des standardformats yyyy/MM/dddatetime platziert:

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

.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 ist z. B. customer_id=5/dt=20190201, und die Namen der Datendateien 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 Abfrageprdikat 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("yyyMMdd",Date)

Verwandte Inhalte