使用 BI 分析工具和 ODBC 驱动程序连接到 Azure Cosmos DBConnect to Azure Cosmos DB using BI analytics tools with the ODBC driver

借助 Azure Cosmos DB ODBC 驱动程序,可以使用 SQL Server Integration Services、Power BI Desktop 和 Tableau 等 BI 分析工具连接到 Azure Cosmos DB,以便能够在这些解决方案中分析和创建 Azure Cosmos DB 数据的可视化效果。The Azure Cosmos DB ODBC driver enables you to connect to Azure Cosmos DB using BI analytics tools such as SQL Server Integration Services, Power BI Desktop, and Tableau so that you can analyze and create visualizations of your Azure Cosmos DB data in those solutions.

Azure Cosmos DB ODBC 驱动程序符合 ODBC 3.8 规范,支持 ANSI SQL-92 语法。The Azure Cosmos DB ODBC driver is ODBC 3.8 compliant and supports ANSI SQL-92 syntax. 该驱动程序提供丰富的功能来帮助用户重新规范化 Azure Cosmos DB 中的数据。The driver offers rich features to help you renormalize data in Azure Cosmos DB. 使用该驱动程序,能够以表和视图的形式表示 Azure Cosmos DB 中的数据。Using the driver, you can represent data in Azure Cosmos DB as tables and views. 使用该驱动程序还可以针对表和视图执行 SQL 操作,包括 group by 查询、插入、更新和删除。The driver enables you to perform SQL operations against the tables and views including group by queries, inserts, updates, and deletes.

备注

目前仅 Azure Cosmos DB SQL API 帐户支持使用 ODBC 驱动程序连接到 Azure Cosmos DB。Connecting to Azure Cosmos DB with the ODBC driver is currently supported for Azure Cosmos DB SQL API accounts only.

为何需要将数据规范化?Why do I need to normalize my data?

Azure Cosmos DB 是一个无架构数据库,可用于快速开发应用程序并对数据模型进行迭代,不受限于严格的架构。Azure Cosmos DB is a schemaless database, which enables rapid application development and the ability to iterate on data models without being confined to a strict schema. 一个 Azure Cosmos 数据库可以包含各种结构的 JSON 文档。A single Azure Cosmos database can contain JSON documents of various structures. 这非常适合快速应用程序开发,但如果用户想要使用数据分析和 BI 工具来分析和创建数据报表,则数据通常需要平展并遵守特定的架构。This is great for rapid application development, but when you want to analyze and create reports of your data using data analytics and BI tools, the data often needs to be flattened and adhere to a specific schema.

这就是 ODBC 驱动程序的作用所在。This is where the ODBC driver comes in. 通过使用 ODBC 驱动程序,你现在可以将 Azure Cosmos DB 中的数据重新规范化为适合数据分析和报告需求的表和视图。By using the ODBC driver, you can now renormalize data in Azure Cosmos DB into tables and views that fit your data analytics and reporting needs. 重新规范化的架构不会对基础数据造成影响,不强制要求开发人员遵守。The renormalized schemas have no impact on the underlying data and do not confine developers to adhere to them. 相反,它们使你可以利用符合 ODBC 规范的工具来访问数据。Rather, they enable you to leverage ODBC-compliant tools to access the data. 因此,现在 Azure Cosmos 数据库不仅是开发团队的最爱,数据分析师也对它青睐有加。So, now your Azure Cosmos database will not only be a favorite for your development team, but your data analysts will love it too.

接下来让我们开始使用 ODBC 驱动程序。Let's get started with the ODBC driver.

