使用无服务器 SQL 池查询 Azure Cosmos DB 数据

借助无服务器 SQL 池，可以近乎实时地分析 Azure Cosmos DB 容器中的数据，这些容器启用了 Azure Synapse Link，而不会影响事务工作负荷的性能。 它提供了一种熟悉的 Transact-SQL（T-SQL）语法，用于通过 T-SQL 接口查询 分析存储 中的数据和与各种商业智能（BI）和临时查询工具的集成连接。

对于查询 Azure Cosmos DB，可以通过 OPENROWSET 函数支持完整的 SELECT 功能范围，其中包括大多数 SQL 函数和运算符。 还可以使用 创建外部表作为 select （CETAS），将从 Azure Cosmos DB 读取数据的查询结果存储在 Azure Blob Storage 或 Azure Data Lake Storage 中的数据一起。 目前无法使用 CETAS 将无服务器 SQL 池查询结果存储到 Azure Cosmos DB。

本文介绍如何使用无服务器 SQL 池编写查询，以查询通过 Azure Synapse Link 启用的 Azure Cosmos DB 容器中的数据。 然后，可以在 本教程详细了解如何通过 Azure Cosmos DB 容器生成无服务器 SQL 池视图并将其连接到 Power BI 模型。 本教程使用具有 Azure Cosmos DB 定义完善的架构的容器。 还可以查看 Learn 模块，了解如何使用 SQL 无服务器为 Azure Synapse Analytics 查询 Azure Cosmos DB。

先决条件

• 请确保准备好分析存储：
  • 在 your Azure Cosmos DB 容器上启用分析存储。
  • 获取可用于查询分析存储的只读密钥的连接字符串。
  • 获取用于访问Azure Cosmos DB 容器的只读密钥。
• 请确保已应用所有 最佳做法，例如：
  • 确保 Azure Cosmos DB 分析存储与无服务器 SQL 池位于同一区域。
  • 请确保客户端应用程序（Power BI、分析服务）与无服务器 SQL 池位于同一区域。
  • 如果要返回大量数据（大于 80 GB），请考虑使用 Analysis Services 等缓存层，并在 Analysis Services 模型中加载小于 80 GB 的分区。
  • 如果要使用字符串列筛选数据，请确保将 OPENROWSET 函数与具有最小可能类型的显式 WITH 子句一起使用。 例如，如果你知道该属性最多包含 5 个字符，请不要使用 VARCHAR(1000)。

概述

使用无服务器 SQL 池，可以使用 OPENROWSET 函数查询 Azure Cosmos DB 分析存储。

OPENROWSET( 
       'CosmosDB',
       '<SQL connection string for Azure Cosmos DB>',
       <other parameters>
    )  [ < with clause > ] AS alias

适用于 Azure Cosmos DB 的 SQL connection string包含以下组件：

• account - 所针对的 Azure Cosmos DB 帐户的名称。
• database - 在 OPENROWSET 语法中指定没有引号的容器名称。 如果容器名称包含特殊字符（例如短划线 -），则应用方括号（[]）括起来。
• 区域（可选）- 您的 Cosmos DB 分析存储的区域。 如果省略，则使用容器的主要区域。
• endpoint - 所需的 Cosmos DB 终结点 URI（例如 https://<account name>.documents.azure.cn）。

可以从标准 Cosmos DB connection string标识这些属性，例如：

AccountEndpoint=https://<database account name>.documents.azure.cn:443/;AccountKey=<database account master key>;

SQL connection string的格式如下：

account=<database account name>;database=<database name>;region=<region name>;endpoint=<endpoint>

此连接字符串不包括连接到 Cosmos DB 分析存储所需的身份验证信息。 需要其他信息，具体取决于使用的身份验证类型：

• 如果 OPENROWSET 使用工作区托管标识来access分析存储，则应添加 AuthType 属性。
• 如果 OPENROWSET 使用内联帐户密钥，则应添加 key 该属性。 这样就可以查询 Azure Cosmos DB 集合，而无需准备凭据。
• OPENROWSET可以引用包含 Azure Cosmos DB 帐户密钥的凭据，而不是在“connection string”中包含身份验证信息。 此方法可用于在 Azure Cosmos DB 集合上创建视图。

下面介绍这些选项。

OPENROWSET( 
       'CosmosDB',
       '<SQL connection string for Azure Cosmos DB>',
       <Container name>
    )  [ < with clause > ] AS alias

