ALTER TABLE

description

  1. 该语句用于对已有的 table 进行修改。如果没有指定 rollup index，默认操作 base index。
  2. 该语句分为三种操作类型： schema change 、rollup 、partition
  3. 这三种操作类型不能同时出现在一条 ALTER TABLE 语句中。
  4. 其中 schema change 和 rollup 是异步操作，任务提交成功则返回。之后可使用 SHOW ALTER 命令查看进度。
  5. partition 是同步操作，命令返回表示执行完毕。
  7. 语法：
  8.     ALTER TABLE [database.]table
  9.     alter_clause1[, alter_clause2, ...];
  11. alter_clause 分为 partition 、rollup、schema change、rename 和index五种。
  13. partition 支持如下几种修改方式
  14. 1. 增加分区
  15.     语法：
  16.         ADD PARTITION [IF NOT EXISTS] partition_name 
  17.         partition_desc ["key"="value"]
  18.         [DISTRIBUTED BY HASH (k1[,k2 ...]) [BUCKETS num]]
  19.     注意：
  20.         1) partition_desc 支持以下两种写法：
  21.             * VALUES LESS THAN [MAXVALUE|("value1", ...)]
  22.             * VALUES [("value1", ...), ("value1", ...))
  23.         1) 分区为左闭右开区间，如果用户仅指定右边界，系统会自动确定左边界
  24.         2) 如果没有指定分桶方式，则自动使用建表使用的分桶方式
  25.         3) 如指定分桶方式，只能修改分桶数，不可修改分桶方式或分桶列
  26.         4) ["key"="value"] 部分可以设置分区的一些属性，具体说明见 CREATE TABLE
  27.         5) 如果建表时用户未显式创建Partition,则不支持通过ALTER的方式增加分区
  29. 2. 删除分区
  30.     语法：
  31.         DROP PARTITION [IF EXISTS] partition_name [FORCE]
  32.     注意：
  33.         1) 使用分区方式的表至少要保留一个分区。
  34.         2) 执行 DROP PARTITION 一段时间内，可以通过 RECOVER 语句恢复被删除的分区。详见 RECOVER 语句
  35.         3) 如果执行 DROP PARTITION FORCE，则系统不会检查该分区是否存在未完成的事务，分区将直接被删除并且不能被恢复，一般不建议执行此操作
  37. 3. 修改分区属性
  38.     语法：
  39.         MODIFY PARTITION p1|(p1[, p2, ...]) SET ("key" = "value", ...)
  40.     说明：
  41.         1) 当前支持修改分区的下列属性：
  42.             - storage_medium
  43.             - storage_cooldown_time
  44.             - replication_num 
  45.             — in_memory
  46.         2) 对于单分区表，partition_name 同表名。
  48. rollup 支持如下几种创建方式：
  49. 1. 创建 rollup index
  50.     语法：
  51.         ADD ROLLUP rollup_name (column_name1, column_name2, ...)
  52.         [FROM from_index_name]
  53.         [PROPERTIES ("key"="value", ...)]
  55.         properties: 支持设置超时时间，默认超时时间为1天。
  56.     例子：
  57.         ADD ROLLUP r1(col1,col2) from r0
  58. 1.2 批量创建 rollup index
  59.     语法：
  60.         ADD ROLLUP [rollup_name (column_name1, column_name2, ...)
  61.                     [FROM from_index_name]
  62.                     [PROPERTIES ("key"="value", ...)],...]
  63.     例子：
  64.         ADD ROLLUP r1(col1,col2) from r0, r2(col3,col4) from r0
  65. 1.3 注意：
  66.         1) 如果没有指定 from_index_name，则默认从 base index 创建
  67.         2) rollup 表中的列必须是 from_index 中已有的列
  68.         3) 在 properties 中，可以指定存储格式。具体请参阅 CREATE TABLE
  70. 2. 删除 rollup index
  71.     语法：
  72.         DROP ROLLUP rollup_name [PROPERTIES ("key"="value", ...)]
  73.     例子：
  74.         DROP ROLLUP r1
  75. 2.1 批量删除 rollup index
  76.     语法：DROP ROLLUP [rollup_name [PROPERTIES ("key"="value", ...)],...]
  77.     例子：DROP ROLLUP r1,r2
  78. 2.2 注意：
  79.         1) 不能删除 base index
  81. schema change 支持如下几种修改方式：
  82. 1. 向指定 index 的指定位置添加一列
  83.     语法：
  84.         ADD COLUMN column_name column_type [KEY | agg_type] [DEFAULT "default_value"]
  85.         [AFTER column_name|FIRST]
  86.         [TO rollup_index_name]
  87.         [PROPERTIES ("key"="value", ...)]
  88.     注意：
  89.         1) 聚合模型如果增加 value 列，需要指定 agg_type
  90.         2) 非聚合模型（如 DUPLICATE KEY）如果增加key列，需要指定KEY关键字
  91.         3) 不能在 rollup index 中增加 base index 中已经存在的列
  92.             如有需要，可以重新创建一个 rollup index）
  94. 2. 向指定 index 添加多列
  95.     语法：
  96.         ADD COLUMN (column_name1 column_type [KEY | agg_type] DEFAULT "default_value", ...)
  97.         [TO rollup_index_name]
  98.         [PROPERTIES ("key"="value", ...)]
  99.     注意：
  100.         1) 聚合模型如果增加 value 列，需要指定agg_type
  101.         2) 非聚合模型如果增加key列，需要指定KEY关键字
  102.         3) 不能在 rollup index 中增加 base index 中已经存在的列
  103.         （如有需要，可以重新创建一个 rollup index）
  105. 3. 从指定 index 中删除一列
  106.     语法：
  107.         DROP COLUMN column_name
  108.         [FROM rollup_index_name]
  109.     注意：
  110.         1) 不能删除分区列
  111.         2) 如果是从 base index 中删除列，则如果 rollup index 中包含该列，也会被删除
  113. 4. 修改指定 index 的列类型以及列位置
  114.     语法：
  115.         MODIFY COLUMN column_name column_type [KEY | agg_type] [NULL | NOT NULL] [DEFAULT "default_value"]
  116.         [AFTER column_name|FIRST]
  117.         [FROM rollup_index_name]
  118.         [PROPERTIES ("key"="value", ...)]
  119.     注意：
  120.         1) 聚合模型如果修改 value 列，需要指定 agg_type
  121.         2) 非聚合类型如果修改key列，需要指定KEY关键字
  122.         3) 只能修改列的类型，列的其他属性维持原样（即其他属性需在语句中按照原属性显式的写出，参见 example 8）
  123.         4) 分区列和分桶列不能做任何修改
  124.         5) 目前支持以下类型的转换（精度损失由用户保证）
  125.             TINYINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE 类型向范围更大的数字类型转换
  126.             TINTINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE/DECIMAL 转换成 VARCHAR
  127.             VARCHAR 支持修改最大长度
  128.             VARCHAR/CHAR 转换成 TINTINT/SMALLINT/INT/BIGINT/LARGEINT/FLOAT/DOUBLE
  129.             VARCHAR/CHAR 转换成 DATE (目前支持"%Y-%m-%d", "%y-%m-%d", "%Y%m%d", "%y%m%d", "%Y/%m/%d, "%y/%m/%d"六种格式化格式)
  130.             DATETIME 转换成 DATE(仅保留年-月-日信息, 例如: `2019-12-09 21:47:05` <--> `2019-12-09`)
  131.             DATE 转换成 DATETIME(时分秒自动补零， 例如: `2019-12-09` <--> `2019-12-09 00:00:00`)
  132.             FLOAT 转换成 DOUBLE
  133.             INT 转换成 DATE (如果INT类型数据不合法则转换失败，原始数据不变)
  134.         6) 不支持从NULL转为NOT NULL
  136. 5. 对指定 index 的列进行重新排序
  137.     语法：
  138.         ORDER BY (column_name1, column_name2, ...)
  139.         [FROM rollup_index_name]
  140.         [PROPERTIES ("key"="value", ...)]
  141.     注意：
  142.         1) index 中的所有列都要写出来
  143.         2) value 列在 key 列之后
  145. 6. 修改table的属性，目前支持修改bloom filter列, colocate_with 属性和dynamic_partition属性，replication_num和default.replication_num属性
  146.     语法：
  147.         PROPERTIES ("key"="value")
  148.     注意：
  149.         也可以合并到上面的schema change操作中来修改，见下面例子
  151. 7. 启用批量删除支持
  152.     语法：
  153.         ENABLE FEATURE "BATCH_DELETE"
  154.     注意：
  155.         1） 只能用在unique 表
  156.         2) 用于旧表支持批量删除功能，新表创建时已经支持
  158. 8. 启用按照sequence column的值来保证导入顺序的功能
  159.     语法:
  160.         ENABLE FEATURE "SEQUENCE_LOAD" WITH PROPERTIES ("function_column.sequence_type" = "Date")
  161.     注意：
  162.         1）只能用在unique表
  163.         2) sequence_type用来指定sequence列的类型，可以为整型和时间类型
  164.         3) 只支持新导入数据的有序性，历史数据无法更改
  166. 9. 修改表的分区默认分桶数
  167.     语法:
  168.         MODIFY DISTRIBUTION DISTRIBUTED BY HASH (k1[,k2 ...]) BUCKETS num
  169.     注意：
  170.         1）只能用在分区类型为RANGE，采用哈希分桶的非colocate表
  172. 10. 修改表注释
  173.     语法:
  174.         MODIFY COMMENT "new table comment"
  176. 11. 修改列注释
  177.     语法:
  178.         MODIFY COLUMN col1 COMMENT "new column comment"
  180. 12. 修改引擎类型
  182.     仅支持将 MySQL 类型修改为 ODBC 类型。driver 的值为 odbc.init 配置中的 driver 名称。
  184.     语法：
  185.         MODIFY ENGINE TO odbc PROPERTIES("driver" = "MySQL");
  187. rename 支持对以下名称进行修改：
  188. 1. 修改表名
  189.     语法：
  190.         RENAME new_table_name;
  192. 2. 修改 rollup index 名称
  193.     语法：
  194.         RENAME ROLLUP old_rollup_name new_rollup_name;
  196. 3. 修改 partition 名称
  197.     语法：
  198.         RENAME PARTITION old_partition_name new_partition_name;
  200. bitmap index 支持如下几种修改方式
  201. 1. 创建bitmap 索引
  202.     语法：
  203.         ADD INDEX [IF NOT EXISTS] index_name (column [, ...],) [USING BITMAP] [COMMENT 'balabala'];
  204.     注意：
  205.         1. 目前仅支持bitmap 索引
  206.         1. BITMAP 索引仅在单列上创建
  208. 2. 删除索引
  209.     语法：
  210.         DROP INDEX [IF EXISTS] index_name；