步骤 1:安装 Azure Cosmos DB ODBC 驱动程序Step 1: Install the Azure Cosmos DB ODBC driver

  1. 下载适用于环境的驱动程序:Download the drivers for your environment:

    安装程序Installer 支持的操作系统Supported operating systems
    Azure Cosmos DB ODBC 64-bit.msi(适用于 64 位 Windows)Azure Cosmos DB ODBC 64-bit.msi for 64-bit Windows 64 位的 Windows 8.1 或更高版本、Windows 8、Windows 7、Windows Server 2012 R2、Windows Server 2012 和 Windows Server 2008 R2。64-bit versions of Windows 8.1 or later, Windows 8, Windows 7, Windows Server 2012 R2, Windows Server 2012, and Windows Server 2008 R2.
    Azure Cosmos DB ODBC 32x64-bit.msi(适用于 32 位或 64 位 Windows)Azure Cosmos DB ODBC 32x64-bit.msi for 32-bit on 64-bit Windows 64 位的 Windows 8.1 或更高版本、Windows 8、Windows 7、Windows XP、Windows Vista、Windows Server 2012 R2、Windows Server 2012、Windows Server 2008 R2 和 Windows Server 2003。64-bit versions of Windows 8.1 or later, Windows 8, Windows 7, Windows XP, Windows Vista, Windows Server 2012 R2, Windows Server 2012, Windows Server 2008 R2, and Windows Server 2003.
    Azure Cosmos DB ODBC 32-bit.msi(适用于 32 位 Windows)Azure Cosmos DB ODBC 32-bit.msi for 32-bit Windows 32 位的 Windows 8.1 或更高版本、Windows 8、Windows 7、Windows XP 和 Windows Vista。32-bit versions of Windows 8.1 or later, Windows 8, Windows 7, Windows XP, and Windows Vista.

    在本地运行 msi 文件,启动 Azure Cosmos DB ODBC 驱动程序安装向导Run the msi file locally, which starts the Azure Cosmos DB ODBC Driver Installation Wizard.

  2. 使用默认输入完成安装向导,安装 ODBC 驱动程序。Complete the installation wizard using the default input to install the ODBC driver.

  3. 在计算机上打开 ODBC 数据源管理器应用。Open the ODBC Data source Administrator app on your computer. 可以通过在 Windows 搜索框中键入 ODBC 数据源来执行此操作。You can do this by typing ODBC Data sources in the Windows search box. 可通过单击“驱动程序”选项卡并检查“Azure Cosmos DB ODBC 驱动程序”是否已列出,来确认是否已安装该驱动程序。 You can confirm the driver was installed by clicking the Drivers tab and ensuring Azure Cosmos DB ODBC Driver is listed.

    Azure Cosmos DB ODBC 数据源管理器

