ALTER TABLE

description

  1. This statement is used to modify an existing table. If no rollup index is specified, the base operation is the default.
  2. The statement is divided into three types of operations: schema change, rollup, partition
  3. These three types of operations cannot appear in an ALTER TABLE statement at the same time.
  4. Where schema change and rollup are asynchronous operations and are returned if the task commits successfully. You can then use the SHOW ALTER command to view the progress.
  5. Partition is a synchronous operation, and a command return indicates that execution is complete.
  7. grammar:
  8.     ALTER TABLE [database.]table
  9.     Alter_clause1[, alter_clause2, ...];
  11. The alter_clause is divided into partition, rollup, schema change, rename and bimmap index.
  13. Partition supports the following modifications
  14. Increase the partition
  15.     grammar:
  16.         ADD PARTITION [IF NOT EXISTS] partition_name
  17.         Partition_desc ["key"="value"]
  18.         [DISTRIBUTED BY HASH (k1[,k2 ...]) [BUCKETS num]]
  19.     note:
  20.         1) partition_desc supports two ways of writing:
  21.             * VALUES LESS THAN [MAXVALUE|("value1", ...)]
  22.             * VALUES [("value1", ...), ("value1", ...))
  23.         1) The partition is the left closed right open interval. If the user only specifies the right boundary, the system will automatically determine the left boundary.
  24.         2) If the bucket mode is not specified, the bucket method used by the built-in table is automatically used.
  25.         3) If the bucket mode is specified, only the bucket number can be modified, and the bucket mode or bucket column cannot be modified.
  26.         4) ["key"="value"] section can set some properties of the partition, see CREATE TABLE for details.
  28. 2. Delete the partition
  29.     grammar:
  30.         DROP PARTITION [IF EXISTS] partition_name
  31.     note:
  32.         1) Use a partitioned table to keep at least one partition.
  33.         2) Execute DROP PARTITION For a period of time, the deleted partition can be recovered by the RECOVER statement. See the RECOVER statement for details.
  34.         3) If DROP PARTITION FORCE is executed, the system will not check whether the partition has unfinished transactions, the partition will be deleted directly and cannot be recovered, generally this operation is not recommended
  36. 3. Modify the partition properties
  37.     grammar:
  38.         MODIFY PARTITION p1|(p1[, p2, ...]) SET ("key" = "value", ...)
  39.     Description:
  40.         1) The following attributes of the modified partition are currently supported.
  41.             - storage_medium
  42.             - storage_cooldown_time
  43.             - replication_num 
  44.             — in_memory
  45.         2) For single-partition tables, partition_name is the same as the table name.
  47. Rollup supports the following ways to create:
  48. 1. Create a rollup index
  49.     grammar:
  50.         ADD ROLLUP rollup_name (column_name1, column_name2, ...)
  51.         [FROM from_index_name]
  52.         [PROPERTIES ("key"="value", ...)]
  54.         properties: Support setting timeout time, the default timeout time is 1 day.
  55.     example:
  56.         ADD ROLLUP r1(col1,col2) from r0
  57. 1.2 Batch create rollup index
  58.     grammar:
  59.         ADD ROLLUP [rollup_name (column_name1, column_name2, ...)
  60.                                 [FROM from_index_name]
  61.                                 [PROPERTIES ("key"="value", ...)],...]
  62.     example:
  63.         ADD ROLLUP r1(col1,col2) from r0, r2(col3,col4) from r0
  64. 1.3 note:
  65.         1) If from_index_name is not specified, it is created by default from base index
  66.         2) The columns in the rollup table must be existing columns in from_index
  67.         3) In properties, you can specify the storage format. See CREATE TABLE for details.
  69. 2. Delete the rollup index
  70.     grammar:
  71.         DROP ROLLUP rollup_name
  72.         [PROPERTIES ("key"="value", ...)]
  73.     example:
  74.        DROP ROLLUP r1
  75. 2.1 Batch Delete rollup index
  76.     grammar: DROP ROLLUP [rollup_name [PROPERTIES ("key"="value", ...)],...]
  77.     example: DROP ROLLUP r1,r2
  78. 2.2 note:
  79.         1) Cannot delete base index
  82. Schema change supports the following modifications:
  83. 1. Add a column to the specified location of the specified index
  84.     grammar:
  85.         ADD COLUMN column_name column_type [KEY | agg_type] [DEFAULT "default_value"]
  86.         [AFTER column_name|FIRST]
  87.         [TO rollup_index_name]
  88.         [PROPERTIES ("key"="value", ...)]
  89.     note:
  90.         1) Aggregate model If you add a value column, you need to specify agg_type
  91.         2) Non-aggregate models (such as DUPLICATE KEY) If you add a key column, you need to specify the KEY keyword.
  92.         3) You cannot add a column that already exists in the base index to the rollup index
  93.             Recreate a rollup index if needed
  95. 2. Add multiple columns to the specified index
  96.     grammar:
  97.         ADD COLUMN (column_name1 column_type [KEY | agg_type] DEFAULT "default_value", ...)
  98.         [TO rollup_index_name]
  99.         [PROPERTIES ("key"="value", ...)]
  100.     note:
  101.         1) Aggregate model If you add a value column, you need to specify agg_type
  102.         2) Non-aggregate model If you add a key column, you need to specify the KEY keyword.
  103.         3) You cannot add a column that already exists in the base index to the rollup index
  104.         (You can recreate a rollup index if needed)
  106. 3. Remove a column from the specified index
  107.     grammar:
  108.         DROP COLUMN column_name
  109.         [FROM rollup_index_name]
  110.     note:
  111.         1) Cannot delete partition column
  112.         2) If the column is removed from the base index, it will also be deleted if the column is included in the rollup index
  114. 4. Modify the column type and column position of the specified index
  115.     grammar:
  116.         MODIFY COLUMN column_name column_type [KEY | agg_type] [NULL | NOT NULL] [DEFAULT "default_value"]
  117.         [AFTER column_name|FIRST]
  118.         [FROM rollup_index_name]
  119.         [PROPERTIES ("key"="value", ...)]
  120.     note:
  121.         1) Aggregate model If you modify the value column, you need to specify agg_type
  122.         2) Non-aggregate type If you modify the key column, you need to specify the KEY keyword.
  123.         3) Only the type of the column can be modified. The other attributes of the column remain as they are (ie other attributes need to be explicitly written in the statement according to the original attribute, see example 8)
  124.         4) The partition column cannot be modified
  125.         5) The following types of conversions are currently supported (accuracy loss is guaranteed by the user)
  126.             TINYINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE convert to a wider range of numeric types
  127.             TINTINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE/DECIMAL is converted to VARCHAR
  128.             VARCHAR supports modification of maximum length
  129.             Convert VARCHAR/CHAR to TINYINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE.
  130.             Convert VARCHAR/CHAR to DATE (currently support six formats: "%Y-%m-%d", "%y-%m-%d", "%Y%m%d", "%y%m%d", "%Y/%m/%d, "%y/%m/%d")
  131.             Convert DATETIME to DATE(Only year-month-day information is retained, For example: `2019-12-09 21:47:05` <--> `2019-12-09`)
  132.             Convert DATE to DATETIME(Set hour, minute, second to zero, For example: `2019-12-09` <--> `2019-12-09 00:00:00`)
  133.             Convert FLOAT to DOUBLE
  134.             Convert INT to DATE (If the INT data fails to convert, the original data remains the same)
  135.         6) Does not support changing from NULL to NOT NULL
  137. 5. Reorder the columns of the specified index
  138.     grammar:
  139.         ORDER BY (column_name1, column_name2, ...)
  140.         [FROM rollup_index_name]
  141.         [PROPERTIES ("key"="value", ...)]
  142.     note:
  143.         1) All columns in index must be written
  144.         2) value is listed after the key column
  146. 6. Modify the properties of the table, currently supports modifying the bloom filter column, the colocate_with attribute and the dynamic_partition attribute,  the replication_num and default.replication_num.
  147.     grammar:
  148.         PROPERTIES ("key"="value")
  149.     note:
  150.         Can also be merged into the above schema change operation to modify, see the example below
  152. 7. Enable batch delete support
  153.     grammar:
  154.         ENABLE FEATURE "BATCH_DELETE"
  155.     note:
  156.         1) Only support unique tables
  157.         2) Batch deletion is supported for old tables, while new tables are already supported when they are created
  159. 8. Enable the ability to import in order by the value of the sequence column
  160.     grammer:
  161.         ENABLE FEATURE "SEQUENCE_LOAD" WITH PROPERTIES ("function_column.sequence_type" = "Date")
  162.     note:
  163.         1) Only support unique tables
  164.         2) The sequence_type is used to specify the type of the sequence column, which can be integral and time type
  165.         3) Only the orderliness of newly imported data is supported. Historical data cannot be changed
  167. 9. Modify default buckets number of partition 
  168.     grammer:
  169.         MODIFY DISTRIBUTION DISTRIBUTED BY HASH (k1[,k2 ...]) BUCKETS num
  170.     note：
  171.         1）Only support non colocate table with RANGE partition and HASH distribution
  173. 10. Modify table comment
  174.     grammer:
  175.         MODIFY COMMENT "new table comment"
  177. 11. Modify column comment
  178.     grammer:
  179.         MODIFY COLUMN col1 COMMENT "new column comment"
  181. 12. Modify engine type
  183.     Only the MySQL type can be changed to the ODBC type. The value of driver is the name of the driver in the odbc.init configuration.
  185.     grammar:
  186.         MODIFY ENGINE TO odbc PROPERTIES("driver" = "MySQL");
  188. Rename supports modification of the following names:
  189. 1. Modify the table name
  190.     grammar:
  191.         RENAME new_table_name;
  193. 2. Modify the rollup index name
  194.     grammar:
  195.         RENAME ROLLUP old_rollup_name new_rollup_name;
  197. 3. Modify the partition name
  198.     grammar:
  199.         RENAME PARTITION old_partition_name new_partition_name;
  201. Bitmap index supports the following modifications:
  202. 1. create bitmap index
  203.     grammar:
  204.         ADD INDEX index_name (column [, ...],) [USING BITMAP] [COMMENT 'balabala'];
  205.     note:
  206.         1. only supports bitmap index for current version
  207.         2. BITMAP index only supports apply on single column
  208. 2. drop index
  209.     grammar:
  210.         DROP INDEX index_name;

