Though it is uncommon, we do sometimes change the column names when the situation calls for it. Apache Hive allows us to change the column names similar to many RDBMSs. Let’s see how the user-defined and system-generated column names can be modified.
If the user-defined column name:
Syntax:
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
Example:
Table Creation:
CREATE TABLE TestTable (Col1 INT, Col2 int, Col3 int);
Renaming a column:
ALTER TABLE TestTable CHANGE Col3 QtyCol INT;
In the above example, we modified Col3 to QtyCol.
If the system-defined column name:
The syntax and method are the same as in the above example. The only difference is that backticks must be used to surround the column. Take a look at the sample below.
ALTER TABLE TestTable CHANGE _c0 QtyCol STRING;
This will return the below error:
Error while compiling statement: FAILED: ParseException line 1:71 cannot recognize input near ‘_c0’ ‘QtyCol’ ‘STRING’ in rename column name.
The column must be enclosed with backticks as shown below:
ALTER TABLE TestTable CHANGE `_c0` QtyCol STRING;
Hope you find this article helpful.
Please subscribe for more interesting updates.
Big Data and SQL 