Creare e modificare tabelle esterne di Archiviazione di Azure

Si applica a: ✅Microsoft Fabric✅Azure Esplora dati

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.

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à [, ...])]

Altre informazioni sulle convenzioni di sintassi.

Parametri

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.

Formattazione delle partizioni

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

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:

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

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.

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

Contenuto correlato

• Esportare i dati in una tabella esterna.

• Esportazione continua dei dati in una tabella esterna.