DorisDB | 使用

阅读目录(Content)

1. 建表

字段类型
分区
数据模型

① AGGREGATE KEY 聚合模型
② UNIQUE KEY 更新模型
③ DUPLICATE KEY 明细模型

Rollup物化索引结构

数据导入

Broker Load
Stream Load
Routine Load

动态分区

建表的参数
使用
批量创建和修改分区

数据导出
SQL函数

Colocation Join

回到顶部(go to top)

1. 建表

字段类型

tinyint	1字节	范围：-2^7 + 1 ~ 2^7 - 1
smallint	2字节	范围：-2^15 + 1 ~ 2^15 - 1
bigint	8字节	范围：-2^63 + 1 ~ 2^63 - 1
largeint	16字节	范围：-2^127 + 1 ~ 2^127 - 1
float	4字节	支持科学计数法
double	12字节	支持科学计数法
decamal[(precision, scale)]	16字节	保证精度的小数类型。默认是 DECIMAL(10, 0) precision: 1 ~ 27 scale: 0 ~ 9 其中整数部分为 1 ~ 18 不支持科学计数法
date	3字节	范围：0000-01-01 ~ 9999-12-31
datetime	8字节	范围：0000-01-01 00:00:00 ~ 9999-12-31 23:59:59
char[(length)]		定长字符串。长度范围：1 ~ 255。默认为1
varchar[(length)]		变长字符串。长度范围：1 ~ 65533
hll	1~16385个字节	hll列类型，不需要指定长度和默认值、长度根据数据的聚合程度系统内控制，并且HLL列只能通过配套的hll_union_agg、Hll_cardinality、hll_hash进行查询或使用
bitmap		bitmap列类型，不需要指定长度和默认值。表示整型的集合，元素最大支持到2^64 - 1
agg_type	聚合类型，如果不指定，则该列为 key 列。否则，该列为 value 列	SUM、MAX、MIN、REPLACE

分区

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

在复合分区中：

第一级称为Partition, 即分区。用户指定某一维度列做为分区列（当前只支持整型和时间类型的列），并指定每个分区的取值范围。

第二级称为Distribution, 即分桶。用户可以指定一个或多个维度列以及桶数进行HASH分布。

以下场景推荐使用复合分区

1. 有时间维或类似带有有序值的维度，可以以这类维度列作为分区列。分区粒度可以根据导入频次、分区数据量等进行评估。

2. 历史数据删除需求：如有删除历史数据的需求（比如仅保留最近N天的数据）。使用复合分区，可以通过删除历史分区来达到目的。也可以通过在指定分区内发送DELETE语句进行删除。

3. 解决数据倾斜的问题：每个分区可以单独指定分桶数量。如按天分区，当每天的数据量差异很大的时，可以通过指定分区的分桶数，合理规划不同分区的数据，分桶列建议选择区分度大的列。

也可以不是用复合分区，仅使用单分区。则数据只做HASH分布。

========================创建单分区表
CREATE TABLE student(
id INT,
name VARCHAR(50) DEFAULT '',
age INT,
count  BIGINT SUM DEFAULT '0'
)
AGGREGATE KEY (id,name,age)
DISTRIBUTED BY HASH(id) buckets 10
PROPERTIES("replication_num" = "1");

建立一张student表。分桶列为id,桶数为10，副本数为1。


======================创建复合分区表
CREATE TABLE student2(
dt DATE,
id INT,
name VARCHAR(50) DEFAULT '',
age INT,
count  BIGINT SUM DEFAULT '0'
)
AGGREGATE KEY (dt,id,name,age)
PARTITION BY RANGE(dt) (
  PARTITION p202007 VALUES LESS THAN ('2020-08-01'),
  PARTITION p202008 VALUES LESS THAN ('2020-09-01'),
  PARTITION p202009 VALUES LESS THAN ('2020-10-01')
)
DISTRIBUTED BY HASH(id) buckets 10
PROPERTIES("replication_num" = "1");

创建student2表，使用dt字段作为分区列，并且创建3个分区，分别是:
　　P202007 范围值是[最小值, 2020-08-01) 
　　P202008 范围值是[2020-08-01, 2020-09-01)
　　P202009 范围值是[2020-09-01, 2020-10-01)

数据模型

Doris数据模型上目前分为三种

　　AGGREGATE KEY, UNIQUE KEY, DUPLICATE KEY。

三种模型都是按KEY进行排序

① AGGREGATE KEY 聚合模型

适用场景

在数据分析领域，有很多需要对数据进行统计和汇总操作的场景。比如:

分析网站或APP访问流量，统计用户的访问总时长、访问总次数;
广告厂商为广告主提供的广告点击总量、展示总量、消费统计等;
分析电商的全年的交易数据, 获得某指定季度或者月份的, 各人口分类(geographic)的爆款商品.

