設定 PolyBase 存取 MongoDB 中的外部資料

文章
10/16/2024

適用於：SQL Server

本文說明如何在 SQL Server 執行個體上使用 PolyBase 查詢位於 MongoDB 中的外部資料。

必要條件

如果您尚未安裝 PolyBase，請參閱 PolyBase 安裝。

在您建立資料庫有限範圍認證之前，資料庫必須具有保護認證的主要金鑰。如需詳細資訊，請參閱 CREATE MASTER KEY (機器翻譯)。

設定 MongoDB 外部資料來源

若要查詢來自 MongoDB 資料來源的資料，您必須建立外部表格來參考外部資料。本節提供建立這些外部資料表的範例程式碼。

本節中使用下列 Transact-SQL 命令：

建立資料庫範圍認證以存取 MongoDB 資料來源。

下列指令碼會建立資料庫有限範圍認證。在您為您的環境執行指令碼更新之前：
- 使用認證的名稱取代 <credential_name>。
- 使用外部來源的使用者名稱取代 <username>。
- 使用適當密碼取代 <password>。
```
CREATE DATABASE SCOPED CREDENTIAL [<credential_name>] WITH IDENTITY = '<username>', Secret = '<password>';
```
重要

適用於 PolyBase 的 MongoDB ODBC 連接器僅支援基本驗證，不支援 Kerberos 驗證。
建立外部資料來源。

下列指令碼會建立外部資料來源。如需參考資料，請參閱 CREATE EXTERNAL DATA SOURCE (機器翻譯)。在您為您的環境執行指令碼更新之前：
- 更新位置。為您的環境設定 <server> 和 <port>。
- 使用您在上一步建立的認證名稱，取代 <credential_name>。
- 如果您想要指定外部來源的下推計算，可以選擇性指定 PUSHDOWN = ON 或 PUSHDOWN = OFF。
```
CREATE EXTERNAL DATA SOURCE external_data_source_name
WITH (LOCATION = '<mongodb://<server>[:<port>]>'
[ [ , ] CREDENTIAL = <credential_name> ]
[ [ , ] CONNECTION_OPTIONS = '<key_value_pairs>'[,...]]
[ [ , ] PUSHDOWN = { ON | OFF } ])
[ ; ]
```
查詢 MongoDB 中的外部結構描述。

您可以使用適用於 Azure Data Studio 的資料虛擬化延伸模組，根據 PolyBase ODBC Driver for MongoDB 驅動程式偵測到的結構描述，連線並產生 CREATE EXTERNAL TABLE 陳述式。您也可以根據系統預存程序 sp_data_source_objects (Transact-SQL) 的輸出，手動自訂指令碼。適用於 Azure Data Studio 的資料虛擬化延伸模組和 sp_data_source_table_columns，會使用相同的內部預存程序，查詢外部結構描述。

若要建立包含陣列之 MongoDB 集合的外部表格，建議使用適用於 Azure Data Studio 的資料虛擬化延伸模組。驅動程式會自動執行壓平合併動作。 sp_data_source_table_columns 預存程序也會透過 PolyBase ODBC Driver for MongoDB 驅動程式，自動執行壓平合併。
建立外部資料表。

如果您使用適用於 Azure Data Studio 的資料虛擬化延伸模組，您可以略過此步驟，因為會為您產生 CREATE EXTERNAL TABLE 陳述式。若要手動提供結構描述，請考慮下列範例指令碼來建立外部表格。如需參考資料，請參閱 CREATE EXTERNAL TABLE (機器翻譯)。

在您執行指令碼之前，請為您的環境加以更新：
- 使用其名稱、定序和集合 (若有) 更新欄位，然後指定集合名稱和欄位名稱。在此範例中，friends 是自訂資料類型。
- 更新位置。設定資料庫名稱和資料表名稱。請注意，不允許使用三部分名稱，因此您無法為 system.profile 資料表建立。您也無法指定檢視，因為檢視無法從中取得中繼資料。
- 使用您在上一步建立的資料來源名稱，更新資料來源。
```
CREATE EXTERNAL TABLE [MongoDbRandomData](
  [_id] NVARCHAR(24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [RandomData_friends_id] INT,
  [RandomData_tags] NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS)
WITH (
  LOCATION='MyDb.RandomData',
  DATA_SOURCE=[MongoDb])
```
選擇性： 在外部資料表上建立統計資料。

