MS-TDS 客户端和 Azure 数据资源管理器MS-TDS clients and Azure Data Explorer

Azure 数据资源管理器为 MS-SQL 客户端实现了与 TDS 兼容的终结点。Azure Data Explorer implements TDS-compliant endpoints for MS-SQL clients. 兼容性在协议级别实现。Compatibility is on the protocol level. 任何可以连接到采用 Azure Active Directory (Azure AD) 身份验证的 SQL Azure 数据库的库或应用程序,都可以与 Azure 数据资源管理器服务器一起使用。Any library or application that can connect to the SQL Azure database with Azure Active Directory (Azure AD) authentication, will work with the Azure Data Explorer server. 因此,你可以使用服务器域名,就像它是 SQL Azure 服务器一样。Therefore, you can use the server domain name like it was the SQL Azure server.

Azure 数据资源管理器实现了 T-SQL 的一个子集和 SQL Server 模拟的一个子集。Azure Data Explorer implements a subset of the T-SQL and a subset of the SQL server emulation. 有关详细信息,请参阅已知问题,以了解 SQL Server 实现的 T-SQL 与 Azure 数据资源管理器实现的 T-SQL 之间的差异。For more information, see known issues for differences between the SQL Server's implementation of T-SQL and Azure Data Explorer's.

.NET SQL 客户端.NET SQL client

Azure 数据资源管理器支持对 SQL 客户端进行 Azure AD 身份验证。Azure Data Explorer supports Azure AD authentication for SQL clients. 有关详细信息,请参阅 .NET SQL 客户端(用户身份验证).NET SQL 客户端(应用程序身份验证)For more information, see .NET SQL Client (user authentication) and .NET SQL Client (application authentication)

JDBCJDBC

Microsoft JDBC 驱动程序可用于连接到采用 Azure AD 身份验证的 Azure 数据资源管理器。The Microsoft JDBC driver can be used to connect to Azure Data Explorer with Azure AD authentication.

创建一个应用程序来使用 mssql-jdbc JAR 和 adal4j JAR 的版本之一及其所有依赖项。Create an application to use one of the versions of mssql-jdbc JAR and adal4j JAR, and all their dependencies. 例如,For example,

mssql-jdbc-7.0.0.jre8.jar
adal4j-1.6.3.jar
accessors-smart-1.2.jar
activation-1.1.jar
asm-5.0.4.jar
commons-codec-1.11.jar
commons-lang3-3.5.jar
gson-2.8.0.jar
javax.mail-1.6.1.jar
jcip-annotations-1.0-1.jar
json-smart-2.3.jar
lang-tag-1.4.4.jar
nimbus-jose-jwt-6.5.jar
oauth2-oidc-sdk-5.64.4.jar
slf4j-api-1.7.21.jar

创建一个应用程序来使用 JDBC 驱动程序类 com.microsoft.sqlserver.jdbc.SQLServerDriver。Create an application to use the JDBC driver class com.microsoft.sqlserver.jdbc.SQLServerDriver.

使用如下所示的连接字符串。Use a connection string like the following.

jdbc:sqlserver://<cluster_name.region>.kusto.chinacloudapi.cn:1433;database=<database_name>;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.kusto.chinacloudapi.cn;loginTimeout=30;authentication=ActiveDirectoryPassword

备注

如果要使用 Azure Active Directory 集成身份验证模式,请将 ActiveDirectoryPassword 替换为 ActiveDirectoryIntegrated。If you want to use the Azure Active Directory integrated authentication mode, then replace ActiveDirectoryPassword with ActiveDirectoryIntegrated. 有关详细信息,请参阅 JDBC(用户身份验证)JDBC(应用程序身份验证)For more information, see JDBC (user authentication) and JDBC (application authentication).

ODBCODBC

支持 ODBC 的应用程序可以连接到 Azure 数据资源管理器。Applications that support ODBC can connect to Azure Data Explorer.

创建一个 ODBC 数据源:Create an ODBC data source:

  1. 启动 ODBC 数据源管理器。Launch the ODBC Data Source Administrator.
  2. 选择“添加”以创建一个新的数据源,并设置“ODBC Driver 17 for SQL Server”。Select Add to create a new data source, and set ODBC Driver 17 for SQL Server.
  3. 为数据源指定一个名称,并在“服务器”字段中指定 Azure 数据资源管理器群集名称。Give the data source a name, and specify the Azure Data Explorer cluster name in the Server field. 例如“mykusto.kusto.chinacloudapi.cn”。For example, mykusto.kusto.chinacloudapi.cn.
  4. 对于身份验证选项,请设置“Active Directory 集成”。Set Active Directory Integrated, for the authentication option.
  5. 选择“下一步”以设置数据库。Select Next to set the database.
  6. 对于后面的选项卡中的所有其他设置,只需要保留默认值即可。You can just leave the defaults for all the other settings in the tabs that follow.
  7. 选择“完成”以打开数据源摘要窗口,在该窗口中可以测试连接。Select Finish to open the data source summary window, where the connection can be tested.

