Condividi tramite

Creare e modificare tabelle esterne di Archiviazione di Azure

  • Articolo

I comandi in questo articolo possono essere usati per creare o modificare una tabella esterna Archiviazione di Azure nel database da cui viene eseguito il comando. Una tabella esterna Archiviazione di Azure fa riferimento ai dati presenti in Archiviazione BLOB di Azure, Azure Data Lake Store Gen1 o Azure Data Lake Store Gen2.

Nota

Se la tabella esiste, il .create comando avrà esito negativo con un errore. Utilizzare .create-or-alter o .alter per modificare le tabelle esistenti.

Autorizzazioni

Per .create richiedere almeno le autorizzazioni utente del database e per .alter richiedere almeno le autorizzazioni di amministratore tabella .

Per .create-or-alter una tabella esterna tramite l'autenticazione dell'identità gestita sono necessarie autorizzazioni AllDatabasesAdmin .

Sintassi

(.create | | .alter.create-or-alter) external table Schema storage =) kindTableName( [partition by(Partitions) [pathformat(= PathFormat)]] dataformat = DataFormat (StorageConnectionString [, ...] ) [with (Proprietà [, ...])]

Nota

kindè storage per tutti i tipi di archivio dati esterni Archiviazione di Azure. blob e adl sono termini deprecati.

Altre informazioni sulle convenzioni di sintassi.

Parametri

Nome Digita Obbligatorio Descrizione
TableName string ✔️ Nome di tabella esterno conforme alle regole dei nomi di entità. Una tabella esterna non può avere lo stesso nome di una tabella normale nello stesso database.
Schema string ✔️ Lo schema dei dati esterni è un elenco delimitato da virgole di uno o più nomi di colonne e tipi di dati, in cui ogni elemento segue il formato ColumnName : ColumnType. Se lo schema è sconosciuto, usare infer_storage_schema per dedurre lo schema in base al contenuto del file esterno.
Partitions string Elenco delimitato da virgole di colonne in base al quale la tabella esterna è partizionata. La colonna di partizione può esistere nel file di dati stesso o come parte del percorso del file. Per informazioni sull'aspetto di questo valore, vedere formattazione delle partizioni.
PathFormat string Formato del percorso URI della cartella dati esterna da usare con le partizioni. Vedere il formato del percorso.
DataFormat string ✔️ Formato di dati, che può essere uno qualsiasi dei formati di inserimento. È consigliabile usare il Parquet formato per le tabelle esterne per migliorare le prestazioni di query ed esportazione, a meno che non si usi JSON il mapping dei percorsi. Quando si usa una tabella esterna per lo scenario di esportazione, sono limitati ai formati seguenti: CSV, JSON TSVe Parquet.
StorageConnectionString string ✔️ Uno o più percorsi delimitati da virgole per Archiviazione BLOB di Azure contenitori BLOB, file system di Azure Data Lake Gen 2 o contenitori di Azure Data Lake Gen 1, incluse le credenziali. Il tipo di archiviazione tabelle esterne è determinato dalle stringa di connessione fornite. Vedere stringa di connessione di archiviazione.
Proprietà string Coppia di proprietà chiave-valore nel formato PropertyName = PropertyValue. Vedere proprietà facoltative.

Nota

I file CSV con schema non identico potrebbero comportare la visualizzazione o la mancanza di dati. È consigliabile separare i file CSV con schemi distinti per separare i contenitori di archiviazione e definire una tabella esterna per ogni contenitore di archiviazione con lo schema appropriato.

Suggerimento

Fornire più di un singolo account di archiviazione per evitare la limitazione delle risorse di archiviazione durante l'esportazione di grandi quantità di dati nella tabella esterna. L'esportazione distribuirà le scritture tra tutti gli account forniti.

Autenticazione e autorizzazione

Il metodo di autenticazione per accedere a una tabella esterna si basa sul stringa di connessione fornito durante la creazione e le autorizzazioni necessarie per accedere alla tabella variano a seconda del metodo di autenticazione.

Nella tabella seguente sono elencati i metodi di autenticazione supportati per Archiviazione di Azure tabelle esterne e le autorizzazioni necessarie per leggere o scrivere nella tabella.

Authentication method Archiviazione BLOB di Azure/Data Lake Storage Gen2 Data Lake Storage Gen1
Rappresentazione Autorizzazioni di lettura: Lettore di dati BLOB di archiviazione
Autorizzazioni di scrittura: Collaboratore ai dati dei BLOB di archiviazione		 Autorizzazioni di lettura: Lettore
Autorizzazioni di scrittura: Collaboratore
Identità gestita Autorizzazioni di lettura: Lettore di dati BLOB di archiviazione
Autorizzazioni di scrittura: Collaboratore ai dati dei BLOB di archiviazione		 Autorizzazioni di lettura: Lettore
Autorizzazioni di scrittura: Collaboratore
Token di accesso condiviso Autorizzazioni di lettura: Elenco + Lettura
Autorizzazioni di scrittura: Scrittura		 Questo metodo di autenticazione non è supportato in Gen1.
Token di accesso di Microsoft Entra Non sono necessarie autorizzazioni aggiuntive. Non sono necessarie autorizzazioni aggiuntive.
Chiave di accesso dell'account di archiviazione Non sono necessarie autorizzazioni aggiuntive. Questo metodo di autenticazione non è supportato in Gen1.

Formattazione delle partizioni

L'elenco delle partizioni è qualsiasi combinazione di colonne di partizione, specificate utilizzando uno dei moduli illustrati nella tabella seguente.

Tipo di partizione Sintassi Note
Colonna virtuale PartitionName : (datetime | string) Altre informazioni sulle colonne virtuali.
Valore colonna stringa ColumnName PartitionName : string =
String column value hash() PartitionName =long hash(: ColumnName Number, ) L'hash è il numero modulo.
Colonna datetime troncata (valore) PartitionName (startofyear | startofmonthstartofday | | startofweek) ( ColumnName : datetime = ) Vedere la documentazione sulle funzioni startofyear, startofmonth, startofweek o startofday .
Colonna datetime troncata (valore) PartitionName ColumnName , = bin datetime : ( TimeSpan ) Altre informazioni sulla funzione bin .

Formato percorso

Il parametro PathFormat consente di specificare il formato per il percorso URI della cartella dati esterna oltre alle partizioni. È costituito da una sequenza di elementi di partizione e separatori di testo. Un elemento di partizione fa riferimento a una partizione dichiarata nella clausola di partizione by e il separatore di testo è qualsiasi testo racchiuso tra virgolette. Gli elementi di partizione consecutivi devono essere impostati separatamente usando il separatore di testo.

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

Per costruire il prefisso del percorso del file originale, gli elementi della partizione vengono visualizzati come stringhe e separati con i separatori di testo corrispondenti. È possibile utilizzare la datetime_pattern macro (datetime_pattern(DateTimeFormat, PartitionName)) per specificare il formato usato per il rendering di un valore di partizione datetime. La macro è conforme alla specifica di formato .NET e consente agli identificatori di formato di essere racchiusi tra parentesi graffe. Ad esempio, i due formati seguenti sono equivalenti:

  • 'year='yyyy'/month='MM
  • year={aaaa}/month={MM}

Per impostazione predefinita, il rendering dei valori datetime viene eseguito usando i formati seguenti:

Funzione di partizione Formato predefinito
startofyear yyyy
startofmonth yyyy/MM
startofweek yyyy/MM/dd
startofday yyyy/MM/dd
bin(Istogramma, 1d) yyyy/MM/dd
bin(Istogramma, 1h) yyyy/MM/dd/HH
bin(Istogramma, 1m) yyyy/MM/dd/HH/mm

Suggerimento

Per controllare la correttezza delle definizioni Partitions e PathFormat , utilizzare la proprietà sampleUris o filesPreview durante la creazione di una tabella esterna.

Colonne virtuali

Quando i dati vengono esportati da Spark, le colonne di partizione (fornite al metodo del writer di partitionBy dataframe) non vengono scritte nei file di dati. Questo processo evita la duplicazione dei dati perché i dati sono già presenti nei nomi delle cartelle (ad esempio , column1=<value>/column2=<value>/) e Spark possono riconoscerli al momento della lettura.

Le tabelle esterne supportano la lettura di questi dati sotto forma di virtual colums. Le colonne virtuali possono essere di tipo string o datetimee vengono specificate usando la sintassi seguente:

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

Per filtrare in base alle colonne virtuali in una query, specificare i nomi di partizione nel predicato di query:

external_table("ExternalTable")
 | where Date between (datetime(2020-01-01) .. datetime(2020-02-01))
 | where CustomerName in ("John.Doe", "Ivan.Ivanov")

Proprietà facoltative

Proprietà Type Descrizione
folder string Cartella della tabella
docString string Stringa che documenta la tabella
compressed bool Se impostato, indica se i file vengono compressi come .gz file (usati solo nello scenario di esportazione)
includeHeaders string Per i formati di testo delimitati (CSV, TSV, ...), indica se i file contengono un'intestazione. I valori possibili sono: All (tutti i file contengono un'intestazione), FirstFile (il primo file in una cartella contiene un'intestazione), None (nessun file contiene un'intestazione).
namePrefix string Se impostato, indica il prefisso dei file. In caso di operazioni di scrittura, tutti i file verranno scritti con questo prefisso. Nelle operazioni di lettura vengono letti solo i file con questo prefisso.
fileExtension string Se impostato, indica le estensioni di file dei file. In scrittura, i nomi dei file terminano con questo suffisso. In lettura, verranno letti solo i file con questa estensione di file.
encoding string Indica la modalità di codifica del testo: UTF8NoBOM (impostazione predefinita) o UTF8BOM.
sampleUris bool Se impostato, il risultato del comando fornisce diversi esempi di URI di file di dati esterni simulati, come previsto dalla definizione della tabella esterna. Questa opzione consente di verificare se i parametri Partitions e PathFormat sono definiti correttamente.
filesPreview bool Se impostata, una delle tabelle dei risultati dei comandi contiene un'anteprima del comando .show external table artifacts . Analogamente sampleUria , l'opzione consente di convalidare i parametri Partitions e PathFormat della definizione di tabella esterna.
validateNotEmpty bool Se impostato, i stringa di connessione vengono convalidati per la presenza di contenuto in essi contenuti. Il comando avrà esito negativo se il percorso URI specificato non esiste o se non sono presenti autorizzazioni sufficienti per accedervi.
dryRun bool Se impostata, la definizione della tabella esterna non viene salvata in modo permanente. Questa opzione è utile per convalidare la definizione della tabella esterna, in particolare in combinazione con il filesPreview parametro o sampleUris .

