HCatalog Alter Table

The ALTER TABLE statement is used to change the properties of an existing table and can also modify its structure. This process updates the table's information in the metastore database shared with HCatalog, but it does not change the actual data files. Any needed changes to the files must be done manually.

Syntax -

ALTER TABLE [old_db_name.]old_table_name 
 RENAME TO [new_db_name.]new_table_name;

ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...]);

ALTER TABLE name DROP [COLUMN] column_name;

ALTER TABLE name CHANGE column_name new_name new_type;

ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec ...]);

ALTER TABLE name { ADD | DROP } PARTITION (partition_spec);

ALTER TABLE name [PARTITION (partition_spec)]
  SET { FILEFORMAT PARQUET | TEXTFILE | RCFILE
   | SEQUENCEFILE | AVRO
  | LOCATION 'hdfs_path_of_directory'
  | TBLPROPERTIES (table_properties)
  | SERDEPROPERTIES (serde_properties) };

ALTER TABLE name [PARTITION (partition_spec)] 
SET { CACHED IN 'pool_name' | UNCACHED };

The below are the detailed options in the above syntax -

• col_spec ::= col_name type_name
• partition_spec ::= partition_col=constant_value
• table_properties ::= 'name'='value'[, 'name'='value' ...]
• serde_properties ::= 'name'='value'[, 'name'='value' ...]

Let’s discuss about each ALTER Statement use in detail with example.

Example -

Assume we have a requirement to create a student information system with the following columns: student number, name, class, and grade for a school.

Std No	Std Name	Std Class	Std Grade
1	Pawan	10	A
2	Srinivas	10	A
3	Sridhar	10	A
4	Kumar	10	B

CREATE TABLE statement is as follows -

CREATE TABLE std_details (std_no INT, std_name
 STRING, std_class INT, std_grade STRING)
COMMENT ‘Student Information’
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘\t’
LINES TERMINATED BY ‘\n’
STORED AS TEXTFILE;

-- Move table from one database to another.

ALTER TABLE std_db1.std_details
 RENAME TO std_db2.std_details;

-- Switch a table from internal to external.

ALTER TABLE std_details SET TBLPROPERTIES('EXTERNAL'='TRUE');

-- Each ADD PARTITION clause creates a subdirectory in HDFS.

ALTER TABLE std_details ADD PARTITION
 (month=1, day=1);

-- Alter table to a specific different directory.

ALTER TABLE std_details SET LOCATION 
'/usr/data/new_db2;

-- Alter table to a comment in TBLPROPERTIES.

ALTER TABLE std_details SET TBLPROPERTIES
 ("COMMENT"=’Student information’);

-- Alter table to a delimiter in SERDEPROPERTIES.

ALTER TABLE std_details SET SERDEPROPERTIES 
('field.delim' = ',');

-- Alter table to add column std_address.

ALTER TABLE std_details ADD COLUMNS 
 (std_address STRING);

-- Alter table to replace the column type from INT to STRING.

ALTER TABLE std_details REPLACE COLUMNS
 (std_zip INT std_zip STRING);

-- Alter table to change the column name from c4 to col4.

ALTER TABLE std_details CHANGE std_zip std_zipcode INT;

-- Alter table to drop the column col4.

ALTER TABLE std_details DROP std_zipcode;

---