Import MongoDB data to AnalyticDB for MySQL by using external tables - AnalyticDB

This topic describes how to use external tables to query MongoDB data and import the data into AnalyticDB for MySQL.

Prerequisites

An AnalyticDB for MySQL Enterprise Edition, Basic Edition, or Data Lakehouse Edition cluster is created.
The ENI switch is enabled in the Network Information section of the Cluster Information page in the AnalyticDB for MySQL console.
Important
Enabling or disabling the ENI network interrupts the database connection for about 2 minutes. During this period, read and write operations cannot be performed. Carefully evaluate the impact before you enable or disable the ENI network.
The MongoDB instance and the AnalyticDB for MySQL cluster are in the same VPC.
The VPC CIDR block of the AnalyticDB for MySQL cluster is added to the whitelist of the MongoDB instance.

Import data from non-nested documents in MongoDB

Sample data

In this example, a collection named person is created in the test_mongodb database of a MongoDB instance.

use test_mongodb;
db.createCollection("person");

Insert documents into the person collection.

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});

Procedure

Go to the SQL editor.
1. Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. Find the cluster that you want to manage and click the cluster ID.
2. In the navigation pane on the left, click Job Development > SQL Development.
Execute the following statement to create an external database.
```
CREATE EXTERNAL DATABASE adb_external_db;
```

Create an external table.

Important

The AnalyticDB for MySQL external table must have the same field names, number of fields, field order, and data types as the MongoDB document.

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

Table 1. Parameters

Parameter	Description
mapped_name	The name of the MongoDB collection. In this example, the name is `person`.
location	The VPC endpoint of the ApsaraDB for MongoDB instance. Format: `mongodb://testuser:**@dds-bp113d414bca8.mongodb.rds.aliyuncs.com:3717,dds-bp113d414bca8.mongodb.rds.aliyuncs.com:3717/database`. Example: `mongodb://testuser:@dds-bp113d414bca8.mongodb.rds.aliyuncs.com:3717,dds-bp113d414bca8.mongodb.rds.aliyuncs.com:3717/test_mongodb`. Note** Do not use the endpoints of secondary nodes to connect to the MongoDB instance.
username	The database account of the MongoDB instance. Note MongoDB verifies the database account and password in the destination database. Use the account of the database that is specified in the VPC endpoint of the MongoDB instance. If you have questions, contact technical support.
password	The password of the MongoDB database account.

Query data.

After the external table is created, you can use a SELECT statement to query data in the person collection.

SELECT * FROM adb_external_db.person;

The following result is returned:

+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | james |   10 |
|    2 | bond  |   20 |
|    3 | jack  |   30 |
|    4 | lock  |   40 |
+------+-------+------+
4 rows in set (0.35 sec)

Create a database and a table in the AnalyticDB for MySQL cluster to store the data imported from MongoDB.
1. Create a database named adb_demo.
```
CREATE DATABASE adb_demo; 
```
2. Create a table named adb_demo.adb_import_test.
  Important
  The table created in the AnalyticDB for MySQL Enterprise Edition, Basic Edition, or Data Lakehouse Edition cluster must have the same field names, number of fields, field order, and data types as the AnalyticDB for MySQL external table.
```
 CREATE TABLE IF NOT EXISTS adb_demo.adb_import_test(id int,name varchar(1023),age int ) DISTRIBUTED BY HASH(id);
```
Import data from MongoDB to the AnalyticDB for MySQL Enterprise Edition, Basic Edition, or Data Lakehouse Edition cluster.
- Method 1: Use the INSERT INTO statement to import data. If a primary key value is duplicated, the new data is ignored. This is equivalent to using INSERT IGNORE INTO. For more information, see INSERT INTO.
```
INSERT INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;
```
- Method 2: Use the INSERT OVERWRITE INTO statement to synchronously import data. This overwrites the existing data in the table.
```
INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;
```
- Method 3: Use the INSERT OVERWRITE INTO statement to asynchronously import data. For more information, see Asynchronous write.
```
SUBMIT JOB INSERT OVERWRITE adb_demo.adb_import_test SELECT * FROM adb_external_db.person;
```

After the data is imported, you can use a SELECT statement to query data in the adb_demo.adb_import_test table.

SELECT * FROM adb_demo.adb_import_test;

The following result is returned:

+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | james |   10 |
|    2 | bond  |   20 |
|    3 | jack  |   30 |
|    4 | lock  |   40 |
+------+-------+------+

Query nested documents in MongoDB

Sample data

In the test_mongodb database, create a collection named test_json.

db.createCollection("test_json");

Insert a document into the test_json collection. The city and name fields are nested fields.

db.test_json.insert(    {
        'id': 1,
        'details':{'city': "hangzhou", "name":"jack"}
    })

Procedure

Go to the SQL editor.
1. Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. Find the cluster that you want to manage and click the cluster ID.
2. In the navigation pane on the left, click Job Development > SQL Development.
Execute the following statement to create an external database.
```
CREATE EXTERNAL DATABASE adb_external_db;
```
Create an external table.
Important
- The AnalyticDB for MySQL external table must have the same field names, number of fields, field order, and data types as the MongoDB document.
- External tables cannot be used to write data to nested documents in 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",
}';
```
Parameters:
- COLUMN_MAPPING: Defines the mappings between the fields of the external table and the fields of the MongoDB document. For example, a mapping is created between the city field of the external table and the details.city field of the MongoDB document.
- For information about other parameters for the external table, see Parameter descriptions.
Query data.
After the external table is created, you can use a SELECT statement to query data in the test_json collection.
```
SELECT * FROM adb_external_db.test_json;
```
The following result is returned:
```
+------+----------+-------+
| id   |  city    |  name |
+------+----------+-------+
|    1 | hangzhou |  jack |
+------+----------+-------+
```
Note
To import data from nested documents in MongoDB to AnalyticDB for MySQL, you must first create a database and a table. For more information, see Steps 5 to 7 in the Import data from non-nested documents in MongoDB section of this topic.

Query ObjectId fields

Sample data

In the test_mongodb database, create a collection named test_objectid.

db.createCollection("test_objectid");

Insert a document into the test_objectid collection.

db.test_objectid.insert(    {
        'id': 1,
    })

Query the documents in the test_objectid collection.

db.test_objectid.find()

The following result is returned:

{
   "_id":"ObjectId("641002ad883a73eb0d7291a7")"
   "id":1
}

Procedure

Go to the SQL editor.
1. Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. Find the cluster that you want to manage and click the cluster ID.
2. In the navigation pane on the left, click Job Development > SQL Development.
Execute the following statement to create an external database.
```
CREATE EXTERNAL DATABASE adb_external_db;
```

Create an external table.

Important

The AnalyticDB for MySQL external table must have the same field names, number of fields, field order, and data types as the MongoDB document.

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

Query data.
After the external table is created, you can use a SELECT statement to query data in the test_objectid collection.
```
SELECT cast(_id as string) FROM adb_external_db.test_objectid;
```
Sample result:
```
+----------------------------+
|    CAST(_id AS string)     | 
+----------------------------+
|  641002ad883a73eb0d7291a7  |
+----------------------------+
```
Note
To import data that contains ObjectId fields into AnalyticDB for MySQL, you must first create a database and a table to store the data. For more information, see Steps 5 to 7 in Import data from non-nested documents in MongoDB.

Data type mappings between AnalyticDB for MySQL external tables and MongoDB collections

AnalyticDB for MySQL external table	MongoDB collection
Boolean	Boolean
ObjectId	ObjectId
String	String
Int	32-bit Integer, Int
Bigint	64-bit Integer Long
Double	Double
Date	Date