example

  1. [table]
  2. 1. Modify the default number of replications of the table, which is used as default number of replications while creating new partition.
  3.     ALTER TABLE example_db.my_table 
  4.     SET ("default.replication_num" = "2");
  6. 2. Modify the actual number of replications of a unpartitioned table (unpartitioned table only)
  7.     ALTER TABLE example_db.my_table
  8.     SET ("replication_num" = "3");
  10. [partition]
  11. 1. Add partition, existing partition [MIN, 2013-01-01), add partition [2013-01-01, 2014-01-01), use default bucket mode
  12.     ALTER TABLE example_db.my_table
  13.     ADD PARTITION p1 VALUES LESS THAN ("2014-01-01");
  15. 2. Increase the partition and use the new number of buckets
  16.     ALTER TABLE example_db.my_table
  17.     ADD PARTITION p1 VALUES LESS THAN ("2015-01-01")
  18.     DISTRIBUTED BY HASH(k1) BUCKETS 20;
  20. 3. Increase the partition and use the new number of copies
  21.     ALTER TABLE example_db.my_table
  22.     ADD PARTITION p1 VALUES LESS THAN ("2015-01-01")
  23.     ("replication_num"="1");
  25. 4. Modify the number of partition copies
  26.     ALTER TABLE example_db.my_table
  27.     MODIFY PARTITION p1 SET("replication_num"="1");
  29. 5. Batch modify the specified partitions
  30.     ALTER TABLE example_db.my_table
  31.     MODIFY PARTITION (p1, p2, p4) SET("in_memory"="true");
  33. 6. Batch modify all partitions
  34.     ALTER TABLE example_db.my_table
  35.     MODIFY PARTITION (*) SET("storage_medium"="HDD");
  37. 7. Delete the partition
  38.     ALTER TABLE example_db.my_table
  39.     DROP PARTITION p1;
  41. 8. Add a partition that specifies the upper and lower bounds
  43.     ALTER TABLE example_db.my_table
  44.     ADD PARTITION p1 VALUES [("2014-01-01"), ("2014-02-01"));
  46. [rollup]
  47. 1. Create index: example_rollup_index, based on base index(k1,k2,k3,v1,v2). Columnar storage.
  48.     ALTER TABLE example_db.my_table
  49.     ADD ROLLUP example_rollup_index(k1, k3, v1, v2);
  51. 2. Create index: example_rollup_index2, based on example_rollup_index(k1,k3,v1,v2)
  52.     ALTER TABLE example_db.my_table
  53.     ADD ROLLUP example_rollup_index2 (k1, v1)
  54.     FROM example_rollup_index;
  56. 3. Create index: example_rollup_index3, based on base index (k1, k2, k3, v1), custom rollup timeout time is one hour.
  58.     ALTER TABLE example_db.my_table
  59.     ADD ROLLUP example_rollup_index(k1, k3, v1)
  60.     PROPERTIES("timeout" = "3600");
  62. 3. Delete index: example_rollup_index2
  63.     ALTER TABLE example_db.my_table
  64.     DROP ROLLUP example_rollup_index2;
  66. [schema change]
  67. 1. Add a key column new_col to the col1 of example_rollup_index (non-aggregate model)
  68.     ALTER TABLE example_db.my_table
  69.     ADD COLUMN new_col INT KEY DEFAULT "0" AFTER col1
  70.     TO example_rollup_index;
  72. 2. Add a value column new_col to the col1 of example_rollup_index (non-aggregate model)
  73.       ALTER TABLE example_db.my_table
  74.       ADD COLUMN new_col INT DEFAULT "0" AFTER col1
  75.       TO example_rollup_index;
  77. 3. Add a key column new_col (aggregation model) to col1 of example_rollup_index
  78.       ALTER TABLE example_db.my_table
  79.       ADD COLUMN new_col INT DEFAULT "0" AFTER col1
  80.       TO example_rollup_index;
  82. 4. Add a value column to the col1 of example_rollup_index. new_col SUM aggregation type (aggregation model)
  83.       ALTER TABLE example_db.my_table
  84.       ADD COLUMN new_col INT SUM DEFAULT "0" AFTER col1
  85.       TO example_rollup_index;
  87. 5. Add multiple columns to the example_rollup_index (aggregate model)
  88.     ALTER TABLE example_db.my_table
  89.     ADD COLUMN (col1 INT DEFAULT "1", col2 FLOAT SUM DEFAULT "2.3")
  90.     TO example_rollup_index;
  92. 6. Remove a column from example_rollup_index
  93.     ALTER TABLE example_db.my_table
  94.     DROP COLUMN col2
  95.     FROM example_rollup_index;
  97. 7. Modify the base index's col1 key column to be of type BIGINT and move to the col2 column
  98.    (*Attention: Whether to modify the key column or the value column, complete column information need to be declared. For example, MODIFY COLUMN xxx COLUMNTYPE [KEY|agg_type]*)
  99.     ALTER TABLE example_db.my_table
  100.     MODIFY COLUMN col1 BIGINT KEY DEFAULT "1" AFTER col2;
  102. 8. Modify the maximum length of the val1 column of the base index. The original val1 is (val1 VARCHAR(32) REPLACE DEFAULT "abc")
  103.     ALTER TABLE example_db.my_table
  104.     MODIFY COLUMN val1 VARCHAR(64) REPLACE DEFAULT "abc";
  106. 9. Reorder the columns in example_rollup_index (set the original column order: k1, k2, k3, v1, v2)
  107.     ALTER TABLE example_db.my_table
  108.     ORDER BY (k3, k1, k2, v2, v1)
  109.     FROM example_rollup_index;
  111. 10. Perform both operations simultaneously
  112.     ALTER TABLE example_db.my_table
  113.     ADD COLUMN v2 INT MAX DEFAULT "0" AFTER k2 TO example_rollup_index,
  114.     ORDER BY (k3,k1,k2,v2,v1) FROM example_rollup_index;
  116. 11. Modify the bloom filter column of the table
  117.     ALTER TABLE example_db.my_table SET ("bloom_filter_columns"="k1,k2,k3");
  119.     Can also be merged into the above schema change operation (note that the syntax of multiple clauses is slightly different)
  120.     ALTER TABLE example_db.my_table
  121.     DROP COLUMN col2
  122.     PROPERTIES ("bloom_filter_columns"="k1,k2,k3");
  124. 12. Modify the Colocate property of the table
  126.     ALTER TABLE example_db.my_table set ("colocate_with" = "t1");
  128. 13. Change the bucketing mode of the table from Random Distribution to Hash Distribution
  130.     ALTER TABLE example_db.my_table set ("distribution_type" = "hash");
  132. 14. Modify the dynamic partition properties of the table (support adding dynamic partition properties to tables without dynamic partition properties)
  134.     ALTER TABLE example_db.my_table set ("dynamic_partition.enable" = "false");
  136.     If you need to add dynamic partition attributes to a table without dynamic partition attributes, you need to specify all dynamic partition attributes
  138.     ALTER TABLE example_db.my_table set ("dynamic_partition.enable"= "true", "dynamic_partition.time_unit" = "DAY", "dynamic_partition.end "= "3", "dynamic_partition.prefix" = "p", "dynamic_partition.buckets" = "32");
  140. 15. Modify the in_memory property of the table
  142.     ALTER TABLE example_db.my_table set ("in_memory" = "true");
  143. 16. Enable batch delete support
  145.     ALTER TABLE example_db.my_table ENABLE FEATURE "BATCH_DELETE"
  146. 17. Enable the ability to import in order by the value of the Sequence column
  148.     ALTER TABLE example_db.my_table ENABLE FEATURE "SEQUENCE_LOAD" WITH PROPERTIES ("function_column.sequence_type" = "Date")
  150. 18. Modify the default buckets number of example_db.my_table to 50
  152.     ALTER TABLE example_db.my_table MODIFY DISTRIBUTION DISTRIBUTED BY HASH(k1) BUCKETS 50;
  154. 19. Modify table comment
  156.     ALTER TABLE example_db.my_table MODIFY COMMENT "new comment";
  158. 20. Modify column comment
  160.     ALTER TABLE example_db.my_table MODIFY COLUMN k1 COMMENT "k1", MODIFY COLUMN k2 COMMENT "k2";
  162. 21. Modify engine Type
  164.     ALTER TABLE example_db.mysql_table MODIFY ENGINE TO odbc PROPERTIES("driver" = "MySQL");
  166. [rename]
  167. 1. Modify the table named table1 to table2
  168.     ALTER TABLE table1 RENAME table2;
  170. 2. Modify the rollup index named rollup1 in the table example_table to rollup2
  171.     ALTER TABLE example_table RENAME ROLLUP rollup1 rollup2;
  173. 3. Modify the partition named p1 in the table example_table to p2
  174.     ALTER TABLE example_table RENAME PARTITION p1 p2;
  176. [index]
  177. 1. create index on table1 column siteid using bitmap 
  178.     ALTER TABLE table1 ADD INDEX index_name  [USING BITMAP] (siteid) COMMENT 'balabala';
  179. 2. drop bitmap index of table1
  180.     ALTER TABLE table1 DROP INDEX index_name;

keyword

  1. ALTER, TABLE, ROLLUP, COLUMN, PARTITION, RENAME