步骤 2:连接到 Azure Cosmos 数据库Step 2: Connect to your Azure Cosmos database

  1. 安装 Azure Cosmos DB ODBC 驱动程序后,在“ODBC 数据源管理器”窗口中单击“添加”。 After Installing the Azure Cosmos DB ODBC driver, in the ODBC Data Source Administrator window, click Add. 可以创建一个用户 DSN 或系统 DSN。You can create a User or System DSN. 在本示例中,将创建一个用户 DSN。In this example, you are creating a User DSN.

  2. 在“创建新数据源”窗口中选择“Azure Cosmos DB ODBC 驱动程序”,然后单击“完成”。 In the Create New Data Source window, select Azure Cosmos DB ODBC Driver, and then click Finish.

  3. 在“Azure Cosmos DB ODBC 驱动程序 SDN 设置” 窗口中填写以下信息:In the Azure Cosmos DB ODBC Driver SDN Setup window, fill in the following information:

    “Azure Cosmos DB ODBC 驱动程序 DSN 设置”窗口

    • 数据源名称:ODBC DSN 的友好名称。Data Source Name: Your own friendly name for the ODBC DSN. 此名称对于 Azure Cosmos DB 帐户是唯一的,因此,如果有多个帐户,请适当地为数据源命名。This name is unique to your Azure Cosmos DB account, so name it appropriately if you have multiple accounts.

    • 说明:数据源的简短说明。Description: A brief description of the data source.

    • 主机:Azure Cosmos DB 帐户的 URI。Host: URI for your Azure Cosmos DB account. 可在 Azure 门户的“Azure Cosmos DB 密钥”页中检索此信息,如以下屏幕截图所示。You can retrieve this from the Azure Cosmos DB Keys page in the Azure portal, as shown in the following screenshot.

    • 访问密钥:从 Azure 门户中“Azure Cosmos DB 密钥”页获取的主要或辅助读写或只读密钥,如以下屏幕截图所示。Access Key: The primary or secondary, read-write or read-only key from the Azure Cosmos DB Keys page in the Azure portal as shown in the following screenshot. 如果 DSN 用于只读数据的处理和报告,我们建议使用只读密钥。We recommend you use the read-only key if the DSN is used for read-only data processing and reporting.

      “Azure Cosmos DB 密钥”页

    • 加密以下对象的访问密钥:根据此计算机的用户选择最合适的选项。Encrypt Access Key for: Select the best choice based on the users of this machine.

  4. 单击“测试”按钮,确保可以连接到 Azure Cosmos DB 帐户。 Click the Test button to make sure you can connect to your Azure Cosmos DB account.

  5. 单击“高级选项” 并设置以下值:Click Advanced Options and set the following values:

    • REST API 版本:选择操作的 REST API 版本REST API Version: Select the REST API version for your operations. 默认为 2015-12-16。The default 2015-12-16. 如果有使用大分区键的容器且需要 REST API 版本 2018-12-31,请执行以下操作:If you have containers with large partition keys and require REST API version 2018-12-31:
      • 键入 2018-12-31 作为 REST API 版本Type in 2018-12-31 for REST API version

      • 在“开始”菜单中 键入“regedit”,找到并打开“注册表编辑器” 应用程序。In the Start menu, type "regedit" to find and open the Registry Editor application.

      • 在注册表编辑器中,导航到此路径:Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INIIn Registry Editor, navigate to the path: Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI

      • 创建新子项,使用与 DSN 相同的名称,例如“Contoso Account ODBC DSN”。Create a new subkey with the same name as your DSN, e.g. "Contoso Account ODBC DSN".

      • 导航到“Contoso Account ODBC DSN”子项。Navigate to the "Contoso Account ODBC DSN" subkey.

      • 通过右键单击添加新的字符串值:Right-click to add a new String value:

        • 值名称:IgnoreSessionTokenValue Name: IgnoreSessionToken
        • 值数据:1Value data: 1

        注册表编辑器设置

    • 查询一致性:选择操作的一致性级别Query Consistency: Select the consistency level for your operations. 默认值为“会话”。The default is Session.
    • 重试次数:输入当初始请求因服务速率限制而未能完成时,重试操作的次数。Number of Retries: Enter the number of times to retry an operation if the initial request does not complete due to service rate limiting.
    • 架构文件:此处有多个选项供你选择。Schema File: You have a number of options here.
      • 默认情况下,可将此项保留原样(空白),驱动程序会在第一页的数据中扫描所有容器,确定每个容器的架构。By default, leaving this entry as is (blank), the driver scans the first page of data for all containers to determine the schema of each container. 这称为“容器映射”。This is known as Container Mapping. 如果未定义架构文件,驱动程序必须针对每个驱动程序会话执行扫描,这可能会导致使用 DSN 启动应用程序时需要更长的时间。Without a schema file defined, the driver has to perform the scan for each driver session and could result in a higher startup time of an application using the DSN. 我们建议始终关联 DSN 的架构文件。We recommend that you always associate a schema file for a DSN.
      • 如果已有一个架构文件(也许是使用架构编辑器创建的文件),则可以单击“浏览”,导航到该文件,单击“保存”,并单击“确定”。 If you already have a schema file (possibly one that you created using the Schema Editor), you can click Browse, navigate to your file, click Save, and then click OK.
      • 如果想要创建新架构,请单击“确定”,并在主窗口中单击“架构编辑器”。 If you want to create a new schema, click OK, and then click Schema Editor in the main window. 接下来,继续在架构编辑器中提供信息。Then proceed to the Schema Editor information. 创建新架构文件后,请记得返回“高级选项”窗口以包含新建的架构文件 。After creating the new schema file, remember to go back to the Advanced Options window to include the newly created schema file.
  6. 完成并关闭“Azure Cosmos DB ODBC 驱动程序 DSN 设置” 窗口后,新的用户 DSN 会添加到“用户 DSN”选项卡。Once you complete and close the Azure Cosmos DB ODBC Driver DSN Setup window, the new User DSN is added to the User DSN tab.

    “用户 DSN”选项卡上的新 Azure Cosmos DB ODBC DSN

