All Products
Search
Document Center

Create a MongoDB table and read and write data from and to the table

Last Updated: Apr 23, 2019

Create a MongoDB table

After you created a MongoDB schema, you can create a MongoDB table. Create a table named person in your MongoDB database, as shown in the following example:

  1. create external table dla_person (
  2. id int,
  3. title varchar(127),
  4. age int,
  5. create_time timestamp
  6. )TBLPROPERTIES (
  7. TABLE_MAPPING = 'person',
  8. COLUMN_MAPPING = 'title,name'
  9. );
  • external: indicates the external table.

  • tblproperties: indicates the mapping between the external table and the source table.

  • table_mapping: indicates the mapping of the table names.

  • column_mapping: indicates the mapping of the fields.

After you created the table, you can use the MySQL client or MySQL CLI tool to connect to the MongoDB schema in DLA and run SQL statements to read and write data from and to the MongoDB database.

Use DLA to read data from OSS and write it to the MongoDB database

After you analyze the massive amounts of data stored in OSS or Table Store, you can write the result back to the MongoDB database in DLA for front-end service use.

Take the dla_person table as an example. Connect to DLA by using the MySQL CLI tool. Run the following SQL statements to convert the first 10 customer records in the oss_db table and then insert them into the mongo_test.dla_person table:

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