doris日常维护

 

1.分区相关

创建分区表
CREATE TABLE `app_message_all_new` (
`user_id` bigint(20) NOT NULL,
`message_type` int(11),
`massive_type` int(11),
`status` int(11),
`deleted` int(11),
`create_time` datetime,
`message_id` bigint(20) NOT NULL,
`month` varchar(10) not null,
`sender_seq_no` varchar(45) DEFAULT NULL,
`title` varchar(100) DEFAULT NULL,
`message` varchar(500) DEFAULT NULL,
`extra` varchar(2048) DEFAULT NULL,
`send_date` datetime
)
UNIQUE KEY(user_id, message_type,massive_type,status,deleted,create_time,message_id,month)
PARTITION BY LIST(`month`)
(
PARTITION `p_202301` VALUES IN ("202301"),
PARTITION `p_202302` VALUES IN ("202302"),
PARTITION `p_202303` VALUES IN ("202303")
)
DISTRIBUTED BY HASH(user_id) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);

添加分区
alter table app_message_all_new add partition p_202304 values in ("202304");

 

删除分区
alter table app_message_all_new drop partition p_202304

 

 

2.动态分区

 

CREATE TABLE `app_message_all_new_dy` (
  `user_id` bigint(20) NOT NULL,
  `message_type` int(11),
  `massive_type` int(11),
  `status` int(11),
  `deleted` int(11),
  `create_time` datetime,
  `message_id` bigint(20) NOT NULL,
  `sender_seq_no` varchar(45) DEFAULT NULL,
  `title` varchar(100) DEFAULT NULL,
  `message` varchar(500) DEFAULT NULL,
  `extra` varchar(2048) DEFAULT NULL,
  `send_date` datetime
)
UNIQUE KEY(user_id, message_type,massive_type,status,deleted,create_time,message_id)
PARTITION BY RANGE(create_time)()
DISTRIBUTED BY HASH(user_id) BUCKETS 10
PROPERTIES (
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "MONTH", ##按月分区
    "dynamic_partition.end" = "2",    ##提前创建分区数比如现在是3月份 那么会自动创建4月份和5月份的分区
    "dynamic_partition.prefix" = "p", ##分区前缀
    "dynamic_partition.buckets" = "8",
    "dynamic_partition.start_day_of_month" = "1" ##每月1号为起点创建分区
);

 

mysql> show partitions from app_message_all_new_dy\G;
*************************** 1. row ***************************
             PartitionId: 619528
           PartitionName: p202303
          VisibleVersion: 1
      VisibleVersionTime: 2023-03-30 10:58:33
                   State: NORMAL
            PartitionKey: create_time
                   Range: [types: [DATETIME]; keys: [2023-03-01 00:00:00]; ..types: [DATETIME]; keys: [2023-04-01 00:00:00]; )
         DistributionKey: user_id
                 Buckets: 8
          ReplicationNum: 3
           StorageMedium: HDD
            CooldownTime: 9999-12-31 23:59:59
     RemoteStoragePolicy: 
LastConsistencyCheckTime: NULL
                DataSize: 0.000 
              IsInMemory: false
       ReplicaAllocation: tag.location.default: 3
*************************** 2. row ***************************
             PartitionId: 619561
           PartitionName: p202304
          VisibleVersion: 1
      VisibleVersionTime: 2023-03-30 10:58:33
                   State: NORMAL
            PartitionKey: create_time
                   Range: [types: [DATETIME]; keys: [2023-04-01 00:00:00]; ..types: [DATETIME]; keys: [2023-05-01 00:00:00]; )
         DistributionKey: user_id
                 Buckets: 8
          ReplicationNum: 3
           StorageMedium: HDD
            CooldownTime: 9999-12-31 23:59:59
     RemoteStoragePolicy: 
LastConsistencyCheckTime: NULL
                DataSize: 0.000 
              IsInMemory: false
       ReplicaAllocation: tag.location.default: 3
*************************** 3. row ***************************
             PartitionId: 619594
           PartitionName: p202305
          VisibleVersion: 1
      VisibleVersionTime: 2023-03-30 10:58:33
                   State: NORMAL
            PartitionKey: create_time
                   Range: [types: [DATETIME]; keys: [2023-05-01 00:00:00]; ..types: [DATETIME]; keys: [2023-06-01 00:00:00]; )
         DistributionKey: user_id
                 Buckets: 8
          ReplicationNum: 3
           StorageMedium: HDD
            CooldownTime: 9999-12-31 23:59:59
     RemoteStoragePolicy: 
LastConsistencyCheckTime: NULL
                DataSize: 0.000 
              IsInMemory: false
       ReplicaAllocation: tag.location.default: 3
3 rows in set (0.00 sec)

 

alter table app_message_all_new_dy SET ("dynamic_partition.enable" = "false");
alter table app_message_all_new_dy drop partition p202303;
alter table app_message_all_new_dy SET ("dynamic_partition.enable" = "true"); ##开启后系统会自动创建p202303分区

 

 

3.添加字段:
mysql> alter table app_message_all_01 add column msg_template_id int(11) null default null comment '消息模板id' after user_id;
ERROR 1105 (HY000): errCode = 2, detailMessage = Invalid column order. value should be after key. index[app_message_all_01]

需要加在表定义的UNIQUE KEY后面,message_id之后
UNIQUE KEY(`user_id`, `message_type`, `massive_type`, `status`, `deleted`, `create_time`, `message_id`)

alter table app_message_all_01 add column msg_template_id int(11) null default null comment '消息模板id' after message_id;

 

4.删除字段
alter table app_message_all_01 drop column msg_template_id;