使用 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	Widget	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	Widget	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	Widget
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

建立資料庫檢視

如果您需要經常查詢相同的資料，或者需要使用依賴 SELECT 陳述式但不包含 OPENROWSET 函式的報告和視覺效果工具，您可以使用「檢視」來總結資料。若要建立檢視，建議您建立新的資料庫以定義檢視 (不支援 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 分析存放區中的資料時，請考慮下列最佳做法：