The ALTER TABLE statement changes properties of an existing table. Sometimes ALTER TABLE statement changes the structure as well. ALTER TABLE is a logical operation that updates the table metadata in the metastore database that shares with Hive. ALTER TABLE does not actually rewrite, move, etc, on the actual data files.

The file operations need to perform manually corresponding physical filesystem operations. The operations that needs to perform manually includes moving data files to a different HDFS directory, rewriting the data files to include extra fields, converting them to a different file format and etc,.

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.

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

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;