Apace Doris基本操作测试

最新推荐文章于 2024-05-16 15:36:57 发布

程裕强

最新推荐文章于 2024-05-16 15:36:57 发布

阅读量977

点赞数

分类专栏： Doris

本文链接：https://blog.csdn.net/chengyuqiang/article/details/119457089

版权

Doris 专栏收录该内容

21 篇文章 7 订阅

订阅专栏

注：本文仅是记录操作过程
参考官方文档：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]>

程裕强

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Apace Doris基本操作测试

注：本文仅是记录操作过程参考官方文档：https://doris.apache.org/master/zh-CN/getting-started/basic-usage.html1、修改密码MySQL [(none)]> SET PASSWORD FOR 'root' = PASSWORD('123456');Query OK, 0 rows affected (0.01 sec)MySQL [(none)]> MySQL [(none)]> exitBye[root@
复制链接

扫一扫

专栏目录