Name

ALTER TABLE COLUMN

Description

This statement is used to perform a schema change operation on an existing table. The schema change is asynchronous, and the task is returned when the task is submitted successfully. After that, you can use the SHOW ALTER TABLE COLUMN command to view the progress.

Doris has the concept of materialized index after table construction. After successful table construction, it is the base table and the materialized index is the base index. rollup index can be created based on the base table. Both base index and rollup index are materialized indexes. If rollup_index_name is not specified during the schema change operation, the operation is based on the base table by default.

ALTER-TABLE-COLUMN - 图1tip

Doris 1.2.0 supports light schema change for light scale structure changes, and addition and subtraction operations for value columns can be completed more quickly and synchronously. You can manually specify “light_schema_change” = ‘true’ when creating a table. This parameter is enabled by default for versions 2.0.0 and later.

Grammar:

  1. ALTER TABLE [database.]table alter_clause;

The alter_clause of schema change supports the following modification methods:

1. Add a column to the specified position at the specified index

Grammar

  1. ALTER TABLE [database.]table table_name ADD COLUMN column_name column_type [KEY | agg_type] [DEFAULT "default_value"]
  2. [AFTER column_name|FIRST]
  3. [TO rollup_index_name]
  4. [PROPERTIES ("key"="value", ...)]

Example

  1. Add a key column new_col to example_db.my_table after key_1 (non-aggregated model)

    1. ALTER TABLE example_db.my_table
    2. ADD COLUMN new_col INT KEY DEFAULT "0" AFTER key_1;

  2. Add a value column new_col to example_db.my_table after value_1 (non-aggregate model)

    1. ALTER TABLE example_db.my_table
    2. ADD COLUMN new_col INT DEFAULT "0" AFTER value_1;

  3. Add a key column new_col (aggregate model) to example_db.my_table after key_1

    1. ALTER TABLE example_db.my_table
    2. ADD COLUMN new_col INT KEY DEFAULT "0" AFTER key_1;

  4. Add a value column to example_db.my_table after value_1 new_col SUM Aggregation type (aggregation model)

    1. ALTER TABLE example_db.my_table   
    2. ADD COLUMN new_col INT SUM DEFAULT "0" AFTER value_1;

  5. Add new_col to the first column position of the example_db.my_table table (non-aggregated model)

    1. ALTER TABLE example_db.my_table
    2. ADD COLUMN new_col INT KEY DEFAULT "0" FIRST;

ALTER-TABLE-COLUMN - 图2tip

  • If you add a value column to the aggregation model, you need to specify agg_type
  • For non-aggregated models (such as DUPLICATE KEY), if you add a key column, you need to specify the KEY keyword
  • You cannot add columns that already exist in the base index to the rollup index (you can recreate a rollup index if necessary)

2. Add multiple columns to the specified index

Grammar

  1. ALTER TABLE [database.]table table_name ADD COLUMN (column_name1 column_type [KEY | agg_type] DEFAULT "default_value", ...)
  2. [TO rollup_index_name]
  3. [PROPERTIES ("key"="value", ...)]

Example

  1. Add multiple columns to example_db.my_table, where new_col and new_col2 are SUM aggregate types (aggregate model)

    1. ALTER TABLE example_db.my_table
    2. ADD COLUMN (new_col1 INT SUM DEFAULT "0" ,new_col2 INT SUM DEFAULT "0");

  2. Add multiple columns to example_db.my_table (non-aggregated model), where new_col1 is the KEY column and new_col2 is the value column

    1. ALTER TABLE example_db.my_table
    2. ADD COLUMN (new_col1 INT key DEFAULT "0" , new_col2 INT DEFAULT "0");

ALTER-TABLE-COLUMN - 图3tip

  • If you add a value column to the aggregation model, you need to specify agg_type
  • If you add a key column to the aggregation model, you need to specify the KEY keyword
  • You cannot add columns that already exist in the base index to the rollup index (you can recreate a rollup index if necessary)

3. Delete a column from the specified index

Grammar*

  1. ALTER TABLE [database.]table table_name DROP COLUMN column_name
  2. [FROM rollup_index_name]

Example

  1. Delete column col1 from example_db.my_table

    1. ALTER TABLE example_db.my_table DROP COLUMN col1;

ALTER-TABLE-COLUMN - 图4tip

  • Cannot drop partition column
  • The aggregate model cannot delete KEY columns
  • If the column is removed from the base index, it will also be removed if it is included in the rollup index

4. Modify the column type and column position of the specified index

Grammar

  1. ALTER TABLE [database.]table table_name MODIFY COLUMN column_name column_type [KEY | agg_type] [NULL | NOT NULL] [DEFAULT "default_value"]
  2. [AFTER column_name|FIRST]
  3. [FROM rollup_index_name]
  4. [PROPERTIES ("key"="value", ...)]

Example

  1. Modify the type of the key column col1 of the base index to BIGINT and move it to the back of the col2 column

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

    ALTER-TABLE-COLUMN - 图5tip

    Whether you modify the key column or the value column, you need to declare complete column information

  2. Modify the maximum length of the val1 column of base index. The original val1 is (val1 VARCHAR(32) REPLACE DEFAULT “abc”)

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

    ALTER-TABLE-COLUMN - 图6tip

    You can only modify the column’s data type; other attributes of the column must remain unchanged.

  3. Modify the length of a field in the Key column of the Duplicate key table

    1. ALTER TABLE example_db.my_table 
    2. MODIFY COLUMN k3 VARCHAR(50) KEY NULL COMMENT 'to 50';

