Hive - Alter Table
In Hive, we can perform modifications in the existing table like changing the table name, column name, comments, and table properties. It provides SQL like commands to alter the table.
Rename a Table
If we want to change the name of an existing table, we can rename that table by using the following signature: -
Alter table old_table_name rename to new_table_name;
Let's see the existing tables present in the current database.
Hive Alter Table
Now, change the name of the table by using the following command: -
Alter table emp rename to employee_data;
Hive Alter Table
Let's check whether the name has changed or not.
Hive Alter Table
Here, we got the desired output.
Adding column
In Hive, we can add one or more columns in an existing table by using the following signature: -
Alter table table_name add columns(column_name datatype);
Let's see the schema of the table.
Hive Alter Table
Let's see the data of columns exists in the table.
Hive Alter Table
Now, add a new column to the table by using the following command: -
Alter table employee_data add columns (age int);
Hive Alter Table
Let's see the updated schema of the table.
Hive Alter Table
Let's see the updated data of the table.
Hive Alter Table
As we didn't add any data to the new column, hive consider NULL as the value.
Change Column
In Hive, we can rename a column, change its type and position. Here, we are changing the name of the column by using the following signature: -
Alter table table_name change old_column_name new_column_name datatype;
Let's see the existing schema of the table.
Hive Alter Table
Now, change the name of the column by using the following command: -
Alter table employee_data change name first_name string;
Hive Alter Table
Let's check whether the column name has changed or not.
Hive Alter Table
Delete or Replace Column
Hive allows us to delete one or more columns by replacing them with the new columns. Thus, we cannot drop the column directly.
Let's see the existing schema of the table.
Hive Alter Table
Now, drop a column from the table.
alter table employee_data replace columns( id string, first_name string, age int);
Hive Alter Table
Let's check whether the column has dropped or not.