步骤 3:使用容器映射方法创建架构定义Step 3: Create a schema definition using the container mapping method

可以使用两种类型的采样方法:容器映射表分隔符There are two types of sampling methods that you can use: container mapping or table-delimiters. 采样会话可以利用这两种采样方法,但每个容器只能使用特定的采样方法。A sampling session can utilize both sampling methods, but each container can only use a specific sampling method. 以下步骤使用容器映射方法为一个或多个容器中的数据创建架构。The steps below create a schema for the data in one or more containers using the container mapping method. 此采样方法会检索容器页面中的数据,确定数据的结构。This sampling method retrieves the data in the page of a container to determine the structure of the data. 它会将容器转置到 ODBC 端的某个表。It transposes a container to a table on the ODBC side. 如果容器中的数据是同构的,此采样方法十分快速高效。This sampling method is efficient and fast when the data in a container is homogenous. 如果容器包含异构类型的数据,我们建议使用表分隔符映射方法,因为它提供了确定容器中数据结构的更可靠采样方法。If a container contains heterogeneous type of data, we recommend you use the table-delimiters mapping method as it provides a more robust sampling method to determine the data structures in the container.

  1. 完成连接到 Azure Cosmos 数据库中的步骤 1-4 后,请在“Azure Cosmos DB ODBC 驱动程序 DSN 设置”窗口中单击“架构编辑器”。 After completing steps 1-4 in Connect to your Azure Cosmos database, click Schema Editor in the Azure Cosmos DB ODBC Driver DSN Setup window.

    “Azure Cosmos DB ODBC 驱动程序 DSN 设置”窗口中的架构编辑器按钮

  2. 在“架构编辑器”窗口中单击“新建”。 In the Schema Editor window, click Create New. “生成架构”窗口会显示 Azure Cosmos DB 帐户中的所有容器。 The Generate Schema window displays all the containers in the Azure Cosmos DB account.

  3. 选择要采样的一个或多个容器,并单击“采样” 。Select one or more containers to sample, and then click Sample.

  4. “设计视图”选项卡中显示了数据库、架构和表。 In the Design View tab, the database, schema, and table are represented. 在表视图中,扫描会显示与列名称(“SQL 名称”、“源名称”等)关联的属性集。In the table view, the scan displays the set of properties associated with the column names (SQL Name, Source Name, etc.). 可以修改每个列,包括“SQL 名称”、“SQL 类型”、“SQL 长度”(如果适用)、“小数位数”(如果适用)、“精度”(如果适用)和“可为 Null”。For each column, you can modify the column SQL name, the SQL type, SQL length (if applicable), Scale (if applicable), Precision (if applicable) and Nullable.

    • 如果想要从查询结果中排除某个列,可将对应的“隐藏列”设置为 trueYou can set Hide Column to true if you want to exclude that column from query results. 标记为“隐藏列 = true”的列不会返回供选择和投影,不过它们仍是架构的一部分。Columns marked Hide Column = true are not returned for selection and projection, although they are still part of the schema. 例如,可以隐藏以“”开头的所有 Azure Cosmos DB 系统必需属性。For example, you can hide all of the Azure Cosmos DB system required properties starting with "".
    • “Id”列是唯一不能隐藏的字段,因为它用作规范化架构中的主键。 The id column is the only field that cannot be hidden as it is used as the primary key in the normalized schema.
  5. 完成定义架构后,请单击“文件” | “保存”,导航到用于保存该架构的目录,并单击“保存”。 Once you have finished defining the schema, click File | Save, navigate to the directory to save the schema, and then click Save.

  6. 若要配合 DSN 使用此架构,请打开“Azure Cosmos DB ODBC 驱动程序 DSN 设置”窗口(通过“ODBC 数据源管理器”),单击“高级选项”,并在“架构文件”框中导航到保存的架构。 To use this schema with a DSN, open the Azure Cosmos DB ODBC Driver DSN Setup window (via the ODBC Data Source Administrator), click Advanced Options, and then in the Schema File box, navigate to the saved schema. 将架构文件保存到现有 DSN 会将 DSN 连接范围修改为架构定义的数据和结构。Saving a schema file to an existing DSN modifies the DSN connection to scope to the data and structure defined by schema.

