All Products
Search
Document Center

Automatically create tables based on MongoDB collections

Last Updated: Jul 25, 2019

Prerequisites

To facilitate the demonstration, complete the following steps to prepare the test data in MongoDB.

  1. Create a MongoDB instance.

    DLA and the MongoDB instance must be in the same region. Otherwise, the operations in this topic are not allowed.

    DLA is connected to a MongoDB database over the VPC of the MongoDB instance. Therefore, we recommend that you select VPC as the network type when creating the MongoDB instance. In addition, you can change the network type from classic network to VPC by following the instructions provided in Switch to VPC.

  2. Set a whitelist.

  3. Connect to the instance.

  4. Insert test data.

    The example in this topic describes how to create a database named mongo_test in your MongoDB instance, create a collection named person in the mongo_test database, and then insert the following test data into the person collection:

    1. db.person.insert({id:1, name: "james", age: 10, create_time: new Date()});
    2. db.person.insert({id:2, name: "bond", age: 20, create_time: new Date()});
    3. db.person.insert({id:3, name: "lily", age: 30, create_time: new Date()});
    4. db.person.insert({id:4, name: "lucy", age: 20, create_time: new Date()});
  5. Create a MongoDB database.

    All collections and tables in the example use the following MongoDB database:

    1. CREATE DATABASE mongo_test WITH DBPROPERTIES (
    2. catalog = 'mongodb',
    3. location = 'mongodb://<your-user-name>:<your-password>@dds-bp1694axxxxxxxx.mongodb.rds.aliyuncs.com:3717,dds-bp1694ayyyyyyyy.mongodb.rds.aliyuncs.com:3717/admin? replicaSet=zzzzz',
    4. database = 'mongo_test',
    5. instance_id = 'mongodb_instance_id',
    6. vpc_id = 'vpc-*******'
    7. );

    Parameters

    • location: indicates the ConnectionStringURI of your MongoDB instance.

      1

    • database: indicates the name of the MongoDB database.

    • instance_id: indicates the ID of the MongoDB instance.

      2

    • vpc_id: indicates the ID of the VPC where the MongoDB instance is located.

Considerations

Before creating a MongoDB database in DLA, you must add the CIDR block 100.104.0.0/16 to the MongoDB whitelist.

Your MongoDB instance is in a VPC, and by default DLA cannot access resources in the VPC. To enable DLA to access your MongoDB instance, you need to use the reverse VPC technology, that is, add the CIDR block 100.104.0.0/16 to the whitelist of your MongoDB instance.

Permission statement: When you add the CIDR block 100.104.0.0/16 to the whitelist of your MongoDB instance, you agree that we can use the reverse VPC technology to read and write data from and to your MongoDB database.

Example 1: Create a table by using CREATE TABLE LIKE MAPPING

Create a table:

  1. create external table person like mapping('person');
  2. +-------------+-----------+-----------------+
  3. desc person;
  4. +-------------+-----------+-----------------+
  5. | Field | Type | Collation |
  6. +-------------+-----------+-----------------+
  7. | age | double | utf8_general_ci |
  8. | create_time | timestamp | utf8_general_ci |
  9. | id | double | utf8_general_ci |
  10. | name | varchar | utf8_general_ci |

Query table data:

  1. select * from person limit 4;
  2. +------+-------------------+---+------+
  3. | age | create_time | id| name |
  4. +------+-----------------------+------+
  5. | 10 | 2019-04-19 18:03:03 | 1 | james|
  6. | 20 | 2019-04-19 18:03:04 | 2 | bond |
  7. | 30 | 2019-04-19 18:03:04 | 3 | lily |
  8. | 20 | 2019-04-19 18:03:05 | 4 | lucy |

Example 2: Copy a table by using CREATE TABLE LIKE

Traditional databases allow you to create new tables or collections by copying existing ones. DLA provides CREATE TABLE LIKE, which can achieve the same purpose.

Syntax:

  1. create external table_name2 like table_name_1;

Examples:

In the preceding example, you can run CREATE TABLE LIKE to copy the person collection as a new collection named person_2, which has the same structure and data as the person collection.

  1. create external table person_2 like person;
  2. +------+-------------------+---+------+
  3. select * from person_2 limit 4;
  4. +------+-------------------+---+------+
  5. | age | create_time | id| name |
  6. +------+-----------------------+------+
  7. | 10 | 2019-04-19 18:03:03 | 1 | james|
  8. | 20 | 2019-04-19 18:03:04 | 2 | bond |
  9. | 30 | 2019-04-19 18:03:04 | 3 | lily |
  10. | 20 | 2019-04-19 18:03:05 | 4 | lucy |