AnalyticDB for MySQL:Import data from ApsaraDB for MongoDB to AnalyticDB for MySQL

Import data from non-nested documents of ApsaraDB for MongoDB

Sample data

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.
   2. In the upper-left corner of the page, select the region where the cluster resides.
   3. In the left-side navigation pane, click Clusters.
   4. On the Data Lakehouse Edition (V3.0) tab, find the cluster and click the Cluster ID.
   5. In the left-side navigation pane, choose Job Development > SQL Development.
2. Execute the following statement to create an external database:

   CREATE EXTERNAL DATABASE adb_external_db;

3. Create an external table.
   Important The AnalyticDB for MySQL external table must have the same names, quantity, order, and data types of fields as the ApsaraDB for MongoDB collection.

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

   Table 1. Parameters

   Parameter	Description
   mapped_name	The name of the ApsaraDB for MongoDB collection. In this example, person is used.
   location	The VPC endpoint that is used to connect to the ApsaraDB for MongoDB instance. For more information about how to obtain the VPC endpoint, see Overview of instance connections. 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.
   username	The name of the database account that is used to connect to the ApsaraDB for MongoDB instance. For more information about how to create a database account, see Manage user permissions on MongoDB databases. Note ApsaraDB for MongoDB verifies the specified database account and the password. You must use the database account that is contained in the VPC endpoint of the ApsaraDB for MongoDB instance. If you have any questions, contact technical support.
   password	The password of the database account.

4. Query data.
   After the external table is created, you can execute the SELECT statement to query data of the person collection.

   SELECT * FROM adb_external_db.person;

   The following information 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 a table in the AnalyticDB for MySQL Data Lakehouse Edition (V3.0) cluster to store data that is imported from the ApsaraDB for MongoDB instance.
   1. Create a database named adb_demo.

      CREATE DATABASE adb_demo; 

   2. Create a table named adb_demo.adb_import_test.
      Important The table that is created in the AnalyticDB for MySQL Data Lakehouse Edition (V3.0) cluster must have the same names, quantity, order, and data types of fields as the AnalyticDB for MySQL external table.

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

6. Import data from the ApsaraDB for MongoDB instance to the AnalyticDB for MySQL Data Lakehouse Edition (V3.0) cluster.
   • Method 1: Execute the INSERT INTO statement to import data. If the primary key has duplicate values, data is not repeatedly inserted, and the INSERT INTO statement is 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: Execute the INSERT OVERWRITE INTO statement to synchronously import data. If the primary key has duplicate values, the original value is overwritten by the new value.

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

   • Method 3: Execute the INSERT OVERWRITE INTO statement to asynchronously import data. For more information, see Asynchronous writing.

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

7. After data is imported, execute the SELECT statement to query data of the adb_demo.adb_import_test table.

   SELECT * FROM adb_demo.adb_import_test;

   The following information is returned:

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

Query data in nested documents of ApsaraDB for MongoDB

Sample data

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.
   2. In the upper-left corner of the page, select the region where the cluster resides.
   3. In the left-side navigation pane, click Clusters.
   4. On the Data Lakehouse Edition (V3.0) tab, find the cluster and click the Cluster ID.
   5. In the left-side navigation pane, choose Job Development > SQL Development.
2. Execute the following statement to create an external database:

   CREATE EXTERNAL DATABASE adb_external_db;

3. Create an external table.
   Important

   • The AnalyticDB for MySQL external table must have the same names, quantity, order, and data types of fields as the ApsaraDB for MongoDB collection.
   • You cannot use external tables to import data to nested documents of ApsaraDB for 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",
   }';

   Parameters:

   • COLUMN_MAPPING: defines the mappings between external table fields and ApsaraDB for MongoDB document fields. For example, a mapping between the city field of the external table and the details.city field of the ApsaraDB for MongoDB document is created.
   • For information about other parameters that are used to create an external table, see the "Parameters" section of this topic.
4. Query data.
   After the external table is created, you can execute the SELECT statement to query data of the test_json collection.

   SELECT * FROM adb_external_db.test_json;

   The following information is returned:

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

   Note To import data from nested documents of ApsaraDB for MongoDB to AnalyticDB for MySQL, you must create a database and a table first. For more information, see Steps 5 to 7 in the "Import data from non-nested documents of ApsaraDB for MongoDB" section of this topic.

Query data of ObjectId fields

Sample data

db.createCollection("test_objectid");

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

db.test_objectid.find()

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

Procedure

1. Go to the SQL editor.
   1. Log on to the AnalyticDB for MySQL console.
   2. In the upper-left corner of the page, select the region where the cluster resides.
   3. In the left-side navigation pane, click Clusters.
   4. On the Data Lakehouse Edition (V3.0) tab, find the cluster and click the Cluster ID.
   5. In the left-side navigation pane, choose Job Development > SQL Development.
2. Execute the following statement to create an external database:

   CREATE EXTERNAL DATABASE adb_external_db;

3. Create an external table.
   Important The AnalyticDB for MySQL external table must have the same names, quantity, order, and data types of fields as the ApsaraDB for MongoDB collection.

   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. Query data.
   After the external table is created, you can execute the SELECT statement to query data of the test_objectid collection.

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

   The following information is returned:

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

   Note To import data from ObjectId fields to AnalyticDB for MySQL, you must create a database and a table first. For more information, see Steps 5 to 7 in the "Import data from non-nested documents of ApsaraDB for MongoDB" section of this topic.

Data types supported by external tables