步骤 4:使用表分隔符映射方法创建架构定义Step 4: Create a schema definition using the table-delimiters mapping method

可以使用两种类型的采样方法:容器映射表分隔符There are two types of sampling methods that you can use: container mapping or table-delimiters. 采样会话可以利用这两种采样方法,但每个容器只能使用特定的采样方法。A sampling session can utilize both sampling methods, but each container can only use a specific sampling method.

以下步骤使用表分隔符映射方法为一个或多个容器中的数据创建架构。The following steps create a schema for the data in one or more containers using the table-delimiters mapping method. 如果容器包含异构类型的数据,我们建议使用此采样方法。We recommend that you use this sampling method when your containers contain heterogeneous type of data. 可以使用此方法将采样范围限定为一组属性及其对应的值。You can use this method to scope the sampling to a set of attributes and its corresponding values. 例如,如果某个文档包含“Type”属性,可将采样范围限定为此属性的值。For example, if a document contains a "Type" property, you can scope the sampling to the values of this property. 采样的最终结果为针对每个指定 Type 的值返回的一组表。The end result of the sampling would be a set of tables for each of the values for Type you have specified. 例如,Type = Car 会生成 Car 表,Type = Plane 会生成 Plane 表。For example, Type = Car will produce a Car table while Type = Plane would produce a Plane table.

  1. 完成连接到 Azure Cosmos 数据库中的步骤 1-4 后,请在“Azure Cosmos DB ODBC 驱动程序 DSN 设置”窗口中单击“架构编辑器”。 After completing steps 1-4 in Connect to your Azure Cosmos database, click Schema Editor in the Azure Cosmos DB ODBC Driver DSN Setup window.

  2. 在“架构编辑器”窗口中单击“新建”。 In the Schema Editor window, click Create New. “生成架构”窗口会显示 Azure Cosmos DB 帐户中的所有容器。 The Generate Schema window displays all the containers in the Azure Cosmos DB account.

  3. 在“采样视图”选项卡上的容器“映射定义”列中选择一个容器,并单击“编辑”。 Select a container on the Sample View tab, in the Mapping Definition column for the container, click Edit. 在“映射定义”窗口中,选择“表分隔符”方法。 Then in the Mapping Definition window, select Table Delimiters method. 然后执行以下操作:Then do the following:

    a.a. 在“属性”框中,键入分隔符属性的名称。 In the Attributes box, type the name of a delimiter property. 这是文档中要将采样范围限定到的属性(例如 City)。并按 Enter 键。This is a property in your document that you want to scope the sampling to, for instance, City and press enter.

    b.b. 如果只想将采样范围限定为你在上面输入的属性的某些值,请在选择框中选择该属性,在“值”框中输入一个值(例如 Seattle),然后按 Enter 键。 If you only want to scope the sampling to certain values for the attribute you entered above, select the attribute in the selection box, enter a value in the Value box (e.g. Seattle), and press enter. 可以继续添加属性的多个值。You can continue to add multiple values for attributes. 只需确保在输入值时选择正确的属性。Just ensure that the correct attribute is selected when you're entering values.

    例如,如果包含 City 的 Attributes 值,并想要将表限制为仅包含带有 Beijing 和 Dubai 城市值的行,请在“属性”框中输入 City,在“值”框中输入 Beijing 和 Dubai。 For example, if you include an Attributes value of City, and you want to limit your table to only include rows with a city value of Beijing and Dubai, you would enter City in the Attributes box, and Beijing and then Dubai in the Values box.

  4. 单击 “确定”Click OK.

  5. 完成想要采样的容器的映射定义后,请在“架构编辑器”窗口中单击“采样”。 After completing the mapping definitions for the containers you want to sample, in the Schema Editor window, click Sample. 可以修改每个列,包括“SQL 名称”、“SQL 类型”、“SQL 长度”(如果适用)、“小数位数”(如果适用)、“精度”(如果适用)和“可为 Null”。For each column, you can modify the column SQL name, the SQL type, SQL length (if applicable), Scale (if applicable), Precision (if applicable) and Nullable.

    • 如果想要从查询结果中排除某个列,可将对应的“隐藏列”设置为 trueYou can set Hide Column to true if you want to exclude that column from query results. 标记为“隐藏列 = true”的列不会返回供选择和投影,不过它们仍是架构的一部分。Columns marked Hide Column = true are not returned for selection and projection, although they are still part of the schema. 例如,可以隐藏以 _ 开头的所有 Azure Cosmos DB 系统必需属性。For example, you can hide all the Azure Cosmos DB system required properties starting with _.
    • “Id”列是唯一不能隐藏的字段,因为它用作规范化架构中的主键。 The id column is the only field that cannot be hidden as it is used as the primary key in the normalized schema.
  6. 完成定义架构后,请单击“文件” | “保存”,导航到用于保存该架构的目录,并单击“保存”。 Once you have finished defining the schema, click File | Save, navigate to the directory to save the schema, and then click Save.

  7. 返回“Azure Cosmos DB ODBC 驱动程序 DSN 设置”窗口,单击“高级选项” 。Back in the Azure Cosmos DB ODBC Driver DSN Setup window, click Advanced Options. 然后,在“架构文件”框中,导航到保存的架构文件并单击“确定”。 Then, in the Schema File box, navigate to the saved schema file and click OK. 再次单击“确定”保存 DSN。 Click OK again to save the DSN. 这会将创建的架构保存到 DSN。This saves the schema you created to the DSN.

