文档

表结构变更

更新时间:
一键部署

当您需要对云数据库 SelectDB 版数据库表结构进行调整以适应新的业务需求时,本文档提供了详细的变更表结构操作指南以及注意事项,以帮助您完成表结构变更。

概述

通过结构(Schema)变更操作来修改已存在表的结构(Schema),支持以下几种修改方式。

  • 增加、删除列。

  • 修改列类型。

  • 调整列顺序。

  • 增加、修改BloomFilter Index。

  • 增加、删除Bitmap Index。

名词解释

  • BASE Table:基表。每一个表被创建时,都对应一个基表。

  • ROLLUP:上卷表。基于基表或者其他ROLLUP创建出来的上卷表。

  • Index:物化索引。ROLLUP或BASE Table都被称为物化索引。

  • Transaction:事务。每一个导入任务都是一个事务,每个事务有一个唯一递增的Transaction ID。

基本原理

执行Schema变更的基本过程,是通过原Index的数据,生成一份新Schema的Index数据。主要进行两部分数据转换,一是已存在的历史数据的转换,二是在Schema变更执行过程中,新导入数据的转换。

+----------+
| Load Job |
+----+-----+
     |
     | Load job generates both origin and new index data
     |
     |      +------------------+ +---------------+
     |      | Origin Index     | | Origin Index  |
     +------> New Incoming Data| | History Data  |
     |      +------------------+ +------+--------+
     |                                  |
     |                                  | Convert history data
     |                                  |
     |      +------------------+ +------v--------+
     |      | New Index        | | New Index     |
     +------> New Incoming Data| | History Data  |
            +------------------+ +---------------+

在开始转换历史数据前,SelectDB会获取一个最新的Transaction ID。并等待这个Transaction ID之前的所有导入事务完成。这个Transaction ID称为分水岭。SelectDB保证在分水岭之后的所有导入任务,都会同时为原Index和新Index生成数据。因此当历史数据转换完成后,SelectDB可以保证新的Index中的数据是完整的。

创建变更作业

该语句用于对已有Table进行Schema变更操作。Schema变更是异步的,任务提交成功则返回结果,提交任务后可使用SHOW ALTER命令查看执行进度。

语法

ALTER TABLE [database.]table <alter_clause>;
说明
  • 一张表在同一时间只能有一个Schema变更作业在运行。

  • Schema变更操作不阻塞导入和查询操作。

  • 变更Schema时不能修改分区列和分桶列。

  • 如果Schema中有REPLACE方式聚合的Value列,则不允许删除Key列。

    如果删除Key列,SelectDB将无法决定REPLACE列的取值。

    Unique数据模型表的所有非Key列都是REPLACE聚合方式。

  • 新增聚合类型为SUM或者REPLACE的Value列时,该列的默认值对历史数据没有意义。

    因为历史数据已经失去详细信息,所以默认值的取值并不能反映实际聚合后的取值。

  • 当修改列类型时,除Type以外的字段都需要按被修改列上的信息补全。

    如修改列k1 INT SUM NULL DEFAULT "1"类型为BIGINT,则需执行命令如下:

    ALTER TABLE tbl1 MODIFY COLUMN k1 BIGINT SUM NULL DEFAULT "1";

    除新的列类型外,如聚合方式,Nullable属性,以及默认值都要按照原信息补全。

  • 不支持修改列名称、聚合类型、Nullable属性、默认值以及列注释。

