创建和更改外部 SQL 表Create and alter external SQL tables

在执行命令的数据库中创建或更改外部 SQL 表。Creates or alters an external SQL table in the database in which the command is executed.

语法Syntax

(.create | .alter | .create-or-alter) external table TableName ([columnName:columnType], ...)(.create | .alter | .create-or-alter) external table TableName ([columnName:columnType], ...)
kind = sqlkind = sql
table = SqlTableNametable = SqlTableName
(SqlServerConnectionString)(SqlServerConnectionString)
[with ([docstring = Documentation ] [, folder = FolderName ], property_name = value,...)][with ([docstring = Documentation ] [, folder = FolderName ], property_name = value,...)]

parametersParameters

  • TableName - 外部表名。TableName - External table name. 必须遵循实体名称的规则。Must follow the rules for entity names. 外部表不能与同一数据库中的常规表具有相同的名称。An external table can't have the same name as a regular table in the same database.
  • SqlTableName - SQL 表的名称。SqlTableName - The name of the SQL table.
  • SqlServerConnectionString - SQL Server 的连接字符串。SqlServerConnectionString - The connection string to the SQL server. 可以是以下方法之一:Can be one of the following methods:
    • AAD 集成身份验证 (Authentication="Active Directory Integrated"):用户或应用程序通过 AAD 向 Kusto 进行身份验证,然后使用同一令牌访问 SQL Server 网络终结点。AAD-integrated authentication (Authentication="Active Directory Integrated"): The user or application authenticates via AAD to Kusto, and the same token is then used to access the SQL Server network endpoint.
    • 用户名/密码身份验证 (User ID=...; Password=...;)。Username/Password authentication (User ID=...; Password=...;). 如果外部表用于连续导出,则必须使用此方法执行身份验证。If the external table is used for continuous export, authentication must be performed by using this method.

警告

应该对包含机密信息的连接字符串和查询进行模糊处理,使其在任何 Kusto 跟踪中都被忽略。Connection strings and queries that include confidential information should be obfuscated so that they'll be omitted from any Kusto tracing. 有关详细信息,请参阅经过模糊处理的字符串文本For more information, see obfuscated string literals.

可选属性Optional properties

属性Property 类型Type 说明Description
folder string 表的文件夹。The table's folder.
docString string 一个用来记录表的字符串。A string documenting the table.
firetriggers true/false 如果为 true,则指示目标系统激发 SQL 表上定义的 INSERT 触发器。If true, instructs the target system to fire INSERT triggers defined on the SQL table. 默认为 falseThe default is false. (有关详细信息,请参阅 BULK INSERTSystem.Data.SqlClient.SqlBulkCopy(For more information, see BULK INSERT and System.Data.SqlClient.SqlBulkCopy)
createifnotexists true/ false 如果为 true,则会在目标 SQL 表不存在的情况下创建该表;在这种情况下,必须提供 primarykey 属性来指示作为主键的结果列。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. 默认为 falseThe default is false.
primarykey string 如果 createifnotexiststrue,则生成的列名称将用作 SQL 表的主键(如果是此命令创建的)。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.

备注

  • 如果该表存在,则 .create 命令会失败并显示错误。If the table exists, the .create command will fail with an error. 使用 .create-or-alter.alter 修改现有表。Use .create-or-alter or .alter to modify existing tables.
  • 不支持更改外部 SQL 表的架构或格式。Altering the schema or format of an external SQL table is not supported.

需要数据库用户权限(对于 .create)和表管理员权限(对于 .alter)。Requires database user permission for .create and table admin permission for .alter.

示例Example

.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

TableNameTableName TableTypeTableType 文件夹Folder DocStringDocString 属性Properties
MySqlExternalTableMySqlExternalTable SqlSql ExternalTablesExternalTables DocsDocs {{
"TargetEntityKind": "sqltable`","TargetEntityKind": "sqltable`",
"TargetEntityName":"MySqlTable","TargetEntityName": "MySqlTable",
"TargetEntityConnectionString":"Server=tcp:myserver.database.chinacloudapi.cn,1433;Authentication=Active Directory Integrated;Initial Catalog=mydatabase;","TargetEntityConnectionString": "Server=tcp:myserver.database.chinacloudapi.cn,1433;Authentication=Active Directory Integrated;Initial Catalog=mydatabase;",
"FireTriggers": true,"FireTriggers": true,
"CreateIfNotExists": true,"CreateIfNotExists": true,
"PrimaryKey": "x""PrimaryKey": "x"
}}

查询 SQL 类型的外部表Querying an external table of type SQL

支持查询外部 SQL 表。Querying an external SQL table is supported. 请参阅查询外部表See querying external tables.

备注

SQL 外部表查询实现将执行 SELECT x, s FROM MySqlTable 语句,其中 xs 为外部表列名。SQL external table query implementation will execute SELECT x, s FROM MySqlTable statement, where x and s are external table column names. 查询的其余部分将在 Kusto 端执行。The rest of the query will execute on the Kusto side.

请考虑以下外部表查询:Consider the following external table query:

external_table('MySqlExternalTable') | count

Kusto 将对 SQL 数据库执行 SELECT x, s FROM MySqlTable 查询,然后在 Kusto 端计数。Kusto will execute a SELECT x, s FROM MySqlTable query to the SQL database, followed by a count on Kusto side. 在这种情况下,如果直接用 T-SQL 来编写 (SELECT COUNT(1) FROM MySqlTable) 并使用 sql_request 插件来执行(而不是使用外部表函数),则性能应会更好。In such cases, performance is expected to be better if written in T-SQL directly (SELECT COUNT(1) FROM MySqlTable) and executed using the sql_request plugin, instead of using the external table function. 同样,筛选器不会推送到 SQL 查询。Similarly, filters are not pushed to the SQL query.

如果查询需要读取整个表(或相关列)以便在 Kusto 端进一步执行,请使用外部表来查询 SQL 表。Use the external table to query the SQL table when the query requires reading the entire table (or relevant columns) for further execution on Kusto side. 如果 SQL 查询可在 T-SQL 中优化,请使用 sql_request 插件When an SQL query can be optimized in T-SQL, use the sql_request plugin.

后续步骤Next steps