(可选)设置链接服务器连接(Optional) Set up linked server connection

通过设置链接服务器连接,可以从 SQL Server Management Studio (SSMS) 查询 Azure Cosmos DB。You can query Azure Cosmos DB from SQL Server Management Studio (SSMS) by setting up a linked server connection.

  1. 步骤 2 中所述创建系统数据源并为其命名,如 SDS NameCreate a system data source as described in Step 2, named for example SDS Name.

  2. 安装 SQL Server Management Studio 并连接到服务器。Install SQL Server Management Studio and connect to the server.

  3. 在 SSMS 查询编辑器中,使用以下命令为数据源创建链接服务器对象 DEMOCOSMOSIn the SSMS query editor, create a linked server object DEMOCOSMOS for the data source with the following commands. DEMOCOSMOS 替换为链接服务器的名称,并将 SDS Name 替换为系统数据源的名称。Replace DEMOCOSMOS with the name for your linked server, and SDS Name with the name of your system data source.

    USE [master]
    GO
    
    EXEC master.dbo.sp_addlinkedserver @server = N'DEMOCOSMOS', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'SDS Name'
    
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DEMOCOSMOS', @useself=N'False', @locallogin=NULL, @rmtuser=NULL, @rmtpassword=NULL
    
    GO
    

要查看新的链接服务器名称,请刷新链接服务器列表。To see the new linked server name, refresh the Linked Servers list.

