Create and alter SQL external tables

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

Supported SQL external table types

1. Microsoft 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-or-alter an external table using managed identity authentication requires AllDatabasesAdmin permissions. Currently, this is only relevant for Microsoft SQL Server 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 SQL external table. Microsoft 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 will be 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 will be used as the SQL table's primary key if it is created by this command.

Authentication and authorization

To interact with an external SQL table from Azure Data Explorer, 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 SQL external table authentication methods.

Examples

The following examples show how to create each type of 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