Quickly Creating a Doris Cluster and Performing Report Analysis

Background Information

• Create the example_tbl table.
• Add the visit date, city, gender, residence time, and consumption to the table.
• Query basic user information by user ID.
• Delete the user information table after the service ends.

  Table 1 User information table

  user_id	date	city	age	sex	last_visit_date	cost	max_dwell_time	min_dwell_time
  10000	2017-10-01	A	20	0	2017-10-01 06:00:00	20	10	10
  10000	2017-10-01	A	20	0	2017-10-01 07:00:00	15	2	2
  10001	2017-10-01	A	30	1	2017-10-01 17:05:45	2	22	22
  10002	2017-10-02	B	20	1	2017-10-02 12:59:12	200	5	5
  10003	2017-10-02	C	32	0	2017-10-02 11:20:00	30	11	11
  10004	2017-10-01	D	35	0	2017-10-01 10:00:15	100	3	3
  10004	2017-10-03	D	35	0	2017-10-03 10:20:22	11	6	6

• The following table describes the parameter values listed in the previous table.

  Table 2 Parameter description

  Value	Description
  10000	User ID, which uniquely identifies a user.
  2017-10-01	Time when data is imported to the database. The value is accurate to date.
  A	City where a user is located
  20	Age of a user
  0	Gender male (1 indicates female)
  2017-10-01 06:00:00	Time when a user visits the page. The value is accurate to second.
  20	Consumption generated by the current visit
  10	Time spent on the page during the current visit
  10	Time spent on the page during the current visit (redundancy)

Step 1: Buying a Doris Cluster

1. Log in to the CloudTable console.
2. Select a region in the upper left corner.
3. Click Cluster Management.
4. Click Buy Cluster in the upper right corner of the Cluster Management page and set related parameters. For details about how to configure ports for security group rules, see Doris security group rules.
5. Click Buy Now. On the displayed page, confirm the specifications and click Finish.
6. Return to the cluster list to view the cluster creation progress. If the cluster status is In service, the cluster is created. For details, see Creating a Doris Cluster.

   Table 3 Doris security group rules

   Direction	Action	Port/Range	Type	Destination/Source Address	Usage
   Outbound	Allow	All	IPv4/IPv6	0.0.0.0/0	Permit in the outbound direction
   Inbound	Allow	9030		Security group of the CloudTable Doris cluster	MySQL server port on the FE node
   	Allow	8030			HTTP server port on the FE node
   	Allow	8040			HTTP server port on the BE node
   	Allow	8050			HTTPS server port on the FE node

Step 2: Preparing an ECS

1. Purchase an ECS and log in to the ECS console.
2. Select a region in the upper left corner.
3. In the service list on the left, choose Computing > Elastic Cloud Server. The Elastic Cloud Server page is displayed.
4. Click Buy ECS in the upper right corner. The parameter configuration page is displayed.
5. Configure ECS parameters, including basic settings, instance, OS, storage replica, network, security group, public access, ECS management, advanced settings, and quantity.
6. Check the configurations, select the agreement, and click Submit. After the ECS is created, it will be started by default.

   For details, see User Guide > "Getting Started" > "Creating an ECS".

Step 3: Adding a Security Group

Add the IP address of the local host to the ECS security group.

1. Obtain the IP address of the local host. Press Win+R. The Run dialog box is displayed.
2. Enter cmd in the text box and click OK. The cmd window is displayed.
3. Enter ipconfig in the command window and press Enter to query the IP address of the local host.
4. Log in to the ECS console.
5. On the ECS list page, click the ECS name. On the Basic Information tab page, click the Security Group tab. On the displayed page, click Inbound Rules.
6. Click Add Rule in the upper right corner of the page.
7. Enter the local IP address obtained in 3 as the source IP address. Click OK. The security group is added.

Step 4: Installing the Doris Client

1. Use the SSH login tool (such as PuTTY) to log in to the Linux ECS through the EIP.

   For details about how to log in to the ECS, see "Remotely Logging In to a Linux ECS (Using an SSH Password)" in Logging In to a Linux ECS of the Elastic Cloud Server User Guide.

2. Upload the Doris client to the ECS created in Step 2.
3. Decompress the installation package.

   cd <Path of the client installation package>
   tar xzvf Name of the client package
   
   

   Note

   Replace <Path of the client installation package> mentioned in 3 with the actual path.

4. Go to the bin directory.

   cd mysql-5.7.22-linux-glibc2.12-x86_64/bin/
   
   