SSMS 中的链接服务器

查询链接的数据库Query linked database

若要查询链接的数据库,请输入 SSMS 查询。To query the linked database, enter an SSMS query. 在此示例中,查询从名为 customers 的容器的表中选择:In this example, the query selects from the table in the container named customers:

SELECT * FROM OPENQUERY(DEMOCOSMOS, 'SELECT * FROM [customers].[customers]')

执行查询。Execute the query. 结果应如下所示:The result should be similar to this:

attachments/  1507476156    521 Bassett Avenue, Wikieup, Missouri, 5422   "2602bc56-0000-0000-0000-59da42bc0000"   2015-02-06T05:32:32 +05:00 f1ca3044f17149f3bc61f7b9c78a26df
attachments/  1507476156    167 Nassau Street, Tuskahoma, Illinois, 5998   "2602bd56-0000-0000-0000-59da42bc0000"   2015-06-16T08:54:17 +04:00 f75f949ea8de466a9ef2bdb7ce065ac8
attachments/  1507476156    885 Strong Place, Cassel, Montana, 2069       "2602be56-0000-0000-0000-59da42bc0000"   2015-03-20T07:21:47 +04:00 ef0365fb40c04bb6a3ffc4bc77c905fd
attachments/  1507476156    515 Barwell Terrace, Defiance, Tennessee, 6439     "2602c056-0000-0000-0000-59da42bc0000"   2014-10-16T06:49:04 +04:00      e913fe543490432f871bc42019663518
attachments/  1507476156    570 Ruby Street, Spokane, Idaho, 9025       "2602c156-0000-0000-0000-59da42bc0000"   2014-10-30T05:49:33 +04:00 e53072057d314bc9b36c89a8350048f3

备注

链接的 Cosmos DB 服务器不支持由四部分组成的命名。The linked Cosmos DB server does not support four-part naming. 将返回类似于以下消息的错误:An error is returned similar to the following message:

Msg 7312, Level 16, State 1, Line 44

Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "DEMOCOSMOS". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

(可选)创建视图(Optional) Creating views

可在采样过程中定义和创建视图。You can define and create views as part of the sampling process. 这些视图相当于 SQL 视图。These views are equivalent to SQL views. 它们是只读的,并且是定义的 Azure Cosmos DB SQL 查询的选择和投影范围。They are read-only and are scope the selections and projections of the Azure Cosmos DB SQL query defined.

若要创建数据的视图,请在“架构编辑器”窗口中的“视图定义”列内,单击要采样的容器行中的“添加”。 To create a view for your data, in the Schema Editor window, in the View Definitions column, click Add on the row of the container to sample. 创建数据视图Create a view of data

然后在“视图定义” 窗口中执行以下操作:Then in the View Definitions window, do the following:

  1. 单击“新建”,输入视图的名称(例如 EmployeesfromSeattleView),并单击“确定”。 Click New, enter a name for the view, for example, EmployeesfromSeattleView and then click OK.

  2. 在“编辑视图”窗口中,输入一个 Azure Cosmos DB 查询。 In the Edit view window, enter an Azure Cosmos DB query. 这必须是一个 Azure Cosmos DB SQL 查询(例如 SELECT c.City, c.EmployeeName, c.Level, c.Age, c.Manager FROM c WHERE c.City = "Seattle"),然后单击“确定”。 This must be an Azure Cosmos DB SQL query, for example SELECT c.City, c.EmployeeName, c.Level, c.Age, c.Manager FROM c WHERE c.City = "Seattle", and then click OK.

    创建视图时添加查询

可以创建任意数量的视图。You can create a many views as you like. 定义完视图后,可以采样数据。Once you are done defining the views, you can then sample the data.

