AnalyticDB:Import MongoDB data using external tables

Sample data

The examples use a collection named person in the test_mongodb database.

use test_mongodb;
db.createCollection("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});

Procedure

1. 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 left-side navigation pane, click Job Development > SQL Development.

2. Create an external database.

   CREATE EXTERNAL DATABASE adb_external_db;

3. Create an external table.

   Important

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

   Parameter	Required	Description
   mapped_name	Yes	The name of the MongoDB collection. In this example: person.
   location	Yes	The VPC endpoint of the ApsaraDB for MongoDB instance. Format: mongodb://<username>:<password>@<host>:<port>,<host>:<port>/<database>. Do not use endpoints of secondary nodes.
   username	Yes	The database account of the MongoDB instance. MongoDB verifies the account against the destination database specified in the location value. Important Clusters of Enterprise Edition, Basic Edition, and Data Lakehouse Edition with kernel version 3.1.8.0 require an account of the admin database. Otherwise, an error is reported. Clusters of Enterprise Edition, Basic Edition, and Data Lakehouse Edition with kernel version 3.1.8.1 use the account of the database that is specified in the VPC endpoint of the MongoDB instance. Clusters of Enterprise Edition, Basic Edition, and Data Lakehouse Edition with kernel version 3.1.8.1 use the account of the database that is specified in the VPC endpoint of the MongoDB instance. To view the kernel version of a cluster, see View instance information. To upgrade the kernel version, contact technical support.
   password	Yes	The password of the MongoDB database account.
   COLUMN_MAPPING	No	Maps external table fields to nested fields in the MongoDB document. Format: <external_field>,<mongo_nested_field>. Separate multiple mappings with semicolons. Example: city,details.city;name,details.name. Required only for nested documents.

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

   The following table describes the TABLE_PROPERTIES parameters.

4. Query data from the external table.

   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)

5. Create a database and table in AnalyticDB for MySQL to store the imported data.

   1. Create a database named adb_demo.

      CREATE DATABASE adb_demo;

   2. Create a table named adb_import_test.

      Important

      The table must have the same field names, number of fields, field order, and data types as the external table.

      CREATE TABLE IF NOT EXISTS adb_demo.adb_import_test(
        id int,
        name varchar(1023),
        age int
      ) DISTRIBUTED BY HASH(id);

6. Import data from MongoDB into AnalyticDB for MySQL using one of the following methods.

   • Method 1: INSERT INTO — If a primary key value is duplicated, the new data is ignored (equivalent to INSERT IGNORE INTO). For more information, see INSERT INTO.

     INSERT INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;

   • Method 2: INSERT OVERWRITE INTO (synchronous) — Overwrites all existing data in the target table.

     INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;

   • Method 3: SUBMIT JOB INSERT OVERWRITE (asynchronous) — Submits the import as a background job. For more information, see Asynchronous write.

     SUBMIT JOB INSERT OVERWRITE adb_demo.adb_import_test SELECT * FROM adb_external_db.person;

7. Verify the import by querying the target 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 |
   +------+-------+------+

Sample data

The examples use a collection named test_json in the test_mongodb database, where city and name are nested under the details field.

db.createCollection("test_json");

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

Procedure

1. 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 left-side navigation pane, click Job Development > SQL Development.

2. Create an external database.

   CREATE EXTERNAL DATABASE adb_external_db;

3. Create an external table with COLUMN_MAPPING.

   Important

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

   • External tables cannot 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",
   }';

   The COLUMN_MAPPING parameter maps each external table field to its corresponding nested path in the MongoDB document. In this example, city maps to details.city and name maps to details.name. For a description of all parameters, see the TABLE_PROPERTIES parameters table in Import data from non-nested documents.

4. Query data from the external table.

   SELECT * FROM adb_external_db.test_json;

   The following result is returned:

   +------+----------+-------+
   | id   |  city    |  name |
   +------+----------+-------+
   |    1 | hangzhou |  jack |
   +------+----------+-------+

To import data from nested MongoDB documents into AnalyticDB for MySQL, create a target database and table first. Follow steps 5 to 7 in the Import data from non-nested documents section.

Sample data

The examples use a collection named test_objectid in the test_mongodb database.

db.createCollection("test_objectid");
db.test_objectid.insert({'id': 1});

After inserting, the document contains an auto-generated _id field of type ObjectId:

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

Procedure

1. 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 left-side navigation pane, click Job Development > SQL Development.

2. Create an external database.

   CREATE EXTERNAL DATABASE adb_external_db;

3. Create an external table with an objectid column.

   Important

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

4. Query the ObjectId field as a string.

   SELECT cast(_id as string) FROM adb_external_db.test_objectid;

   The following result is returned:

   +----------------------------+
   |    CAST(_id AS string)     |
   +----------------------------+
   |  641002ad883a73eb0d7291a7  |
   +----------------------------+

To import data that contains ObjectId fields into AnalyticDB for MySQL, create a target database and table first. Follow steps 5 to 7 in the Import data from non-nested documents section.