AnalyticDB:Import data from ApsaraDB for MongoDB

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. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. On the Data Lakehouse Edition tab, find the cluster that you want to manage and click the cluster ID.

   2. 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 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 ApsaraDB for MongoDB collection. In this example, person is used.
   location	The VPC endpoint of the ApsaraDB for MongoDB instance. For 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. Note When you connect to an ApsaraDB for MongoDB instance, do not use the endpoints of secondary nodes.
   username	The name of the database account of the ApsaraDB for MongoDB instance. For information about how to create a database account, see Manage the permissions of MongoDB database users. Note ApsaraDB for MongoDB verifies the specified database account name and password. You must use the database account name that is included 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 of the ApsaraDB for MongoDB instance.

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;

   Sample result:

   +------+-------+------+
   | 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 the "Asynchronous writing" section of the INSERT OVERWRITE SELECT topic.

     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;

   Sample result:

   +------+-------+------+
   | 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. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. On the Data Lakehouse Edition tab, find the cluster that you want to manage and click the cluster ID.

   2. 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 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 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" table 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;

   Sample result:

   +------+----------+-------+
   | 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");

A document is inserted into the test_objectid collection.

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

Data of the test_objectid collection is queried.

db.test_objectid.find()

Sample result:

{
   "_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. On the Data Lakehouse Edition tab, find the cluster that you want to manage and click the cluster ID.

   2. 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 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 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;

   Sample result:

   +----------------------------+
   |    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