实时数仓Hologres：ALTER TABLE

使用限制

注意事项

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

修改数据类型

Hologres V3.0版本起，支持修改内表列的数据类型。

• 使用限制

  • 支持修改非分区表、分区父表的列类型，不支持修改分区子表的列类型。

  • 不支持修改分区父表的分区列类型。

  • 不支持COLLATE语法和USING语法。

  • 仅支持如下数据类型的转换：

    源数据类型	目标类型	备注
    VARCHAR(N)	VARCHAR(M)	要求M>N
    VARCHAR(N)	TEXT	无
    CHAR(N)	CHAR(M)	要求M>N
    CHAR(N)	VARCHAR(M)	要求M>=N
    CHAR(N)	TEXT	无
    JSON	TEXT	无
    VARCHAR(N)[]	VARCHAR(M)[]	要求M>N
    VARCHAR(N)[]	TEXT[]	无

• 使用语法

  ALTER TABLE [ IF EXISTS ] <schema_name>.<table_name> ALTER [ COLUMN ] <column_name> TYPE <data_type>;

• 使用示例

  DROP TABLE IF EXISTS t;
  
  CREATE TABLE IF NOT EXISTS t (
      a varchar(5)
  );
  
  INSERT INTO t VALUES ('holo'), ('gres');
  
  ALTER TABLE t ALTER COLUMN a TYPE text;

重命名

增加列

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;

删除列（Beta）

Hologres从V2.0版本开始，支持删除列，具体语法如下。

• 使用限制

  • 仅适用于Hologres V2.0及以上版本，如果您的实例是V2.0以下版本，请您使用自助升级或加入Hologres钉钉交流群反馈，详情请参见如何获取更多的在线支持？。

  • 若您的表是分区表，仅能删除分区父表的列，无法直接删除分区子表的列。对于分区子表，在父表的列删除后自动删除。此操作开销较高，建议在业务低峰期执行。

  • 仅只有表Owner才能删除列，若您的数据库使用的是简单权限模型，需要设置为developer用户组权限。

  • 设置了Primary Key、Distribution Key、Clustering Key、Event_time_column属性的列无法删除。

  • 不支持删除外部表的列。

  • 删除JSONB相关列后，相关JSONB索引会一并删除。

  • 删除proxima_vector列时，需要指定cascade参数。

  • 删除Serial列时，如果Sequence是基于这一列创建的，则Sequence会一并被删除。

  • 如果表创建了物化视图，不支持删除源表，也不支持删除源表中被物化视图引用的列。

• 使用语法

  重要

  Hologres V2.0以下版本不支持删除列。

  set hg_experimental_enable_drop_column = on; --通过该GUC打开功能
  ALTER TABLE IF EXISTS <table_name> DROP COLUMN  [ IF EXISTS ] <column> [ RESTRICT | CASCADE ]

• 使用示例

  --建表
  begin;
  CREATE TABLE tbl (
   "id" bigint NOT NULL,
   "name" text NOT NULL,
   "age" bigint,
   "class" text NOT NULL,
   "reg_timestamp" timestamptz NOT NULL,
  PRIMARY KEY (id,age)
  );
  call set_table_property('tbl', 'orientation', 'column');
  call set_table_property('tbl', 'distribution_key', 'id');
  call set_table_property('tbl', 'clustering_key', 'age');
  call set_table_property('tbl', 'event_time_column', 'reg_timestamp');
  call set_table_property('tbl', 'bitmap_columns', 'name,class');
  call set_table_property('tbl', 'dictionary_encoding_columns', 'class:auto');
  commit;
  
  --删除指定列
  set hg_experimental_enable_drop_column = on;--Beta阶段，需要通过GUC参数打开这个功能
  ALTER TABLE IF EXISTS tbl DROP COLUMN name;

  查询表：

  SELECT*FROMtbl;
  
  --返回结果
  id    age    class    reg_timestamp
  ----+-----+---------+--------------
                      

重命名列

• 使用语法

  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和内存资源，建议在业务低峰期执行变更。

  • 使用语法

    -- 修改dictionary_encoding_columns（2.1版本起）
    ALTER TABLE <schema_name>.<table_name> SET (dictionary_encoding_columns = '[columnName{:[on|off|auto]}[,...]]'); --只支持全量修改
    
    -- 修改dictionary_encoding_columns（所有版本）
    --修改全量
    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]}[,...]]');

    重要

    Hologres V2.0版本起，针对UPDATE_TABLE_PROPERTY语法进行了优化，运行如下语句时，表的dictionary_encoding_columns属性保持不变。Hologres V2.0以前版本则是会清空表的dictionary_encoding_columns属性。

    CALL UPDATE_TABLE_PROPERTY('<table_name>','dictionary_encoding_columns','');

  • 参数说明

    参数	说明
    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，无需再重新建表即可修改表属性。

  • 使用语法

    -- 修改bitmap_columns（2.1版本起）
    ALTER TABLE <schema_name>.<table_name> SET (bitmap_columns = '[columnName{:[on|off]}[,...]]');
    
    -- 修改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]}[,...]]');

    重要

    Hologres V2.0版本起，针对UPDATE_TABLE_PROPERTY语法进行了优化，运行如下语句时，表的bitmap_columns属性保持不变。Hologres V2.0以前版本则是会清空表的bitmap_columns属性。

    CALL UPDATE_TABLE_PROPERTY('<table_name>','bitmap_columns','');

  • 参数说明

    参数	说明
    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. 单击右上角的提交，完成表修改。