Using Doris from Scratch

Prerequisite

• A cluster containing the Doris service has been created, and all services in the cluster are running properly.
• The nodes to be connected to the Doris database can communicate with the MRS cluster.
• The MySQL client has been installed. For details, see Installing a MySQL Client.

Procedure

1. Create a user with the Doris management permission.
   • Kerberos authentication is enabled for the cluster (the cluster is in security mode)
     1. Log in to FusionInsight Manager and choose System. In the navigation pane on the left, click Permission > Role, and click Create Role. On the displayed page, enter the role name, for example, dorisrole. In the Configure Resource Permission area, select target cluster > Doris, select Doris Admin Privilege, and click OK.
     2. Choose User > Create, enter a username, for example, dorisuser, set User Type to Human-Machine, retain the default value for Password Policy, enter the user password, confirm the password, associate the user with the dorisrole role, and click OK.
     3. Log in to FusionInsight Manager as the created dorisuser user and change the initial password of the user.
   • Kerberos authentication is disabled for the cluster (the cluster is in normal mode)
     1. Log in to the node where the MySQL client is installed and connect to the Doris service as user admin.

        mysql -uadmin -PConnection port for FE queries -hIP address of the Doris FE instance

        

        • The default password of user admin is empty.
        • To obtain the query connection port of the Doris FE instance, you can log in to FusionInsight Manager, choose Cluster > Services > Doris > Configurations, and query the value of query_port of the Doris service.
        • To obtain the IP address of the Doris FE instance, log in to FusionInsight Manager of the MRS cluster and choose Cluster > Services > Doris > Instances to view the IP address of any FE instance.
        • You can also use the MySQL connection software or Doris web UI to connect to the database.
     2. Run the following command to create a role:

        CREATE ROLE dorisrole;

     3. Grant permissions to the role. For details about the permissions, see User Permissions. For example, to grant the ADMIN_PRIV permission to the role, run the following command:

        GRANT ADMIN_PRIV ON *.*.* TO ROLE 'dorisrole';

     4. Run the following commands to create a user and bind the user to a role:

        CREATE USER 'dorisuser'@'%' IDENTIFIED BY 'password' DEFAULT ROLE 'dorisrole';

        Commands carrying authentication passwords pose security risks. Disable historical command recording before running such commands to prevent information leakage.

2. Log in to the node where MySQL is installed and run the following command to connect to the Doris database:

   If Kerberos authentication is enabled for the cluster (the cluster is in security mode), run the following command to connect to the Doris database:

   export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1

   mysql -uDatabase login username -pDatabase login password -PConnection port for FE queries -hIP address of the Doris FE instance

   

   • To obtain the query connection port of the Doris FE instance, you can log in to FusionInsight Manager, choose Cluster > Services > Doris > Configurations, and query the value of query_port of the Doris service.
   • To obtain the IP address of the Doris FE instance, log in to FusionInsight Manager of the MRS cluster and choose Cluster > Services > Doris > Instances to view the IP address of any FE instance.
   • You can also use the MySQL connection software or Doris web UI to connect to the database.

3. Run the following commands to check the status of the FE and BE instances:

   SHOW FRONTENDS\G;

   SHOW BACKENDS\G;

4. Run the following commands to create a database:

   create database if not exists mrs_demo;

   use mrs_demo;

5. Run the following statement to create a data table.

   CREATE TABLE IF NOT EXISTS mrs_table

   (

   `user_id` LARGEINT NOT NULL COMMENT "User ID",

   `date` DATE NOT NULL COMMENT "Data ingestion date",

   `city` VARCHAR(20) COMMENT "City",

   `age` SMALLINT COMMENT "Age",

   `sex` TINYINT COMMENT "Gender",

   `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "Last access time",

   `cost` BIGINT SUM DEFAULT "0" COMMENT "Total consumption",

   `max_dwell_time` INT MAX DEFAULT "0" COMMENT "Dwell time",

   `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "Minimum dwell time"

   )

   AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)

   DISTRIBUTED BY HASH(`user_id`) BUCKETS 1

   PROPERTIES (

   "replication_allocation" = "tag.location.default: 1"

   );

6. Create the test.csv file in any directory on the current node. The file content is as follows:

   10000,2017-10-01,city1,20,0,2017-10-01 06:00:00,20,10,10
   10000,2017-10-01,city2,20,0,2017-10-01 07:00:00,15,2,2
   10001,2017-10-01,city3,30,1,2017-10-01 17:05:45,2,22,22
   10002,2017-10-02,city4,20,1,2017-10-02 12:59:12,200,5,5
   10003,2017-10-02,city5,32,0,2017-10-02 11:20:00,30,11,11
   10004,2017-10-01,city6,35,0,2017-10-01 10:00:15,100,3,3
   10004,2017-10-03,city7,35,0,2017-10-03 10:20:22,11,6,6

7. Import data to the test.csv file to the table created in 5 using Stream load.

   cd Directory where test.csv is stored

   curl -k --location-trusted -u Doris user name:Password-H "label:table1_20230217" -H "column_separator:," -T test.csv http://IP address of Doris FE instance:HTTP port/api/mrs_demo/mrs_table/_stream_load

   

   • To view the IP address of the active Doris FE instance, log in to FusionInsight Manager and choose Cluster > Services > Doris > Instances.
   • You can log in to FusionInsight Manager, choose Cluster > Services > Doris > Configurations, and search for http_port to view the HTTP port.

8. Query data.

   select * from mrs_table where city='city1';