ALTER-TABLE-COLUMN - 图7tip

  • If you modify the value column in the aggregation model, you need to specify agg_type
  • If you modify the key column for non-aggregate types, you need to specify the KEY keyword
  • Only the type of the column can be modified, and other attributes of the column remain as they are (that is, other attributes need to be explicitly written in the statement according to the original attributes, see example 8)
  • Partitioning and bucketing columns cannot be modified in any way
  • The following types of conversions are currently supported (loss of precision is guaranteed by the user)
    • Conversion of TINYINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE types to larger numeric types
    • Convert TINTINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE/DECIMAL to VARCHAR
    • VARCHAR supports modifying the maximum length
    • VARCHAR/CHAR converted to TINTINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE
    • Convert VARCHAR/CHAR to DATE (currently supports “%Y-%m-%d”, “%y-%m-%d”, “%Y%m%d”, “%y%m%d”, “%Y/%m/%d, “%y/%m/%d” six formats)
    • Convert DATETIME to DATE (only keep year-month-day information, for example: 2019-12-09 21:47:05 <—> 2019-12-09)
    • DATE is converted to DATETIME (hours, minutes and seconds are automatically filled with zeros, for example: 2019-12-09 <—> 2019-12-09 00:00:00)
    • Convert FLOAT to DOUBLE
    • INT is converted to DATE (if the INT type data is illegal, the conversion fails, and the original data remains unchanged)
    • All can be converted to STRING except DATE and DATETIME, but STRING cannot be converted to any other type

5. Reorder the column at the specified index

Grammar

  1. ALTER TABLE [database.]table table_name ORDER BY (column_name1, column_name2, ...)
  2. [FROM rollup_index_name]
  3. [PROPERTIES ("key"="value", ...)]

Example

  1. Adjust the order of the key and value columns of example_db.my_table (non-aggregate model)

    1. CREATE TABLE `my_table`(
    2. `k_1` INT NULL,
    3. `k_2` INT NULL,
    4. `v_1` INT NULL,
    5. `v_2` varchar NULL,
    6. `v_3` varchar NULL
    7. ) ENGINE=OLAP
    8. DUPLICATE KEY(`k_1`, `k_2`)
    9. COMMENT 'OLAP'
    10. DISTRIBUTED BY HASH(`k_1`) BUCKETS 5
    11. PROPERTIES (
    12. "replication_allocation" = "tag.location.default: 1"
    13. );
    15. ALTER TABLE example_db.my_table ORDER BY (k_2,k_1,v_3,v_2,v_1);
    17. mysql> desc my_table;
    18. +-------+------------+------+-------+---------+-------+
    19. | Field | Type       | Null | Key   | Default | Extra |
    20. +-------+------------+------+-------+---------+-------+
    21. | k_2   | INT        | Yes  | true  | NULL    |       |
    22. | k_1   | INT        | Yes  | true  | NULL    |       |
    23. | v_3   | VARCHAR(*) | Yes  | false | NULL    | NONE  |
    24. | v_2   | VARCHAR(*) | Yes  | false | NULL    | NONE  |
    25. | v_1   | INT        | Yes  | false | NULL    | NONE  |
    26. +-------+------------+------+-------+---------+-------+

  2. Do two actions simultaneously

    1. CREATE TABLE `my_table` (
    2. `k_1` INT NULL,
    3. `k_2` INT NULL,
    4. `v_1` INT NULL,
    5. `v_2` varchar NULL,
    6. `v_3` varchar NULL
    7. ) ENGINE=OLAP
    8. DUPLICATE KEY(`k_1`, `k_2`)
    9. COMMENT 'OLAP'
    10. DISTRIBUTED BY HASH(`k_1`) BUCKETS 5
    11. PROPERTIES (
    12. "replication_allocation" = "tag.location.default: 1"
    13. );
    15. ALTER TABLE example_db.my_table
    16. ADD COLUMN col INT DEFAULT "0" AFTER v_1,
    17. ORDER BY (k_2,k_1,v_3,v_2,v_1,col);
    19. mysql> desc my_table;
    20. +-------+------------+------+-------+---------+-------+
    21. | Field | Type       | Null | Key   | Default | Extra |
    22. +-------+------------+------+-------+---------+-------+
    23. | k_2   | INT        | Yes  | true  | NULL    |       |
    24. | k_1   | INT        | Yes  | true  | NULL    |       |
    25. | v_3   | VARCHAR(*) | Yes  | false | NULL    | NONE  |
    26. | v_2   | VARCHAR(*) | Yes  | false | NULL    | NONE  |
    27. | v_1   | INT        | Yes  | false | NULL    | NONE  |
    28. | col   | INT        | Yes  | false | 0       | NONE  |
    29. +-------+------------+------+-------+---------+-------+

ALTER-TABLE-COLUMN - 图8tip

  • All columns in index are written out
  • the value column comes after the key column
  • You can adjust the key column only within the range of the key column. The same applies to the value column

Keywords

  1. ALTER, TABLE, COLUMN, ALTER TABLE

Best Practice