Creare e modificare tabelle esterne in Archiviazione di Azure o Azure Data LakeCreate and alter external tables in Azure Storage or Azure Data Lake

Il comando seguente illustra come creare una tabella esterna che si trova nell'archivio BLOB di Azure, Azure Data Lake Store Gen1 o Azure Data Lake Store Gen2.The following command describes how to create an external table located in Azure Blob Storage, Azure Data Lake Store Gen1, or Azure Data Lake Store Gen2.

Per un'introduzione alla funzionalità tabelle di archiviazione di Azure esterna, vedere eseguire query sui dati in Azure Data Lake usando Azure Esplora dati.For an introduction to the external Azure Storage tables feature, see Query data in Azure Data Lake using Azure Data Explorer.

. Create o. Alter External Table.create or .alter external table

SintassiSyntax

( .create | .alter | .create-or-alter ) external table TableName ( Schema TableName)(.create | .alter | .create-or-alter) external table TableName ( Schema )
kind = (blob | adl)kind = (blob | adl)
[ partition by ( Partizioni ) [ pathformat = ( PathFormat ) ]][partition by ( Partitions ) [pathformat = ( PathFormat )]]
dataformat``= * Formato*dataformat = Format
(StorageConnectionString [ , ...]) ( StorageConnectionString [, ...] )
[ with ( PropertyName = valore , ... ) ][with (PropertyName = Value , ... )]

Crea o modifica una nuova tabella esterna nel database in cui viene eseguito il comando.Creates or alters a new external table in the database in which the command is executed.

Nota

  • Se la tabella esiste, il .create comando avrà esito negativo con un errore.If the table exists, .create command will fail with an error. Utilizzare .create-or-alter o .alter per modificare le tabelle esistenti.Use .create-or-alter or .alter to modify existing tables.
  • La modifica dello schema, del formato o della definizione della partizione di una tabella BLOB esterna non è supportata.Altering the schema, format, or the partition definition of an external blob table isn't supported.
  • L'operazione richiede l' autorizzazione dell'utente di database per .create e l'autorizzazione di amministratore della tabella per .alter .The operation requires database user permission for .create and table admin permission for .alter.

ParametersParameters

TableNameTableName

Nome della tabella esterna che rispetta le regole dei nomi delle entità .External table name that adheres to entity names rules. Una tabella esterna non può avere lo stesso nome di una tabella normale nello stesso database.An external table can't have the same name as a regular table in the same database.

SchemaSchema

Lo schema di dati esterni viene descritto utilizzando il formato seguente:External data schema is described using the following format:

  ColumnName : ColumnType [ , ColumnName : ColumnType ...]  ColumnName : ColumnType [, ColumnName : ColumnType ...]

dove ColumnName rispetta le regole di denominazione delle entità e ColumnType è uno dei tipi di dati supportati.where ColumnName adheres to entity naming rules, and ColumnType is one of supported data types.

Suggerimento

Se lo schema dati esterno è sconosciuto, utilizzare il plug-in deduci _ _ schema di archiviazione , che consente di dedurre lo schema in base al contenuto del file esterno.If the external data schema is unknown, use the infer_storage_schema plug-in, which helps infer the schema based on external file contents.

PartizioniPartitions

Elenco delimitato da virgole di colonne in base alle quali viene partizionata una tabella esterna.Comma-separated list of columns by which an external table is partitioned. La colonna di partizione può essere presente nel file di dati o in una parte del percorso del file (altre informazioni sulle colonne virtuali).Partition column can exist in the data file itself, or sa part of the file path (read more on virtual columns).

Elenco partizioni è una qualsiasi combinazione di colonne di partizione, specificata utilizzando uno dei seguenti formati:Partitions list is any combination of partition columns, specified using one of the following forms:

  • Partition, che rappresenta una colonna virtuale.Partition, representing a virtual column.

    PartitionName : (datetime | string)PartitionName : (datetime | string)

  • Partition, in base a un valore di colonna stringa.Partition, based on a string column value.

    PartitionName : string = ColumnNamePartitionName : string = ColumnName

  • Partition, basata su un valore hashdella colonna stringa, numeromodulo.Partition, based on a string column value hash, modulo Number.

    PartitionName : long = hash ( Numero ColumnName , Number)PartitionName : long = hash ( ColumnName , Number )

  • Partition, basata sul valore troncato di una colonna DateTime.Partition, based on truncated value of a datetime column. Vedere la documentazione sulle funzioni STARTOFYEAR, STARTOFMONTH, startOfWeek, startofday o bin .See documentation on startofyear, startofmonth, startofweek, startofday or bin functions.

    PartitionName : datetime = ( startofyear | startofmonth | startofweek | startofday ) ( ColumnName)PartitionName : datetime = (startofyear | startofmonth | startofweek | startofday) ( ColumnName )
    PartitionName : datetime = bin ( TimeSpan ColumnName , TimeSpan)PartitionName : datetime = bin ( ColumnName , TimeSpan )