Nota

La tabella esterna non è accessibile durante la creazione, solo durante la query e l'esportazione. Usare la validateNotEmpty proprietà facoltativa durante la creazione per assicurarsi che la definizione della tabella sia valida e che lo spazio di archiviazione sia accessibile.

Suggerimento

Per altre informazioni sul ruolo namePrefix e fileExtension sulle proprietà del filtro dei file di dati durante la query, vedere la sezione Logica di filtro dei file.

Logica di filtro dei file

Quando si esegue una query su una tabella esterna, le prestazioni vengono migliorate filtrando i file di archiviazione esterni irrilevanti. Il processo di iterazione dei file e decidere se un file deve essere elaborato è il seguente:

  1. Creare un modello URI che rappresenta una posizione in cui vengono trovati i file. Inizialmente, il modello URI è uguale a un stringa di connessione fornito come parte della definizione della tabella esterna. Se sono presenti partizioni definite, viene eseguito il rendering usando PathFormat, quindi aggiunto al modello URI.

  2. Per tutti i file presenti nei modelli URI creati, verificare che:

    • I valori di partizione corrispondono ai predicati usati in una query.
    • Il nome del BLOB inizia con NamePrefix, se tale proprietà è definita.
    • Il nome del BLOB termina con FileExtension, se tale proprietà è definita.

