Altering Tables

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'
);

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';