创建分区表:
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.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';