步骤 5:在 Power BI Desktop 等 BI 工具中查看数据Step 5: View your data in BI tools such as Power BI Desktop

可以使用新 DSN 将 Azure Cosmos DB 连接到任何符合 ODBC 规范的工具 - 本步骤仅说明如何连接到 Power BI Desktop 并创建 Power BI 可视化效果。You can use your new DSN to connect to Azure Cosmos DB with any ODBC-compliant tools - this step simply shows you how to connect to Power BI Desktop and create a Power BI visualization.

  1. 打开 Power BI Desktop。Open Power BI Desktop.

  2. 单击“获取数据”。 Click Get Data.

    在 Power BI Desktop 中获取数据

  3. 在“获取数据”窗口中,单击“其他” | “ODBC” | “连接”。 In the Get Data window, click Other | ODBC | Connect.

    在 Power BI“获取数据”中选择 ODBC 数据源

  4. 在“从 ODBC”窗口中,选择创建的数据源名称,并单击“确定”。 In the From ODBC window, select the data source name you created, and then click OK. 可将“高级选项”项保留空白。 You can leave the Advanced Options entries blank.

    在 Power BI“获取数据”中选择数据源名称 (DSN)

  5. 在“使用 ODBC 驱动程序访问数据源”窗口中,选择“默认或自定义”,并单击“连接”。 In the Access a data source using an ODBC driver window, select Default or Custom and then click Connect. 不需要包括“凭据连接字符串属性”。 You do not need to include the Credential connection string properties.

  6. 在“导航器” 窗口的左窗格中,展开数据库和架构,并选择表。In the Navigator window, in the left pane, expand the database, the schema, and then select the table. 结果窗格包含使用所创建的架构的数据。The results pane includes the data using the schema you created.

    在 Power BI“获取数据”中选择表

  7. 如果要在 Power BI Desktop 中可视化数据,请选中表名称前面的框,并单击“加载” 。To visualize the data in Power BI desktop, check the box in front of the table name, and then click Load.

  8. 在 Power BI Desktop 的最左侧,选择“数据”选项卡In Power BI Desktop, on the far left, select the Data tab Power BI Desktop 中的“数据”选项卡 确认数据是否已导入。to confirm your data was imported.

  9. 现在,可以使用 Power BI 创建视觉对象:单击“报表”选项卡Power BI Desktop 中的“报表”选项卡,单击“新建视觉对象”,并自定义磁贴。 You can now create visuals using Power BI by clicking on the Report tab Report tab in Power BI Desktop, clicking New Visual, and then customizing your tile. 有关在 Power BI Desktop 中创建可视化效果的详细信息,请参阅 Power BI 中的可视化效果类型For more information about creating visualizations in Power BI Desktop, see Visualization types in Power BI.

故障排除Troubleshooting

如果遇到以下错误,请确保执行步骤 2 时在 Azure 门户中复制的“主机”和“访问密钥”值正确,并重试。 If you receive the following error, ensure the Host and Access Key values you copied the Azure portal in Step 2 are correct and then retry. 在 Azure 门户中使用“主机”和“访问密钥”值右侧的复制按钮可以正确无误地复制这些值。 Use the copy buttons to the right of the Host and Access Key values in the Azure portal to copy the values error free.

[HY000]: [Microsoft][Azure Cosmos DB] (401) HTTP 401 Authentication Error: {"code":"Unauthorized","message":"The input authorization token can't serve the request. Please check that the expected payload is built as per the protocol, and check the key being used. Server used the following payload to sign: 'get\ndbs\n\nfri, 20 jan 2017 03:43:55 gmt\n\n'\r\nActivityId: 9acb3c0d-cb31-4b78-ac0a-413c8d33e373"}`

后续步骤Next steps

若要了解有关 Azure Cosmos DB 的详细信息,请参阅欢迎使用 Azure Cosmos DBTo learn more about Azure Cosmos DB, see Welcome to Azure Cosmos DB.