OPENROWSET( 
       PROVIDER = 'CosmosDB',
       CONNECTION = '<SQL connection string for Azure Cosmos DB without account key>',
       OBJECT = '<Container name>',
       [ CREDENTIAL | SERVER_CREDENTIAL ] = '<credential name>'
    )  [ < with clause > ] AS alias

account=<database account name>;database=<database name>;region=<region name>;endpoint=<endpoint>

示例数据集

本文中的示例基于来自欧洲疾病预防控制中心（ECDC）COVID-19病例和COVID-19开放研究数据集（CORD-19）的数据。

可以在这些页面上查看数据的许可证和结构。 还可以下载 ECDC 和 CORD-19 数据集的示例数据。

若要按照本文中的说明如何使用无服务器 SQL 池查询 Azure Cosmos DB 数据，请确保创建以下资源：

• 已启用 Azure Synapse Link的 Azure Cosmos DB 数据库帐户。
• 名为 covid 的 Azure Cosmos DB 数据库。
• Azure Cosmos DB 的两个容器 Ecdc 和 Cord19，这些容器加载了前面的示例数据集。

请注意，此连接不能保证性能表现，因为此帐户可能位于远离 Synapse SQL 终结点的区域。

使用自动架构推理探索 Azure Cosmos DB 数据

在 Azure Cosmos DB 中浏览数据的最简单方法是使用自动架构推理功能。 通过省略 WITH 语句中的 OPENROWSET 子句，您可以指示无服务器 SQL 池自动检测并推断 Azure Cosmos DB 容器分析存储的架构。

SELECT TOP 10 *
FROM OPENROWSET( 
       'CosmosDB',
       'Account=your-cosmosdb;Database=covid;Key=yourcosmosdbkey',
       Ecdc) as documents

/*  Setup - create server-level or database scoped credential with Azure Cosmos DB account key:
    CREATE CREDENTIAL MyCosmosDbAccountCredential
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'yourcosmosdbkey';
*/
SELECT TOP 10 *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=your-cosmosdb;Database=covid',
      OBJECT = 'Ecdc',
      SERVER_CREDENTIAL = 'MyCosmosDbAccountCredential'
    ) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows

在前面的示例中，我们要求无服务器 SQL 池通过使用 Azure Cosmos DB 密钥（在上例中是虚拟的）验证来连接到 Azure Cosmos DB 帐户中的 MyCosmosDbAccount 数据库。 然后，我们访问了 Ecdc 区域中 China North 3 容器的分析存储。 由于没有特定属性的投影，OPENROWSET 函数从 Azure Cosmos DB 项返回所有属性。

假设 Azure Cosmos DB 容器中的项具有 date_rep、cases 和 geo_id 属性，则下表显示了此查询的结果：

如果需要在同一 Azure Cosmos DB 数据库中浏览来自其他容器的数据，则可以使用相同的connection string，并将所需的容器引用为第三个参数：

SELECT TOP 10 *
FROM OPENROWSET( 
       'CosmosDB',
       'Account=your-cosmosdb;Database=covid;Key=yourcosmosdbkey',
       Cord19) as cord19

显式指定架构

尽管OPENROWSET中的自动架构推理功能提供了简单易用的体验，但在某些业务场景中，您可能需要显式指定架构，以便从 Azure Cosmos DB 数据中仅提取相关属性。

OPENROWSET 函数使你可以显式指定要从容器中的数据读取哪些属性以及指定其数据类型。

假设我们已将一些数据从 ECDC COVID 数据集导入，其结构如下Azure Cosmos DB：

{"date_rep":"2020-08-13","cases":254,"countries_and_territories":"Serbia","geo_id":"RS"}
{"date_rep":"2020-08-12","cases":235,"countries_and_territories":"Serbia","geo_id":"RS"}
{"date_rep":"2020-08-11","cases":163,"countries_and_territories":"Serbia","geo_id":"RS"}

Azure Cosmos DB 中的这些平面 JSON 文档可以表示为 Synapse SQL 中的一组行和列。 使用 OPENROWSET 函数可以在 WITH 子句中指定要读取的属性的子集和确切的列类型：

SELECT TOP 10 *
FROM OPENROWSET(
      'CosmosDB',
      'Account=your-cosmosdb;Database=covid;Key=yourcosmosdbkey',
       Ecdc
    ) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows

/*  Setup - create server-level or database scoped credential with Azure Cosmos DB account key:
    CREATE CREDENTIAL MyCosmosDbAccountCredential
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'yourcosmosdbkey';
*/
SELECT TOP 10 *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=your-cosmosdb;Database=covid',
      OBJECT = 'Ecdc',
      SERVER_CREDENTIAL = 'MyCosmosDbAccountCredential'
    ) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows
   