现在,你可以将该 ODBC 数据源与应用程序配合使用。You can now use the ODBC data source with the applications.

如果 ODBC 应用程序可以接受连接字符串来替代 DSN 或与其一起使用,则使用以下命令。If the ODBC application can accept a connection string instead of, or in addition to DSN, then use the following.

"Driver={ODBC Driver 17 for SQL Server};Server=mykustocluster.kusto.chinacloudapi.cn;Database=mykustodatabase;Authentication=ActiveDirectoryIntegrated"

某些 ODBC 应用程序不能很好地与 NVARCHAR(MAX) 类型配合使用。Some ODBC applications don't work well with the NVARCHAR(MAX) type. 有关详细信息,请参阅 https://docs.microsoft.com/sql/relational-databases/native-client/features/using-large-value-types?view=sql-server-2017#sql-server-native-client-odbc-driverFor more information, see https://docs.microsoft.com/sql/relational-databases/native-client/features/using-large-value-types?view=sql-server-2017#sql-server-native-client-odbc-driver.

常见的解决方法是将返回的数据强制转换为 NVARCHAR(n) ,用某个值替换 n。The common workaround, is to cast the returned data to NVARCHAR(n), with some value for n. 例如 NVARCHAR(4000)。For example, NVARCHAR(4000). 但是,这种解决方法对于 Azure 数据资源管理器将不起作用,因为 Azure 数据资源管理器只有一个字符串类型,并且对于 SQL 客户端,它将编码为 NVARCHAR(MAX)。Such a workaround, however, won't work for Azure Data Explorer, since Azure Data Explorer has only one string type and for SQL clients it's encoded as NVARCHAR(MAX).

Azure 数据资源管理器提供了另一种解决方法。Azure Data Explorer offers a different workaround. 你可以通过连接字符串将 Azure 数据资源管理器配置为将所有字符串编码为 NVARCHAR(n)。You can configure Azure Data Explorer to encode all strings as NVARCHAR(n) via a connection string. 可以使用连接字符串中的 language 字段以“language@OptionName1:OptionValue1,OptionName2:OptionValue2”格式指定优化选项。The language field in the connection string can be used to specify tuning options in the format, *language@OptionName1:OptionValue1,OptionName2:OptionValue2*.

例如,以下连接字符串会指示 Azure 数据资源管理器将字符串编码为 NVARCHAR(8000)。For example, the following connection string will instruct Azure Data Explorer to encode strings as NVARCHAR(8000).

"Driver={ODBC Driver 17 for SQL Server};Server=mykustocluster.kusto.chinacloudapi.cn;Database=mykustodatabase;Authentication=ActiveDirectoryIntegrated,Language=any@MaxStringSize:8000"

PowerShellPowerShell

下面是使用 ODBC 驱动程序的 PowerShell 脚本的示例。Here is an example of the PowerShell script that uses the ODBC driver.

$conn = [System.Data.Common.DbProviderFactories]::GetFactory("System.Data.Odbc").CreateConnection()
$conn.ConnectionString = "Driver={ODBC Driver 17 for SQL Server};Server=mykustocluster.kusto.chinacloudapi.cn;Database=mykustodatabase;Authentication=ActiveDirectoryIntegrated"
$conn.Open()
$conn.GetSchema("Tables")
$conn.Close()

LINQPadLINQPad

可以通过将 Linq 应用程序像 SQL Server 一样进行连接,将其与 Azure 数据资源管理器一起使用。A Linq application can be used with Azure Data Explorer, by connecting it like it's an SQL server. 可以使用 LINQPad 探究 Linq 兼容性并浏览 Azure 数据资源管理器。Use LINQPad to explore Linq compatibility and to browse Azure Data Explorer. 它还可以执行 SQL 查询。建议使用此工具来浏览 Azure 数据资源管理器 TDS (SQL) 终结点。It can also execute SQL queries, and is the recommended tool to explore Azure Data Explorer TDS (SQL) endpoints.

