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

keyword

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