本文介绍如何通过AnalyticDB MySQL湖仓版（3.0）外表查询MongoDB数据，以及如何将MongoDB中的数据导入至AnalyticDB MySQL湖仓版（3.0）。

前提条件

  • 已在MongoDB实例所在的同一VPC中创建AnalyticDB MySQL湖仓版（3.0）集群。具体操作，请参见创建湖仓版（3.0）集群。
  • 已在AnalyticDB MySQL控制台集群信息页面的网络信息栏启用ENI网络开关。
  • 已将AnalyticDB MySQL湖仓版（3.0）集群的VPC网段加入MongoDB实例的白名单中。具体操作，请参见设置白名单。

导入MongoDB非嵌套文档

示例数据说明

本文示例的MongoDB数据库名为test_mongodb，并在该库中创建名为person的集合，示例如下：
use test_mongodb;
db.createCollection("person");
向person集合中插入文档，示例如下：
db.person.insert({"id":1,"name":"james","age":10});
db.person.insert({"id":2,"name":"bond","age":20});
db.person.insert({"id":3,"name":"jack","age":30});
db.person.insert({"id":4,"name":"lock","age":40});

操作步骤

  1. 进入SQL开发编辑器。
    1. 登录云原生数据仓库AnalyticDB MySQL控制台。
    2. 在页面左上角，选择集群所在地域。
    3. 在左侧导航栏，单击集群列表。
    4. 在湖仓版（3.0）页签下，单击目标集群ID。
    5. 在左侧导航栏，单击作业开发 > SQL开发。
  2. 执行以下语句，创建外部数据库。示例如下：
    CREATE EXTERNAL DATABASE adb_external_db;
  3. 创建外表。示例如下：
    重要 AnalyticDB MySQL的外表和MongoDB文档中的字段（field）名称、字段数量、字段顺序、数据类型必须相同。
    CREATE TABLE adb_external_db.person (
  id int,
  name string,
  age int
) ENGINE = 'MONGODB' TABLE_PROPERTIES ='{
"mapped_name":"person",
"location":"mongodb://testuser:****@dds-bp113d414bca8****.mongodb.rds.aliyuncs.com:3717,dds-bp113d414bca8****.mongodb.rds.aliyuncs.com:3717/test_mongodb",
"username":"testuser",
"password":"password",
}';
    表 1. 参数说明
    参数说明
    mapped_nameMongoDB集合的名称。本文示例中为person。
    locationMongoDB的专有网络地址。如何获取专有网络的连接地址，请参见实例连接地址说明。

    格式：mongodb://testuser:****@dds-bp113d414bca8****.mongodb.rds.aliyuncs.com:3717,dds-bp113d414bca8****.mongodb.rds.aliyuncs.com:3717/database。

    示例：mongodb://testuser:****@dds-bp113d414bca8****.mongodb.rds.aliyuncs.com:3717,dds-bp113d414bca8****.mongodb.rds.aliyuncs.com:3717/test_mongodb。

    usernameMongoDB数据库的账号。如何创建数据库账号，请参见MongoDB数据库账号权限管理。
    说明 MongoDB需要在目标数据库中校验数据库的账号和密码，请使用MongoDB专有网络地址中指定数据库的账号，如遇问题，请联系技术支持。
    passwordMongoDB数据库账号的密码。
  4. 查询数据。
    外表创建成功后，您可以使用SELECT语句查询person集合中的数据。
    SELECT * FROM adb_external_db.person;
    返回结果：
    +------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | james |   10 |
|    2 | bond  |   20 |
|    3 | jack  |   30 |
|    4 | lock  |   40 |
+------+-------+------+
4 rows in set (0.35 sec)
  5. 在AnalyticDB MySQL湖仓版（3.0）集群中创建数据库和表，用于存储从MongoDB中导入的数据。
    1. 创建名为adb_demo的数据库。
      CREATE DATABASE adb_demo;
    2. 创建名为adb_demo.adb_import_test的数据表。
      重要 AnalyticDB MySQL湖仓版（3.0）中创建的表和AnalyticDB MySQL外表中的字段名称、字段数量、字段顺序、数据类型必须相同。
       CREATE TABLE IF NOT EXISTS adb_demo.adb_import_test(id int,name varchar(1023),age int ) DISTRIBUTE BY HASH(id);
  6. 将MongoDB中的数据导入至AnalyticDB MySQL湖仓版（3.0）中。
    • 方法一：使用INSERT INTO语句导入数据，当主键重复时会自动忽略当前写入数据，数据不做更新，作用等同于INSERT IGNORE INTO，更多信息，请参见INSERT INTO。示例如下：
      INSERT INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;
    • 方法二：使用INSERT OVERWRITE INTO语句同步导入数据，会覆盖表中原有的数据。示例如下：
      INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;
    • 方法三：使用INSERT OVERWRITE INTO语句异步导入数据，更多信息，请参见异步写入。示例如下：
      SUBMIT JOB INSERT OVERWRITE adb_demo.adb_import_test SELECT * FROM adb_external_db.person;
  7. 数据导入完成后，您可以使用SELECT语句查询adb_demo.adb_import_test表中的数据。
    SELECT * FROM adb_demo.adb_import_test;
    返回结果：
    +------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | james |   10 |
