Apace Doris基本操作测试

刺骨的言语ヽ痛彻心扉 2022-09-03 09:22 107阅读 0赞

注：本文仅是记录操作过程  
参考官方文档：[https://doris.apache.org/master/zh-CN/getting-started/basic-usage.html][https_doris.apache.org_master_zh-CN_getting-started_basic-usage.html]

### 1、修改密码 ###

MySQL [(none)]> SET PASSWORD FOR 'root' = PASSWORD('123456');
    Query OK, 0 rows affected (0.01 sec)
    
    MySQL [(none)]>

MySQL [(none)]> exit
    Bye
    [root@node3 fe]# mysql -h 10.17.12.160 -P 9030 -uroot -p123456 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 1
    Server version: 5.1.0 Doris version 0.12.0-rc03
    
    Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MySQL [(none)]>

### 2、创建数据库 ###

MySQL [(none)]> CREATE DATABASE tpa;
    Query OK, 0 rows affected (0.00 sec)
    
    MySQL [(none)]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | tpa                |
    +--------------------+
    2 rows in set (0.01 sec)
    
    MySQL [(none)]>

其中，information\_schema是为了兼容MySQL协议而存在

### 3、创建新用户 ###

MySQL [(none)]> CREATE USER 'test' IDENTIFIED BY 'test';
    Query OK, 0 rows affected (0.01 sec)
    
    MySQL [(none)]>

### 4、授权 ###

MySQL [(none)]> GRANT ALL ON tpa TO test;
    Query OK, 0 rows affected (0.01 sec)
    
    MySQL [(none)]>

[root@node3 fe]# mysql -h 10.17.12.160 -P 9030 -utest -ptest
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.1.0 Doris version 0.12.0-rc03
    
    Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MySQL [(none)]> use tpa;
    Database changed
    MySQL [tpa]>

### 5、建表 ###

Doris支持支持单分区和复合分区两种建表方式。

（1）建立一个名字为 table1 的单分区表。分桶列为 siteid，桶数为 10。

建表语句：

CREATE TABLE table1
    (
        siteid INT DEFAULT '10',
        citycode SMALLINT,
        username VARCHAR(32) DEFAULT '',
        pv BIGINT SUM DEFAULT '0'
    )
    AGGREGATE KEY(siteid, citycode, username)
    DISTRIBUTED BY HASH(siteid) BUCKETS 10
    PROPERTIES("replication_num" = "1");

建表操作如下：

MySQL [tpa]> CREATE TABLE table1
        -> (
        ->     siteid INT DEFAULT '10',
        ->     citycode SMALLINT,
        ->     username VARCHAR(32) DEFAULT '',
        ->     pv BIGINT SUM DEFAULT '0'
        -> )
        -> AGGREGATE KEY(siteid, citycode, username)
        -> DISTRIBUTED BY HASH(siteid) BUCKETS 10
        -> PROPERTIES("replication_num" = "1");
    Query OK, 0 rows affected (0.32 sec)
    
    MySQL [tpa]>

（2）建立一个名字为 table2 的复合分区表。  
使用 event\_day 列作为分区列，建立3个分区: p201706, p201707, p201708

*  p201706：范围为 \[最小值, 2017-07-01)
 *  p201707：范围为 \[2017-07-01, 2017-08-01)
 *  p201708：范围为 \[2017-08-01, 2017-09-01)

建表语句：

CREATE TABLE table2
    (
        event_day DATE,
        siteid INT DEFAULT '10',
        citycode SMALLINT,
        username VARCHAR(32) DEFAULT '',
        pv BIGINT SUM DEFAULT '0'
    )
    AGGREGATE KEY(event_day, siteid, citycode, username)
    PARTITION BY RANGE(event_day)
    (
        PARTITION p201706 VALUES LESS THAN ('2017-07-01'),
        PARTITION p201707 VALUES LESS THAN ('2017-08-01'),
        PARTITION p201708 VALUES LESS THAN ('2017-09-01')
    )
    DISTRIBUTED BY HASH(siteid) BUCKETS 10
    PROPERTIES("replication_num" = "1");

操作如下：

MySQL [tpa]> CREATE TABLE table2
        -> (
        ->     event_day DATE,
        ->     siteid INT DEFAULT '10',
        ->     citycode SMALLINT,
        ->     username VARCHAR(32) DEFAULT '',
        ->     pv BIGINT SUM DEFAULT '0'
        -> )
        -> AGGREGATE KEY(event_day, siteid, citycode, username)
        -> PARTITION BY RANGE(event_day)
        -> (
        ->     PARTITION p201706 VALUES LESS THAN ('2017-07-01'),
        ->     PARTITION p201707 VALUES LESS THAN ('2017-08-01'),
        ->     PARTITION p201708 VALUES LESS THAN ('2017-09-01')
        -> )
        -> DISTRIBUTED BY HASH(siteid) BUCKETS 10
        -> PROPERTIES("replication_num" = "1");
    Query OK, 0 rows affected (0.29 sec)
    
    MySQL [tpa]>

### 6、查看表信息 ###

MySQL [tpa]> SHOW TABLES;
    +---------------+
    | Tables_in_tpa |
    +---------------+
    | table1        |
    | table2        |
    +---------------+
    2 rows in set (0.00 sec)
    
    MySQL [tpa]> DESC table1;
    +----------+-------------+------+-------+---------+-------+
    | Field    | Type        | Null | Key   | Default | Extra |
    +----------+-------------+------+-------+---------+-------+
    | siteid   | INT         | Yes  | true  | 10      |       |
    | citycode | SMALLINT    | Yes  | true  | N/A     |       |
    | username | VARCHAR(32) | Yes  | true  |         |       |
    | pv       | BIGINT      | Yes  | false | 0       | SUM   |
    +----------+-------------+------+-------+---------+-------+
    4 rows in set (0.01 sec)
    
    MySQL [tpa]> DESC table2;
    +-----------+-------------+------+-------+---------+-------+
    | Field     | Type        | Null | Key   | Default | Extra |
    +-----------+-------------+------+-------+---------+-------+
    | event_day | DATE        | Yes  | true  | N/A     |       |
    | siteid    | INT         | Yes  | true  | 10      |       |
    | citycode  | SMALLINT    | Yes  | true  | N/A     |       |
    | username  | VARCHAR(32) | Yes  | true  |         |       |
    | pv        | BIGINT      | Yes  | false | 0       | SUM   |
    +-----------+-------------+------+-------+---------+-------+
    5 rows in set (0.00 sec)
    
    MySQL [tpa]> select * from table1;
    Empty set (0.04 sec)
    
    MySQL [tpa]> select * from table1;
    +--------+----------+----------+------+
    | siteid | citycode | username | pv   |
    +--------+----------+----------+------+
    |      5 |        3 | helen    |    3 |
    |      1 |        1 | jim      |    2 |
    |      3 |        2 | tom      |    2 |
    |      4 |        3 | bush     |    3 |
    |      2 |        1 | grace    |    2 |
    +--------+----------+----------+------+
    5 rows in set (0.06 sec)
    
    MySQL [tpa]> select * from table3;
    ERROR 1051 (42S02): errCode = 2, detailMessage = Unknown table 'table3'
    MySQL [tpa]> select * from table2;
    +------------+--------+----------+----------+------+
    | event_day  | siteid | citycode | username | pv   |
    +------------+--------+----------+----------+------+
    | 2017-07-12 |      3 |        2 | tom      |    2 |
    | 2017-07-15 |      4 |        3 | bush     |    3 |
    | 2017-07-05 |      2 |        1 | grace    |    2 |
    | 2017-07-03 |      1 |        1 | jim      |    2 |
    | 2017-07-12 |      5 |        3 | helen    |    3 |
    +------------+--------+----------+----------+------+
    5 rows in set (0.06 sec)
    
    MySQL [tpa]>

### 7、导入数据 ###

（1）示例1，以 “table1\_20170707” 为 Label，使用本地文件 table1\_data 导入 table1 表。

[root@node3 ~]# vi table1_data
    [root@node3 ~]# cat table1_data 
    1,1,jim,2
    2,1,grace,2
    3,2,tom,2
    4,3,bush,3
    5,3,helen,3
    [root@node3 ~]#

[root@node3 ~]# curl --location-trusted -u test:test -H "label:table1_20170707" -H "column_separator:," -T table1_data http://node3:8030/api/tpa/table1/_stream_load
    {
        "TxnId": 2,
        "Label": "table1_20170707",
        "Status": "Success",
        "Message": "OK",
        "NumberTotalRows": 5,
        "NumberLoadedRows": 5,
        "NumberFilteredRows": 0,
        "NumberUnselectedRows": 0,
        "LoadBytes": 55,
        "LoadTimeMs": 463
    }[root@node3 ~]#

MySQL [tpa]> select * from table1;
    +--------+----------+----------+------+
    | siteid | citycode | username | pv   |
    +--------+----------+----------+------+
    |      5 |        3 | helen    |    3 |
    |      1 |        1 | jim      |    2 |
    |      3 |        2 | tom      |    2 |
    |      4 |        3 | bush     |    3 |
    |      2 |        1 | grace    |    2 |
    +--------+----------+----------+------+
    5 rows in set (0.06 sec)
    
    MySQL [tpa]>

（2）示例2: 以 “table2\_20170707” 为 Label，使用本地文件 table2\_data 导入 table2 表。

[root@node3 ~]# vi table2_data
    [root@node3 ~]# cat table2_data
    2017-07-03|1|1|jim|2
    2017-07-05|2|1|grace|2
    2017-07-12|3|2|tom|2
    2017-07-15|4|3|bush|3
    2017-07-12|5|3|helen|3
    [root@node3 ~]#

[root@node3 ~]# curl --location-trusted -u test:test -H "label:table2_20170707" -H "column_separator:|" -T table2_data http://node3:8030/api/tpa/table2/_stream_load
    {
        "TxnId": 3,
        "Label": "table2_20170707",
        "Status": "Success",
        "Message": "OK",
        "NumberTotalRows": 5,
        "NumberLoadedRows": 5,
        "NumberFilteredRows": 0,
        "NumberUnselectedRows": 0,
        "LoadBytes": 110,
        "LoadTimeMs": 41
    }[root@node3 ~]#

MySQL [tpa]> select * from table2;
    +------------+--------+----------+----------+------+
    | event_day  | siteid | citycode | username | pv   |
    +------------+--------+----------+----------+------+
    | 2017-07-12 |      3 |        2 | tom      |    2 |
    | 2017-07-15 |      4 |        3 | bush     |    3 |
    | 2017-07-05 |      2 |        1 | grace    |    2 |
    | 2017-07-03 |      1 |        1 | jim      |    2 |
    | 2017-07-12 |      5 |        3 | helen    |    3 |
    +------------+--------+----------+----------+------+
    5 rows in set (0.06 sec)
    
    MySQL [tpa]>

### 8、关联查询 ###

MySQL [tpa]> SELECT SUM(table1.pv) FROM table1 JOIN table2 WHERE table1.siteid = table2.siteid;
    +--------------------+
    | sum(`table1`.`pv`) |
    +--------------------+
    |                 12 |
    +--------------------+
    1 row in set (0.03 sec)
    
    MySQL [tpa]>

MySQL [tpa]> SELECT SUM(pv) FROM table2 WHERE siteid IN (SELECT siteid FROM table1 WHERE siteid > 2);
    +-----------+
    | sum(`pv`) |
    +-----------+
    |         8 |
    +-----------+
    1 row in set (0.08 sec)
    
    MySQL [tpa]>

[https_doris.apache.org_master_zh-CN_getting-started_basic-usage.html]: https://doris.apache.org/master/zh-CN/getting-started/basic-usage.html