Per controllare la correttezza delle definizioni di partizionamento, utilizzare la proprietà sampleUris durante la creazione di una tabella esterna.To check partitioning definition correctness, use the property sampleUris when creating an external table.

PathFormatPathFormat

Formato percorso file URI dati esterno, che può essere specificato in aggiunta alle partizioni.External data URI file path format, which can be specified in addition to partitions. Il formato del percorso è una sequenza di elementi della partizione e di separatori di testo:Path format is a sequence of partition elements and text separators:

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

dove Partition fa riferimento a una partizione dichiarata in partition by Clause e StringSeparator è un testo racchiuso tra virgolette.where Partition refers to a partition declared in partition by clause, and StringSeparator is any text enclosed in quotes. Gli elementi di partizione consecutivi devono essere impostati separatamente usando StringSeparator.Consecutive partition elements must be set apart using StringSeparator.

Il prefisso del percorso file originale può essere costruito usando gli elementi Partition sottoposti a rendering come stringhe e separati con i separatori di testo corrispondenti.Original file path prefix can be constructed using partition elements rendered as strings and separated with corresponding text separators. Per specificare il formato utilizzato per il rendering di un valore di partizione DateTime, è possibile utilizzare la seguente macro:To specify format used for rendering a datetime partition value, the following macro can be used:

  datetime_pattern``( DateTimeFormat , PartitionName di DateTimeFormat)  datetime_pattern ( DateTimeFormat , PartitionName )

dove DateTimeFormat rispetta la specifica di formato .NET, con un'estensione che consente di racchiudere gli identificatori di formato in parentesi graffe.where DateTimeFormat adheres to the .NET format specification, with an extension allowing to enclose format specifiers into curly brackets. Ad esempio, i due formati seguenti sono equivalenti:For example, the following two formats are equivalent:

  'year='yyyy'/month='MM e year={yyyy}/month={MM}  'year='yyyy'/month='MM and year={yyyy}/month={MM}

Per impostazione predefinita, viene eseguito il rendering dei valori DateTime usando i formati seguenti:By default, datetime values are rendered using the following formats:

Funzione di partizionePartition function Formato predefinitoDefault format
startofyear yyyy
startofmonth yyyy/MM
startofweek yyyy/MM/dd
startofday yyyy/MM/dd
bin(Colonna, 1d)bin(Column, 1d) yyyy/MM/dd
bin(Colonna, 1h)bin(Column, 1h) yyyy/MM/dd/HH
bin(Colonna, 1m)bin(Column, 1m) yyyy/MM/dd/HH/mm

Se PathFormat viene omesso dalla definizione della tabella esterna, si presuppone che tutte le partizioni, nello stesso ordine in cui vengono definite, siano separate mediante il / separatore.If PathFormat is omitted from the external table definition, it's assumed that all partitions, in exactly the same order as they're defined, are separated using / separator. Viene eseguito il rendering delle partizioni utilizzando la relativa presentazione di stringa predefinita.Partitions are rendered using their default string presentation.

Per verificare la correttezza della definizione del formato del percorso, utilizzare la proprietà sampleUris durante la creazione di una tabella esterna.To check path format definition correctness, use the property sampleUris when creating an external table.

FormatoFormat

Formato dati, uno dei formatidi inserimento.The data format, any of the ingestion formats.

Nota

L'utilizzo della tabella esterna per lo scenario di esportazione è limitato ai formati seguenti: CSV , TSV JSON e Parquet .Using external table for export scenario is limited to the following formats: CSV, TSV, JSON and Parquet.

StorageConnectionStringStorageConnectionString

Uno o più percorsi per i contenitori BLOB di archiviazione BLOB di Azure o Azure Data Lake Store file System (directory virtuali o cartelle), incluse le credenziali.One or more paths to Azure Blob Storage blob containers or Azure Data Lake Store file systems (virtual directories or folders), including credentials. Per informazioni dettagliate, vedere stringhe di connessione di archiviazione .See storage connection strings for details.

Suggerimento

Fornire più di un singolo account di archiviazione per evitare la limitazione delle richieste di archiviazione mentre si esportano grandi quantità di dati nella tabella esterna.Provide more than a single storage account to avoid storage throttling while exporting large amounts of data to the external table. L'esportazione distribuirà le scritture tra tutti gli account forniti.Export will distribute the writes between all accounts provided.

