Hive Alter Table

The ALTER TABLE command in Hive is used to modify the structure or behavior of an existing table. In Hive, you may want to:

• Add or remove columns
• Rename the table
• Change file formats
• Add or replace table properties
• Move data to a new location

The best part is that ALTER TABLE does not delete your existing data—it only updates the way Hive understands and handles the table.

Why Do We Use ALTER TABLE in Hive?

In real projects, you often realize after some time that:

• A new piece of information needs to be stored (so you need to add a new column).
• The table name should better reflect its purpose (so you rename the table).
• We might want to change how the data is stored or organized (by changing the file format or location).

Instead of creating a new table every time, you can simply alter the existing one using Hive's ALTER TABLE.

Syntax -

Rename a Table:

ALTER TABLE old_table_name 
	RENAME TO new_table_name;

Add a Column to a Table:

ALTER TABLE table_name 
	ADD COLUMNS (column_name data_type);

Replace All Columns in a Table:

ALTER TABLE table_name 
	REPLACE COLUMNS (col1 datatype, col2 datatype, ...);

Change the File Format of a Table:

ALTER TABLE table_name 
	SET FILEFORMAT file_format;

Move Table Data to a New Location:

ALTER TABLE table_name 
	SET LOCATION 'new_hdfs_path';

Add or Update Table Properties:

ALTER TABLE table_name 
	SET TBLPROPERTIES ('property_name' = 'property_value');

Examples -

Assume we have a std_details table with the columns student number, name, class, 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

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

---