此查询的结果可能如下表所示：

有关应用于 Azure Cosmos DB 值的 SQL 类型的详细信息，请参阅本文末尾的 Azure Cosmos DB 到 SQL 类型映射。

创建视图

建议不要在 master 或默认数据库中创建视图，该操作也不受支持。 因此，你需要为视图创建用户数据库。

确定架构后，可以在 Azure Cosmos DB 数据的基础上准备视图。 应将 Azure Cosmos DB 帐户密钥放在单独的凭据中，并从 OPENROWSET 函数引用此凭据。 不要在视图定义中保留你的帐户密钥。

CREATE CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'yourcosmosdbkey';
GO
CREATE OR ALTER VIEW Ecdc
AS SELECT *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=your-cosmosdb;Database=covid',
      OBJECT = 'Ecdc',
      SERVER_CREDENTIAL = 'MyCosmosDbAccountCredential'
    ) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows

不要使用没有显式定义的架构的 OPENROWSET，因为它可能会影响性能。 请确保使用列的最小可能大小，例如 VARCHAR(100)，而不是使用默认的 VARCHAR(8000)。 应使用某些 UTF-8 排序规则作为默认数据库排序规则，或将其设置为显式列排序规则，以避免 UTF-8 转换问题。 排序规则 Latin1_General_100_BIN2_UTF8 可以在你使用某些字符串列来筛选数据时发挥最佳性能。

查询视图时，可能会遇到错误或意外结果。 视图引用列或对象可能已修改或不再存在。 需要手动调整视图定义，以便与基础架构更改保持一致。 请记住，在视图中使用自动架构推理和显式指定架构时，可能发生此情况。

查询嵌套对象

使用 Azure Cosmos DB，可以通过将它们组合为嵌套对象或数组来表示更复杂的数据模型。 Azure Synapse Link for Azure Cosmos DB 的自动同步功能开箱即用地管理分析存储中的架构表示，并包括处理允许从无服务器 SQL 池进行丰富查询的嵌套数据类型。

例如，CORD-19 数据集具有遵循以下结构的 JSON 文档：

{
    "paper_id": <str>,                   # 40-character sha1 of the PDF
    "metadata": {
        "title": <str>,
        "authors": <array of objects>    # list of author dicts, in order
        ...
     }
     ...
}

当 OPENROWSET 函数读取它们时，Azure Cosmos DB 中的嵌套对象和数组表示为查询结果中的 JSON 字符串。 使用 WITH 子句时，可以指定对象中嵌套值的路径：

SELECT TOP 10 *
FROM OPENROWSET( 
       'CosmosDB',
       'Account=your-cosmosdb;Database=covid;Key=yourcosmosdbkey',
       Cord19)
WITH (  paper_id    varchar(8000),
        title        varchar(1000) '$.metadata.title',
        metadata     varchar(max),
        authors      varchar(max) '$.metadata.authors'
) AS docs;

此查询的结果可能如下表所示：

若要了解详细信息，请参阅 在 Azure Synapse Analytics 中分析复杂数据类型 或 使用无服务器 SQL 池查询 Parquet 和 JSON 文件中的嵌套类型。

扁平化嵌套数组

Azure Cosmos DB 数据中可能包含嵌套的子数组，如 CORD-19 数据集中的作者数组：

