通过外表将MongoDB数据导入至湖仓版 - 云原生数据仓库AnalyticDB

本文介绍如何通过外表查询MongoDB数据，以及如何将MongoDB中的数据导入至云原生数据仓库 AnalyticDB MySQL 版。

前提条件

AnalyticDB for MySQL集群的产品系列为湖仓版。
已在AnalyticDB for MySQL控制台集群信息页面的网络信息栏启用ENI网络开关。
MongoDB实例与AnalyticDB for MySQL所属同一VPC。具体操作，请参见创建集群。
已将AnalyticDB for MySQL集群的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});

操作步骤

进入SQL开发编辑器。
1. 登录云原生数据仓库AnalyticDB MySQL控制台，在左上角选择集群所在地域。在左侧导航栏，单击集群列表，在湖仓版页签下，单击目标集群ID。
2. 在左侧导航栏，单击作业开发 > SQL开发。
执行以下语句，创建外部数据库。示例如下：
```
CREATE EXTERNAL DATABASE adb_external_db;
```

创建外表。示例如下：

重要

AnalyticDB for MySQL的外表和MongoDB文档中的字段（field）名称、字段数量、字段顺序、数据类型必须相同。

CREATE EXTERNAL 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_name	MongoDB集合的名称。本文示例中为`person`。
location	MongoDB的专有网络地址。如何获取专有网络的连接地址，请参见实例连接地址说明。格式：`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`。说明在连接MongoDB时，请勿使用Secondary节点地址。
username	MongoDB数据库的账号。如何创建数据库账号，请参见MongoDB数据库账号权限管理。说明 MongoDB需要在目标数据库中校验数据库的账号和密码，请使用MongoDB专有网络地址中指定数据库的账号，如遇问题，请联系技术支持。
password	MongoDB数据库账号的密码。

查询数据。

外表创建成功后，您可以使用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)

在AnalyticDB for MySQL集群中创建数据库和表，用于存储从MongoDB中导入的数据。
1. 创建名为adb_demo的数据库。
```
CREATE DATABASE adb_demo; 
```
2. 创建名为adb_demo.adb_import_test的数据表。
  重要
  AnalyticDB for MySQL企业版、基础版及湖仓版中创建的表和AnalyticDB for MySQL外表中的字段名称、字段数量、字段顺序、数据类型必须相同。
```
 CREATE TABLE IF NOT EXISTS adb_demo.adb_import_test(id int,name varchar(1023),age int ) DISTRIBUTED BY HASH(id);
```
将MongoDB中的数据导入至AnalyticDB for MySQL企业版、基础版及湖仓版中。
- 方法一：使用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;
```

数据导入完成后，您可以使用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"}
    })

操作步骤

进入SQL开发编辑器。
1. 登录云原生数据仓库AnalyticDB MySQL控制台，在左上角选择集群所在地域。在左侧导航栏，单击集群列表，在湖仓版页签下，单击目标集群ID。
2. 在左侧导航栏，单击作业开发 > SQL开发。
执行以下语句，创建外部数据库。示例如下：
```
CREATE EXTERNAL DATABASE adb_external_db;
```
创建外表。示例如下：
重要
- AnalyticDB for MySQL的外表和MongoDB文档中的字段（field）名称、字段数量、字段顺序、数据类型必须相同。
- 不支持通过外表将数据写入MongoDB嵌套文档。
```
CREATE EXTERNAL 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字段为映射关系。
- 外表中其他参数的详细说明，请参见参数说明。
查询数据。
外表创建成功后，您可以使用SELECT语句查询test_json集合中的数据。
```
SELECT * FROM adb_external_db.test_json;
```
返回结果：
```
+------+----------+-------+
| id   |  city    |  name |
+------+----------+-------+
|    1 | hangzhou |  jack |
+------+----------+-------+
```
说明
如果要将MongoDB嵌套文档导入到AnalyticDB for 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
}

操作步骤

进入SQL开发编辑器。
1. 登录云原生数据仓库AnalyticDB MySQL控制台，在左上角选择集群所在地域。在左侧导航栏，单击集群列表，在湖仓版页签下，单击目标集群ID。
2. 在左侧导航栏，单击作业开发 > SQL开发。
执行以下语句，创建外部数据库。示例如下：
```
CREATE EXTERNAL DATABASE adb_external_db;
```

创建外表。示例如下：

重要

AnalyticDB for MySQL的外表和MongoDB文档中的字段（field）名称、字段数量、字段顺序、数据类型必须相同。

CREATE EXTERNAL 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",
}';

查询数据。
外表创建成功后，您可以使用SELECT语句查询test_objectid集合中的数据。
```
SELECT cast(_id as string) FROM adb_external_db.test_objectid;
```
返回结果：
```
+----------------------------+
|    CAST(_id AS string)     | 
+----------------------------+
|  641002ad883a73eb0d7291a7  |
+----------------------------+
```
说明
如果要将ObjectId字段导入到AnalyticDB for MySQL中，需要先创建用于存储ObjectId字段的数据库和数据表，具体操作，请参见导入MongoDB非嵌套文档中的步骤5~7。

外表支持的数据类型

AnalyticDB for MySQL外表	MongoDB数据表
Boolean	Boolean
ObjectId	ObjectId
String	String
Int	32-bit Integer、Int
Bigint	64-bit Integer Long
Double	Double
Date	Date