适合采用聚合模型来分析的场景具有如下特点：

业务方进行的查询为汇总类查询，比如sum、count、 max等类型的查询；
不需要召回原始的明细数据；
老数据不会被频繁更新，只会追加新数据。

模型原理

DorisDB会将指标列按照相同维度列进行聚合。当多条数据具有相同的维度时，DorisDB会把指标进行聚合。从而能够减少查询时所需要的处理的数据量，进而提升查询的效率。

AGGREGATE KEY相同时，新旧记录将会进行聚合操作，目前支持SUM,MIN,MAX,REPLACE。
AGGREGATE KEY模型可以提前聚合数据，适合报表和多维度业务。

注意点：

聚合表中数据会分批次多次导入, 每次导入会形成一个版本. 相同排序键的数据行聚合有三种触发方式: ①. 数据导入时, 数据落盘前的聚合; ②. 数据落盘后, 后台的多版本异步聚合; ③. 数据查询时, 多版本多路归并聚合.
数据查询时, 指标列采用先聚合后过滤的方式, 把没必有做指标的列存储为维度列.

（1）建表
CREATE TABLE site_visit(
siteid INT,
city SMALLINT,
username VARCHAR(32),
pv BIGINT SUM DEFAULT '0'
)
AGGREGATE KEY(siteid, city, username)
DISTRIBUTED BY HASH(siteid) BUCKETS 10;
PROPERTIES("replication_num" = "1");

排序列为siteid、city、username
指标列为pv，使用聚合函数SUM 

（2）插入2条数据
mysql> insert into site_visit values(1,1,'name1',10);
mysql> insert into site_visit values(1,1,'name1',20);

（3）查看结果
select * from site_visit;

② UNIQUE KEY 更新模型

适用场景

有些分析场景之下，数据会更新, DorisDB采用更新模型来满足这种需求。比如在电商场景中，订单的状态经常会发生变化，每天的订单更新量可突破上亿。在这种量级的更新场景下进行实时数据分析，如果在明

细模型下通过delete+insert的方式，是无法满足频繁更新需求的; 因此, 用户需要使用更新模型来满足数据分析需求。

以下是一些适合更新模型的场景特点：

已经写入的数据有大量的更新需求；
需要进行实时数据分析。

模型原理

更新模型中, 排序键满足唯一性约束, 成为主键.

DorisDB存储内部会给每一个批次导入数据分配一个版本号, 同一主键的数据可能有多个版本, 查询时, 最大(最新)版本的数据胜出.

注意事项

导入数据时需要将所有字段补全才能够完成更新操作，即，下述例子中的orderid、status、username和amount四个字段都需必须存在。
对于更新模型的数据读取，需要在查询时完成多版本合并，当版本过多时会导致查询性能降低。所以在向更新模型导入数据时，应该适当降低导入频率，从而提升查询性能。建议在设计导入频率时以满足业务对实时性的要求为准。如果业务对实时性的要求是分钟级别，那么每分钟导入一次更新数据即可，不需要秒级导入。
在查询时，对于value字段的过滤通常在多版本合并之后。将经常过滤字段且不会被修改的字段放在主键上, 能够在合并之前就将数据过滤掉，从而提升查询性能。
因为合并过程需要将所有主键字段进行比较，所以应该避免放置过多的主键字段，以免降低查询性能。如果某个字段只是偶尔会作为查询中的过滤条件存在，不需要放在主键中。

UNIQUE KEY相同时，新记录覆盖旧记录。目前UNIQUE KEY和AGGREGATE KEY的REPLACE聚合方法一致。适用于有更新需求的业务。

1）建表
CREATE TABLE sales_order(
    orderid     BIGINT,
    status      TINYINT,
    username    VARCHAR(32),
    amount      BIGINT DEFAULT '0'
)
UNIQUE KEY(orderid)
DISTRIBUTED BY HASH(orderid) BUCKETS 10;
PROPERTIES("replication_num" = "1");

UNIQUE(orderid)为主键，orderid为排序列，定义在其他列之前；
其他列为指标列，其聚合类型为REPLACE;


（2）插入2条数据
mysql> insert into sales_order values(1,1,'name1',100);
mysql> insert into sales_order values(1,1,'name1',200);

（3）查询

③ DUPLICATE KEY 明细模型

适用场景：

需要保留原始的数据（例如原始日志，原始操作记录等）来进行分析；
查询方式灵活, 不局限于预先定义的分析方式, 传统的预聚合方式难以命中;
数据更新不频繁。导入数据的来源一般为日志数据或者是时序数据, 以追加写为主要特点, 数据产生后就不会发生太多变化

模型原理：

