在 Azure Synapse Link 中使用无服务器 SQL 池查询 Azure Cosmos DB 数据

利用无服务器 SQL 池，可以近乎实时地分析通过 Azure Synapse Link 启用的 Azure Cosmos DB 容器中的数据，而不会影响事务工作负荷的性能。 它提供熟悉的 T-SQL 语法，用于查询分析存储中的数据，并提供通过 T-SQL 接口建立的集成连接，用于连接到各种商业智能 (BI) 和即席查询工具。

查询 Azure Cosmos DB 时，可通过 OPENROWSET 函数支持完整的 SELECT 外围应用，其中包括大多数 SQL 函数和运算符。 你还可以使用 create external table as select (CETAS) 将从 Azure Cosmos DB 读取数据的查询的结果和数据存储在 Azure Blob 存储或 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 模块，了解如何使用适用于 Azure Synapse Analytics 的 SQL Serverless 查询 Azure Cosmos DB

先决条件

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

概述

无服务器 SQL 池可用于通过 OPENROWSET 函数查询 Azure Cosmos DB 分析存储。

• 带内联密钥的 OPENROWSET。 此语法可用于查询 Azure Cosmos DB 集合，无需准备凭据。
• OPENROWSET，其引用的凭据包含 Azure Cosmos DB 帐户密钥。 此语法可用于在 Azure Cosmos DB 集合上创建视图。

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

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

'account=<database account name>;database=<database name>;region=<region name>;key=<database account master key>'

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>'

示例数据集

本文中的示例基于欧洲疾病预防控制中心 (ECDC) COVID-19 病例和 COVID-19 开放式研究数据集 (CORD-19)，doi:10.5281/zenodo.3715505 中提供的数据。

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

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

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

你可以将以下连接字符串用于测试目的：Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==。 请注意，此连接将不能保证性能，因为此帐户可能位于远程区域（与 Synapse SQL 终结点相比）。

利用自动架构推理浏览 Azure Cosmos DB 数据

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

SELECT TOP 10 *
FROM OPENROWSET( 
       'CosmosDB',
       'Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       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 = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';
*/
SELECT TOP 10 *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=synapselink-cosmosdb-sqlsample;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 中的 covid 数据库。 然后，我们访问了 China North 3 区域中 Ecdc 容器的分析存储。 由于没有特定属性的投影，因此 OPENROWSET 函数会返回 Azure Cosmos DB 项中的所有属性。

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

如果需要从同一个 Azure Cosmos DB 数据库中的另一个容器浏览数据，则可使用相同的连接字符串，将所需的容器作为第三个参数引用：

SELECT TOP 10 *
FROM OPENROWSET( 
       'CosmosDB',
       'Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       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=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       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 = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';
*/
SELECT TOP 10 *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=synapselink-cosmosdb-sqlsample;Database=covid',
      OBJECT = 'Ecdc',
      SERVER_CREDENTIAL = 'MyCosmosDbAccountCredential'
    ) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows
   

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

若要详细了解应该用于 Azure Cosmos DB 值的 SQL 类型，请参阅本文末尾的 SQL 类型映射规则。

创建视图

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

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

CREATE CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';
GO
CREATE OR ALTER VIEW Ecdc
AS SELECT *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=synapselink-cosmosdb-sqlsample;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=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       Cord19)
WITH (  paper_id    varchar(8000),
        title        varchar(1000) '$.metadata.title',
        metadata     varchar(max),
        authors      varchar(max) '$.metadata.authors'
) AS docs;

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

详细了解如何分析复杂数据类型，例如 Azure Synapse Link for Azure Cosmos DB 中的 Parquet 文件和容器或无服务器 SQL 池中的嵌套结构。

平展嵌套数组

Azure Cosmos DB 数据可能具有与 CORD-19 数据集中的 authors 数组类似的嵌套子数组：

{
    "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=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
       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 数据类型。

SQL (Core) API 的 Azure Cosmos DB 帐户支持数字、字符串、布尔值、Null、嵌套对象或数组 JSON 属性类型。 如果在 OPENROWSET 中使用 WITH 子句，则需选择与这些 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 for 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.int32) 的 cases。

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

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 查询的潜在问题的已知问题或故障排除步骤。

后续步骤

有关详细信息，请参阅以下文章：

• 在无服务器 SQL 池中创建和使用视图
• 教程：如何通过 DirectQuery 构建基于 Azure Cosmos DB 的无服务器 SQL 池视图并将其连接到 Power BI 模型
• 如果遇到一些错误或遇到性能问题，请访问 Azure Cosmos DB 自助页的 Azure Synapse 链接。
• 还可查看 Learn 模块，了解如何使用适用于 Azure Synapse Analytics 的 SQL Serverless 查询 Azure Cosmos DB。