使用 Synapse SQL 查询 Cosmos DB

5 分钟

除了使用 Spark 池，还可以使用 Azure Synapse Analytics 中的内置无服务器 SQL 池查询 Azure Cosmos DB 分析容器。为此，可以使用 OPENROWSET SQL 函数连接到 Azure Cosmos DB 数据库的链接服务。

将 OPENROWSET 与身份验证密钥配合使用

默认情况下，对 Azure Cosmos DB 帐户的访问通过身份验证密钥进行身份验证。可以将此密钥用作 OPENROWSET 语句中连接字符串的一部分，通过 SQL 池的链接服务进行连接，如以下示例所示：

SELECT *
FROM OPENROWSET(
    'CosmosDB',
    'Account=my-cosmos-db;Database=my-db;Key=abcd1234....==',
    [my-container]) AS products_data

提示

可以在 Azure 门户中的“密钥”页上找到 Cosmos DB 帐户的主密钥和辅助密钥。

此查询的结果可能如下所示，包括来自 Azure Cosmos DB 容器中的项的元数据和应用程序定义的字段：

_rid	_ts	productID	productName	id	_etag
mjMaAL...==	1655414791	123	小组件	7248f072-11c3-42b1-a368-...	54004b09-0000-2300-...
mjMaAL...==	1655414829	124	Wotsit	dc33131c-65c7-421a-a0f7-...	5400ca09-0000-2300-...
mjMaAL...==	1655414835	125	Thingumy	ce22351d-78c7-428a-a1h5-...	5400ca09-0000-2300-...
...	...	...	...	...	...

数据从分析存储中检索，查询不会影响操作存储。

将 OPENROWSET 与凭据配合使用

可以定义封装 Cosmos DB 帐户的身份验证信息的凭据，并在后续查询中使用该凭据，而不是在每次调用 OPENROWSET 时包括身份验证密钥。若要创建凭据，请使用 CREATE CREDENTIAL 语句，如以下示例所示：

 CREATE CREDENTIAL my_credential
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = 'abcd1234....==';

凭据准备就绪后，可以在 OPENROWSET 函数中使用它，如下所示：

SELECT *
FROM OPENROWSET(PROVIDER = 'CosmosDB',
                CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                OBJECT = 'my-container',
                SERVER_CREDENTIAL = 'my_credential'
) AS products_data

结果再次包括分析存储中的元数据和应用程序定义的字段：

_rid	_ts	productID	productName	id	_etag
mjMaAL...==	1655414791	123	小组件	7248f072-11c3-42b1-a368-...	54004b09-0000-2300-...
mjMaAL...==	1655414829	124	Wotsit	dc33131c-65c7-421a-a0f7-...	5400ca09-0000-2300-...
mjMaAL...==	1655414835	125	Thingumy	ce22351d-78c7-428a-a1h5-...	5400ca09-0000-2300-...
...	...	...	...	...	...

指定架构

OPENROWSET 语法包含一个 WITH 子句，可用于为生成的行集定义架构。可以使用此语法指定各个字段并分配数据类型，如以下示例所示：

 SELECT *
 FROM OPENROWSET(PROVIDER = 'CosmosDB',
                 CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                 OBJECT = 'my-container',
                 SERVER_CREDENTIAL = 'my_credential'
 )
 WITH (
    productID INT,
    productName VARCHAR(20)
 ) AS products_data

在这种情况下，假设分析存储中的字段包括 productID 和 productName，生成的行集将类似于下表：

productID	productName
123	小组件
124	Wotsit
125	Thingumy
...	...

当然，可以在 SELECT 子句中指定单个列名（例如 SELECT productID, productName ...），因此这种指定单个列的功能可能看起来使用受限。但是，请考虑存储在操作存储中的源 JSON 文档包含多级字段的情况，如以下示例所示：

{
    "productID": 126,
    "productName": "Sprocket",
    "supplier": {
        "supplierName": "Contoso",
        "supplierPhone": "555-123-4567"
    }
    "id": "62588f072-11c3-42b1-a738-...",
    "_rid": "mjMaAL...==",
    ...
}

WITH 子句支持包含显式 JSON 路径，使你能够处理嵌套字段并将别名分配给字段名称，如以下示例所示：

 SELECT *
 FROM OPENROWSET(PROVIDER = 'CosmosDB',
                 CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                 OBJECT = 'my-container',
                 SERVER_CREDENTIAL = 'my_credential'
 )
 WITH (
    ProductNo INT '$.productID',
    ProductName VARCHAR(20) '$.productName',
    Supplier VARCHAR(20) '$.supplier.supplierName',
    SupplierPhoneNo VARCHAR(15) '$.supplier.supplierPhone'
 ) AS products_data

此查询的结果将包括产品 126 的以下行：

ProductNo	ProductName	供应商	SupplierPhoneNo
126	Sprocket	Contoso	555-123-4567

在数据库中创建视图

如果需要频繁查询相同的数据，或者需要使用依赖于不包含 OPENROWSET 函数的 SELECT 语句的报表和可视化工具，可以使用视图来抽象数据。若要创建视图，应创建一个新数据库来定义它（不支持 master 数据库中用户定义的视图），如以下示例所示：

CREATE DATABASE sales_db
   COLLATE Latin1_General_100_BIN2_UTF8;
 GO;

 USE sales_db;
 GO;

 CREATE VIEW products
 AS
 SELECT *
 FROM OPENROWSET(PROVIDER = 'CosmosDB',
                 CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                 OBJECT = 'my-container',
                 SERVER_CREDENTIAL = 'my_credential'
 )
 WITH (
    ProductNo INT '$.productID',
    ProductName VARCHAR(20) '$.productName',
    Supplier VARCHAR(20) '$.supplier.supplierName',
    SupplierPhoneNo VARCHAR(15) '$.supplier.supplierPhone'
 ) AS products_data
 GO

提示

创建将访问 Cosmos DB 中的数据的数据库时，最好使用基于 UTF-8 的排序规则来确保与 Cosmos DB 中的字符串兼容。

创建视图后，用户和客户端应用程序可以像查询任何其他 SQL 视图或表一样对其进行查询：

SELECT * FROM products;

无服务器 SQL 池和 Azure Cosmos DB 的注意事项

在计划使用无服务器 SQL 池查询 Azure Cosmos DB 分析存储中的数据时，请考虑以下最佳做法：