5. Connect to the Doris cluster.

   ./mysql -uadmin -pPassword -hInternal IP address of the cluster -P9030
   
   

   Note

   • Internal IP address of the cluster: Enter the cluster access address on the cluster details page. Replace it with the access address of the cluster you purchased. (All access addresses of the FE node can be used to access the cluster.)
   • Password is the password set when you purchase the cluster. If there are special characters, use backslashes (\) to escape them. If the password is enclosed in single quotation marks ('), the special characters do not need to be escaped.
   • Port: MySQL server port 9030 on the FE node

Step 5: Running the MySQL Command to Insert Data

1. Create a database.

   CREATE DATABASE demo;
   
   

2. Create a data table.
   • Use the database.

     USE demo;
     
     

   • Create a table.

     CREATE TABLE IF NOT EXISTS demo.example_tbl
     (
         `user_id` LARGEINT NOT NULL COMMENT "User ID",
         `date` DATE NOT NULL COMMENT "Data import date and time",
         `city` VARCHAR(20) COMMENT "City where the user locates",
         `age` SMALLINT COMMENT "User age",
         `sex` TINYINT COMMENT "User gender",
         `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "Last visit date of the user",
         `cost` BIGINT SUM DEFAULT "0" COMMENT "Total consumption",
         `max_dwell_time` INT MAX DEFAULT "0" COMMENT "Maximum residence time",
         `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "Minimum residence time",
     )
     AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
     DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
     PROPERTIES (
         "replication_allocation" = "tag.location.default: 3"
     );
     
     

3. Insert data.

   INSERT INTO demo.example_tbl (user_id,date,city,age,sex,last_visit_date,cost,max_dwell_time,min_dwell_time) VALUES('10000','2017-10-01','A','20','0','2017-10-01 07:00:00','35','10','2'),('10001','2017-10-01','A','30','1','2017-10-01 17:05:45','2','22','22'),('10002','2017-10-02','B','20','1','2017-10-02 12:59:12','200','5','5'),('10003','2017-10-02','C','32','0','2017-10-02 11:20:12','30','11','11'),('10004','2017-10-01','D','35','0','2017-10-01 10:00:15','100','3','3'),('10004','2017-10-03','D','35','0','2017-10-03 10:20:22','11','6','6');
   
   

4. Query the data.
   • The following is an example of using Doris to perform quick data query and analysis.

     mysql> SELECT * FROM demo.example_tbl;                                                                                                                                                                      +---------+------------+------+------+------+---------------------+------+----------------+----------------+
     | user_id | date       | city | age  | sex  | last_visit_date     | cost | max_dwell_time | min_dwell_time |
     +---------+------------+------+------+------+---------------------+------+----------------+----------------+
     | 10000   | 2017-10-01 | A    |   20 |    0 | 2017-10-01 07:00:00 |   35 |             10 |              2 |
     | 10001   | 2017-10-01 | A    |   30 |    1 | 2017-10-01 17:05:45 |    2 |             22 |             22 |
     | 10002   | 2017-10-02 | B    |   20 |    1 | 2017-10-02 12:59:12 |  200 |              5 |              5 |
     | 10003   | 2017-10-02 | C    |   32 |    0 | 2017-10-02 11:20:12 |   30 |             11 |             11 |
     | 10004   | 2017-10-01 | D    |   35 |    0 | 2017-10-01 10:00:15 |  100 |              3 |              3 |
     | 10004   | 2017-10-03 | D    |   35 |    0 | 2017-10-03 10:20:22 |   11 |              6 |              6 |
     +---------+------------+------+------+------+---------------------+------+----------------+----------------+
     6 rows in set (0.02 sec)
     
     

   • View information about a specified city.

     mysql> SELECT * FROM demo.example_tbl where city='B';
     +---------+------------+------+------+------+---------------------+------+----------------+----------------+
     | user_id | date       | city | age  | sex  | last_visit_date     | cost | max_dwell_time | min_dwell_time |
     +---------+------------+------+------+------+---------------------+------+----------------+----------------+
     | 10002   | 2017-10-02 | B    |   20 |    1 | 2017-10-02 12:59:12 |  200 |              5 |              5 |
     +---------+------------+------+------+------+---------------------+------+----------------+----------------+
     1 row in set (0.10 sec)
     
     

5. Delete data.
   1. Delete a specified row of data.

      mysql> DELETE FROM demo.example_tbl WHERE user_id = 10003;
      Query OK, 0 rows affected (0.04 sec)
      {'label':'delete_77ed273a-a052-4d64-bac0-23916b698003', 'status':'VISIBLE', 'txnId':'39'}
      
      

   2. Delete the table.

      mysql> DROP TABLE demo.example_tbl;
      Query OK, 0 rows affected (0.01 sec)