指定数据表的排序列, 没有明确指定的情况下, 那么DorisDB会为表选择默认的几个列作为排序列。这样，在查询中，有相关排序列的过滤条件时，DorisDB能够快速地过滤数据，降低整个查询的时延。

注意：

在向DorisDB明细模型表中导入完全相同的两行数据时，DorisDB会认为是两行数据。只指定排序列，相同的行并不会合并。

充分利用排序列，在建表时将经常在查询中用于过滤的列放在表的前面，这样能够提升查询速度。

明细模型中, 可以指定部分的维度列为排序键; 而聚合模型和更新模型中, 排序键只能是全体维度列.

（1）建表
CREATE TABLE session_data(
    visitorid   SMALLINT,
    sessionid   BIGINT,
    city        CHAR(20),
    ip          varchar(32)
)
DUPLICATE KEY(visitorid, sessionid)
DISTRIBUTED BY HASH(sessionid, visitorid) BUCKETS 10
PROPERTIES("replication_num" = "1");

以visitorid和 sessionid作为排序列，且排序列在其他列之前；

（2）插入数据
mysql> insert into session_data values(1,1,'beijing','www.111.com');
mysql> insert into session_data values(1,1,'beijing','www.111.com');
mysql> insert into session_data values(3,2,'beijing','www.111.com');
mysql> insert into session_data values(2,2,'beijing','www.111.com');
mysql> insert into session_data values(2,1,'beijing','www.111.com');

（3）查询
mysql> select * from session_data;
+-----------+-----------+---------+-------------+
| visitorid | sessionid | city | ip |
+-----------+-----------+---------+-------------+
| 1 | 1 | beijing | www.111.com |
| 1 | 1 | beijing | www.111.com |
| 2 | 1 | beijing | www.111.com |
| 3 | 2 | beijing | www.111.com |
| 2 | 2 | beijing | www.111.com |
+-----------+-----------+---------+-------------+
5 rows in set (0.02 sec)

Rollup物化索引结构

Rollup可以理解为表的一个物化索引结构。Rollup可以调整列的顺序以增加前缀索引的命中率，也可以减少key列以增加数据的聚合度。

（1）以session_data为例添加Rollup

（2）比如我经常需要看某个城市的ip数，那么可以建立一个只有ip和city的rollup；

mysql> alter table session_data add rollup rollup_city_ip(city, ip);

（3）创建完毕后，再次查看表结构

（4）然后可以通过explain查看执行计划，是否使用到了rollup

mysql> explain select ip from session_data where city='shanghai';

回到顶部(go to top)

数据导入

为适配不同的数据导入需求，Doris系统提供5种不同的导入方式。每种导入方式支持不同的数据源，存在不同的方式（异步、同步）

（1）Broker load

通过Broker进程访问并读取外部数据源（HDFS）导入Doris。用户通过MySql协议提交导入作业后，异步执行。通过show load命令查看导入结果。

（2）Stream load

用户通过HTTP协议提交请求并携带原始数据创建导入。主要用于快速将本地文件或数据流中的数据导入到Doris。导入命令同步返回导入结果。

（3）Insert

类似MySql中的insert语句，Doris提供insert into tbl select ...;的方式从Doris的表中读取数据并导入到另一张表。或者通过insert into tbl values(...);的方式插入单条数据

（4）Multi load

用户可以通过HTTP协议提交多个导入作业。Multi Load可以保证多个导入作业的原子生效

（5）Routine load

用户通过MySql协议提交例行导入作业，生成一个常住线程，不间断的从数据源（如Kafka）中读取数据并导入Doris中。

Broker Load

Broker load是一个导入的异步方式，支持的数据源取决于Broker进程支持的数据源。

适用场景：

① 源数据在Broker可以访问的存储系统中，如HDFS ；

② 数据量在几十到百GB级别。

基本原理：用户在提交导入任务后，FE（Doris系统的元数据和调度节点）会生成相应的PLAN(导入执行计划，BE会执行导入计划将输入导入Doris中)并根据BE（Doris系统的计算和存储节点）的个数和文件的大小，将Plan分给多个BE执行，每个BE导入一部分数

据。BE在执行过程中会从Broker拉取数据，在对数据转换之后导入系统。所有BE均完成导入，由FE最终决定是否导入是否成功。

（1）启动hdfs集群，启动hive的元数据 memstore

　　hive --service metastore &

（2）进入到hive创建student_tmp表,虽然官网提示说支持列式存储，但测试发现并不支持，会提示一下错误

所以在hive表里创建行式存储表

[kris@hadoop102 ~]# hive
create table student_tmp_h(
id int,
name string,
age int,
score decimal(10,4))
partitioned by (
`dt` string)
 row format delimited fields terminated by '\t';

