Create and alter Azure SQL external tables

Learn how to create and alter an SQL external table.

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 , … ])]

Parameters

NameTypeRequiredDescription
TableNamestring✔️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.
Schemastring✔️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.
SqlTableNamestringThe 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.
SqlConnectionStringstring✔️The connection string to the SQL server.
SqlDialectstringIndicates 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.
PropertystringA key-value property pair in the format PropertyName = PropertyValue. See optional properties.

Optional properties

PropertyTypeDescription
folderstringThe table’s folder.
docStringstringA string documenting the table.
firetriggerstrue/falseIf 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)
createifnotexiststrue/ falseIf 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.
primarykeystringIf 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.windows.net,1433;Authentication=Active Directory Integrated;Initial Catalog=mydatabase;'
)
with 
(
   docstring = "Docs",
   folder = "ExternalTables", 
   createifnotexists = true,
   primarykey = x,
   firetriggers=true
)

Output

TableNameTableTypeFolderDocStringProperties
MySqlExternalTableSqlExternalTablesDocs{
“TargetEntityKind”: “sqltable`”,
“TargetEntityName”: “MySqlTable”,
“TargetEntityConnectionString”: “Server=tcp:myserver.database.windows.net,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.windows.net;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.azure.com; 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.com/;Database=MyDatabase;Collection=MyCollection;AccountKey=' h'R8PM...;'
)
with 
(
   sqlDialect = "CosmosDbSQL",
   docstring = "Docs",
   folder = "ExternalTables", 
)

Related content