example

  1. [table]
  2. 1. 修改表的默认副本数量, 新建分区副本数量默认使用此值
  3.     ALTER TABLE example_db.my_table 
  4.     SET ("default.replication_num" = "2");
  6. 2. 修改单分区表的实际副本数量(只限单分区表)
  7.     ALTER TABLE example_db.my_table
  8.     SET ("replication_num" = "3");
  10. [partition]
  11. 1. 增加分区, 现有分区 [MIN, 2013-01-01)，增加分区 [2013-01-01, 2014-01-01)，使用默认分桶方式
  12.     ALTER TABLE example_db.my_table
  13.     ADD PARTITION p1 VALUES LESS THAN ("2014-01-01");
  15. 2. 增加分区，使用新的分桶数
  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. 增加分区，使用新的副本数
  21.     ALTER TABLE example_db.my_table
  22.     ADD PARTITION p1 VALUES LESS THAN ("2015-01-01")
  23.     ("replication_num"="1");
  25. 4. 修改分区副本数
  26.     ALTER TABLE example_db.my_table
  27.     MODIFY PARTITION p1 SET("replication_num"="1");
  29. 5. 批量修改指定分区
  30.     ALTER TABLE example_db.my_table
  31.     MODIFY PARTITION (p1, p2, p4) SET("in_memory"="true");
  33. 6. 批量修改所有分区
  34.     ALTER TABLE example_db.my_table
  35.     MODIFY PARTITION (*) SET("storage_medium"="HDD");
  37. 7. 删除分区
  38.     ALTER TABLE example_db.my_table
  39.     DROP PARTITION p1;
  41. 8. 增加一个指定上下界的分区
  43.     ALTER TABLE example_db.my_table
  44.     ADD PARTITION p1 VALUES [("2014-01-01"), ("2014-02-01"));
  46. [rollup]
  47. 1. 创建 index: example_rollup_index，基于 base index（k1,k2,k3,v1,v2）。列式存储。
  48.     ALTER TABLE example_db.my_table
  49.     ADD ROLLUP example_rollup_index(k1, k3, v1, v2);
  51. 2. 创建 index: example_rollup_index2，基于 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. 创建 index: example_rollup_index3, 基于 base index (k1,k2,k3,v1), 自定义 rollup 超时时间一小时。
  57.     ALTER TABLE example_db.my_table
  58.     ADD ROLLUP example_rollup_index(k1, k3, v1)
  59.     PROPERTIES("timeout" = "3600");
  61. 4. 删除 index: example_rollup_index2
  62.     ALTER TABLE example_db.my_table
  63.     DROP ROLLUP example_rollup_index2;
  67. [schema change]
  68. 1. 向 example_rollup_index 的 col1 后添加一个key列 new_col(非聚合模型)
  69.     ALTER TABLE example_db.my_table
  70.     ADD COLUMN new_col INT KEY DEFAULT "0" AFTER col1
  71.     TO example_rollup_index;
  73. 2. 向example_rollup_index的col1后添加一个value列new_col(非聚合模型)
  74.       ALTER TABLE example_db.my_table   
  75.       ADD COLUMN new_col INT DEFAULT "0" AFTER col1    
  76.       TO example_rollup_index;
  78. 3. 向example_rollup_index的col1后添加一个key列new_col(聚合模型)
  79.       ALTER TABLE example_db.my_table   
  80.       ADD COLUMN new_col INT DEFAULT "0" AFTER col1    
  81.       TO example_rollup_index;
  83. 4. 向example_rollup_index的col1后添加一个value列new_col SUM聚合类型(聚合模型)
  84.       ALTER TABLE example_db.my_table   
  85.       ADD COLUMN new_col INT SUM DEFAULT "0" AFTER col1    
  86.       TO example_rollup_index;
  88. 5. 向 example_rollup_index 添加多列(聚合模型)
  89.     ALTER TABLE example_db.my_table
  90.     ADD COLUMN (col1 INT DEFAULT "1", col2 FLOAT SUM DEFAULT "2.3")
  91.     TO example_rollup_index;
  93. 6. 从 example_rollup_index 删除一列
  94.     ALTER TABLE example_db.my_table
  95.     DROP COLUMN col2
  96.     FROM example_rollup_index;
  98. 7. 修改 base index 的 key 列 col1 的类型为 BIGINT，并移动到 col2 列后面
  99.    (*注意，无论是修改 key 列还是 value 列都需要声明完整的 column 信息*) 例如：MODIFY COLUMN xxx COLUMNTYPE [KEY|agg_type] 
  100.     ALTER TABLE example_db.my_table
  101.     MODIFY COLUMN col1 BIGINT KEY DEFAULT "1" AFTER col2;
  103. 8. 修改 base index 的 val1 列最大长度。原 val1 为 (val1 VARCHAR(32) REPLACE DEFAULT "abc")
  104.     ALTER TABLE example_db.my_table
  105.     MODIFY COLUMN val1 VARCHAR(64) REPLACE DEFAULT "abc";
  107. 9. 重新排序 example_rollup_index 中的列（设原列顺序为：k1,k2,k3,v1,v2）
  108.     ALTER TABLE example_db.my_table
  109.     ORDER BY (k3,k1,k2,v2,v1)
  110.     FROM example_rollup_index;
  112. 10. 同时执行两种操作
  113.     ALTER TABLE example_db.my_table
  114.     ADD COLUMN v2 INT MAX DEFAULT "0" AFTER k2 TO example_rollup_index,
  115.     ORDER BY (k3,k1,k2,v2,v1) FROM example_rollup_index;
  117. 11. 修改表的 bloom filter 列
  118.     ALTER TABLE example_db.my_table SET ("bloom_filter_columns"="k1,k2,k3");
  120.     也可以合并到上面的 schema change 操作中（注意多子句的语法有少许区别）
  121.     ALTER TABLE example_db.my_table
  122.     DROP COLUMN col2
  123.     PROPERTIES ("bloom_filter_columns"="k1,k2,k3");
  125. 12. 修改表的Colocate 属性
  127.     ALTER TABLE example_db.my_table set ("colocate_with" = "t1");
  129. 13. 将表的分桶方式由 Random Distribution 改为 Hash Distribution
  131.     ALTER TABLE example_db.my_table set ("distribution_type" = "hash");
  133. 14. 修改表的动态分区属性(支持未添加动态分区属性的表添加动态分区属性)
  134.     ALTER TABLE example_db.my_table set ("dynamic_partition.enable" = "false");
  136.     如果需要在未添加动态分区属性的表中添加动态分区属性，则需要指定所有的动态分区属性
  137.     (注:非分区表不支持添加动态分区属性)        
  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. 修改表的 in_memory 属性
  142.     ALTER TABLE example_db.my_table set ("in_memory" = "true");
  143. 16. 启用 批量删除功能
  144.     ALTER TABLE example_db.my_table ENABLE FEATURE "BATCH_DELETE"
  145. 17. 启用按照sequence column的值来保证导入顺序的功能
  147.     ALTER TABLE example_db.my_table ENABLE FEATURE "SEQUENCE_LOAD" WITH PROPERTIES ("function_column.sequence_type" = "Date")
  149. 18. 将表的默认分桶数改为50
  151.     ALTER TABLE example_db.my_table MODIFY DISTRIBUTION DISTRIBUTED BY HASH(k1) BUCKETS 50;
  153. 19. 修改表注释
  155.     ALTER TABLE example_db.my_table MODIFY COMMENT "new comment";
  157. 20. 修改列注释
  159.     ALTER TABLE example_db.my_table MODIFY COLUMN k1 COMMENT "k1", MODIFY COLUMN k2 COMMENT "k2";
  161. 21. 修改引擎类型
  163.     ALTER TABLE example_db.mysql_table MODIFY ENGINE TO odbc PROPERTIES("driver" = "MySQL");
  165. [rename]
  166. 1. 将名为 table1 的表修改为 table2
  167.     ALTER TABLE table1 RENAME table2;
  169. 2. 将表 example_table 中名为 rollup1 的 rollup index 修改为 rollup2
  170.     ALTER TABLE example_table RENAME ROLLUP rollup1 rollup2;
  172. 3. 将表 example_table 中名为 p1 的 partition 修改为 p2
  173.     ALTER TABLE example_table RENAME PARTITION p1 p2;
  174. [index]
  175. 1. 在table1 上为siteid 创建bitmap 索引
  176.     ALTER TABLE table1 ADD INDEX [IF NOT EXISTS] index_name (siteid) [USING BITMAP] COMMENT 'balabala';
  177. 2. 删除table1 上的siteid列的bitmap 索引
  178.     ALTER TABLE table1 DROP INDEX [IF EXISTS] index_name;

keyword

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