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

适用于:SQL Server

本文介绍如何使用 SQL Server 实例上的 PolyBase 来查询 MongoDB 中的外部数据。

先决条件

如果尚未安装 PolyBase,请参阅 PolyBase 安装

创建数据库范围凭据之前,数据库必须具有主密钥用于保护凭据。 有关详细信息,请参阅 CREATE MASTER KEY

配置 MongoDB 外部数据源

若要查询 MongoDB 数据源中的数据,必须创建外部表以引用外部数据。 本节提供用于创建这些外部表的示例代码。

此部分中使用了以下 Transact-SQL 命令:

  1. 创建数据库范围凭据以访问 MongoDB 数据源。

    下面的脚本将创建数据库范围的凭据。 在运行脚本之前,请针对你的环境更新它:

    • <credential_name> 替换为凭据的名称。
    • <username> 替换为外部源的用户名。
    • <password> 替换为适当的密码。
    CREATE DATABASE SCOPED CREDENTIAL [<credential_name>] WITH IDENTITY = '<username>', Secret = '<password>';
    

    重要

    用于 PolyBase 的 MongoDB ODBC 连接器仅支持基本身份验证,不支持 Kerberos 身份验证。

  2. 创建外部数据源。

    以下脚本将创建外部数据源。 有关详细信息,请参阅 CREATE EXTERNAL DATA SOURCE。 在运行脚本之前,请针对你的环境更新它:

    • 更新位置。 为你的环境设置 <server><port>
    • <credential_name> 替换为在上一步中创建的凭据的名称。
    • (可选)如果想要指定外部源的下推计算,可以指定 PUSHDOWN = ONPUSHDOWN = 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 } ])
    [ ; ]
    
  3. 在 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 驱动程序自动执行平展。

  4. 创建外部表。

    如果使用适用于 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])
    
  5. 可选:在外部表上创建统计信息。

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

    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 的概述