Doris--基础--3.8--SQL--表结构变更_doris 修改表结构

Doris–基础–3.8–SQL–表结构变更

1、表结构变更

1. 增加列
2. 删除列
3. 修改列类型
4. 改变列顺序

2、测试数据

CREATE TABLE IF NOT EXISTS test_db.table11
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `date` DATE NOT NULL COMMENT "数据灌入日期时间",
    `city` VARCHAR(20) COMMENT "用户所在城市",
    `age` SMALLINT COMMENT "用户年龄",
    `sex` TINYINT COMMENT "用户性别",
    `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
    `cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
    `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
    `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"
)
AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
PROPERTIES (
    "replication_allocation" = "tag.location.default: 1"
);
 

insert into test_db.table11 values(10000,'2017-10-01','北京',20,0,'2017-10-01 06:00:00',20,10,10);
insert into test_db.table11 values(10001,'2017-10-01','北京',30,1,'2017-10-01 17:05:45',2,22,22); 
insert into test_db.table11 values(10002,'2017-10-02','上海',20,1,'2017-10-02 12:59:12',200,5,5); 
insert into test_db.table11 values(10003,'2017-10-02','广州',32,0,'2017-10-02 11:20:00',30,11,11); 
insert into test_db.table11 values(10004,'2017-10-01','深圳',35,0,'2017-10-01 10:00:15',100,3,3); 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

3、案例

3.1、增加列

对上面的 table11 添加一列

3.1.1、命令

# 添加一列
MySQL [test_db]> ALTER TABLE table11 ADD COLUMN uv BIGINT SUM DEFAULT '0' after min_dwell_time;
Query OK, 0 rows affected (0.02 sec)

# 查看
MySQL [test_db]> desc table11;
+-----------------+-------------+------+-------+---------------------+---------+
| Field           | Type        | Null | Key   | Default             | Extra   |
+-----------------+-------------+------+-------+---------------------+---------+
| user_id         | LARGEINT    | No   | true  | NULL                |         |
| date            | DATE        | No   | true  | NULL                |         |
| city            | VARCHAR(20) | Yes  | true  | NULL                |         |
| age             | SMALLINT    | Yes  | true  | NULL                |         |
| sex             | TINYINT     | Yes  | true  | NULL                |         |
| last_visit_date | DATETIME    | Yes  | false | 1970-01-01 00:00:00 | REPLACE |
| cost            | BIGINT      | Yes  | false | 0                   | SUM     |
| max_dwell_time  | INT         | Yes  | false | 0                   | MAX     |
| min_dwell_time  | INT         | Yes  | false | 99999               | MIN     |
| uv              | BIGINT      | Yes  | false | 0                   | SUM     |
+-----------------+-------------+------+-------+---------------------+---------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

3.1.2、查看执行进度

# 查看执行进度
MySQL [test_db]> show alter table column \G;
*************************** 1. row ***************************
        JobId: 13025
    TableName: table11
   CreateTime: 2023-02-12 22:15:51
   FinishTime: 2023-02-12 22:15:52
    IndexName: table11
      IndexId: 13026
OriginIndexId: 13006
SchemaVersion: 1:435566721
TransactionId: 12
		# FINISHED，表示作业完成
        State: FINISHED
          Msg: 
     Progress: NULL
      Timeout: 86400
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

3.1.3、如果想取消掉正在执行的alter, 则使用

CANCEL ALTER TABLE COLUMN FROM table11;
1