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以下版本，請您使用自助升級或加入HologresDingTalk交流群反饋，詳情請參見如何擷取更多的線上支援？。

  • 若您的表是分區表，僅能刪除分區父表的列，無法直接刪除分區子表的列。對於分區子表，在父表的列刪除後自動刪除。此操作開銷較高，建議在業務低峰期執行。

  • 僅只有表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. 單擊右上方的提交，完成表修改。