Using the MySQL Client to Connect to a Doris Normal Cluster

Constraints

• The Doris cluster and the ECS must be in the same region, AZ, and VPC.
• The Doris cluster and the ECS must be in the same security group.
• The IP address of the local host has been added to the ECS security group.

Installing the Client

1. Prepare a Linux ECS. For details, see Preparing an ECS.
2. Install the client and connect to the cluster.
   1. Use the SSH login tool to remotely log in to the Linux ECS through the EIP.

      For details, see Logging In to a Linux ECS Using an SSH Password in the Elastic Cloud Server User Guide.

   2. Decompress the installation package.

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

      

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

   3. Go to the bin directory.

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

   4. Connect to the Doris cluster.

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

      

      • 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 on the FE node. For details, see Table 1.

      Table 1 Custom security 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

Getting Started with Doris

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)