像往常一样连接到 Microsoft SQL Server。Connect like you do, to the Microsoft SQL Server. LINQPad 支持 Active Directory 身份验证。LINQPad supports Active Directory authentication.

  1. 选择“添加连接”。Select Add connection.
  2. 设置“自动生成数据上下文”。Set Build data context automatically.
  3. 设置“默认值(LINQ to SQL)”,即 LINQPad 驱动程序。Set Default (LINQ to SQL), the LINQPad driver.
  4. 设置“SQL Azure”。Set SQL Azure.
  5. 对于“服务器”,请指定 Azure 数据资源管理器群集的名称。For the server, specify the name of the Azure Data Explorer cluster. 例如“mykusto.kusto.chinacloudapi.cn”。For example, mykusto.kusto.chinacloudapi.cn.
  6. 为登录设置“Windows 身份验证(Active Directory)”。Set Windows Authentication (Active Directory), for signing in.
  7. 选择“测试”来验证连接性。Select Test to verify connectivity.
  8. 选择“确定” 。Select OK. 浏览器窗口将显示其中包含数据库的树状视图。The browser window displays the tree view with the databases.
  9. 你可以浏览数据库、表和列。You can browse through the databases, tables, and columns.
  10. 你可以在查询窗口中运行 SQL 查询。You can run SQL queries in the query window. 指定 SQL 语言,然后选择到数据库的连接。Specify the SQL language, and select a connection to the database.
  11. 你还可以在查询窗口中运行 LINQ 查询。You can also run LINQ queries in the query window. 例如,在浏览器窗口中选择一个表。For example, select a table in the browser window. 选择“计数”,并让它运行。Select Count, and let it run.

Azure Data Studio(1.3.4 及更高版本)Azure Data Studio (1.3.4 and above)

创建一个新连接。Make a new connection.

  1. 将连接类型设置为“Microsoft SQL Server”。Set the connection type to Microsoft SQL Server.
  2. 指定 Azure 数据资源管理器群集的名称作为服务器名称。Specify the name of the Azure Data Explorer cluster as a server name. 例如“mykusto.kusto.chinacloudapi.cn”。For example, mykusto.kusto.chinacloudapi.cn.
  3. 设置身份验证类型“Azure Active Directory - 支持 MFA 的通用方法”。Set the authentication type Azure Active Directory - Universal with MFA support.
  4. 指定在 Azure AD 中预配的帐户。Specify the account that is provisioned in the Azure AD. 例如, *myname@contoso.com* 。For example, *myname@contoso.com*. 首次添加该帐户。Add the account the first time.
  5. 使用数据库选取器选择数据库。Use Database picker to select the database.
  6. 选择“连接”以转到数据库仪表板,并设置连接。Select Connect to take you to the database dashboard and set the connection.
  7. 选择“新建查询”以打开查询窗口,或者在仪表板上选择“新建查询”任务。Select New Query to open the query window, or select the New Query task on the dashboard.

Power BI DesktopPower BI desktop

像往常一样连接到 SQL Azure 数据库。Connect like you do, to the SQL Azure Database.

  1. 在“获取数据”下,选择“更多”。Under Get Data, select More.
  2. 设置“Azure”,然后设置“Azure SQL 数据库”。 Set Azure, and then Azure SQL Database.
  3. 指定 Azure 数据资源管理器服务器名称。Specify the Azure Data Explorer server name. 例如“mykusto.kusto.chinacloudapi.cn”。For example, mykusto.kusto.chinacloudapi.cn.
  4. 选择“DirectQuery”。Select DirectQuery.
  5. 设置“Microsoft 帐户”身份验证(不是 Windows),然后选择“登录”。Set Microsoft account authentication (not Windows), and select sign in.
  6. 数据库选取器会显示可用的数据库。The database picker shows the available databases. 继续像之前那样操作,但使用真实的 SQL Server。Continue like you do, with a real SQL server.

ExcelExcel

像往常一样连接到 SQL Azure 数据库。Connect like you do, to the SQL Azure Database.

  1. 在“数据”选项卡下选择“获取数据”,然后设置“从 Azure”和“从 Azure SQL 数据库”。 Select Get Data under the Data tab, then set From Azure and From Azure SQL Database.
  2. 指定 Azure 数据资源管理器服务器名称。Specify the Azure Data Explorer server name. 例如“mykusto.kusto.chinacloudapi.cn”。For example, mykusto.kusto.chinacloudapi.cn.
  3. 设置“Microsoft 帐户”身份验证(不是 Windows),然后选择“登录”。Set Microsoft account authentication (not Windows), and select sign in.
  4. 数据库选取器会显示可用的数据库。The database picker shows the available databases. 继续像之前那样操作,但使用真实的 SQL Server。Continue like you do, with a real SQL server.
  5. 在登录后,选择“连接”。Once signed in, select Connect.
  6. 数据库选取器会显示可用的数据库。The database picker shows the available databases. 继续像之前那样操作,但使用真实的 SQL Server。Continue like you do, with a real SQL server.

