Create and alter Azure SQL external tables

Applies to: ✅ Azure Data Explorer

Creates or alters an Azure SQL external table in the database in which the command is executed.

Supported Azure SQL external table types

1. SQL Server
2. MySQL
3. PostgreSQL
4. Cosmos DB

Permissions

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

To .create, .alter, or .create-or-alter an external table using managed identity authentication requires Database Admin permissions. This method is supported for SQL Server and Cosmos DB external tables.

Syntax

(.create | .alter | .create-or-alter) external table TableName (Schema) kind = sql [ table = SqlTableName ] (SqlConnectionString) [with ( [ sqlDialect = SqlDialect ] , [ Property , ... ])]

Learn more about syntax conventions.

Parameters

Name	Type	Required	Description
TableName	string	✔️	The name of the external table. The name must follow the rules for entity names, and an external table can't have the same name as a regular table in the same database.
Schema	string	✔️	The 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.
SqlTableName	string		The name of the SQL table not including the database name. For example, "MySqlTable" and not "db1.MySqlTable". If the name of the table contains a period ("."), use ['Name.of.the.table'] notation.
This specification is required for all types of tables except for Cosmos DB, as for Cosmos DB the collection name is part of the connection string.
SqlConnectionString	string	✔️	The connection string to the SQL server.
SqlDialect	string		Indicates the type of Azure SQL external table. SQL Server is the default. For MySQL, specify MySQL. For PostgreSQL, specify PostgreSQL. For Cosmos DB, specify CosmosDbSql.
Property	string		A key-value property pair in the format PropertyName = PropertyValue. See optional properties.

Optional properties

Property	Type	Description
folder	string	The table's folder.
docString	string	A string documenting the table.
firetriggers	true/false	If true, instructs the target system to fire INSERT triggers defined on the SQL table. The default is false. (For more information, see BULK INSERT and System.Data.SqlClient.SqlBulkCopy)
createifnotexists	true/ false	If true, the target SQL table is created if it doesn't already exist; the primarykey property must be provided in this case to indicate the result column that is the primary key. The default is false.
primarykey	string	If createifnotexists is true, the resulting column name is used as the SQL table's primary key if it's created by this command.

Authentication and authorization

To interact with an external Azure SQL table, you must specify authentication means as part of the SqlConnectionString. The SqlConnectionString defines the resource to access and its authentication information.

For more information, see Azure SQL external table authentication methods.

Examples

The following examples show how to create each type of Azure SQL external table.

SQL Server

.create external table MySqlExternalTable (x:long, s:string) 
kind=sql
table=MySqlTable
( 
   h@'Server=tcp:myserver.database.chinacloudapi.cn,1433;Authentication=Active Directory Integrated;Initial Catalog=mydatabase;'
)
with 
(
   docstring = "Docs",
   folder = "ExternalTables", 
   createifnotexists = true,
   primarykey = x,
   firetriggers=true
)  

Output

TableName	TableType	Folder	DocString	Properties
MySqlExternalTable	Sql	ExternalTables	Docs	{ "TargetEntityKind": "sqltable`", "TargetEntityName": "MySqlTable", "TargetEntityConnectionString": "Server=tcp:myserver.database.chinacloudapi.cn,1433;Authentication=Active Directory Integrated;Initial Catalog=mydatabase;", "FireTriggers": true, "CreateIfNotExists": true, "PrimaryKey": "x" }

MySQL

.create external table MySqlExternalTable (x:long, s:string) 
kind=sql
table=MySqlTable
( 
   h@'Server=myserver.mysql.database.chinacloudapi.cn;Port = 3306;UID = USERNAME;Pwd = PASSWORD;Database = mydatabase;'
)
with 
(
   sqlDialect = "MySql",
   docstring = "Docs",
   folder = "ExternalTables", 
)  

PostgreSQL

.create external table PostgreSqlExternalTable (x:long, s:string) 
kind=sql
table=PostgreSqlTable
( 
   h@'Host = hostname.postgres.database.chinacloudapi.cn; Port = 5432; Database= db; User Id=user; Password=pass; Timeout = 30;'
)
with 
(
   sqlDialect = "PostgreSQL",
   docstring = "Docs",
   folder = "ExternalTables", 
)  

Cosmos DB

.create external table CosmosDBSQLExternalTable (x:long, s:string) 
kind=sql
( 
   h@'AccountEndpoint=https://cosmosdbacc.documents.azure.cn/;Database=MyDatabase;Collection=MyCollection;AccountKey=' h'R8PM...;'
)
with 
(
   sqlDialect = "CosmosDbSQL",
   docstring = "Docs",
   folder = "ExternalTables", 
)  

Related content

• External tables overview
• SQL external table authentication methods
• Create and alter Azure Storage external tables