实时数仓Hologres：ALTER TABLE

使用限制

注意事项

进行修改dictionary_encoding_columns、bitmap_columns、time_to_live_in_seconds等表属性操作时，可能会触发后台异步执行Compaction，占用一定的CPU资源，同时实例的存储量可能会出现先上升后回落的情况。

重命名

增加列

ALTER TABLE语句可以给表增加列，仅支持在表的最后一列之后增加新的列。

• 使用语法

  --新增一列
  ALTER TABLE IF EXISTS [schema_name.]<table_name> ADD COLUMN <new_column> <data_type>;
  
  --新增多列
  ALTER TABLE IF EXISTS [schema_name.]<table_name> ADD COLUMN <new_column_1> <data_type>, ADD COLUMN <new_column_2> <data_type>; 

• 使用示例

  --在表holo_test中增加id列
  ALTER TABLE IF EXISTS public.holo_test ADD COLUMN id int;

重命名列

• 使用语法

  ALTER TABLE [schema_name.]<table_name> RENAME COLUMN <old_column_name> TO <new_column_name>;

• 使用示例

  --将表holo_test的id列重命名为name
  ALTER TABLE public.holo_test RENAME COLUMN id TO name; 

修改默认值

ALTER TABLE语句支持修改默认值设置（常量或常量表达式），该默认值仅对设置之后新写入/更新数据有效，不会更新表中已有数据的默认值。当前仅Hologres V0.9.23及以上版本支持修改默认值。具体修改方式说明如下：

• 使用语法

  --修改表字段的默认值
  ALTER TABLE [schema_name.]<table_name> ALTER COLUMN <column> SET DEFAULT <expression>;
  
  --删除表字段的默认值
  ALTER TABLE [schema_name.]<table_name> ALTER COLUMN <column> DROP DEFAULT;

• 使用示例

  --修改表holo_test中id列的默认值为0
  ALTER TABLE holo_test ALTER COLUMN id SET DEFAULT 0;
  
  --删除表holo_test中id列的默认值
  ALTER TABLE holo_test ALTER COLUMN id DROP DEFAULT;

修改表属性

Hologres支持通过执行语句修改参数，达到修改表属性的目的。具体修改方式说明如下：

• 修改dictionary_encoding_columns字典编码列。修改Dictionary Encoding设置，会引起数据文件重新编码存储，会在一段时间内消耗一部分CPU和内存资源，建议在业务低峰期执行变更。
  • 使用语法

    --修改全量
    CALL SET_TABLE_PROPERTY('[schema_name.]<table_name>', 'dictionary_encoding_columns', '[columnName{:[on|off|auto]}[,...]]');
    
    --修改增量，只修改call里面的指定字段，其余字段不变
    CALL UPDATE_TABLE_PROPERTY('[schema_name.]<table_name>', 'dictionary_encoding_columns', '[columnName{:[on|off|auto]}[,...]]');

  • 参数说明

    参数	说明
    table_name	需要和待修改的表名大小写保持一致，可以携带Schema信息。
    on	表示当前字段打开dictionary_encoding_columns。
    off	表示当前字段关闭dictionary_encoding_columns。
    auto	表示自动。如果是设置了auto，Hologres会根据所在列数值的重复程度自动选择是否进行dictionary_encoding_columns，值的重复度越高，字典编码的收益越大。在Hologres V0.8版本及更早版本中默认所有text列都会被设置为dictionary_encoding_columns，在Hologres V0.9版本及之后版本，会根据数据特征自动选择是否创建字典编码。

  • 使用示例
    • 对a列显示创建dictionary，b列自动选择是否创建dictionary，c、d两列不创建dictionary。

      CREATE TABLE dwd.holo_test (
       a text NOT NULL,
       b text NOT NULL,
       c text NOT NULL,
       d text
      );
      CALL UPDATE_TABLE_PROPERTY('dwd.holo_test','dictionary_encoding_columns','a:on,b:auto');

    • 对a列显示关闭dictionary，系统也会自动给b、c、d字段加上dictionary索引。

      CREATE TABLE dwd.holo_test (
       a text NOT NULL,
       b text NOT NULL,
       c text NOT NULL,
       d text
      );
      CALL SET_TABLE_PROPERTY('dwd.holo_test','dictionary_encoding_columns','a:off');

• 修改bitmap_columns比特编码列

  Hologres从V0.9版本开始支持通过执行以下语句修改bitmap_columns，无需再重新建表即可修改表属性。

  • 使用语法

    --修改全量
    CALL SET_TABLE_PROPERTY('[schema_name.]<table_name>', 'bitmap_columns', '[columnName{:[on|off]}[,...]]');
    
    --修改增量，只修改call里面的指定字段，其余字段不变
    CALL UPDATE_TABLE_PROPERTY('[schema_name.]<table_name>', 'bitmap_columns', '[columnName{:[on|off]}[,...]]');

  • 参数说明

    参数	说明
    table_name	需要和待修改的表名大小写保持一致，可以携带Schema信息。
    on	当前字段打开bitmap_columns。
    off	当前字段关闭bitmap_columns。

  • 使用示例
    • 对a列启动bitmap索引，对b、c、d不启动bitmap索引。

      CREATE TABLE dwd.holo_test (
       a text NOT NULL,
       b text NOT NULL,
       c text NOT NULL,
       d text
      );
      CALL UPDATE_TABLE_PROPERTY('dwd.holo_test','bitmap_columns','a:on');

    • 对b关闭bitmap索引，系统会自动给a、c、d创建bitmap索引。

      CREATE TABLE dwd.holo_test_1 (
       a text NOT NULL,
       b text NOT NULL,
       c text NOT NULL,
       d text
      );
      CALL SET_TABLE_PROPERTY('dwd.holo_test_1','bitmap_columns','b:off');

• 修改表数据的生存时间
  • 使用语法

    call set_table_property('[schema_name.]<table_name>', 'time_to_live_in_seconds', '<non_negative_literal>');

  • 参数说明

    参数	说明
    time_to_live_in_seconds	简称TTL，表数据的生存时间，单位为秒，必须是正整数。

    说明 表数据生存时间是按照数据写入Hologres开始，超过该指定时间，表数据将会在某个时间内被删除，但并不是精准的时间。
  • 使用示例

    call set_table_property('dwd.holo_test', 'time_to_live_in_seconds', '600');

修改表所在Schema

Hologres从 V1.3版本开始，支持修改表所在的Schema，例如将表从schema1移动至schema2，无须重新建表导数据，实现快速表路径切换。

• 使用语法

  ALTER TABLE [ IF EXISTS ] [<schema>.]<table_name>
      SET SCHEMA <new_schema>;

  schema为表所在的Schema名称；table_name为修改表的名称；new_schema为移动至新Schema的名称。
• 使用示例

  将表名称为tb1的表从publicSchema移动至testschemaSchema下。

  ALTER TABLE IF EXISTS public.tbl
      SET SCHEMA testschema;

HoloWeb可视化修改表

HoloWeb提供可视化编辑表功能，无需写SQL命令就能修改表字段和部分表属性，步骤如下。

1. 进入HoloWeb页面，详情请参见连接HoloWeb。
2. 在HoloWeb页面顶部菜单栏，单击元数据管理。
3. 在元数据管理页面左侧的已登录实例列表，双击要修改的目标表。
4. 在表的详情页面，可视化修改表的字段和部分表属性。
5. 单击右上角的提交表，完成表修改。