TableauTableau

创建一个 ODBC 数据源。Create an ODBC data source. 有关详细信息,请参阅 ODBC 部分。For more information, see the ODBC section.

  1. 通过“其他数据库(ODBC)”进行连接。Connect via Other Databases (ODBC).
  2. 在“DSN”中设置 ODBC 数据源。Set the ODBC data source in DSN.
  3. 选择“连接”以建立连接。Select Connect to establish a connection.
  4. 选择“登录”(在按钮可用后),并登录到 Azure 数据资源管理器。Select Sign In, once the button is available, and sign in to Azure Data Explorer.

DBeaver(5.3.3 及更高版本)DBeaver (5.3.3 and above)

配置 DBeaver,以采用与 Azure 数据资源管理器兼容的方式处理结果集。Configure DBeaver for handling result sets in a manner that is compatible with Azure Data Explorer.

  1. 在“窗口”菜单中选择“首选项”。Select Preferences in the Window menu.
  2. 在“编辑器”部分中选择“数据编辑器”。Select Data Editor in the Editors section.
  3. 请确保选中“读取下个页面时刷新数据”。Make sure that Refresh data on next page reading is marked.

创建到 Azure 数据资源管理器数据库的连接。Create a connection to the Azure Data Explorer database.

  1. 在“数据库”菜单中选择“新建连接”。Select New Connection in the Database menu.
  2. 查找“Azure”,然后设置“Azure SQL 数据库”。 Look for Azure and set Azure SQL Database. 选择“下一步”。Select Next.
  3. 指定主机。Specify the host. 例如“mykusto.kusto.chinacloudapi.cn”。For example, mykusto.kusto.chinacloudapi.cn.
  4. 指定数据库。Specify the database. 例如“mydatabase”。For example, mydatabase.

警告

不要使用“master”作为数据库名称。Don't use master as the database name. Azure 数据资源管理器需要连接到特定数据库。Azure Data Explorer requires a connection to a specific database.

  1. 对于“身份验证”,请设置“Active Directory - 密码”。Set Active Directory - Password for Authentication.
  2. 指定 Active Directory 用户的凭据,Specify the credentials of the active directory user. 例如 myname@contoso.com,并为此用户设置相应的密码。For example, *myname@contoso.com*, and set the corresponding password for this user.
  3. 选择“测试连接 …”Select Test Connection … 以验证连接详细信息是否正确。to verify that the connection details are correct.

Microsoft SQL Server Management Studio (v18.x)Microsoft SQL Server Management Studio (v18.x)

  1. 选择“连接”,然后在“对象资源管理器”下选择“数据库引擎” 。Select Connect, and then Database Engine under Object Explorer.
  2. 指定 Azure 数据资源管理器群集的名称作为服务器名称。Specify the name of Azure Data Explorer cluster as a server name. 例如“mykusto.kusto.chinacloudapi.cn”。For example, mykusto.kusto.chinacloudapi.cn.
  3. 对于身份验证,请设置“Active Directory - 集成”。Set Active Directory - Integrated for authentication.
  4. 选择“选项”。Select Options.
  5. 在“连接到数据库”下选择“浏览服务器”以浏览可用数据库。 Select Browse Server under Connect to database to browse available databases.
  6. 选择“是”以继续浏览。Select Yes to continue browsing.
  7. 窗口将显示一个树状视图,其中包含所有可用的数据库。The window displays a tree view with all the available databases. 选择其中一个以连接到数据库。Select one, to connect to the database.
  8. 另一种可能的操作是,在“连接到数据库”下选择“默认”,然后选择“连接”。 Another possibility, is to select default under Connect to database, and then select Connect. 对象资源管理器将显示所有数据库。The object Explorer will display all the databases.

备注

尚不支持通过 SSMS 浏览数据库对象,因为 SSMS 使用关联子查询来浏览数据库架构。Browsing database objects via SSMS is not supported yet, since SSMS uses correlate subqueries to browse database schema. Azure 数据资源管理器不支持相关子查询。Correlated subqueries are not supported by Azure Data Explorer. 有关详细信息,请参阅相关子查询For more information, see correlated subqueries.

  1. 选择“新建查询”以打开查询窗口并设置数据库。Select New Query to open the query window and set your database.

