ApsaraDB for SelectDB:Schema evolution

Add a column

Inserts a single column at a specified position in a specified index.

ADD COLUMN column_name column_type [KEY | agg_type] [DEFAULT "default_value"]
[AFTER column_name | FIRST]
[TO rollup_index_name]
[PROPERTIES ("key"="value", ...)]

For an AGGREGATE KEY model value column, specify agg_type. For a key column in a non-aggregate model (e.g., DUPLICATE KEY), specify the KEY keyword. Columns that already exist in a base index cannot be added to a rollup index — create a new rollup index instead.

Example: Add a key column to a rollup index (DUPLICATE KEY model)

ALTER TABLE example_db.my_table
ADD COLUMN new_col INT KEY DEFAULT "0" AFTER col1
TO example_rollup_index;

Example: Add a value column to a rollup index (DUPLICATE KEY model)

ALTER TABLE example_db.my_table
ADD COLUMN new_col INT DEFAULT "0" AFTER col1
TO example_rollup_index;

Example: Add a value column with SUM aggregation (AGGREGATE KEY model)

ALTER TABLE example_db.my_table
ADD COLUMN new_col INT SUM DEFAULT "0" AFTER col1
TO example_rollup_index;

Add multiple columns

Inserts several columns into a specified index in a single statement.

ADD COLUMN (column_name1 column_type [KEY | agg_type] DEFAULT "default_value", ...)
[TO rollup_index_name]
[PROPERTIES ("key"="value", ...)]

For AGGREGATE KEY model value columns, specify agg_type. For AGGREGATE KEY model key columns, specify the KEY keyword. Columns already in a base index cannot be added to a rollup index.

Example: Add a key column and a value column in one statement

ALTER TABLE example_db.my_table
ADD COLUMN (col1 INT DEFAULT "1", col2 FLOAT SUM DEFAULT "2.3")
TO example_rollup_index;

Example: Add columns to multiple rollup tables

The original schemas:

+-----------+-------+------+------+------+---------+-------+
| 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     |       |
+-----------+-------+------+------+------+---------+-------+

Add k4 to rollup1, and add k4 and k5 to rollup2:

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;

The updated schemas:

+-----------+-------+------+------+------+---------+-------+
| 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       |       |
+-----------+-------+------+------+------+---------+-------+

Columns added to a rollup table are automatically added to the base table (tbl1 in this example).

Drop a column

Removes a column from a specified index.

DROP COLUMN column_name [FROM rollup_index_name]

Partition key columns cannot be dropped. Dropping a column from a base index also drops it from any rollup index that contains that column.

Example: Drop a column from a rollup index

ALTER TABLE example_db.my_table
DROP COLUMN col2
FROM example_rollup_index;

Modify a column type or position

Changes a column's type, position, or both.

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", ...)]

For AGGREGATE KEY model value columns, specify agg_type. For non-aggregate model key columns, specify the KEY keyword. Partition key columns and bucket columns cannot be modified. Always include all original column attributes (aggregation method, nullability, default value) in the statement — only the type changes.

Supported type conversions

STRING columns cannot be converted to any other type.

Example: Change a key column type and move it

Change col1 in the base index from INT to BIGINT and move it after col2:

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

Example: Extend a VARCHAR column's max length

The original 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     |       |
+-----------+-------+-------------+------+-------+---------+-------+

Extend k3 from VARCHAR(20) to VARCHAR(50):

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

The updated 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     |       |
+-----------+-------+-------------+------+-------+---------+-------+

Example: Extend a value column's max length

ALTER TABLE example_db.my_table
MODIFY COLUMN val1 VARCHAR(64) REPLACE DEFAULT "abc";

Reorder columns

Changes the column order in a specified index.

ORDER BY (column_name1, column_name2, ...)
[FROM rollup_index_name]
[PROPERTIES ("key"="value", ...)]

All columns in the index must be listed. Value columns must follow key columns.

Example: Reorder columns in a rollup index

Original order: k1, k2, k3, v1, v2

ALTER TABLE example_db.my_table
ORDER BY (k3, k1, k2, v2, v1)
FROM example_rollup_index;

Frontend

Backend

To apply changes, restart the backend after updating these parameters.