注：本文仅是记录操作过程 参考官方文档：https://doris.apache.org/master/zh-CN/getting-started/basic-usage.html
1、修改密码
代码语言：javascript复制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、创建数据库
代码语言：javascript复制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、创建新用户
代码语言：javascript复制MySQL [(none)]> CREATE USER 'test' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.01 sec)
MySQL [(none)]> 4、授权
代码语言：javascript复制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。
建表语句：
代码语言：javascript复制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");建表操作如下：
代码语言：javascript复制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)
建表语句：
代码语言：javascript复制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");操作如下：
代码语言：javascript复制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、查看表信息
代码语言：javascript复制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 表。
代码语言：javascript复制[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 表。
代码语言：javascript复制[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、关联查询
代码语言：javascript复制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]> 
