配置 PolyBase 以访问 MongoDB 中的外部数据

项目
10/05/2023

适用于：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 中查询外部架构。

要创建包含数组的 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 flattening

对象地址将按如下所示平展：

嵌套字段 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 的概述。