Create and alter Azure Storage delta external tables

This article describes how to create and alter delta external tables

The commands in this article can be used to create or alter a delta external table in the database from which the command is executed. A delta external table references Delta Lake table data located in Azure Blob Storage, Azure Data Lake Store Gen1, or Azure Data Lake Store Gen2.

To accelerate queries over external delta tables, see Query acceleration policy.

Permissions

To .create requires at least Database User permissions, and to .alter requires at least Table Admin permissions.

To .create-or-alter an external table using managed identity authentication requires AllDatabasesAdmin permissions.

Syntax

(.create | .alter | .create-or-alter) external table TableName [(Schema)] kind = delta (StorageConnectionString ) [with (Property [, …])]

Parameters

NameTypeRequiredDescription
TableNamestring✔️An external table name that adheres to the entity names rules. An external table can’t have the same name as a regular table in the same database.
SchemastringThe optional external data schema is a comma-separated list of one or more column names and data types, where each item follows the format: ColumnName : ColumnType. If not specified, it will be automatically inferred from the delta log based on the latest delta table version.
StorageConnectionStringstring✔️delta table root folder path, including credentials. Can point to Azure Blob Storage blob container, Azure Data Lake Gen 2 file system or Azure Data Lake Gen 1 container. The external table storage type is determined by the provided connection string. See storage connection strings.
PropertystringA key-value property pair in the format PropertyName = PropertyValue. See optional properties.

Authentication and authorization

The authentication method to access an external table is based on the connection string provided during its creation, and the permissions required to access the table vary depending on the authentication method.

The supported authentication methods are the same as those supported by Azure Storage external tables.

Optional properties

PropertyTypeDescription
folderstringTable’s folder
docStringstringString documenting the table
compressedboolOnly relevant for the export scenario.
If set to true, the data is exported in the format specified by the compressionType property. For the read path, compression is automatically detected.
compressionTypestringOnly relevant for the export scenario.
The compression type of exported files. For non-Parquet files, only gzip is allowed. For Parquet files, possible values include gzip, snappy, lz4_raw, brotli, and zstd. Default is gzip. For the read path, compression type is automatically detected.
namePrefixstringIf set, specifies the prefix of the files. On write operations, all files will be written with this prefix. On read operations, only files with this prefix are read.
fileExtensionstringIf set, specifies extension of the files. On write, files names will end with this suffix. On read, only files with this file extension will be read.
encodingstringSpecifies how the text is encoded: UTF8NoBOM (default) or UTF8BOM.
dryRunboolIf set, the external table definition isn’t persisted. This option is useful for validating the external table definition, especially in conjunction with the filesPreview or sampleUris parameter.

Examples

Create or alter a delta external table with an inferred schema

In the following external table, the schema is automatically inferred from the latest delta table version.

.create-or-alter external table ExternalTable  
kind=delta 
( 
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey'
)

Create a delta external table with a custom schema

In the following external table, a custom schema is specified and overrides the schema of the delta table. If, at some later time, you need to replace the custom schema with the schema based on the latest delta table version, run the .alter | .create-or-alter command without specifying a schema, like in the previous example.

.create external table ExternalTable (Timestamp:datetime, x:long, s:string) 
kind=delta
( 
   h@'abfss://filesystem@storageaccount.dfs.core.windows.net/path;secretKey'
)

Limitations

  • Time travel is not supported. Only the latest delta table version is used.

Related content