Alter table statement in Hive
The Alter table statement is used to change the structure of the existing table in Hive. It allows us to change the column’s name, data type, comment or position of the column.
Rename a column in Hive
ALTER TABLE <Table_Name> CHANGE COLUMN
<Old_Column_Name> <New_Column_Name> <Column_Data_Type>
[COMMENT col_comment] [FIRST|AFTER column_name] ;
As mentioned in the syntax, we need to give the table name, old column name, new column name and its data type in the Alter table statement. Along with this, we can give the column comments and position of the new column in the table.
Here FIRST and AFTER column_name denotes the position of the new column. To add the new column in the first position, we can use the FIRST in the Alter statement. If we want to add the new column after some existing column, we need to use the AFTER <existing_column_name>. Both Comment and Position attributes are optional in the Alter statement.
Example for Rename a column in Hive
Consider that we have a table Course_Details in Hive. It has the columns Course_Id, Title, Price_in_Usd and Last_Updated_Date.
We want to change the column name from Last_Updated_Date to Date_Updated in the table Course_Details. Let’s write the Alter statement to rename the column name in Hive. There is no change in the column data type. So we mentioned the data type of the new column as Date.
ALTER TABLE Course_Details CHANGE COLUMN
Last_Updated_Date Date_Updated Date;
As we shown above, the Alter statement is executed successfully. Now we can verify the column names and data types using Describe command.
As we expected, the column last_updated_date is renamed to date_updated in Hive table. We didn’t mention any comments in the Alter statement. So the comment field is empty in the Describe output.
Let’s try to rename the column Price_in_Usd to Price with comments.
After executing the Alter statement with comments, the column renamed from price_in_usd to price. Also the given comments showing in the Describe output. Since the column names are case insensitive in Hive, it showing all the column names in lower case.
Please note that Alter statement changed only the metadata of the table. The actual data of the table is still remain the same.
- How to rename a table in Hive?
- Hive describe command to check the meta data of the Hive table
- Copy the data or table structure from one table to another in Hive