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. Download the MySQL client and select a version as required. Take the MySQL 5.x client and Linux - Generic operating system as an example.

   

2. Prepare a Linux ECS. For details, see Preparing an ECS.
3. 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. Go to the root directory of the SSH tool.

      cd /

   3. Create a folder in the root directory and upload the installation package downloaded in 1 to the folder.

      mkdir Folder name

   4. Run the commands below to decompress the installation packages. Replace <Path of the client installation package> with the actual client storage path.
      Command for decompressing the .gz package:

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

      Command for decompressing the .xz package:

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

   5. Go to the bin directory.
      Replace mysql-5.7.22-linux-glibc2.12-x86_64 with the actual extracted folder.

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

   6. Connect to the Doris cluster.

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

      Table 1 Parameter description

      Parameter	Description
      ./mysql	This indicates the MySQL client tool. Doris is compatible with the MySQL protocol. Therefore, you can use the MySQL client to connect to a Doris cluster.
      -uadmin	-u is short for user and specifies the username for connection. admin indicates the username, which is the default administrator account of Doris. You can also use a new username.
      -ppassword	-p is short for password and specifies the password for connection. Password indicates the user password. 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. Note: There is no space between -p and the password.
      -hInternal IP address of the cluster	-h is short for host and specifies the address for connecting to a Doris cluster. Replace the internal IP address of the cluster with the internal IP address or domain name of the Doris FE node.
      -P9030	-P is short for port and specifies the connection port. 9030 is the MySQL protocol port of Doris FE and is used to receive client connections.

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.
   • Use Doris to perform quick data query and analysis.

     SELECT * FROM demo.example_tbl;

     The following information is displayed:

     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.

     SELECT * FROM demo.example_tbl where city='B';

     The following information is displayed:

     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.

      DELETE FROM demo.example_tbl WHERE user_id = 10003;

      The following information is displayed:

      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.

      DROP TABLE demo.example_tbl;

      The following information is displayed:

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