Proprietà facoltativeOptional Properties

ProprietàProperty TypeType DescrizioneDescription
folder string Cartella della tabellaTable's folder
docString string Stringa che documenta la tabellaString documenting the table
compressed bool Se impostato, indica se i file vengono compressi come .gz file (utilizzati solo nello scenario di esportazione )If set, indicates whether the files are compressed as .gz files (used in export scenario only)
includeHeaders string Per i formati di testo delimitati (CSV, TSV,...), indica se i file contengono un'intestazione.For delimited text formats (CSV, TSV, ...), indicates whether files contain a header. I valori possibili sono: All (tutti i file contengono un'intestazione), FirstFile (il primo file in una cartella contiene un'intestazione), None ovvero nessun file contiene un'intestazione.Possible values are: All (all files contain a header), FirstFile (first file in a folder contains a header), None (no files contain a header).
namePrefix string Se impostato, indica il prefisso dei file.If set, indicates the prefix of the files. Durante le operazioni di scrittura, tutti i file verranno scritti con questo prefisso.On write operations, all files will be written with this prefix. Durante le operazioni di lettura vengono letti solo i file con questo prefisso.On read operations, only files with this prefix are read.
fileExtension string Se impostato, indica le estensioni dei file.If set, indicates file extensions of the files. In scrittura i nomi dei file termineranno con questo suffisso.On write, files names will end with this suffix. Durante la lettura, verranno letti solo i file con questa estensione di file.On read, only files with this file extension will be read.
encoding string Indica come viene codificato il testo: UTF8NoBOM (impostazione predefinita) o UTF8BOM .Indicates how the text is encoded: UTF8NoBOM (default) or UTF8BOM.
sampleUris bool Se impostato, il risultato del comando fornisce diversi esempi di URI dei file di dati esterni come previsto dalla definizione della tabella esterna. gli esempi vengono restituiti nella seconda tabella dei risultati.If set, the command result provides several examples of external data files URI as they are expected by the external table definition (the samples are returned in the second result table). Questa opzione consente di verificare se le partizioni e i parametri PathFormat sono definiti correttamente.This option helps validate whether Partitions and PathFormat parameters are defined properly.
validateNotEmpty bool Se impostato, le stringhe di connessione vengono convalidate per il loro contenuto.If set, the connection strings are validated for having content in them. Il comando avrà esito negativo se il percorso URI specificato non esiste o se non sono disponibili autorizzazioni sufficienti per accedervi.The command will fail if the specified URI location doesn't exist, or if there are insufficient permissions to access it.

Suggerimento

Per ulteriori informazioni sul ruolo namePrefix e sulle fileExtension Proprietà Riproduci nei filtri dei file di dati durante la query, vedere la sezione logica di filtro file .To learn more about the role namePrefix and fileExtension properties play in data file filtering during query, see file filtering logic section.

EsempiExamples

Tabella esterna non partizionata.A non-partitioned external table. Si prevede che i file di dati siano posizionati direttamente sotto i contenitori definiti:Data files are expected to be placed directly under the container(s) defined:

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

Una tabella esterna partizionata in base alla data.An external table partitioned by date. Si prevede che i file di data siano posizionati in directory con formato DateTime predefinito yyyy/MM/dd :Date files are expected to be placed in directories of default datetime format yyyy/MM/dd:

.create external table ExternalTable (Timestamp:datetime, x:long, s:string) 
kind=adl
partition by (Date:datetime = bin(Timestamp, 1d)) 
dataformat=csv 
( 
   h@'abfss://filesystem@storageaccount.dfs.core.windows.net/path;secretKey'
)

Una tabella esterna partizionata in base al mese, con formato di directory year=yyyy/month=MM :An external table partitioned by month, with a directory format of year=yyyy/month=MM:

.create external table ExternalTable (Timestamp:datetime, x:long, s:string) 
kind=blob 
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' 
) 

Una tabella esterna partizionata prima in base al nome del cliente, quindi per data.An external table partitioned first by customer name, then by date. La struttura di directory prevista è, ad esempio, customer_name=Softworks/2019/02/01 :Expected directory structure is, for example, customer_name=Softworks/2019/02/01:

.create external table ExternalTable (Timestamp:datetime, CustomerName:string) 
kind=blob 
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' 
)