（3）插入数据
hive (default)> set hive.exec.dynamic.partition=true;
hive (default)> set hive.exec.dynamic.partition.mode=nonstrict;
insert into student_tmp_h values(1,'张三',11,99.8,20200908),(2,'李四',12,99.9,20200908),(3,'王五',13,100,20200908),
                               (4,'赵六',14,55.5,20200908),(5,'test1',13,66.5,20200908),(7,'test2',14,80,20200908),(8,'test3',19,75,20200908);

（5）将hadoop集群的配置文件复制到doris集群的broker上

[kris@hadoop102 ~]# cd /opt/module/hadoop-3.1.3/etc/hadoop/
[kris@hadoop102 hadoop]$ scp hdfs-site.xml /opt/module/apache-doris/apache_hdfs_broker/conf 
[kris@hadoop102 conf]$ xsync hdfs-site.xml  #分发到另外两台机器上

（6）使用mysql客户端登录doris创建对应表student_result
[kris@hadoop102 be]$ mysql -hdadoop102 -P 9030 -uroot
mysql> use example_db;
create table student_result
(
id int ,
name varchar(50),
age int ,
score decimal(10,4),
dt varchar(20)
)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 10
PROPERTIES("replication_num" = "1");

（7）编写导入语句,dt是分区列，在数据块读不到所以使用固定值
LOAD LABEL example_db.label1
(
DATA INFILE("hdfs://hadoop102/user/hive/warehouse/student_tmp_h/dt=20200908/*")
INTO TABLE student_result
COLUMNS TERMINATED BY "\t"
(co1,co2,co3,co4)
set(
id=co1,
name=co2,
age=co3,
score=co4,
dt='20200908'
)
)
WITH BROKER 'broker2'
PROPERTIES
(
"timeout" = "3600"
);


mysql> show load \G;
*************************** 15. row ***************************
JobId: 21034
Label: label1
State: FINISHED
Progress: ETL:100%; LOAD:100%
Type: BROKER
EtlInfo: unselected.rows=0; dpp.abnorm.ALL=0; dpp.norm.ALL=7
TaskInfo: cluster:N/A; timeout(s):3600; max_filter_ratio:0.0
ErrorMsg: NULL
CreateTime: 2021-10-24 15:32:37
EtlStartTime: 2021-10-24 15:32:41
EtlFinishTime: 2021-10-24 15:32:41
LoadStartTime: 2021-10-24 15:32:41
LoadFinishTime: 2021-10-24 15:32:43
URL: NULL
JobDetails: {"Unfinished backends":{"98200135-d214-421d-915f-7a2ff9944b84":[]},"ScannedRows":7,"TaskNumber":1,"All backends":{"98200135-d214-421d-915f-7a2ff9944b84":[10003]},"FileNumber":1,"FileSize":138}
15 rows in set (0.00 sec)

Stream Load

适合导入小数据量的文件

Stream Load是一个同步的导入方式，用户通过发送HTTP协议将本地文件或数据流导入到Doris中，Stream load同步执行导入并返回结果。用户可以直接通过返回判断导入是否成功。

具体帮助使用HELP STREAM LOAD 查看

mysql> help stream load

（1）创建文件

（2）将文件上传到集群

（3）通过命令将csv数据导入到doris，-H指定参数，column_separator指定分割符，-T指定数据源文件。

[kris@hadoop102 apache-doris]$ vim 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

[kris@hadoop102 apache-doris]$ curl --location-trusted -u test:123456 -T table2_data -H "label:table2_20170707" -H "column_separator:," http://hadoop102:8030/api/example_db/table2/_stream_load
{
"TxnId": 3015,
"Label": "table2_20170707",
"Status": "Success",
"Message": "OK",
"NumberTotalRows": 4,
"NumberLoadedRows": 4,
"NumberFilteredRows": 0,
"NumberUnselectedRows": 0,
"LoadBytes": 87,
"LoadTimeMs": 117,
"BeginTxnTimeMs": 0,
"StreamLoadPutTimeMs": 2,
"ReadDataTimeMs": 0,
"WriteDataTimeMs": 58,
"CommitAndPublishTimeMs": 52
}

（4）查看对应表,导入成功

Routine Load

例行导入功能为用户提供了一种自动从指定数据源进行数据导入的功能。

当前仅支持Kafka系统进行例行导入。

使用限制：

1. 支持无认证的Kafka访问，以及通过SSL方式认证的Kafka集群

2. 仅支持kafka0.10.0.0 及以上版本

先安装好zookeeper和kafka,创建topic，并往topic里灌一批数据

bin/kafka-topics.sh --zookeeper hadoop102:2181/kafka --create --replication-factor 2 --partitions 3 --topic test

