doris 注意事项

CREATE TABLE IF NOT EXISTS example_db.expamle_tbl2
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `username` VARCHAR(50) REPLACE COMMENT "用户昵称",
    `city` VARCHAR(20) REPLACE COMMENT "用户所在城市",
    `age` SMALLINT REPLACE COMMENT "用户年龄",
    `sex` TINYINT REPLACE COMMENT "用户性别",
    `phone` LARGEINT REPLACE COMMENT "用户电话",
    `address` VARCHAR(500) REPLACE COMMENT "用户地址",
    `register_time` DATETIME REPLACE COMMENT "用户注册时间"
)
AGGREGATE KEY(`user_id`)
DISTRIBUTED BY HASH(user_id) BUCKETS 10
PROPERTIES("replication_num" = "1");

#增加表字段
ALTER TABLE table1 ADD COLUMN uv BIGINT SUM DEFAULT '0' after pv;

select * from expamle_tbl2
select * from expamle_tbl

desc expamle_tbl
desc expamle_tbl2
#查看表rollup
DESC expamle_tbl ALL



#insert into expamle_tbl2(user_id,username,city,age,sex,phone,address,register_time)values(1000,'小明','山东省',10,1,13220193504,'山东临沂','2021-11-01')
#insert into expamle_tbl(user_id,date,city,age,sex,last_visit_date,cost,max_dwell_time,min_dwell_time)values(10008,'2021-08-01','甘肃',41,0,'2020-11-01 12:12:12',44,22,67)
#增加rollup
ALTER TABLE expamle_tbl ADD ROLLUP rollup_cost(user_id,cost);
ALTER TABLE expamle_tbl ADD ROLLUP rollup_age(age,user_id);
ALTER TABLE expamle_tbl ADD ROLLUP rollup_age(user_id,age);
#删除rollup
ALTER TABLE expamle_tbl drop ROLLUP rollup_age;
#删除加分区
delete  from  comments_word PARTITION p202201 where awemeid=7058885941972290831 and dt='2022-01-01' and authorid=98883011580

DESC expamle_tbl ALL

show alter table rollup;
#查看是否走rollup
EXPLAIN select age,user_id from expamle_tbl where age=10 and user_id=10000  group by user_id,age


select user_id,age from expamle_tbl where user_id=10000


CREATE TABLE table1
(
    id INT DEFAULT '10',
    citycode INT,
    username VARCHAR(32) DEFAULT '',
    pv BIGINT SUM DEFAULT '0'
)
AGGREGATE KEY(id, citycode, username)
DISTRIBUTED BY HASH(id) BUCKETS 1
PROPERTIES("replication_num" = "1");

select * from table1

#创建分区表
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 p202110 VALUES LESS THAN ('2021-10-01'),
    PARTITION p202111 VALUES LESS THAN ('2021-11-01'),
    PARTITION p202112 VALUES LESS THAN ('2021-12-01')
)
DISTRIBUTED BY HASH(siteid) BUCKETS 2
PROPERTIES("replication_num" = "1");
#删除表
drop table table2
#增加分区--左闭右开
alter table table2 ADD  PARTITION p202201 VALUES LESS THAN ('2022-01-01')
#删除分区
alter table table2 DROP  PARTITION p202201
#展示分区信息
show partitions from example_db.table2
#修改分区名称
alter table table2  rename  PARTITION p202109 p202110
#清空表中某个分区
TRUNCATE TABLE aweme_pro_incr PARTITION(p202201);
#查看表结构
desc table2
#修改副本数
ALTER TABLE tbl MODIFY PARTITION p1 SET("replication_num" = "2");

SHOW PROC '/statistic';

ADMIN SHOW REPLICA DISTRIBUTION FROM expamle_tbl;
#查看分区信息
SHOW PARTITIONS FROM comment_aweme

#展示指定db下指定表的指定分区的信息

SHOW PARTITIONS FROM comment_aweme WHERE PartitionName = "p202201";

# 命令可以查看整个集群的副本状态
SHOW PROC '/statistic';

ADMIN SHOW REPLICA DISTRIBUTION FROM comment_aweme;