Doris创建动态分区表及常用操作

创建分区表

CREATE TABLE `test_partitioned` (
`gmt_create` datetime NULL COMMENT "创建时间",
`column1` varchar(64) NULL COMMENT "column1",
`column2` int(11) NULL COMMENT "column2",
`column3` varchar(64) NULL COMMENT "column3",
`column4` int(11) NULL COMMENT "column4",
`id` varchar(255) NULL COMMENT "uuid",
) ENGINE=OLAP
UNIQUE KEY(`gmt_create`,`column1`,`column2`,`column3`,`column4`,`id`)
COMMENT "测试表"
PARTITION BY RANGE(`gmt_create`)()
DISTRIBUTED BY HASH(`id`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 3", // 副本数量
"in_memory" = "false",
"storage_format" = "V2",
"dynamic_partition.enable" = "true", // 是否开启动态分区
"dynamic_partition.time_unit" = "DAY", // 时间区间分区单位
"dynamic_partition.end" = "1", // 提前创建分区范围
"dynamic_partition.start" = "-5", // 动态分区其实偏移量，分区范围在此之前的都会被删除，不指定或者设置负数则保存所有历史分区

"dynamic_partition.history_partition_num" = "-1",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "10"
);

　　

查看数据量

SHOW DATA FROM test;

　　

查看分区

SHOW PARTITIONS FROM test;

　　

手动添加分区

ALTER TABLE test SET ("dynamic_partition.enable" = "false");
ALTER TABLE test 
ADD PARTITION p20221101 VALUES LESS THAN ("2022-11-02 00:00:00")
("replication_num"="3");

　　

 

删除分区

ALTER TABLE test
DROP PARTITION p20230101;

　　

导入数据

insert into test
SELECT
`gmt_create` ,
`column1` ,
`column2`,
`column3` ,
`column4` ,
`id` ,
from test2 where gmt_create> '2023-01-01 00:00:00';

修改列类型

ALTER TABLE test MODIFY COLUMN gmt_create datetime NULL COMMENT "创建时间" ;

　　

修改最大连接数

查看当前最大连接数设置

show property for 'username' like 'max_user_connections';

　　

修改最大连接数

set property for 'username' 'max_user_connections'='1000';

　　

添加rollup

添加

alter table tablename add rollup(column1, column2);

查看

show alter table rollup from dbname;　

 

删除

alter table tablename drop rollup rollupName;

 

查看表信息

// 查看表信息
desc tablename all;
// 查看异常状态副本
admin show replica status from tablename where status != "OK";
// 查看pending状态分区
show proc "/cluster_balance/pending_tablets"
// 查看所有分区
show tablets from tablename;  
// 查看指定tablet
SHOW PROC '/dbs/10171/85829/partitions/85831/85830/85846'

 

 

查看集群信息

-- 查看所有be
show backends;