Doris多列分区操作

1. 创建基于Range多列分区的表

CREATE TABLE tbl1
(
    day DATE,
    days SMALLINT,
    user_type SMALLINT,
    product VARCHAR(20),
    message String
)
PARTITION BY RANGE(day,days,user_type) (
    PARTITION `p201701_1000` VALUES LESS THAN ("2017-02-01", "2","2"),
    PARTITION `p201702_2000` VALUES LESS THAN ("2017-03-01", "3","3"),
    PARTITION `p201703_all`  VALUES LESS THAN ("2017-04-01", "4","4")
    )
DISTRIBUTED BY HASH(product)
PROPERTIES (
    "replication_allocation" = "tag.location.default: 1"
);

2.写入数据

INSERT INTO tbl1 VALUES ('2017-02-02',1,1,'a','msg');

3. 查看分区

show PARTITIONS from  tbl1;

4. 查看数据

SELECT * FROM tbl1 where day='2017-02-02' and days =1 and user_type =1;
SELECT * FROM tbl1 PARTITION(p201702_2000);

5. 删除分区

ALTER TABLE tbl1 DROP PARTITION p201702_2000;

6. 添加分区

ALTER TABLE tbl1 ADD PARTITION  `p201702_2000` VALUES LESS THAN ("2017-03-01", "3","3");