import org.apache.kafka.clients.producer.KafkaProducer;
import org.apache.kafka.clients.producer.ProducerRecord;
import java.util.Properties;
public class product {
        public static void main(String[] args) {
            Properties props = new Properties();
            props.put("bootstrap.servers", "hadoop102:9092,hadoop103:9092,hadoop104:9092");
            props.put("acks", "-1");
            props.put("batch.size", "16384");
            props.put("linger.ms", "10");
            props.put("buffer.memory", "33554432");
            props.put("key.serializer",
                    "org.apache.kafka.common.serialization.StringSerializer");
            props.put("value.serializer",
                    "org.apache.kafka.common.serialization.StringSerializer");
            KafkaProducer<String, String> producer = new KafkaProducer<String, String>(props);
            for (int i = 0; i < 100; i++) {
                producer.send(new ProducerRecord<String,String>("test",i+"\tname"+i+"\t18"));

            }
            producer.flush();
            producer.close();
        }


}

① 在doris中创建对应表
create table student_kafka(
id int,
name varchar(50),
age int
)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 10
PROPERTIES("replication_num" = "1");



② 创建导入作业，desired_concurrent_number指定并行度
CREATE ROUTINE LOAD example_db.test ON student_kafka
PROPERTIES(
　　"desired_concurrent_number"="3",
　　"strict_mode" = "false"
)
FROM KAFKA(
"kafka_broker_list"= "hadoop102:9092,hadoop103:9092,hadoop104:9092",
"kafka_topic" = "test",
"property.group.id"="test_group",
"property.kafka_default_offsets" = "OFFSET_BEGINNING",
"property.enable.auto.commit"="false"
);

③ 创建完作业导入作业后查询doris

回到顶部(go to top)

动态分区

在很多实际应用场景中，数据的时效性很重要，需要为新达到数据创建新分区, 删除过期. DorisDB的动态分区机制可以实现分区rollover: 对分区实现进行生命周期管理(TTL)，自动增删分区。

