Data Lake Analytics - Deprecated:Create tables based on ApsaraDB for MongoDB collections

Prerequisites

The test data in ApsaraDB for MongoDB is prepared. To prepare the test data, perform the following steps:

1. Create an ApsaraDB for MongoDB cluster.

   Note

   DLA and the ApsaraDB for MongoDB cluster must be deployed in the same region. Otherwise, the operations described in this topic cannot be performed. DLA is connected to an ApsaraDB for MongoDB database over a virtual private cloud (VPC). We recommend that you select VPC for the network type when you create an ApsaraDB for MongoDB cluster. You can also switch the network type of the ApsaraDB for MongoDB cluster from classic network to VPC.

2. Add IP addresses to a whitelist of the ApsaraDB for MongoDB cluster.

   Note

   Before you create an ApsaraDB for MongoDB schema in DLA, add the classless inter-domain routing (CIDR) block 100.104.0.0/16 to a whitelist of the ApsaraDB for MongoDB cluster.

3. Connect DLA to the ApsaraDB for MongoDB cluster.

4. Write the test data to the ApsaraDB for MongoDB database.

   This example demonstrates how to create the person collection in the mongo_test database of the ApsaraDB for MongoDB cluster. The person collection contains the following test data:

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

5. Create an ApsaraDB for MongoDB schema.

   All data tables in this example use the ApsaraDB for MongoDB schema that is specified in the following statement.

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

   The following table describes the parameters.

   Parameter	Description
   location	The connection string uniform resource identifier (URI) of the ApsaraDB for MongoDB cluster.
   database	The name of the ApsaraDB for MongoDB database.
   instance_id	The ID of the ApsaraDB for MongoDB cluster.
   vpc_id	The ID of the VPC to which the ApsaraDB for MongoDB cluster belongs.

Usage notes

If your ApsaraDB for MongoDB cluster resides in a VPC, DLA cannot access resources in the VPC. To enable DLA to access your ApsaraDB for MongoDB cluster, you must use a reverse proxy to access resources in the VPC. To achieve this purpose, you can add the CIDR block 100.104.0.0/16 to a whitelist of the ApsaraDB for MongoDB cluster.

Procedure

To enable DLA to create a table whose data is automatically mapped to the data of a table in the ApsaraDB for MongoDB database, you can execute one of the following statements:

• CREATE TABLE LIKE MAPPING

  1. Create an external table in DLA.

     create external table person like mapping('person');
     +-------------+-----------+-----------------+
     desc person;
     +-------------+-----------+-----------------+
     | Field       | Type      | Collation       |
     +-------------+-----------+-----------------+
     | age         | double    | utf8_general_ci |
     | create_time | timestamp | utf8_general_ci |
     | id          | double    | utf8_general_ci |
     | name        | varchar   | utf8_general_ci |

  2. Query table data by using DLA.

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

• CREATE TABLE LIKE

  Conventional databases allow you to copy an existing table to create a table. You can also execute the CREATE TABLE LIKE statement in DLA to achieve this purpose.

  1. Copy an existing table in the ApsaraDB for MongoDB database to create a table in DLA.

     In this example, the CREATE TABLE LIKE statement is used to create the person_2 table that has the same schema and data as the person table in the ApsaraDB for MongoDB database.

     create external table person_2 like person;

  2. Query table data by using DLA.

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