Una volta soddisfatte tutte le condizioni, il file viene recuperato ed elaborato.

Nota

Il modello URI iniziale viene compilato usando i valori del predicato di query. Questa soluzione è ideale per un set limitato di valori stringa e per intervalli di tempo chiusi.

Esempi

Tabella esterna non partizionata

Nella tabella esterna non partizionata seguente i file devono essere inseriti direttamente nei contenitori definiti:

.create external table ExternalTable (x:long, s:string)  
kind=storage 
dataformat=csv 
( 
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey' 
)

Partizionato per data

Nella tabella esterna seguente partizionata per data, i file devono essere inseriti nelle directory del formato yyyy/MM/dddatetime predefinito :

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

Partizionato per mese

Nella tabella esterna seguente partizionata per mese, il formato della directory è 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' 
)

Partizionato per nome e data

Nella tabella esterna seguente i dati vengono partizionati prima in base al nome del cliente e quindi alla data, ovvero la struttura di directory prevista è, ad esempio: 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' 
)

Partizionato in base all'hash e alla data

La tabella esterna seguente viene partizionata prima in base all'hash del nome cliente (modulo ten), quindi alla data. La struttura di directory prevista è, ad esempio, customer_id=5/dt=20190201e i nomi dei file di dati terminano con l'estensione .txt :

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

Filtrare in base alle colonne di partizione in una query

Per filtrare in base alle colonne di partizione in una query, specificare il nome della colonna originale nel predicato di query:

external_table("ExternalTable")
 | where Timestamp between (datetime(2020-01-01) .. datetime(2020-02-01))
 | where CustomerName in ("John.Doe", "Ivan.Ivanov")

Output di esempio

TableName TableType Cartella DocString Proprietà ConnectionStrings Partizioni PathFormat
ExternalTable BLOB ExternalTables Documenti {"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)

Contenuto correlato