Una tabella esterna partizionata prima in base al nome del cliente hash (modulo dieci), quindi per data.An external table partitioned first by customer name hash (modulo ten), then by date. La struttura di directory prevista è, ad esempio, customer_id=5/dt=20190201 .Expected directory structure is, for example, customer_id=5/dt=20190201. I nomi dei file di dati terminano con l' .txt estensione:Data file names end with .txt extension:

.create external table ExternalTable (Timestamp:datetime, CustomerName:string) 
kind=blob 
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")

Output di esempioSample Output

TableNameTableName TableTypeTableType CartellaFolder DocStringDocString ProprietàProperties ConnectionStringsConnectionStrings PartizioniPartitions PathFormatPathFormat
ExternalTableExternalTable BLOBBlob ExternalTablesExternalTables DocsDocs {"Format": "CSV", "compresso": false, "CompressionType": null, "FileExtension": null, "IncludeHeaders": "None", "Encoding": null, "NamePrefix": null}{"Format":"Csv","Compressed":false,"CompressionType":null,"FileExtension":null,"IncludeHeaders":"None","Encoding":null,"NamePrefix":null} ["https://storageaccount.blob.core.windows.net/container1;*******"]["https://storageaccount.blob.core.windows.net/container1;*******"] [{"Mod": 10, "Name": "CustomerId", "ColumnName": "CustomerName", "ordinal": 0}, {"Function": "StartOfDay", "Name": "date", "ColumnName": "timestamp", "ordinal": 1}][{"Mod":10,"Name":"CustomerId","ColumnName":"CustomerName","Ordinal":0},{"Function":"StartOfDay","Name":"Date","ColumnName":"Timestamp","Ordinal":1}] "Customer _ ID =" CustomerID "/DT =" DateTime _ pattern ("AAAAMMGG", date)"customer_id=" CustomerId "/dt=" datetime_pattern("yyyyMMdd",Date)

Colonne virtualiVirtual columns

Quando i dati vengono esportati da Spark, le colonne di partizione (specificate nel metodo del writer dataframe partitionBy ) non vengono scritte nei file di dati.When data is exported from Spark, partition columns (that are specified in dataframe writer's partitionBy method) are not written to data files. Questo processo evita la duplicazione dei dati perché i dati sono già presenti nei nomi "cartella".This process avoids data duplication because the data already present in "folder" names. Ad esempio, column1=<value>/column2=<value>/ e Spark può riconoscerlo alla lettura.For example, column1=<value>/column2=<value>/, and Spark can recognize it upon read.

Le tabelle esterne supportano la sintassi seguente per specificare le colonne virtuali:External tables support the following syntax for specifying virtual columns:

.create external table ExternalTable (EventName:string, Revenue:double)  
kind=blob  
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'
)

Logica di filtro fileFile filtering logic

Quando si esegue una query su una tabella esterna, il motore di query migliora le prestazioni filtrando i file di archiviazione esterni irrilevanti.When querying an external table, the query engine improves performance by filtering out irrelevant external storage files. Di seguito è descritto il processo di iterazione nei file e di decidere se un file deve essere elaborato.The process of iterating on files and deciding whether a file should be processed is described below.

  1. Compilare un modello URI che rappresenta una posizione in cui vengono trovati i file.Build a URI pattern that represents a place where files are found. Inizialmente, il modello URI è uguale a una stringa di connessione fornita come parte della definizione della tabella esterna.Initially, the URI pattern equals a connection string provided as part of the external table definition. Se sono presenti partizioni definite, ne viene eseguito il rendering usando PathFormat, quindi aggiunte al modello URI.If there are any partitions defined, they are rendered using PathFormat, then appended to the URI pattern.

  2. Per tutti i file trovati con i modelli URI creati, controllare:For all files found under the URI patterns(s) created, check:

    • I valori della partizione corrispondono ai predicati utilizzati in una query.Partition values match predicates used in a query.
    • Il nome del BLOB inizia con NamePrefix , se tale proprietà è definita.Blob name starts with NamePrefix, if such a property is defined.
    • Il nome del BLOB termina con FileExtension , se tale proprietà è definita.Blob name ends with FileExtension, if such a property is defined.

Una volta soddisfatte tutte le condizioni, il file viene recuperato ed elaborato dal motore di query.Once all the conditions are met, the file is fetched and processed by the query engine.

Nota

Il modello di URI iniziale viene compilato usando i valori dei predicati di query.Initial URI pattern is built using query predicate values. Questa operazione è ottimale per un set limitato di valori stringa e per gli intervalli di tempo chiusi.This works best for a limited set of string values as well as for a closed time ranges.

. Mostra elementi della tabella esterna.show external table artifacts