|    2 | bond  |   20 |
|    3 | jack  |   30 |
|    4 | lock  |   40 |
+------+-------+------+

查询MongoDB嵌套文档

示例数据

在数据库test_mongodb中创建名为test_json的集合，示例如下：
db.createCollection("test_json");
向test_json集合中插入文档，其中city和name为嵌套字段，示例如下：
db.test_json.insert(    {
        'id': 1,
        'details':{'city': "hangzhou", "name":"jack"}
    })

操作步骤

  1. 进入SQL开发编辑器。
    1. 登录云原生数据仓库AnalyticDB MySQL控制台。
    2. 在页面左上角，选择集群所在地域。
    3. 在左侧导航栏，单击集群列表。
    4. 在湖仓版（3.0）页签下，单击目标集群ID。
    5. 在左侧导航栏，单击作业开发 > SQL开发。
  2. 执行以下语句，创建外部数据库。示例如下：
    CREATE EXTERNAL DATABASE adb_external_db;
  3. 创建外表。示例如下：
    重要
    • AnalyticDB MySQL的外表和MongoDB文档中的字段（field）名称、字段数量、字段顺序、数据类型必须相同。
    • 不支持通过外表将数据写入MongoDB嵌套文档。
    CREATE TABLE adb_external_db.test_json (
  id int,
  city string,
  name string
) ENGINE = 'MONGODB' TABLE_PROPERTIES ='{
"mapped_name":"test_json",
"location":"mongodb://testuser:****@dds-bp113d414bca8****.mongodb.rds.aliyuncs.com:3717,dds-bp113d414bca8****.mongodb.rds.aliyuncs.com:3717/test_mongodb",
"username":"testuser",
"password":"password",
"COLUMN_MAPPING":"city,details.city;name,details.name",
}';
    参数说明：
    • COLUMN_MAPPING：定义外表字段与MongoDB字段的映射关系。例如：外表的city字段与MongoDB文档的details.city字段为映射关系。
    • 外表中其他参数的详细说明，请参见参数说明。
  4. 查询数据。
    外表创建成功后，您可以使用SELECT语句查询test_json集合中的数据。
    SELECT * FROM adb_external_db.test_json;
    返回结果：
    +------+----------+-------+
| id   |  city    |  name |
+------+----------+-------+
|    1 | hangzhou |  jack |
+------+----------+-------+
    说明 如果要将MongoDB嵌套文档导入到AnalyticDB MySQL中，需要先创建用于存储MongoDB嵌套文档的数据库和数据表，具体操作，请参见导入MongoDB非嵌套文档中的步骤5~7。

查询ObjectId字段

示例数据

在数据库test_mongodb中创建名为test_objectid的集合，示例如下：
db.createCollection("test_objectid");
向test_objectid集合中插入文档，示例如下：
db.test_objectid.insert(    {
        'id': 1,
    })
查询test_objectid集合中的文档，示例如下：
db.test_objectid.find()
返回结果：
{
   "_id":"ObjectId("641002ad883a73eb0d7291a7")"
   "id":1
}

操作步骤

  1. 进入SQL开发编辑器。
    1. 登录云原生数据仓库AnalyticDB MySQL控制台。
    2. 在页面左上角，选择集群所在地域。
    3. 在左侧导航栏，单击集群列表。
    4. 在湖仓版（3.0）页签下，单击目标集群ID。
    5. 在左侧导航栏，单击作业开发 > SQL开发。
  2. 执行以下语句，创建外部数据库。示例如下：
    CREATE EXTERNAL DATABASE adb_external_db;
  3. 创建外表。示例如下：
    重要 AnalyticDB MySQL的外表和MongoDB文档中的字段（field）名称、字段数量、字段顺序、数据类型必须相同。
    CREATE TABLE adb_external_db.test_objectid (
  id int,
  _id objectid
) ENGINE = 'MONGODB' TABLE_PROPERTIES ='{
"mapped_name":"test_objectid",
"location":"mongodb://testuser:****@dds-bp113d414bca8****.mongodb.rds.aliyuncs.com:3717,dds-bp113d414bca8****.mongodb.rds.aliyuncs.com:3717/test_mongodb",
"username":"testuser",
"password":"password",
}';
  4. 查询数据。
    外表创建成功后，您可以使用SELECT语句查询test_objectid集合中的数据。
    SELECT cast(_id as string) FROM adb_external_db.test_objectid;
    返回结果：
    +----------------------------+
|    CAST(_id AS string)     | 
+----------------------------+
|  641002ad883a73eb0d7291a7  |
+----------------------------+
    说明 如果要将ObjectId字段导入到AnalyticDB MySQL中，需要先创建用于存储ObjectId字段的数据库和数据表，具体操作，请参见导入MongoDB非嵌套文档中的步骤5~7。

外表支持的数据类型

AnalyticDB MySQL外表MongoDB数据表
BooleanBoolean
ObjectIdObjectId
StringString
Int32-bit Integer、Int
Bigint64-bit Integer Long
DoubleDouble
DateDate