Trafodion之修改表结构(Alter Table)

葛骏

2023-12-01

Trafodion支持通过"ALTER TABLE"语法来修改表结构，如修改表名、添加/删除字段、修改字段等。关于"ALER TABLE"的详细语法，读者可参考Apache Trafodion官网之http://trafodion.apache.org/docs/sql_reference/index.html#alter_table_statement。

修改表结构/属性是我们经常会遇到的场景，不过从实现方式上面，由于Trafodion本身是底层基于HBase，所以实现方式上面会略有不同。下面我们使用几个最常用的修改表的结构作为示例介绍相关语法及实现方式。

1 修改表名

语法

alter table table_name rename to new_name;

首先，创建测试表并插入测试数据，

>>create table test_alter(a int, b varchar(10));

--- SQL operation complete.
>>insert into test_alter values(1,'A'),(2,'B'),(3,'C');

--- 3 row(s) inserted.

查看表对应的HDFS目录，

hadoop fs -ls /hbase/data/TRAF_RSRVD_3/TRAFODION.SEABASE.TEST_ALTER
Found 3 items
drwxr-xr-x   - hbase hbase          0 2018-06-04 10:37 /hbase/data/TRAF_RSRVD_3/TRAFODION.SEABASE.TEST_ALTER/.tabledesc
drwxr-xr-x   - hbase hbase          0 2018-06-04 10:37 /hbase/data/TRAF_RSRVD_3/TRAFODION.SEABASE.TEST_ALTER/.tmp
drwxr-xr-x   - hbase hbase          0 2018-06-04 10:37 /hbase/data/TRAF_RSRVD_3/TRAFODION.SEABASE.TEST_ALTER/6f2f75d56f961f7f1b90ea7dcf0e91bf

修改表名为test_alter2，此时test_alter表不存在，可以查询test_alter2，

>>alter table test_alter rename to test_alter2;

--- SQL operation complete.
>>select * from test_alter;

*** ERROR[4082] Object TRAFODION.SEABASE.TEST_ALTER does not exist or is inaccessible.

*** ERROR[8822] The statement was not prepared.

>>select * from test_alter2;

A            B                                       
-----------  ----------------------------------------

          1  A                                       
          2  B                                       
          3  C                                       

--- 3 row(s) selected.

再次查询底层表对应的目录，通过以下结果，我们发现原来的TEST_ALTER目录被移动到/hbase/archive/data路径下，新创建TEST_ALTER2目录在/hbase/data路径，TEST_ALTER2有索引文件指向TEST_ALTER目录。

hadoop fs -ls /hbase/data/TRAF_RSRVD_3/TRAFODION.SEABASE.TEST_ALTER*
Found 3 items
drwxr-xr-x   - hbase hbase          0 2018-06-04 11:01 /hbase/data/TRAF_RSRVD_3/TRAFODION.SEABASE.TEST_ALTER2/.tabledesc
drwxr-xr-x   - hbase hbase          0 2018-06-04 11:01 /hbase/data/TRAF_RSRVD_3/TRAFODION.SEABASE.TEST_ALTER2/.tmp
drwxr-xr-x   - hbase hbase          0 2018-06-04 11:01 /hbase/data/TRAF_RSRVD_3/TRAFODION.SEABASE.TEST_ALTER2/5315b268191286a5e2bdae45cebb5a51

hadoop fs -ls /hbase/archive/data/TRAF_RSRVD_3/TRAFODION.SEABASE.TEST_ALTER
Found 1 items
drwxr-xr-x+  - hbase hbase          0 2018-06-04 11:01 /hbase/archive/data/TRAF_RSRVD_3/TRAFODION.SEABASE.TEST_ALTER/6f2f75d56f961f7f1b90ea7dcf0e91bf

2 添加新列

语法

alter table table_name add [column] column_name date_type;

以下添加两个字段，其中一个包含非空约束及默认值，

>>alter table test_alter2 add c timestamp;

--- SQL operation complete.
>>alter table test_alter2 add d date not null default sysdate;

--- SQL operation complete.

3 删除列

语法

alter table table_name drop [column] column_name;

以下删除两个字段，

SQL>alter table test_alter2 drop column c;

--- SQL operation complete.
SQL>alter table test_alter2 drop column d;

--- SQL operation complete.

4 修改列类型

语法

alter table table_name alter column column_name [set data type] new_datatype;

修改int类型为varchar类型，修改报错，

SQL>alter table test_alter2 add column c int default 1;

--- SQL operation complete.

SQL>alter table test_alter2 alter column c varchar(10);

*** ERROR[1404] Column C cannot be altered. Reason: Old and New datatypes must be compatible. [2018-06-04 11:51:25]

修改int类型为bigint类型，修改成功，

SQL>alter table test_alter2 alter column c bigint;