我們建議在外部資料表資料行上建立統計資料 (尤其是用於聯結、篩選和彙總的資料行)，以取得最佳查詢效能。
```
CREATE STATISTICS statistics_name ON customer (C_CUSTKEY) WITH FULLSCAN; 
```

重要

當您建立外部資料來源之後，可以使用 CREATE EXTERNAL TABLE 命令，透過該來源建立可查詢的資料表。

如需範例，請參閱建立 MongoDB 的外部表格。

MongoDB 連線選項

如需 MongoDB 連線選項的資訊，請參閱 MongoDB 文件：連接字串 URI 格式 (英文)。

壓平合併

針對來自 MongoDB 文件集合的巢狀及重複資料，啟用壓平合併。使用者必須啟用 create an external table，並明確指定可能具有巢狀和/或重複資料之 MongoDB 文件集合的關聯式結構描述。 JSON 巢狀/重複資料類型會以下列方式壓平合併

物件：未排序索引鍵/值集合會包圍在大括弧中 (巢狀)
- SQl Server 會為每個物件機碼建立資料表資料行
  - 資料行名稱：objectname_keyname
陣列：排序值，以逗號分隔，並包圍在方括弧內 (重複)
- SQL Server 會為每個陣列項目新增新的資料表資料列
- SQL Server 會為每個陣列建立資料行，以儲存陣列項目索引
  - 資料行名稱：arrayname_index
  - 資料類型：bigint

此技術有數個潛在問題，其中兩個是：

空白重複欄位會有效遮罩包含在相同記錄一般欄位中的資料
多個重複欄位可能會導致所產生資料列的數量暴增

例如，假設 SQL Server 評估一個儲存在非關聯式 JSON 格式中的 MongoDB 範例資料集餐廳集合。每間餐廳都有巢狀地址欄位，以及在不同天所獲指派的等級。下圖說明具有巢狀地址和巢狀重複等級的典型餐廳。

MongoDB 壓平合併

物件地址會以下列順序壓平合併：

巢狀欄位 restaurant.address.building 會變成 restaurant.address_building
巢狀欄位 restaurant.address.coord 會變成 restaurant.address_coord
巢狀欄位 restaurant.address.street 會變成 restaurant.address_street
巢狀欄位 restaurant.address.zipcode 會變成 restaurant.address_zipcode

陣列等級會以下列順序壓平合併：

grades_date	grades_grade	games_score
1393804800000	A	2
1378857600000	A	6
135898560000	A	10
1322006400000	A	9
1299715200000	B	14

Cosmos DB 連線

使用 Cosmos DB Mongo API 和 Mongo DB PolyBase 連接器時，您可以建立 Cosmos DB 執行個體的外部表格。依照上面所列的相同步驟，即可完成此操作。請確認資料庫範圍認證、伺服器位址、連接埠及位置字串皆反映 Cosmos DB 伺服器的對應設定。

範例

下列範例會建立具有下列參數的外部資料來源：

參數	值
名稱	`external_data_source_name`
服務	`mongodb0.example.com`
執行個體	`27017`
複本集	`myRepl`
TLS	`true`
下推計算	`On`

CREATE EXTERNAL DATA SOURCE external_data_source_name
    WITH (LOCATION = 'mongodb://mongodb0.example.com:27017',
    CONNECTION_OPTIONS = 'replicaSet=myRepl; tls=true',
    PUSHDOWN = ON ,
    CREDENTIAL = credential_name);

下一步

如需將外部資料來源和外部資料表建立到各種資料來源的其他教學課程，請參閱 PolyBase Transact-SQL 參考。

若要深入了解 PolyBase，請參閱 SQL Server PolyBase 概觀。

分享方式：