Restituisce un elenco di tutti i file che verranno elaborati durante l'esecuzione di query su una tabella esterna specificata.Returns a list of all files that will be processed when querying a given external table.

Nota

L'operazione richiede l' autorizzazione utente del database.The operation requires database user permission.

Sintassi:Syntax:

.show``external table TableName artifacts [ limit MaxResults].show external table TableName artifacts [limit MaxResults]

dove MaxResults è un parametro facoltativo, che può essere impostato per limitare il numero di risultati.where MaxResults is an optional parameter, which can be set to limit the number of results.

OutputOutput

Parametro di outputOutput parameter TipoType DescrizioneDescription
UriUri stringstring URI del file di dati di archiviazione esternoURI of external storage data file

Suggerimento

L'iterazione su tutti i file a cui fa riferimento una tabella esterna può essere piuttosto costosa, a seconda del numero di file.Iterating on all files referenced by an external table can be quite costly, depending on the number of files. Assicurarsi di utilizzare il limit parametro se si desidera visualizzare solo alcuni esempi di URI.Make sure to use limit parameter if you just want to see some URI examples.

Esempi:Examples:

.show external table T artifacts

Output:Output:

UriUri
https://storageaccount.blob.core.windows.net/container1/folder/file.csv

. Crea mapping tabella esterna.create external table mapping

.create``external table ExternalTableName json mapping MappingName MappingInJsonFormat.create external table ExternalTableName json mapping MappingName MappingInJsonFormat

Crea un nuovo mapping.Creates a new mapping. Per ulteriori informazioni, vedere mapping dei dati.For more information, see Data Mappings.

EsempioExample

.create external table MyExternalTable json mapping "Mapping1" '[{"Column": "rownumber", "Properties": {"Path": "$.rownumber"}}, {"Column": "rowguid", "Properties": {"Path": "$.rowguid"}}]'

Output di esempioExample output

NomeName TipoKind MappingMapping
mapping1mapping1 JSONJSON [{"ColumnName": "RowNumber", "Properties": {"Path": "$. RowNumber"}}, {"ColumnName": "rowguid", "Properties": {"Path": "$. rowguid"}}][{"ColumnName":"rownumber","Properties":{"Path":"$.rownumber"}},{"ColumnName":"rowguid","Properties":{"Path":"$.rowguid"}}]

. Alter mapping tabella esterna.alter external table mapping

.alter``external table ExternalTableName json mapping MappingName MappingInJsonFormat.alter external table ExternalTableName json mapping MappingName MappingInJsonFormat

Modifica un mapping esistente.Alters an existing mapping.

EsempioExample

.alter external table MyExternalTable json mapping "Mapping1" '[{"Column": "rownumber", "Properties": {"Path": "$.rownumber"}}, {"Column": "rowguid", "Properties": {"Path": "$.rowguid"}}]'

Output di esempioExample output

NomeName TipoKind MappingMapping
mapping1mapping1 JSONJSON [{"ColumnName": "RowNumber", "Properties": {"Path": "$. RowNumber"}}, {"ColumnName": "rowguid", "Properties": {"Path": "$. rowguid"}}][{"ColumnName":"rownumber","Properties":{"Path":"$.rownumber"}},{"ColumnName":"rowguid","Properties":{"Path":"$.rowguid"}}]

. Mostra mapping tabella esterna.show external table mappings

.show``external table ExternalTableName json mapping Mapping di ExternalTableName.show external table ExternalTableName json mapping MappingName

.show``external table ExternalTableName ExternalTableName json``mappings.show external table ExternalTableName json mappings

Mostra i mapping (tutti o quello specificato in base al nome).Show the mappings (all or the one specified by name).

EsempioExample

.show external table MyExternalTable json mapping "Mapping1" 

.show external table MyExternalTable json mappings 

Output di esempioExample output

NomeName TipoKind MappingMapping
mapping1mapping1 JSONJSON [{"ColumnName": "RowNumber", "Properties": {"Path": "$. RowNumber"}}, {"ColumnName": "rowguid", "Properties": {"Path": "$. rowguid"}}][{"ColumnName":"rownumber","Properties":{"Path":"$.rownumber"}},{"ColumnName":"rowguid","Properties":{"Path":"$.rowguid"}}]

. Elimina mapping tabella esterna.drop external table mapping

.drop``external table ExternalTableName json mapping Mapping di ExternalTableName.drop external table ExternalTableName json mapping MappingName

Elimina il mapping dal database.Drops the mapping from the database.

EsempioExample

.drop external table MyExternalTable json mapping "Mapping1" 

Passaggi successiviNext steps