Schema变更的<alter_clause>支持如下几种修改方式。

  • 向指定index的指定位置添加一列。

    ADD COLUMN column_name column_type [KEY | agg_type] [DEFAULT "default_Value"]
    [AFTER column_name|FIRST]
    [TO rollup_index_name]
    [PROPERTIES ("key"="Value", ...)]
    说明
    • 聚合模型如果增加Value列,需要指定agg_type

    • 非聚合模型(如DUPLICATE Key)如果增加Key列,需要指定KEY关键字。

    • 不能在ROLLUP index中增加BASE index中已经存在的列(如果需要在ROLLUP index 中增加BASE index,可以重新创建一个ROLLUP index)。

  • 向指定Index添加多列。

    ADD COLUMN (column_name1 column_type [KEY | agg_type] DEFAULT "default_Value", ...)
    [TO rollup_index_name]
    [PROPERTIES ("key"="Value", ...)]
    说明
    • 聚合模型如果增加Value列,需要指定agg_type

    • 聚合模型如果增加key列,需要指定KEY关键字。

    • 不能在ROLLUP index中增加BASE index中已经存在的列(如果需要在ROLLUP index 中增加BASE index,可以重新创建一个ROLLUP index)。

  • 从指定Index中删除一列。

    DROP COLUMN column_name[FROM rollup_index_name]
    说明
    • 不能删除分区列。

    • 如果是从BASE index中删除列,则如果ROLLUP index中包含该列,也会被删除。

  • 修改指定Index的列类型以及列位置。

    MODIFY COLUMN column_name column_type [KEY | agg_type] [NULL | NOT NULL] [DEFAULT "default_Value"]
    [AFTER column_name|FIRST]
    [FROM rollup_index_name]
    [PROPERTIES ("key"="Value", ...)]
    说明
    • 聚合模型如果修改Value列,需要指定agg_type

    • 非聚合类型如果修改Key列,需要指定KEY关键字。

    • 只能修改列的类型,列的其他属性维持原样(其他属性需要在修改语句中和原属性保持一致,详情请参见示例)。

    • 分区列和分桶列不能做任何修改。

    • 目前支持以下类型的转换(需自行确保精度不会丧失):

      • TINYINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT和DOUBLE类型向范围更大的数字类型转换。

      • TINTINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT、DOUBLE和DECIMAL转换成VARCHAR类型。

      • VARCHAR类型支持修改最大长度。

      • VARCHAR类型或CHAR类型转换成TINTINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT和DOUBLE类型。

      • VARCHAR类型和CHAR类型转换成DATE类型(目前支持"%Y-%m-%d","%y-%m-%d","%Y%m%d","%y%m%d","%Y/%m/%d,"%y/%m/%d"六种格式化格式)。

      • DATETIME类型转换成DATE类型(仅保留年-月-日信息,例如:2019-12-09 21:47:05<-->2019-12-09)。

      • DATE类型转换成DATETIME类型(时分秒自动补零,例如:2019-12-09<-->2019-12-09 00:00:00)。

      • FLOAT类型转换成DOUBLE类型。

      • INT类型转换成DATE类型(如果INT类型数据不合法则转换失败,原始数据不变)。

      • 除DATE类型与DATETIME类型以外都可以转换成STRING类型,但是STRING类型不能转换任何其他类型。

  • 对指定Index的列进行重新排序。

    ORDER BY (column_name1, column_name2, ...)
    [FROM rollup_index_name]
    [PROPERTIES ("key"="Value", ...)]
    说明
    • Index中的所有列都要指明。

    • Value列在Key列之后。

示例

  • 修改示例表example_db.my_table(非聚合模型)的Schema,example_rollup_indexcol1后添加一个Key列new_col

    ALTER TABLE example_db.my_table
    ADD COLUMN new_col INT KEY DEFAULT "0" AFTER col1
    TO example_rollup_index;
  • 修改示例表example_db.my_table(非聚合模型)的Schema,example_rollup_indexcol1后添加一个Value列new_col

    ALTER TABLE example_db.my_table   
    ADD COLUMN new_col INT DEFAULT "0" AFTER col1    
    TO example_rollup_index;
  • 修改示例表example_db.my_table(聚合模型)的Schema,向example_rollup_indexcol1后添加一个Key列new_col

    ALTER TABLE example_db.my_table   
    ADD COLUMN new_col INT DEFAULT "0" AFTER col1    
    TO example_rollup_index;
  • 修改示例表example_db.my_table(聚合模型)的Schema,向example_rollup_indexcol1后添加一个Value列new_col且聚合类型为 SUM。

    ALTER TABLE example_db.my_table   
    ADD COLUMN new_col INT SUM DEFAULT "0" AFTER col1    
    TO example_rollup_index;
  • 修改示例表example_db.my_table(聚合模型)的Schema,example_rollup_index添加两列。一个是Key列col1,另一个是Value列col2且聚合类型为 SUM

    ALTER TABLE example_db.my_table
    ADD COLUMN (col1 INT DEFAULT "1", col2 FLOAT SUM DEFAULT "2.3")
    TO example_rollup_index;
  • 修改示例表example_db.my_table的Schema,example_rollup_index删除一列。

    ALTER TABLE example_db.my_table
    DROP COLUMN col2
    FROM example_rollup_index;
  • 修改示例表example_db.my_table的Schema,修改BASE index的Key列col1的类型为BIGINT,并移动到col2列后面。

    ALTER TABLE example_db.my_table 
    MODIFY COLUMN col1 BIGINT KEY DEFAULT "1" AFTER col2;
    说明

    无论是修改Key列还是Value列都需要声明完整的COLUMN信息。

  • 修改示例表example_db.my_table的Schema,修改BASE index的val1列最大长度,从VARCHAR(32)修改为VARCHAR(64)

    ALTER TABLE example_db.my_table 
    MODIFY COLUMN val1 VARCHAR(64) REPLACE DEFAULT "abc";
  • 修改示例表example_db.my_table的Schema,重新排序example_rollup_index中的列(设原列顺序为:k1,k2,k3,v1,v2)。

    ALTER TABLE example_db.my_table
    ORDER BY (k3,k1,k2,v2,v1)
    FROM example_rollup_index;
  • 修改Duplicate Key表Key列的某个字段的长度

    原Schema如下。

    +-----------+-------+-------------+------+------+---------+-------+
    | IndexName | Field | Type        | Null | Key  | Default | Extra |
    +-----------+-------+-------------+------+------+---------+-------+
    | tbl1      | k1    | INT         | No   | true | N/A     |       |
    |           | k2    | INT         | No   | true | N/A     |       |
    |           | k3    | varchar(20) | No   | true | N/A     |       |
    |           | k4    | INT         | No   | false| N/A     |       |
    +-----------+-------+-------------+------+------+---------+-------+

    k3列的长度改成50,修改语句如下。

    ALTER TABLE example_tbl MODIFY COLUMN k3 varchar(50) key null COMMENT 'to 50'

    修改完成后,Schema如下。

    +-----------+-------+-------------+------+------+---------+-------+
    | IndexName | Field | Type        | Null | Key  | Default | Extra |
    +-----------+-------+-------------+------+------+---------+-------+
    | tbl1      | k1    | INT         | No   | true | N/A     |       |
    |           | k2    | INT         | No   | true | N/A     |       |
    |           | k3    | varchar(50) | No   | true | N/A     |       |
    |           | k4    | INT         | No   | false| N/A     |       |
    +-----------+-------+-------------+------+------+---------+-------+

    因为Schema变更作业是异步操作,同一个表同时只能进行一个Schema变更作业。

  • 在一个作业中,对多个ROLLUP进行不同的修改。

    原Schema如下。

    +-----------+-------+------+------+------+---------+-------+
    | IndexName | Field | Type | Null | Key  | Default | Extra |
    +-----------+-------+------+------+------+---------+-------+
    | tbl1      | k1    | INT  | No   | true | N/A     |       |
    |           | k2    | INT  | No   | true | N/A     |       |
    |           | k3    | INT  | No   | true | N/A     |       |
    |           |       |      |      |      |         |       |
    | rollup2   | k2    | INT  | No   | true | N/A     |       |
    |           |       |      |      |      |         |       |
    | rollup1   | k1    | INT  | No   | true | N/A     |       |
    |           | k2    | INT  | No   | true | N/A     |       |
    +-----------+-------+------+------+------+---------+-------+

    通过以下命令给rollup1rollup2都加入一列k4,并且再给rollup2加入一列k5

    ALTER TABLE tbl1
    ADD COLUMN k4 INT default "1" to rollup1,
    ADD COLUMN k4 INT default "1" to rollup2,
    ADD COLUMN k5 INT default "1" to rollup2;

    修改完成后,Schema如下。

    +-----------+-------+------+------+------+---------+-------+
    | IndexName | Field | Type | Null | Key  | Default | Extra |
    +-----------+-------+------+------+------+---------+-------+
    | tbl1      | k1    | INT  | No   | true | N/A     |       |
    |           | k2    | INT  | No   | true | N/A     |       |
    |           | k3    | INT  | No   | true | N/A     |       |
    |           | k4    | INT  | No   | true | 1       |       |
    |           | k5    | INT  | No   | true | 1       |       |
    |           |       |      |      |      |         |       |
    | rollup2   | k2    | INT  | No   | true | N/A     |       |
    |           | k4    | INT  | No   | true | 1       |       |
    |           | k5    | INT  | No   | true | 1       |       |
    |           |       |      |      |      |         |       |
    | rollup1   | k1    | INT  | No   | true | N/A     |       |
    |           | k2    | INT  | No   | true | N/A     |       |
    |           | k4    | INT  | No   | true | 1       |       |
    +-----------+-------+------+------+------+---------+-------+

    BASE表tbl1也自动加入了k4,k5列。即给任意ROLLUP增加的列,都会自动加入到BASE表中。

    同时,不允许向ROLLUP中加入BASE表已经存在的列。如果需要在ROLLUP index 中增加BASE index,可以重新创建一个ROLLUP index,之后再删除原ROLLUP。

查看变更作业

查看变更作业,查看正在执行或已经完成的Schema变更作业。当一次Schema变更作业涉及到多个Index 时,该命令会显示多行,每行对应一个Index。

语法

SHOW ALTER TABLE COLUMN;

示例

SHOW ALTER TABLE COLUMN\G;
*************************** 1. row ***************************
        JobId: 20021
    TableName: tbl1
   CreateTime: 2019-08-05 23:03:13
   FinishTime: 2019-08-05 23:03:42
    IndexName: tbl1
      IndexId: 20022
OriginIndexId: 20017
SchemaVersion: 2:792557838
TransactionId: 10023
        State: FINISHED
          Msg: 
     Progress: NULL
      Timeout: 86400
1 row in set (0.00 sec)

各个列代表的含义如下

参数名称

描述

JobId

每个Schema变更作业的唯一ID。

TableName

Schema变更对应的BASE表的表名。

CreateTime

作业创建时间。

FinishedTime

作业结束时间。如未结束,则显示“N/A”。

IndexName

本次修改所涉及的某一个Index的名称。

IndexId

新的Index的唯一ID。

OriginIndexId

旧的Index的唯一ID。

SchemaVersion

以M:N的格式展示。其中M表示本次Schema变更的版本,N表示对应的哈希值。每次Schema变更,版本都会递增。

State

作业所在阶段。

  • PENDING:作业在队列中等待被调度。

  • WAITING_TXN:等待分水岭Transaction ID之前的导入任务完成。

  • RUNNING:历史数据转换中。

  • FINISHED:作业成功。

  • CANCELLED:作业失败。

Msg

如果作业失败,这里会显示失败信息。

Progress

作业进度。只有在RUNNING状态才会显示进度。进度是以M/N的形式显示。其中N为Schema变更涉及的总副本数。M为已完成历史数据转换的副本数。

Timeout

作业超时时间。单位秒。

取消变更作业

取消变更作业,在作业状态不为FINISHED或CANCELLED的情况下,可以通过以下命令取消Schema变更作业。

语法

CANCEL ALTER TABLE COLUMN FROM <tbl_name>;

参数说明

参数名称

参数说明

tbl_name

表名称。

示例

撤销针对示例表example_db.my_table的ALTER COLUMN操作。

CANCEL ALTER TABLE COLUMNFROM example_db.my_table;

相关配置​

FE配置​​

  • alter_table_timeout_second:作业默认超时时间,默认为86400秒。

BE配置​

  • alter_tablet_worker_count:在BE端用于执行历史数据转换的线程数,默认为3。如果希望加快Schema变更作业的速度,可以适当调大这个参数后重启BE。但过多的转换线程可能会导致IO压力增加,影响其他操作。该线程和ROLLUP作业共用。

  • alter_index_worker_count:在BE端用于执行历史数据构建索引的线程数。默认为3。如果希望加快Index变更作业的速度,可以适当调大这个参数后重启BE。但过多的线程可能会导致IO压力增加,影响其他操作。

    说明

    该参数只支持倒排索引。

常见问题​

  • Q:Schema变更的执行速度。

    A:目前Schema变更执行速度按照最差效率估计约为10MB/s。可以根据这个速率来设置作业的超时时间。

  • Q:提交作业报错Table xxx is not stable. ...

    A:Schema变更只有在表数据完整且未进行均衡操作的状态下才可以开始。如果表的某些数据分片副本不完整,或者某些副本正在进行均衡操作,则提交会被拒绝。

    数据分片副本是否完整,可以通过以下命令查看:

    ADMIN SHOW REPLICA STATUS FROM tbl WHERE STATUS != "OK";

    如果有返回结果,则说明有副本有问题。通常系统会自动修复这些问题,也可以通过以下命令优先修复这个表。

    ADMIN REPAIR TABLE tbl1;

    或者可以通过以下命令查看是否有正在运行的均衡任务:

    SHOW PROC "/cluster_balance/pending_tablets";

    可以等待均衡任务完成,或者通过以下命令临时禁止均衡操作:

    ADMIN SET FRONTEND CONFIG ("disable_balance" = "true");

  • 本页导读 (1)