This topic describes how to create an ApsaraDB for MongoDB table and read data from and write data to the table.

Create an ApsaraDB for MongoDB table

After an ApsaraDB for MongoDB schema is created, you can create an ApsaraDB for MongoDB table. Execute the following statement to create the person table in the ApsaraDB for MongoDB database in the DMS console.

​create external table dla_person (
    id int,
    title varchar(127),
    age int,
    create_time timestamp
)TBLPROPERTIES (
    TABLE_MAPPING = 'person',
    COLUMN_MAPPING = 'title,name;'
);​
  • external: the table that you want to create is an external table.
  • TBLPROPERTIES: the mapping between the external table and source table.
    • TABLE_MAPPING: the mapping between the names of the external table and source table.
    • COLUMN_MAPPING: the mapping between fields in the external table and source table.

After you create the table, you can connect to the ApsaraDB for MongoDB schema in DLA by using a MySQL client or MySQL command-line tool. Then, you can execute SQL statements to read data from and write data to the ApsaraDB for MongoDB database.

Use DLA to write OSS data to the ApsaraDB for MongoDB database

After you analyze large amounts of data in Object Storage Service (OSS) or Tablestore, you can use DLA to write back result data to the ApsaraDB for MongoDB database for frontend business to use.

For example, you can connect to DLA by using a MySQL command-line tool and execute the following statements to convert the first ten records of a customer in oss_db and inserts these records into the mongo_test.dla_person table.

​mysql> insert into mongo_test.dla_person
    -> select c_custkey, c_name, c_custkey + 20, now() from oss_db.customer limit 10;
+------+
| rows |
+------+
|   10 |
+------+
1 row in set (3.72 sec)
mysql> select * from mongo_test.dla_person;
+------+--------------------+------+-------------------------+
| id   | title              | age  | create_time             |
+------+--------------------+------+-------------------------+
|    1 | james              |   10 | 2018-12-14 14:22:54.369 |
|    2 | bond               |   20 | 2018-12-14 14:23:48.527 |
|    3 | lily               |   30 | 2018-12-14 14:23:48.962 |
|    4 | lucy               |   20 | 2018-12-14 14:23:49.396 |
|    1 | Customer#000000001 |   21 | 2018-12-20 10:15:56.629 |
|    3 | Customer#000000003 |   23 | 2018-12-20 10:15:56.629 |
|    5 | Customer#000000005 |   25 | 2018-12-20 10:15:56.629 |
|    7 | Customer#000000007 |   27 | 2018-12-20 10:15:56.629 |
|    9 | Customer#000000009 |   29 | 2018-12-20 10:15:56.629 |
|    2 | Customer#000000002 |   22 | 2018-12-20 10:15:56.629 |
|    4 | Customer#000000004 |   24 | 2018-12-20 10:15:56.629 |
|    6 | Customer#000000006 |   26 | 2018-12-20 10:15:56.629 |
|    8 | Customer#000000008 |   28 | 2018-12-20 10:15:56.629 |
|   10 | Customer#000000010 |   30 | 2018-12-20 10:15:56.629 |
+------+--------------------+------+-------------------------+
14 rows in set (0.16 sec)​

Query data from an ApsaraDB for MongoDB table with nested fields

A collection with the nested data structure is stored in the ApsaraDB for MongoDB database. The city field is nested in the details field.

​db.Ha.insert({
    id: 1,
    name: "james",
    age: 1,
    details: {
      city: "hangzhou"
    }
});​

Execute the following statement to create an external table in DLA:

CREATE EXTERNAL TABLE Ha (
    id int,
    name string,
    age int,
    city string
)
TBLPROPERTIES (
    COLUMN_MAPPING = 'city,details.city;'
)

COLUMN_MAPPING: the mapping between the fields that are declared in DLA and the fields in the ApsaraDB for MongoDB database.

Query table data in DLA.

​mysql> select * from odps;
+------+-------+------+----------+
| id   | name  | age  | city     |
+------+-------+------+----------+
|    1 | james |    1 | hangzhou |​

Query fields of the ObjectId type

If you want to query a field of the object_id type, such as _id, you can execute the following statement to create a table

:

​CREATE EXTERNAL TABLE Ha (
    _id ObjectId,
    id int,
    name string,
    age int,
    city string
);​

Query table data in DLA.

​​mysql> select * from Ha where _id = ObjectId('5edef42407bcd42c8bad4284');
+---------------------------------------+------+-------+------+----------+
| _id                                   | id   | name  | age  | city     |
+---------------------------------------+------+-------+------+----------+
| 5e de f4 24 07 bc d4 2c   8b ad 42 84 |    1 | james |    1 | hangzhou |​