你可以从查询窗口中运行自定义 SQL 查询。You can run custom SQL queries from the query window.

MATLAB(通过 JDBC)MATLAB (via JDBC)

通过在首选项目录中创建“javaclasspath.txt”文件,将所需的 JAR 文件添加到 MATLAB 的静态类路径的前面。Add the required JAR-files to the front of MATLAB's static classpath by creating a "javaclasspath.txt" file in your preferences directory.

  1. 在 Matlab 的命令窗口中运行以下命令。Run the following command in Matlab's command window.
edit(fullfile(prefdir,'javaclasspath.txt'))
  1. 添加所需的 JAR 文件的完整路径。Add the full paths to the required JAR-files.
<before>
c:\full\path\to\accessors-smart-1.2.jar
c:\full\path\to\activation-1.1.jar
c:\full\path\to\adal4j-1.6.3.jar
c:\full\path\to\asm-5.0.4.jar
c:\full\path\to\commons-codec-1.11.jar
c:\full\path\to\commons-lang3-3.5.jar
c:\full\path\to\gson-2.8.0.jar
c:\full\path\to\javax.mail-1.6.1.jar
c:\full\path\to\jcip-annotations-1.0-1.jar
c:\full\path\to\json-smart-2.3.jar
c:\full\path\to\lang-tag-1.4.4.jar
c:\full\path\to\mssql-jdbc-7.0.0.jre8.jar
c:\full\path\to\nimbus-jose-jwt-6.5.jar
c:\full\path\to\oauth2-oidc-sdk-5.64.4.jar
c:\full\path\to\slf4j-api-1.7.21.jar

备注

顶部需要有 <before>,以便将这些文件添加到类路径的前面。You need the <before> at the top, so that these files are added to the front of the classpath. 另外,请将“c:\full\path\to”替换为这些文件的实际完整路径。Also, replace c:\full\path\to with the actual full paths to these files.

  1. 重启 MATLAB 以确保加载这些类。Restart MATLAB to make sure that these classes are loaded.

  2. 尝试连接之前,请运行以下命令(MATLAB 命令窗口)。Before trying to connect, run the following command (MATLAB command window).

java.lang.System.clearProperty('javax.xml.transform.TransformerFactory')

备注

这会将 TransformerFactory 重置为默认值(MATLAB 通常通过 Saxon 重载此项,但这与 ADAL4J 不兼容)。This resets the TransformerFactory to the default (MATLAB usually overloads this with Saxon, but this is incompatible with ADAL4J).

  1. 使用以下命令(MATLAB 命令窗口)连接到 Azure 数据资源管理器 TDS 终结点。Connect to the Azure Data Explorer TDS endpoint with the following command (MATLAB command window).
conn = database('<<KUSTO_DATABASE>>','<<AAD_USER>>','<<USER_PWD>>','com.microsoft.sqlserver.jdbc.SQLServerDriver',['jdbc:sqlserver://<<MYCLUSTER>>.kusto.chinacloudapi.cn:1433;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.kusto.chinacloudapi.cn;loginTimeout=30;authentication=ActiveDirectoryPassword;database='])

备注

"database=" 结尾是可以的,那样将没有任何值。It's OK to end with "database=" and then no value. database 函数会自动将第一个输入(数据库名称)追加到此字符串。The database function will automatically append the first input, the database name, to this string. 如果要使用 Azure Active Directory 集成身份验证模式,请将 ActiveDirectoryPassword 替换为 ActiveDirectoryIntegrated。If you want to use Azure Active Directory integrated authentication mode, then replace ActiveDirectoryPassword with ActiveDirectoryIntegrated.

  1. 测试连接并运行示例查询。Test the connection and run a sample query. 提交以下命令(MATLAB 命令窗口)。Submit the following commands (MATLAB command window).
data = select(conn, 'SELECT * FROM <<KUSTO_TABLE>>')
data

备注

将 KUSTO_TABLE 替换为 Azure 数据资源管理器中的某个现有表。Replace KUSTO_TABLE with an existing table in Azure Data Explorer.

通过 REST API 发送 T-SQL 查询Sending T-SQL queries over the REST API

Azure 数据资源管理器 REST API 可以接受和执行 T-SQL 查询。The Azure Data Explorer REST API can accept and execute T-SQL queries.

  1. 将请求发送到查询终结点,并将 csl 属性设置为 T-SQL 查询的文本。Send the request to the query endpoint with the csl property set to the text of the T-SQL query.
  2. 请求属性 OptionQueryLanguage 设置为 sqlSet request property OptionQueryLanguage to sql.