--- SQL operation complete.
SQL>showddl test_alter2;

 
CREATE TABLE TRAFODION.SEABASE.TEST_ALTER2
  ( 
    A                                INT DEFAULT NULL NOT SERIALIZED
  , B                                VARCHAR(10 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
  , C                                LARGEINT DEFAULT NULL NOT SERIALIZED
      /*altered_col*/
  )
 ATTRIBUTES ALIGNED FORMAT NAMESPACE 'TRAF_RSRVD_3' 
;
 
-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.TEST_ALTER2 TO DB__ROOT WITH GRANT OPTION;

--- SQL operation complete.

注：新版本中也支持以下ANSI语法，

alter table table_name alter column column_name [set data type] new_datatype;

5 修改列字符编码为UTF8

语法同上，修改默认字符类型(ISO88591)为UTF8类型，

SQL>alter table test_alter2 add d varchar(10) character set iso88591 default 'ABC';

--- SQL operation complete.
SQL>alter table test_alter2 alter column d varchar(10) character set utf8;

--- SQL operation complete.
SQL>showddl test_alter2;

 
CREATE TABLE TRAFODION.SEABASE.TEST_ALTER2
  ( 
    A                                INT DEFAULT NULL NOT SERIALIZED
  , B                                VARCHAR(10 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
  , C                                LARGEINT DEFAULT NULL NOT SERIALIZED
      /*altered_col*/
  , D                                VARCHAR(10 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED /*altered_col*/
  )
 ATTRIBUTES ALIGNED FORMAT NAMESPACE 'TRAF_RSRVD_3' 
;
 
-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.TEST_ALTER2 TO DB__ROOT WITH GRANT OPTION;

--- SQL operation complete.

6 修改列名

语法

alter table table_name alter column column_name rename to new_column_name;

如下，

SQL>alter table test_alter2 alter column d rename to d1;

--- SQL operation complete.

7 禁用索引

语法

alter table table_name disable index index_name;

以下示例在表上创建一个索引，使用showddl能够查看到索引，在disable索引后showddl不显示索引。

SQL>create index idx_b on test_alter2(b);

--- SQL operation complete.

CREATE TABLE TRAFODION.SEABASE.TEST_ALTER2
  ( 
    A                                INT DEFAULT NULL NOT SERIALIZED
  , B                                VARCHAR(10 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
  , C                                LARGEINT DEFAULT NULL NOT SERIALIZED
      /*altered_col*/
  , D1                               VARCHAR(10 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED /*altered_col*/
  )
 ATTRIBUTES ALIGNED FORMAT NAMESPACE 'TRAF_RSRVD_3' 
;

CREATE INDEX IDX_B ON TRAFODION.SEABASE.TEST_ALTER2
  ( 
    B ASC
  )
 ATTRIBUTES ALIGNED FORMAT 
;
 
-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.TEST_ALTER2 TO DB__ROOT WITH GRANT OPTION;

--- SQL operation complete.
SQL>alter table test_alter2 disable index idx_b;

--- SQL operation complete.

SQL>showddl test_alter2;

 
CREATE TABLE TRAFODION.SEABASE.TEST_ALTER2
  ( 
    A                                INT DEFAULT NULL NOT SERIALIZED
  , B                                VARCHAR(10 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
  , C                                LARGEINT DEFAULT NULL NOT SERIALIZED
      /*altered_col*/
  , D1                               VARCHAR(10 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED /*altered_col*/
  )
 ATTRIBUTES ALIGNED FORMAT NAMESPACE 'TRAF_RSRVD_3' 
;
 
-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.TEST_ALTER2 TO DB__ROOT WITH GRANT OPTION;

--- SQL operation complete.

8 启用索引

语法

alter table table_name enable index index_name;

以下步骤重新启用索引，

SQL>alter table test_alter2 enable index idx_b;

--- SQL operation complete.

SQL>showddl test_alter2;

 
CREATE TABLE TRAFODION.SEABASE.TEST_ALTER2
  ( 
    A                                INT DEFAULT NULL NOT SERIALIZED
  , B                                VARCHAR(10 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
  , C                                LARGEINT DEFAULT NULL NOT SERIALIZED
      /*altered_col*/
  , D1                               VARCHAR(10 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED /*altered_col*/
  )
 ATTRIBUTES ALIGNED FORMAT NAMESPACE 'TRAF_RSRVD_3' 
;

CREATE INDEX IDX_B ON TRAFODION.SEABASE.TEST_ALTER2
  ( 
    B ASC
  )
 ATTRIBUTES ALIGNED FORMAT 
;
 
-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.TEST_ALTER2 TO DB__ROOT WITH GRANT OPTION;

--- SQL operation complete.

9 添加主键

语法

alter table table_name add primary key (column-list)

以下示例为在一个没有主键的表上添加主键，

SQL>create table test_alter2(a int, b varchar(10));

--- SQL operation complete.

SQL>alter table test_alter2 add primary key (a);

--- SQL operation complete.

SQL>showddl test_alter2;
 
CREATE TABLE TRAFODION.DAAS_GX.TEST_ALTER2
  ( 
    A                                INT DEFAULT NULL NOT NULL NOT DROPPABLE
      NOT SERIALIZED
  , B                                VARCHAR(10) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL NOT SERIALIZED
  , PRIMARY KEY (A ASC)
  )
 ATTRIBUTES ALIGNED FORMAT NAMESPACE 'TRAF_1500000' 
;
 
-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.DAAS_GX.TEST_ALTER2 TO DB__ROOT WITH GRANT OPTION;

--- SQL operation complete.

从以上结果可知，若添加主键的字段是原本没有NOT NULL属性，增加PRIMARY KEY后会在主键字段上添加NOT NULL属性，因为主键默认是非空的。