Create and alter delta external tables on Azure Storage

  • Article

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.

Note

If the table exists, the .create command will fail with an error. Use .create-or-alter or .alter to modify existing tables.

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

Learn more about syntax conventions.

Parameters

Name Type Required Description
TableName string ✔️ 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.
Schema string The 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.
StorageConnectionString string ✔️ 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.
Property string A key-value property pair in the format PropertyName = PropertyValue. See optional properties.

Note

  • If a custom schema is provided, non-existing columns or columns having incompatible types will be filled with null values.
  • Information about partitions is automatically inferred from the delta log. Partition columns are added as virtual columns to the table schema. For more information, see Virtual columns.
  • The path format is automatically inferred from the partitioning information. For more information, see Path format

Tip

For a custom schema, you can use the infer_storage_schema plugin to infer the schema based on the external file content.

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

Property Type Description
folder string Table's folder
docString string String documenting the table
namePrefix string If set, indicates 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.
fileExtension string If set, indicates file extensions of the files. On write, files names will end with this suffix. On read, only files with this file extension will be read.
encoding string Indicates how the text is encoded: UTF8NoBOM (default) or UTF8BOM.
dryRun bool If 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.

Note

The external delta table is accessed during creation, to infer the partitioning information and, optionally, the schema. Make sure that the table definition is valid and that the storage is accessible.

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.chinacloudapi.cn/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.chinacloudapi.cn/path;secretKey'
)

Limitations

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

Related content