Altering Tables #

Changing/Adding Table Properties #

The following SQL sets write-buffer-size table property to 256 MB.

ALTER TABLE my_table SET (
    'write-buffer-size' = '256 MB'
);

ALTER TABLE my_table SET TBLPROPERTIES (
    'write-buffer-size' = '256 MB'
);

Rename Table Name #

The following SQL rename the table name to new name.

ALTER TABLE my_table RENAME TO my_table_new;

ALTER TABLE my_table RENAME TO my_table_new;

If you use object storage, such as S3 or OSS, please use this syntax carefully, because the renaming of object storage is not atomic, and only partial files may be moved in case of failure.

Removing Table Properties #

The following SQL removes write-buffer-size table property.

ALTER TABLE my_table RESET ('write-buffer-size');

ALTER TABLE my_table UNSET TBLPROPERTIES ('write-buffer-size');

Adding New Columns #

The following SQL adds two columns c1 and c2 to table my_table.

ALTER TABLE my_table ADD COLUMNS (
    c1 INT,
    c2 STRING
);

Renaming Column Name #

The following SQL renames column c0 in table my_table to c1.

ALTER TABLE my_table RENAME COLUMN c0 TO c1;

Dropping Columns #

The syntax is:

ALTER TABLE table_identifier DROP { COLUMN | COLUMNS } [(] col_name [, ... ] [)]

The following SQL drops tow columns c1 and c2 from table my_table.

ALTER TABLE my_table DROP COLUMNS (c1, c2);

Changing Column Nullability #

The following SQL sets column coupon_info to be nullable.

ALTER TABLE my_table ALTER COLUMN coupon_info DROP NOT NULL;

Changing Column Comment #

The following SQL changes comment of column buy_count to buy count.

ALTER TABLE my_table ALTER COLUMN buy_count COMMENT 'buy count';