This topic describes how to create an ApsaraDB for MongoDB external table in Data Lake Analytics (DLA) and use this table to read data from and write data to an ApsaraDB for MongoDB database.

Create an ApsaraDB for MongoDB external table

After an ApsaraDB for MongoDB schema is created in DLA, you can use the schema to create an ApsaraDB for MongoDB external table. Execute the following statement to create an ApsaraDB for MongoDB external table named dla_person in the DLA console. 

​create external table dla_person (
    id int,
    name string,
    age int,
    create_time timestamp
)TBLPROPERTIES (
    TABLE_MAPPING = 'person'
);
Parameters:
  • 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 name of the source table.

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

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

After data in Object Storage Service (OSS) or Tablestore is analyzed, you can use DLA to write result data back to an ApsaraDB for MongoDB database.

For example, an ApsaraDB for MongoDB external table named dla_person is created in DLA. After you access DLA by using a MySQL command-line tool, execute the following statements to convert the first ten records of the customer table in the oss_db database and insert these records into the mongo_test.dla_person table in an ApsaraDB for MongoDB database. 

​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 an 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 ApsaraDB for MongoDB external table in DLA: 

CREATE EXTERNAL TABLE Ha (
    id int,
    name string,
    age int,
    city string
)
TBLPROPERTIES (
    COLUMN_MAPPING = 'city,details.city;'  # The mapping between the fields that are declared in DLA and the fields in the ApsaraDB for MongoDB database. 
)

Query table data by using DLA.

​mysql> select * from Ha;
+------+-------+------+----------+
| 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, from ApsaraDB for MongoDB, execute the following statement to create an external table in DLA: 

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

Query table data by using 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 |​