Kusto 作为 SQL Server 的链接服务器Kusto as a linked server from the SQL server

本地 SQL Server 允许你附加链接服务器,并允许你创建查询,将来自 SQL Server 和来自链接服务器的数据联接在一起。SQL server on-premises lets you attach a linked server and lets you create queries joining data from the SQL server and from linked servers.

可以通过 ODBC 连接使用 Kusto 作为链接服务器。You can use Kusto as a linked server via ODBC connectivity. SQL Server 本地服务需要使用 Active Directory 帐户(而不是默认服务帐户),以便通过 Azure Active Directory (Azure AD) 连接到 Azure 数据资源管理器。The SQL Server on-premises service needs to use an active directory account (not the default Service account) that lets it connect to Azure Data Explorer using Azure Active Directory (Azure AD).

  1. 为 SQL Server 2017(它还附带了 SSMS 18)安装最新的 ODBC 驱动程序: https://www.microsoft.com/download/details.aspx?id=56567Install the latest ODBC Driver for SQL Server 2017 (it also comes with SSMS 18): https://www.microsoft.com/download/details.aspx?id=56567
  2. 为特定的 Azure 数据资源管理器群集和数据库准备 ODBC 驱动程序的无 DSN 连接字符串:Driver={ODBC Driver 17 for SQL Server};Server=<cluster>.kusto.chinacloudapi.cn;Database=<database>;Authentication=ActiveDirectoryIntegrated;Language=any@MaxStringSize:4000Prepare the DSN-less connection string for the ODBC driver, for a specific Azure Data Explorer cluster and database: Driver={ODBC Driver 17 for SQL Server};Server=<cluster>.kusto.chinacloudapi.cn;Database=<database>;Authentication=ActiveDirectoryIntegrated;Language=any@MaxStringSize:4000. 添加了语言选项以优化 Azure 数据资源管理器,以便将字符串编码为 NVARCHAR(4000)。The language option is added to tune Azure Data Explorer to encode strings as NVARCHAR(4000). 有关此解决方法的详细信息,请参阅 ODBCFor more information about this workaround, see ODBC.
  3. 使用红色箭头指向的设置创建链接服务器。Create the Linked Server with the settings pointed to by the red arrows.

链接服务器连接

  1. 使用红色箭头指向的设置定义安全性。Define the Security with the setting pointed to by the red arrow.

链接服务器登录

若要通过 Kusto 查询数据,请执行以下操作:To query data from Kusto:

SELECT * FROM OpenQuery(LINKEDSERVER, 'SELECT * FROM <KustoStoredFunction>[(<Parameters>)]')

备注

  1. 使用 Kusto 存储函数从 Azure 数据资源管理器中提取数据。Use Kusto stored functions for extracting data from Azure Data Explorer. 存储函数可以包含从 Kusto 进行高效查询所需的所有逻辑,这些逻辑使用本机 KQL 语言编写,并由指定的参数值控制。Stored function can include all the logic necessary for efficient queries from Kusto, authored in native KQL language, and controlled by specified parameter values. 用于调用 Kusto 存储函数的 T-SQL 语法与调用 SQL 表格函数的相同。T-SQL syntax for calling the Kusto stored function is identical to calling the SQL tabular function.
  2. SQL Server 不支持在其自己的查询中从链接服务器调用远程表格函数。The SQL server doesn't support calling remote tabular functions from linked servers inside its own queries. 此限制的解决方法是使用 OpenQuery 执行链接服务器上的远程查询。The workaround for this limitation is to use OpenQuery for executing remote queries on the linked server. 这样,便不会在 SQL Server 目录中调用表格函数,而是在查询(在链接服务器上执行)中调用。This way, the tabular function is called not on the SQL server directory, but in a query that is executed on the linked server. 外部 T-SQL 查询可用于联接 SQL Server 上的数据以及通过 OpenQuery 从 Kusto 存储函数返回的数据。The outer T-SQL query can be used to join between data on the SQL server and data returned from the Kusto stored function via OpenQuery.