在 Azure Synapse Link 中使用無伺服器 SQL 集區查詢 Azure Cosmos DB 數據
無伺服器 SQL 集區可讓您分析 Azure Cosmos DB 容器中的數據，這些容器會以近乎即時的方式使用 Azure Synapse Link 啟用，而不會影響交易式工作負載的效能。 它提供熟悉的 T-SQL 語法，透過 T-SQL 介面從分析存放區查詢數據，以及透過 T-SQL 介面對各種商業智慧 （BI） 和臨機操作查詢工具的整合式連線。
若要查詢 Azure Cosmos DB，OPENROWSET 函式支援完整的 SELECT 介面區，其中包含大部分的 SQL 函式和運算符。 您也可以使用建立外部數據表做為 select（CETAS），來儲存從 Azure Cosmos DB 讀取數據的查詢結果，以及 Azure Blob 儲存體 或 Azure Data Lake 儲存體 中的數據。 您目前無法使用 CETAS 將無伺服器 SQL 集區查詢結果儲存至 Azure Cosmos DB。
在本文中，您將瞭解如何使用無伺服器 SQL 集區撰寫查詢，以查詢使用 Azure Synapse Link 啟用的 Azure Cosmos DB 容器中的數據。 接著，您可以深入瞭解如何透過 Azure Cosmos DB 容器建置無伺服器 SQL 集區檢視，並在本教學課程中將它們連線到 Power BI 模型。 本教學課程使用具有 Azure Cosmos DB 定義完善的架構的容器。 您也可以參閱瞭解如何使用適用於 Azure Synapse Analytics 的 SQL Serverless 查詢 Azure Cosmos DB 的學習課程模組
必要條件
- 請確定您已備妥分析存放區：
- 請確定您已套用所有 最佳做法，例如：
- 請確定您的 Azure Cosmos DB 分析記憶體位於與無伺服器 SQL 集區相同的區域中。
- 確定用戶端應用程式 （Power BI， Analysis Service） 位於與無伺服器 SQL 集區相同的區域中。
- 如果您要傳回大量數據（大於 80GB），請考慮使用快取層，例如 Analysis Services，並在 Analysis Services 模型中載入小於 80GB 的數據分割。
- 如果您使用字串數據行來篩選數據，請確定您使用
OPENROWSET函式搭配具有最小可能類型的明確WITH子句（例如，如果您知道屬性最多有 5 個字元，請勿使用 VARCHAR（1000）。
概觀
無伺服器 SQL 集區可讓您使用 OPENROWSET 函式查詢 Azure Cosmos DB 分析記憶體。
OPENROWSET具有內嵌索引鍵。 此語法可用來查詢 Azure Cosmos DB 集合，而不需要準備認證。OPENROWSET包含 Azure Cosmos DB 帳戶密鑰的參考認證。 此語法可用來在 Azure Cosmos DB 集合上建立檢視。
為了支持查詢和分析 Azure Cosmos DB 分析存放區中的數據，會使用無伺服器 SQL 集區。 無伺服器 SQL 集區會使用 OPENROWSET SQL 語法，因此您必須先將 Azure Cosmos DB 連接字串 轉換為下列格式：
OPENROWSET(
'CosmosDB',
'<SQL connection string for Azure Cosmos DB>',
<Container name>
) [ < with clause > ] AS alias
Azure Cosmos DB 的 SQL 連接字串 會指定函式的 Azure Cosmos DB 帳戶名稱、資料庫名稱、資料庫帳戶主要密鑰，以及選擇性的區域名稱OPENROWSET。 部分資訊可從標準 Azure Cosmos DB 連接字串 取得。
從標準 Azure Cosmos DB 轉換 連接字串 格式：
AccountEndpoint=https://<database account name>.documents.azure.com:443/;AccountKey=<database account master key>;
SQL 連接字串 具有下列格式：
'account=<database account name>;database=<database name>;region=<region name>;key=<database account master key>'
區域是選擇性的。 如果省略，則會使用容器的主要區域。
重要
連接字串 中有另一個選擇性參數，稱為 endpoint。 endpoint不符合標準*.documents.azure.com格式的帳戶需要 參數。 例如，如果您的 Azure CosmosDB 帳戶以 結尾.documents.azure.us，請確定您在 連接字串 中新增 endpoint=<account name>.documents.azure.us 。
Azure Cosmos DB 容器名稱是在語法中未加上引號來 OPENROWSET 指定。 例如，如果容器名稱具有任何特殊字元，則名稱應該以方括弧 （[]） 括住語法。OPENROWSET
重要
請確定您使用某些 UTF-8 資料庫定序，例如 ， Latin1_General_100_CI_AS_SC_UTF8因為 Azure Cosmos DB 分析存放區中的字串值會編碼為 UTF-8 文字。
檔案中的文字編碼與定序不符可能會導致非預期的文字轉換錯誤。
您可以使用 T-SQL 語句 alter database current collate Latin1_General_100_CI_AI_SC_UTF8，輕鬆地變更目前資料庫的預設定序。
注意
無伺服器 SQL 集區不支持查詢 Azure Cosmos DB 交易存放區。
範例數據集
本文中的範例是根據歐洲疾病預防控制中心 （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的 Azure Cosmos DB 容器，並使用Cord19上述範例數據集載入。
您可以使用下列 連接字串 進行測試：Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==。 請注意，相較於 Synapse SQL 端點，此連線無法保證效能，因為此帳戶可能位於遠端區域。
使用自動架構推斷探索 Azure Cosmos DB 數據
在 Azure Cosmos DB 中探索數據最簡單的方式是使用自動架構推斷功能。 藉由省略 語句中的 WITHOPENROWSET 子句，您可以指示無伺服器 SQL 集區自動偵測 Azure Cosmos DB 容器分析存放區的架構。。
SELECT TOP 10 *
FROM OPENROWSET(
'CosmosDB',
'Account=synapselink-cosmosdb-sqlsample;Database=covid;Key=s5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==',
Ecdc) as documents
在上述範例中，我們指示無伺服器 SQL 集區連線到 covid 使用 Azure Cosmos DB 密鑰驗證的 Azure Cosmos DB 帳戶 MyCosmosDbAccount 中的資料庫（上述範例中的虛擬專案）。 然後，我們已存取 Ecdc 區域中容器的分析存放區 West US 2 。 由於沒有特定屬性的投影，因此函 OPENROWSET 式會從 Azure Cosmos DB 專案傳回所有屬性。
假設 Azure Cosmos DB 容器中的專案具有 date_rep、 cases和 geo_id 屬性，則下表顯示此查詢的結果：
| date_rep | 案例 | geo_id |
|---|---|---|
| 2020-08-13 | 254 | RS |
| 2020-08-12 | 235 | RS |
| 2020 年 8 月 11 日 | 163 | RS |
如果您需要探索相同 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
此查詢的結果可能如下表所示：
| date_rep | 案例 | geo_id |
|---|---|---|
| 2020-08-13 | 254 | RS |
| 2020-08-12 | 235 | RS |
| 2020 年 8 月 11 日 | 163 | RS |
如需應該用於 Azure Cosmos DB 值之 SQL 類型的詳細資訊，請參閱 本文結尾的 SQL 類型對應 規則。
建立檢視
不建議或支援在 master 或 預設資料庫中建立檢視。 因此，您必須為檢視建立用戶資料庫。
識別架構之後，您就可以在 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 Cosmos DB 的 Azure Synapse Link 自動同步功能會管理現成分析存放區中的架構表示法，其中包括處理巢狀數據類型，以允許從無伺服器 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;
此查詢的結果可能如下表所示：
| paper_id | title | 中繼資料 | 作者 |
|---|---|---|---|
| bb11206963e831f... | 補充資訊 生態癲癇... | {"title":"Supplementary Informati… |
[{"first":"Julien","last":"Mélade","suffix":"","af… |
| bb1206963e831f1... | 在免疫 E 中使用 Convalescent Sera... | {"title":"The Use of Convalescent… |
[{"first":"Antonio","last":"Lavazza","suffix":"", … |
| bb378eca9aac649... | 蒂洛塞馬埃斯庫倫特姆 （馬拉瑪） 圖伯和 B... | {"title":"Tylosema esculentum (Ma… |
[{"first":"Walter","last":"Chingwaru","suffix":"",… |
深入瞭解如何分析 適用於 Azure Cosmos DB 的 Azure Synapse Link 中的 Parquet 檔案和容器等複雜數據類型，或 無伺服器 SQL 集區中的巢狀結構。
重要
如果您在文字中看到非預期的字元，Mélade而不是 Mélade ，則您的資料庫定序不會設定為 UTF-8 定序。
使用類似 的 ALTER DATABASE MyLdw COLLATE LATIN1_GENERAL_100_CI_AS_SC_UTF8SQL 語句，將資料庫的定序變更為 UTF-8 定序。
扁平化巢狀陣列
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） 的所有元素來「聯結」頂端專案 （metadata） 的屬性。 無伺服器 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
此查詢的結果可能如下表所示：
| title | 作者 | 第一 | 最後一 | 聯繫 |
|---|---|---|---|---|
| 補充資訊 生態癲癇... | [{"first":"Julien","last":"Mélade","suffix":"","affiliation":{"laboratory":"Centre de Recher… |
朱利安 | 梅拉德 | {"laboratory":"Centre de Recher… |
| 補充資訊 生態癲癇... | [{"first":"Nicolas","last":"4#","suffix":"","affiliation":{"laboratory":"","institution":"U… |
尼古拉斯 | 4# | {"laboratory":"","institution":"U… |
| 補充資訊 生態癲癇... | [{"first":"Beza","last":"Ramazindrazana","suffix":"","affiliation":{"laboratory":"Centre de Recher… |
Beza | Ramazindrazana | {"laboratory":"Centre de Recher… |
| 補充資訊 生態癲癇... | [{"first":"Olivier","last":"Flores","suffix":"","affiliation":{"laboratory":"UMR C53 CIRAD, … |
奧利維爾 | Flores | {"laboratory":"UMR C53 CIRAD, … |
重要
如果您在文字中看到非預期的字元，Mélade而不是 Mélade ，則您的資料庫定序不會設定為 UTF-8 定序。 使用類似 的 ALTER DATABASE MyLdw COLLATE LATIN1_GENERAL_100_CI_AS_SC_UTF8SQL 語句，將資料庫的定序變更為 UTF-8 定序。
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 屬性類型。 如果您使用 WITH 中的 OPENROWSET子句，則必須選擇符合這些 JSON 類型的 SQL 類型。 下表顯示應該用於 Azure Cosmos DB 中不同屬性類型的 SQL 資料行類型。
| Azure Cosmos DB 屬性類型 | SQL 資料行類型 |
|---|---|
| 布林值 | bit |
| 整數 | bigint |
| Decimal | float |
| String | varchar （UTF-8 資料庫定序） |
| 日期時間 （ISO 格式字串） | varchar（30） |
| 日期時間 （UNIX 時間戳） | bigint |
| Null | any SQL type |
| 巢狀物件或陣列 | varchar（max） （UTF-8 資料庫定序），串行化為 JSON 文字 |
完整逼真度架構
Azure Cosmos DB 完整逼真架構會記錄值及其容器中每個屬性的最佳比對類型。 OPENROWSET容器上具有完整精確度架構的函式會提供每個數據格中的類型和實際值。 假設下列查詢會從具有完整逼真度架構的容器讀取專案：
SELECT *
FROM OPENROWSET(
'CosmosDB',
'account=MyCosmosDbAccount;database=covid;region=westus2;key=C0Sm0sDbKey==',
Ecdc
) as rows
此查詢的結果會傳回格式化為 JSON 文字的類型和值：
| date_rep | 案例 | geo_id |
|---|---|---|
| {“date”：“2020-08-13”} | {“int32”：“254”} | {“string”：“RS”} |
| {“date”：“2020-08-12”} | {“int32”：“235”} | {“string”：“RS”} |
| {“date”：“2020-08-11”} | {“int32”：“316”} | {“string”：“RS”} |
| {“date”：“2020-08-10”} | {“int32”：“281”} | {“string”：“RS”} |
| {“date”：“2020-08-09”} | {“int32”：“295”} | {“string”：“RS”} |
| {“string”：“2020/08/08”} | {“int32”：“312”} | {“string”：“RS”} |
| {“date”：“2020-08-07”} | {“float64”：“339.0”} | {“string”：“RS”} |
針對每個值，您可以看到 Azure Cosmos DB 容器專案中所識別的類型。 屬性的大部分值都包含date值，但其中一些值date_rep在 Azure Cosmos DB 中錯誤地儲存為字串。 完整逼真度架構會同時傳回正確型 date 別的值和格式不正確的 string 值。
案例數目是儲存為 int32 值的資訊，但有一個值會輸入為十進位數。 這個值具有 型別 float64 。 如果有一些值超過最大 int32 數目，則會儲存為 int64 類型。 此範例中的所有 geo_id 值都會儲存為 string 類型。
重要
不含 子句的WITH函OPENROWSET式會公開具有預期型別的值，以及輸入不正確的值。 此函式是針對數據探索所設計，而不是用於報告。 請勿剖析從此函式傳回的 JSON 值，以建置報表。 使用明確的 WITH子句 來建立報表。 您應該清除 Azure Cosmos DB 容器中類型不正確的值，以在完整逼真分析存放區中套用更正。
若要查詢適用於 MongoDB 帳戶的 Azure Cosmos DB 帳戶，您可以深入瞭解分析存放區中的完整精確度架構表示法，以及要用於 什麼是 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=westus2;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 Azure Cosmos DB 容器中清除的值， cases.float64則必須在 子句中 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=westus2;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 查詢的潛在問題。
下一步
如需詳細資訊，請參閱下列文章：
- 搭配 Azure Synapse Link 使用 Power BI 和無伺服器 SQL 集區
- 在無伺服器 SQL 集區中建立和使用檢視
- 透過 Azure Cosmos DB 建置無伺服器 SQL 集區檢視，並透過 DirectQuery 將它們連線到 Power BI 模型的教學課程
- 如果您收到一些錯誤或遇到效能問題，請流覽 Azure Cosmos DB 自助頁面的 Azure Synapse 連結。
- 請參閱瞭解如何使用適用於 Azure Synapse Analytics 的 SQL 無伺服器查詢 Azure Cosmos DB 的學習課程模組。