此 mysql_request 插件将 SQL 查询发送到 MySQL 服务器网络终结点,并返回结果中的第一个行集。The mysql_request plugin sends a SQL query to a MySQL Server network endpoint and returns the first rowset in the results. 查询可能返回多个行集,但只有第一个行集可用于 Kusto 查询的其余部分。The query may return more then one rowset, but only the first rowset is made available for the rest of the Kusto query.
重要
此 mysql_request 插件处于预览模式,默认情况下处于禁用状态。The mysql_request plugin is in preview mode, and is disabled by default.
若要查看启用了哪些插件,请使用.show plugin 管理命令。To see which plugins are enabled, use .show plugin management commands.
语法Syntax
evaluate``mysql_request ( ConnectionString , SqlQuery [ , SqlParameters])evaluate mysql_request ( ConnectionString , SqlQuery [, SqlParameters] )
参数Arguments
名称Name
类型Type
说明Description
必需/可选Required/Optional
ConnectionStringConnectionString
string literalstring literal
指示指向 MySQL 服务器网络终结点的连接字符串。Indicates the connection string that points at the MySQL Server network endpoint. 请参阅 身份验证 和如何指定 网络终结点。
必须Required
SqlQuerySqlQuery
string literalstring literal
指示要对 SQL 终结点执行的查询。Indicates the query that is to be executed against the SQL endpoint. 必须返回一个或多个行集,但只有第一个行集可用于 Kusto 查询的其余部分。Must return one or more rowsets, but only the first one is made available for the rest of the Kusto query.
必须Required
SqlParametersSqlParameters
类型的常数值 dynamicConstant value of type dynamic
保留要作为参数与查询一起传递的键值对。Holds key-value pairs to pass as parameters along with the query.
可选Optional
设置标注策略Set callout policy
此插件向 MySql 数据库进行标注。The plugin makes callouts to the MySql DB. 请确保群集的 标注策略 允许 mysql 向目标 MySqlDbUri 调用类型。Make sure that the cluster's callout policy enables calls of type mysql to the target MySqlDbUri.
下面的示例演示如何为 MySQL DB 定义标注策略。The following example shows how to define the callout policy for MySQL DB. 建议将标注策略限制为特定终结点 (my_endpoint1 , my_endpoint2) 。It's recommended to restrict the callout policy to specific endpoints (my_endpoint1, my_endpoint2).
[
{
"CalloutType": "mysql",
"CalloutUriRegex": "my_endpoint1\\.mysql\\.database\\.azure\\.com",
"CanCall": true
},
{
"CalloutType": "mysql",
"CalloutUriRegex": "my_endpoint2\\.mysql\\.database\\.azure\\.com",
"CanCall": true
}
]
下面的示例演示了 CalloutType 的 alter callout 策略命令 mysql CalloutType:The following example shows an alter callout policy command for mysql CalloutType:
.alter cluster policy callout @'[{"CalloutType": "mysql", "CalloutUriRegex": "\\.mysql\\.database\\.azure\\.com", "CanCall": true}]'
用户名和密码身份验证Username and password authentication
Mysql_request 插件仅支持 MySQL 服务器终结点的用户名和密码身份验证,并且不会与 Azure Active Directory 身份验证集成。The mysql_request plugin supports only username and password authentication to the MySQL Server endpoint and doesn't integrate with Azure Active Directory authentication.
用户名和密码是使用以下参数作为连接字符串的一部分提供的:Username and password are provided as part of the connections string using the following parameters:
User ID=...; Password=...;
警告
机密或受保护的信息应从连接字符串和查询中进行模糊处理,以使其在任何 Kusto 跟踪中被忽略。Confidential or guarded information should be obfuscated from connection strings and queries so that they are omitted from any Kusto tracing. For more information, see obfuscated string literals.
加密和服务器验证Encryption and server validation
出于安全原因,在 SslMode Required 连接到 MySQL 服务器网络终结点时,无条件设置为。For security reasons, SslMode is unconditionally set to Required when connecting to a MySQL Server network endpoint. 因此,必须为 SQL Server 配置有效的 SSL/TLS 服务器证书。As a result, the SQL Server must be configured with a valid SSL/TLS server certificate.
指定网络终结点Specify the network endpoint
将 SQL 网络终结点指定为连接字符串的一部分。Specify the SQL network endpoint as part of the connection string.
语法:Syntax:
Server``= FQDN [ Port = 端口]Server = FQDN [Port = Port]
其中:Where:
FQDN 是终结点的完全限定域名。FQDN is the fully qualified domain name of the endpoint.
Port 是终结点的 TCP 端口。Port is the TCP port of the endpoint. 默认为 3306。By default, 3306 is assumed.
示例Examples
Azure MySQL 数据库的 SQL 查询SQL query to Azure MySQL DB
下面的示例将 SQL 查询发送到 Azure MySQL DB 数据库。The following example sends a SQL query to an Azure MySQL DB database. 它从中检索所有记录 [dbo].[Table] ,并处理结果。It retrieves all records from [dbo].[Table], and then processes the results.
备注
不应以这种方式将此示例用作筛选或投影数据的建议。This example shouldn't be taken as a recommendation to filter or project data in this manner. 应构造 SQL 查询以返回可能的最小数据集,因为 Kusto 优化器当前不会尝试优化 Kusto 和 SQL 之间的查询。SQL queries should be constructed to return the smallest data set possible, since the Kusto optimizer doesn't currently attempt to optimize queries between Kusto and SQL.
evaluate sql_request(
'Server=contoso.mysql.database.azure.com; Port = 3306;'
'Database=Fabrikam;'
h'UID=USERNAME;'
h'Pwd=PASSWORD;',
'select * from [dbo].[Table]')
| where Id > 0
| project Name
具有用户名和密码的 SQL 身份验证SQL authentication with username and password
下面的示例与前一个示例相同,但通过用户名和密码进行 SQL 身份验证。The following example is identical to the previous one, but SQL authentication is done by username and password. 为实现保密性,我们使用了模糊处理的字符串。For confidentiality, we use obfuscated strings.
evaluate sql_request(
'Server=contoso.mysql.database.azure.com; Port = 3306;'
'Database=Fabrikam;'
h'UID=USERNAME;'
h'Pwd=PASSWORD;',
'select * from [dbo].[Table]')
| where Id > 0
| project Name
对 Azure SQL 数据库进行带有修改的 SQL 查询SQL query to Azure SQL DB with modifications
下面的示例向 Azure SQL DB 数据库发送一个 SQL 查询,从 [dbo].[Table] 检索所有记录,同时追加另一个 datetime 列,然后在 Kusto 端处理结果。The following example sends a SQL query to an Azure SQL DB database retrieving all records from [dbo].[Table], while appending another datetime column, and then processes the results on the Kusto side.
它指定要在 SQL 查询中使用的 SQL 参数 (@param0)。It specifies a SQL parameter (@param0) to be used in the SQL query.
evaluate mysql_request(
'Server=contoso.mysql.database.azure.com; Port = 3306;'
'Database=Fabrikam;'
h'UID=USERNAME;'
h'Pwd=PASSWORD;',
'select *, @param0 as dt from [dbo].[Table]',
dynamic({'param0': datetime(2020-01-01 16:47:26.7423305)}))
| where Id > 0
| project Name