{
    "paper_id": <str>,                      # 40-character sha1 of the PDF
    "metadata": {
        "title": <str>,
        "authors": [                        # list of author dicts, in order
            {
                "first": <str>,
                "middle": <list of str>,
                "last": <str>,
                "suffix": <str>,
                "affiliation": <dict>,
                "email": <str>
            },
            ...
        ],
        ...
}

在某些情况下，可能需要将顶部项（元数据）中的属性与数组 (authors) 的所有元素进行联接。 无服务器 SQL 池可用于通过对嵌套数组应用 OPENJSON 函数来平展嵌套结构：

SELECT
    *
FROM
    OPENROWSET(
      'CosmosDB',
      'Account=your-cosmosdb;Database=covid;Key=yourcosmosdbkey',
       Cord19
    ) WITH ( title varchar(1000) '$.metadata.title',
             authors varchar(max) '$.metadata.authors' ) AS docs
      CROSS APPLY OPENJSON ( authors )
                  WITH (
                       first varchar(50),
                       last varchar(50),
                       affiliation nvarchar(max) as json
                  ) AS a

此查询的结果可能如下表所示：

Azure Cosmos DB 到 SQL 类型映射

尽管 Azure Cosmos DB 事务存储与架构无关，但分析存储经过架构化以优化分析查询性能。 借助Azure Synapse Link的自动同步功能，Azure Cosmos DB 管理现成的分析存储中的架构表示形式，其中包括处理嵌套数据类型。 由于无服务器 SQL 池查询分析存储，因此请务必了解如何将 Azure Cosmos DB 输入数据类型映射到 SQL 数据类型。

Azure Cosmos DB SQL（Core）API 的帐户支持 JSON 属性类型，包括 number、string、Boolean、null、嵌套对象 或 array。 如果在 WITH 中使用 OPENROWSET 子句，则需选择与这些 JSON 类型匹配的 SQL 类型。 下表显示了应用于 Azure Cosmos DB 中不同属性类型的 SQL 列类型。

全保真架构

Azure Cosmos DB 完全保真模式记录每个属性的值及其在容器中的最佳匹配类型。 具有全保真架构的容器中的 OPENROWSET 函数同时在每个单元格中提供类型和实际值。 假设下面的查询从具有全保真架构的容器中读取项：

SELECT *
FROM OPENROWSET(
      'CosmosDB',
      'account=MyCosmosDbAccount;database=covid;region=chinanorth3;key=C0Sm0sDbKey==',
       Ecdc
    ) as rows

此查询的结果会返回格式化为 JSON 文本的类型和值：

对于每个值，可以看到在 Azure Cosmos DB 容器项中标识的类型。 date_rep 属性的大多数值都包含 date 值，但其中一些值在 Azure Cosmos DB 中错误地存储为字符串。 全保真架构会返回类型设置正确的 date 值和格式设置不正确的 string 值。

病例数存储为 int32 值，但有一个作为十进制数输入的值。 该值有 float64 类型。 如果某些值超过了最大的 int32 数，则会将其存储为 int64 类型。 此示例中的所有 geo_id 值都存储为 string 类型。

若要查询 Azure Cosmos DB 中的 MongoDB 帐户，可以在 什么是 Azure Cosmos DB 分析存储？ 中详细了解分析存储中的完整保真架构表示形式以及要使用的扩展属性名称。

查询具有全保真架构的项

查询完全保真架构时，需要在 WITH 子句中显式指定 SQL 类型和预期的 Azure Cosmos DB 属性类型。

在下面的示例中，我们假设 string 是 geo_id 属性的正确类型，int32 是 cases 属性的正确类型：

SELECT geo_id, cases = SUM(cases)
FROM OPENROWSET(
      'CosmosDB'
      'account=MyCosmosDbAccount;database=covid;region=chinanorth3;key=C0Sm0sDbKey==',
       Ecdc
    ) WITH ( geo_id VARCHAR(50) '$.geo_id.string',
             cases INT '$.cases.int32'
    ) as rows
GROUP BY geo_id

geo_id 和 cases 的其他类型的值会作为 NULL 值返回。 此查询将只引用在表达式中具有指定类型 (cases) 的 cases.int32。

如果具有其他类型（cases.int64、cases.float64）的值，则无法在 Azure Cosmos DB 容器中清理这些值，则需要在 WITH 子句中显式引用它们并合并结果。 以下查询会聚合存储在 int32 列中的 int64、float64 和 cases：

SELECT geo_id, cases = SUM(cases_int) + SUM(cases_bigint) + SUM(cases_float)
FROM OPENROWSET(
      'CosmosDB',
      'account=MyCosmosDbAccount;database=covid;region=chinanorth3;key=C0Sm0sDbKey==',
       Ecdc
    ) WITH ( geo_id VARCHAR(50) '$.geo_id.string', 
             cases_int INT '$.cases.int32',
             cases_bigint BIGINT '$.cases.int64',
             cases_float FLOAT '$.cases.float64'
    ) as rows
GROUP BY geo_id

在此示例中，病例的数量存储为 int32、int64 或 float64 值。 必须提取所有值才能计算每个国家或地区的病例数。

故障排除

查看 自助页面查找已知问题或故障排除步骤，以帮助解决 Azure Cosmos DB 查询的潜在问题。

相关内容

• 使用 Power BI 和无服务器 Synapse SQL 池分析 Azure Cosmos DB 数据
• 使用无服务器 SQL 池创建和使用视图
• 教程：使用无服务器 SQL 池浏览和分析数据湖
• 如果您遇到错误或性能问题，请参阅无服务器 SQL 池疑难解答
• Module：使用 Azure Cosmos DB 实现Azure Synapse Link