动态分区是在Doris0.12版本加入的功能。旨在对表级别的分区实现生命周期管理(TTL），减少用户的使用负担。

目前实现了动态添加分区及动态删除分区的功能

原理：

在某些场景下，用户会将表按照天进行分区划分，每天定时执行例行任务，这时需要使用方手动管理分区,否则可能由于使用方没有创建数据导致失败这给使用方带来额外的维护成本。

如果没有动态分区这个功能，就要手动去创建分区，如果没有创建就可能导致数据导入失败。

在实现方式上，FE会启动一个后台子线程，根据fe.conf中dynamic_partition_enable（默认是关闭的）及dynamic_partition_check_interval_seconds参数决定线程是否启动以及该线程的调度频率（过多久创建新的分区）。每次调度时，会在注册表中读取动态分区表的属性。

建表的参数

dynamic_partition.enable	是否开启动态分区特性，可指定true或false,默认为true
dynamic_partition.time_unit	动态分区调度的单位，可指定day,week,month。当指定day时格式为yyyyMMDD。当指定week时格式为yyyy_ww，表示属于这一年的第几周。当指定为month时，格式为yyyyMM
dynamic_partition.start	动态分区的开始时间，以当天为准，超过该时间范围的分区将会被删除，如果不填写默认值为Interger.Min_VALUE 即-2147483648
dynamic_partition.end	动态分区的结束时间，以当天为基准，会提前创建N个单位的分区范围
dynamic_partition.prefix	动态创建的分区名前缀
dynamic_partition.buckets	动态创建的分区所对应分桶数量

使用

（1）开启动态分区功能，可以在fe.conf中设置dynamic_partition_enable=true，也可以使用命令进行修改。使用命令进行修改，并dynamic_partition_check_interval_seconds调度时间设置为5秒，意思就是每过5秒根据配置刷新分区。我这里做测试设置为5秒，真实场景可以设置为12小时。

修改表的分区属性

动态分区的属性可以修改，例如需要起/停动态分区的功能，可以通过ALTER TABLE来完成。

ALTER TABLE site_access SET("dynamic_partition.enable"="false");
ALTER TABLE site_access SET("dynamic_partition.enable"="true");

注意事项

动态分区的方式相当于把建分区的判断逻辑交由DorisDB来完成，在配置的过程中一定要保证分区名称满足规范，否则会创建失败。具体规范可以描述如下：

指定为 DAY 时，分区名后缀需为yyyyMMdd，例如20200325。
指定为 WEEK 时，分区名后缀需为yyyy_ww，例如 2020_13, 代表2020年第13周。
指定为 MONTH 时，动态创建的分区名后缀格式为 yyyyMM，例如 202003。

[kris@hadoop102 apache-doris]$ curl --location-trusted -u root -XGET http://hadoop102:8030/api/_set_config?dynamic_partition_enable=true
Enter host password for user 'root':
{"set":{"dynamic_partition_enable":"true"},"err":{}}


[kris@hadoop102 apache-doris]$ curl --location-trusted -u root -XGET http://hadoop102:8030/api/_set_config?dynamic_partition_check_interval_seconds=5
Enter host password for user 'root':
{"set":{"dynamic_partition_check_interval_seconds":"5"},"err":{}}

mysql> ADMIN SET FRONTEND CONFIG ("dynamic_partition_enable" = "true");

mysql> ADMIN SET FRONTEND CONFIG ("dynamic_partition_check_interval_seconds"="5");

（2）创建一张调度单位为天，不删除历史分区的动态分区表

create table student_dynamic_partition (id int,
time date,
name varchar(50),
age int
)
duplicate key(id,time)
PARTITION BY RANGE(time)()
DISTRIBUTED BY HASH(id) buckets 10
PROPERTIES(
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "10",
 "replication_num" = "1"
 );

（3）查看分区表情况SHOW DYNAMIC PARTITION TABLES，更新最后调度时间

mysql>  SHOW DYNAMIC PARTITION TABLES \G;
*************************** 1. row ***************************
             TableName: student_dynamic_partition
                Enable: true
              TimeUnit: DAY
                 Start: -2147483648
                   End: 3
                Prefix: p
               Buckets: 10
        ReplicationNum: 1
               StartOf: NULL
        LastUpdateTime: 2021-10-23 19:49:08
     LastSchedulerTime: 2021-10-23 19:49:55
                 State: NORMAL
LastCreatePartitionMsg: NULL
  LastDropPartitionMsg: NULL
1 row in set (0.00 sec)

ERROR: 
No query specified

（4）插入测试数据,可以全部成功
mysql> insert into student_dynamic_partition values(1,'2021-10-10 20:00:00','name1',18);
ERROR 1064 (HY000): Insert has filtered data in strict mode, tracking_url=http://192.168.1.102:8040/api/_load_error_log?file=__shard_1/error_log_insert_stmt_3e5b75f4-33f8-11ec-8ee9-525400f13278_3e5b75f433f811ec_8ee9525400f13278

mysql> insert into student_dynamic_partition values(1,'2021-10-23 20:00:00','name1',18);
Query OK, 1 row affected (0.05 sec)
{'label':'insert_d6fc4b8c-33f7-11ec-8ee9-525400f13277', 'status':'VISIBLE', 'txnId':'1031'}

 

mysql> insert into student_dynamic_partition values(1,'2021-10-24 20:00:00','name1',18);
Query OK, 1 row affected (0.06 sec)
{'label':'insert_e5a7f0e4-33f7-11ec-8ee9-525400f13277', 'status':'VISIBLE', 'txnId':'1034'}

 

mysql> insert into student_dynamic_partition values(1,'2021-10-25 20:00:00','name1',18);
Query OK, 1 row affected (0.03 sec)
{'label':'insert_ed14936c-33f7-11ec-8ee9-525400f13277', 'status':'VISIBLE', 'txnId':'1037'}

 

（5）使用命令查看表下的所有分区show partitions from student_dynamic_partition;
mysql> show partitions from student_dynamic_partition \G;
*************************** 1. row ***************************
PartitionId: 19212
PartitionName: p20211023
VisibleVersion: 2
VisibleVersionTime: 2021-10-23 19:53:29
VisibleVersionHash: 1189085190502871807
State: NORMAL
PartitionKey: time
Range: [types: [DATE]; keys: [2021-10-23]; ..types: [DATE]; keys: [2021-10-24]; )
DistributionKey: id
Buckets: 10
ReplicationNum: 1
StorageMedium: HDD
CooldownTime: 9999-12-31 23:59:59
LastConsistencyCheckTime: NULL
DataSize: 710.000 B
IsInMemory: false
*************************** 2. row ***************************
PartitionId: 19233
PartitionName: p20211024
VisibleVersion: 2
VisibleVersionTime: 2021-10-23 19:53:54
VisibleVersionHash: 8153597939592092591
State: NORMAL
PartitionKey: time
Range: [types: [DATE]; keys: [2021-10-24]; ..types: [DATE]; keys: [2021-10-25]; )
DistributionKey: id
Buckets: 10
ReplicationNum: 1
StorageMedium: HDD
CooldownTime: 9999-12-31 23:59:59
LastConsistencyCheckTime: NULL
DataSize: 714.000 B
IsInMemory: false
*************************** 3. row ***************************
PartitionId: 19254
PartitionName: p20211025
VisibleVersion: 2
VisibleVersionTime: 2021-10-23 19:54:06
VisibleVersionHash: 1238099035870710564
State: NORMAL
PartitionKey: time
Range: [types: [DATE]; keys: [2021-10-25]; ..types: [DATE]; keys: [2021-10-26]; )
DistributionKey: id
Buckets: 10
ReplicationNum: 1
StorageMedium: HDD
CooldownTime: 9999-12-31 23:59:59
LastConsistencyCheckTime: NULL
DataSize: 713.000 B
IsInMemory: false
*************************** 4. row ***************************
PartitionId: 19275
PartitionName: p20211026
VisibleVersion: 1
VisibleVersionTime: 2021-10-23 19:49:15
VisibleVersionHash: 0
State: NORMAL
PartitionKey: time
Range: [types: [DATE]; keys: [2021-10-26]; ..types: [DATE]; keys: [2021-10-27]; )
DistributionKey: id
Buckets: 10
ReplicationNum: 1
StorageMedium: HDD
CooldownTime: 9999-12-31 23:59:59
LastConsistencyCheckTime: NULL
DataSize: .000
IsInMemory: false
4 rows in set (0.00 sec)

ERROR:
No query specified

mysql> select * from student_dynamic_partition;
+------+------------+-------+------+
| id | time | name | age |
+------+------------+-------+------+
| 1 | 2021-10-25 | name1 | 18 |
| 1 | 2021-10-23 | name1 | 18 |
| 1 | 2021-10-24 | name1 | 18 |
+------+------------+-------+------+
3 rows in set (0.01 sec)

批量创建和修改分区

该功能在1.16版本中添加

1 建表时批量创建日期分区

用户可以通过给出一个START值、一个END值以及一个定义分区增量值的EVERY子句批量产生分区。

其中START值将被包括在内而END值将排除在外。

例如：
CREATE TABLE site_access (
    datekey DATE,
    site_id INT,
    city_code SMALLINT,
    user_name VARCHAR(32),
    pv BIGINT DEFAULT '0'
)
ENGINE=olap
DUPLICATE KEY(datekey, site_id, city_code, user_name)
PARTITION BY RANGE (datekey) (
    START ("2021-01-01") END ("2021-01-04") EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(site_id) BUCKETS 10
PROPERTIES (
    "replication_num" = "1" 
);

相当于
自动创建如下等价的分区

PARTITION p20210101 VALUES [('2021-01-01'), ('2021-01-02')),
PARTITION p20210102 VALUES [('2021-01-02'), ('2021-01-03')),
PARTITION p20210103 VALUES [('2021-01-03'), ('2021-01-04'))

**2 建表时批量创建数字分区**
当分区键为整数类型时直接使用数字进行分区，注意分区值需要使用引号引用，而EVERY则不用引号，如下：

CREATE TABLE site_access (
    datekey INT,
    site_id INT,
    city_code SMALLINT,
    user_name VARCHAR(32),
    pv BIGINT DEFAULT '0'
)
ENGINE=olap
DUPLICATE KEY(datekey, site_id, city_code, user_name)
PARTITION BY RANGE (datekey) (
    START ("1") END ("5") EVERY (1)
)
DISTRIBUTED BY HASH(site_id) BUCKETS 10
PROPERTIES (
    "replication_num" = "1"
);
上面的语句将产生如下分区：

PARTITION p1 VALUES [("1"), ("2")),
PARTITION p2 VALUES [("2"), ("3")),
PARTITION p3 VALUES [("3"), ("4")),
PARTITION p4 VALUES [("4"), ("5"))
3 建表时批量创建不同类型的日期分区

DorisDB也支持建表时同时定义不同类型的分区，只要求这些分区不相交，例如：

CREATE TABLE site_access (
    datekey DATE,
    site_id INT,
    city_code SMALLINT,
    user_name VARCHAR(32),
    pv BIGINT DEFAULT '0'
)
ENGINE=olap
DUPLICATE KEY(datekey, site_id, city_code, user_name)
PARTITION BY RANGE (datekey) (
    START ("2019-01-01") END ("2021-01-01") EVERY (INTERVAL 1 YEAR),
    START ("2021-01-01") END ("2021-05-01") EVERY (INTERVAL 1 MONTH),
    START ("2021-05-01") END ("2021-05-04") EVERY (INTERVAL 1 DAY)
)
DISTRIBUTED BY HASH(site_id) BUCKETS 10
PROPERTIES (
    "replication_num" = "1"
);

上面的语句将会产生如下分区：

PARTITION p2019 VALUES [('2019-01-01'), ('2020-01-01')),
PARTITION p2020 VALUES [('2020-01-01'), ('2021-01-01')),
PARTITION p202101 VALUES [('2021-01-01'), ('2021-02-01')),
PARTITION p202102 VALUES [('2021-02-01'), ('2021-03-01')),
PARTITION p202103 VALUES [('2021-03-01'), ('2021-04-01')),
PARTITION p202104 VALUES [('2021-04-01'), ('2021-05-01')),
PARTITION p20210501 VALUES [('2021-05-01'), ('2021-05-02')),
PARTITION p20210502 VALUES [('2021-05-02'), ('2021-05-03')),
PARTITION p20210503 VALUES [('2021-05-03'), ('2021-05-04'))

**4 建表后批量创建分区**
与建表时批量创建分区类似，DorisDB也支持通过ALTER语句批量创建分区。通过指定ADD PARITIONS关键字，配合START和END以及EVERY的值来创建分区。

ALTER TABLE site_access ADD
PARTITIONS START ("2014-01-01") END ("2014-01-06") EVERY (interval 1 day);

回到顶部(go to top)

数据导出

数据导出是Doris提供的一种将数据导出的功能。该功能可以将用户指定的表或分区的数据以文本的格式，通过Broker进程导出到远端存储上，如HDFS/BOS等。

（1）启动hadoop集群

（2）执行导出计划

export table student_dynamic_partition
to "hdfs://hadoop102/tmp/hive/"
PROPERTIES
(
    "column_separator"=",",
    "load_mem_limit"="2147483648",
    "timeout" = "3600"
)
WITH BROKER "broker2"
;

（3）导出之后查看hdfs对应路径，会多出许多文件

（4）使用命令查看有数据的文件内容,就是简单的文本内容

[kris@hadoop102 hive]# hadoop dfs -cat /tmp/hive/export-data-69208f5fdf5e4e63-b3f75089d7f7611f-1599717109072

回到顶部(go to top)

SQL函数

（1）查看函数名

mysql> show builtin functions in test_db;

（2）查看函数具体信息，比如查看year函数具体信息

mysql> show full builtin functions in test_db like 'year';

（2）官网地址

http://doris.apache.org/master/zh-CN/sql-reference/sql-functions/date-time-functions/convert_tz.html#syntax

Colocation Join

Colocation Join是在Doris0.9版本引入的功能，旨在为Join查询提供本性优化，来减少数据在节点上的传输耗时，加速查询。与广播join比较像；

原理

Colocation Join功能，是将一组拥有CGS 的表组成一个CG。保证这些表对应的数据分片会落在同一个be节点上，那么使得两表再进行join的时候，可以通过本地数据进行直接join，减少数据在节点之间的网络传输时间。

使用限制：

　　1）建表时两张表的分桶列和数量需要完全一致，并且桶的个数也需要一致。

　　2）副本数，两张表的所有分区的副本数需要一致

（1）使用，建两张表，分桶列都为int类型，且桶的个数都是8个。两张表的副本数都为默认副本数。

CREATE TABLE `tbl1` (
    `k1` date NOT NULL COMMENT "",
    `k2` int(11) NOT NULL COMMENT "",
    `v1` int(11) SUM NOT NULL COMMENT ""
) ENGINE=OLAP
AGGREGATE KEY(`k1`, `k2`)
PARTITION BY RANGE(`k1`) (
    PARTITION p1 VALUES LESS THAN ('2019-05-31'),
    PARTITION p2 VALUES LESS THAN ('2019-06-30')
)
DISTRIBUTED BY HASH(`k2`) BUCKETS 8
PROPERTIES (
    "colocate_with" = "group1"
);

 
CREATE TABLE `tbl2` (
    `k1` datetime NOT NULL COMMENT "",
    `k2` int(11) NOT NULL COMMENT "",
    `v1` double SUM NOT NULL COMMENT ""
) ENGINE=OLAP
AGGREGATE KEY(`k1`, `k2`)
DISTRIBUTED BY HASH(`k2`) BUCKETS 8
PROPERTIES (
    "colocate_with" = "group1"
);

（2）编写查询语句，并查看执行计划，HASH JOIN处colocate 显示为true，代表优化成功。

mysql> explain SELECT * FROM tbl1 INNER JOIN tbl2 ON (tbl1.k2 = tbl2.k2);

posted @ 2021-04-13 23:08 kris12 阅读(4241) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

公告

昵称： kris12
园龄： 7年5个月
粉丝： 331
关注： 57

+加关注

2025年3月

日

一

二

三

四

五

六

kris12

Self-discipline gives me freedom.

DorisDB | 使用

1. 建表

字段类型

分区

数据模型

① AGGREGATE KEY 聚合模型

② UNIQUE KEY 更新模型

③ DUPLICATE KEY 明细模型

Rollup物化索引结构

数据导入

Broker Load

Stream Load

Routine Load

动态分区

建表的参数

使用

批量创建和修改分区

数据导出

SQL函数

Colocation Join

公告

搜索

常用链接

我的标签

随笔分类

随笔档案

阅读排行榜