SQL Server 2019 配置 PolyBase 访问外部 Mongodb

以前版本访问外部数据源使用的是 链接服务器。SQL Server 2016 引入了 PolyBase，支持连接到 Hadoop 和 Azure Blob 存储。SQL Server 2019 引入了其他连接器，包括 SQL Server、Oracle、Teradata 和 MongoDB。当然需要安装 PolyBase 查询服务，若要查询 MongoDB 数据源中的数据，必须创建外部表以引用外部数据。

安装完成后，需要启用 Polybase ：

use master
go
exec sp_configure 'polybase enabled',1
reconfigure with override
go

创建主密钥和凭证：

USE Test
GO
-- 1. 创建 MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0me!nfo'
GO
 -- 2. 创建数据库范围凭证
CREATE DATABASE SCOPED CREDENTIAL MongoDBCredentials 
WITH IDENTITY = 'admin', Secret = 'admin'
GO

凭证存储在 SQL Server 中，所以以上需填写 Mongodb 的账号及密码。接下来需要创建外部数据源，外部数据源设置 Mongodb 连接地址和刚创建的凭证。

-- 3. 创建外部数据源
CREATE EXTERNAL DATA SOURCE MongoInstance
WITH (
  LOCATION = 'mongodb://10.10.10.9:27017',
  PUSHDOWN = ON,
  CREDENTIAL = MongoDBCredentials,
  CONNECTION_OPTIONS = 'ssl=false;'
)
GO

这里我们把 ssl 关闭，设置连接选项 ssl=false ，否则可能出错。

消息 105082，级别 16，状态 1，第 44 行
105082;一般 ODBC 错误: [Microsoft][MongoDBODBC] (110) Error from MongoDB Client: No suitable servers found (`serverSelectionTryOnce` set): [connection closed calling ismaster on '10.10.10.9:27017'] (Error Code: 13053) Additional error <2>:ErrorMsg: [Microsoft][MongoDBODBC] (110) Error from MongoDB Client:No suitable servers found (`serverSelectionTryOnce` set): [connection closed calling ismaster on '10.10.10.9:27017'] (Error Code: 13053), SqlState: HY000, NativeError: 110 。

现在创建一个单独的 Mongodb schema，并创建外部表。

-- 4. 创建独立的 mongodb 模式
CREATE SCHEMA MongoDB
GO
-- 5. 创建外部虚拟表
CREATE EXTERNAL TABLE MongoDB.tab(
  [_id] NVARCHAR(24) NOT NULL,
  [id] FLOAT(53) NULL,
  [name] NVARCHAR(4000) NULL,
  [size] FLOAT(53) NULL
)
WITH (
  LOCATION='local.tab',
  DATA_SOURCE= MongoInstance --上面创建的数据源
)
GO

这里的坑要注意，毕竟官方文档没有详细说明，靠自己摸索出来的。表的数据类型要与 Mongodb  的一致，否则会报错，当然报错的时候可以看到相应的数据类型。除了字段 _id ，其他字段都允许为 NULL，因为 Mongodb 中为非结构化，字段可能有不统一的情况。LOCATION 则指定 Mongdb 中的 Collection，格式为 <database>.<collection>。

为了获得最佳查询性能，我们建议在外部表列上创建统计信息，尤其是用于联接、筛选和聚合的统计信息。这是可选项。

-- 6. 创建统计信息
CREATE STATISTICS OrdersOrderKeyStatistics ON MongoDB.tab([id]) WITH FULLSCAN
GO

此技术存在几个潜在问题，其中包括两个问题：

• 空的重复字段将有效地屏蔽包含在相同记录的平面字段中的数据

• 存在多个重复字段可能导致生成的行数呈爆炸式增长