Altering Hive table

Altering Hive table

Rename the table:-

              Syntax:- ALTER TABLE OldTableName RENAME TO  NewTableName;

hive> show tables;
OK
employeetab
ny_cause_of_death_new
ny_death_view
reuse_extrnl_tab
Time taken: 0.111 seconds, Fetched: 4 row(s)

hive> alter table employeetab rename to employee;
OK
Time taken: 1.409 seconds

hive> show tables;
OK
employee
ny_cause_of_death_new
ny_death_view
reuse_extrnl_tab
Time taken: 0.142 seconds, Fetched: 4 row(s)

Change the name and data type on a Column:-
           Syntax:- ALTER TABLE TblName CHANGE OldColName NewColName NewDataType;

hive> describe employee;
OK
empid                   string                                      
fullname                string                                      
ssn                     string                                      
department              string                                      
startdate               string                                      
salary                  int                                         
Time taken: 0.342 seconds, Fetched: 6 row(s)

hive> alter table employee change salary earning string;
OK
Time taken: 1.496 seconds

hive> describe employee;
OK
empid                   string                                      
fullname                string                                      
ssn                     string                                      
department              string                                      
startdate               string                                      
earning                 string                                      
Time taken: 0.26 seconds, Fetched: 6 row(s)

Add Columns to existing Hive table:-
            Syntax:- ALTER TABLE TblName ADD COLUMNS(Col1 DataType,Col2 DataType);

hive (Hivedb)> desc employee;
OK
empid                   string                                      
fullname                string                                      
ssn                     string                                      
department              string                                      
startdate               string                                      
earning                 string                                      
Time taken: 0.175 seconds, Fetched: 6 row(s)

hive (Hivedb)> alter table employee add columns(location string);
OK
Time taken: 0.311 seconds

hive (Hivedb)> desc employee;
OK
empid                   string                                      
fullname                string                                      
ssn                     string                                      
department              string                                      
startdate               string                                      
earning                 string                                      
location                string                                      
Time taken: 0.175 seconds, Fetched: 7 row(s)

If we check the records of the employee table, then we will get NULL records on location column.

hive (Hivedb)> select * from employee limit 10;
OK
EMP001    Faith K. Macias    845-04-3962    Marketing    27-01-2008    73500    NULL
EMP002    Lucian Q. Franklin    345-28-4935    IT/IS    01-03-2008    80000    NULL
EMP003    Blaze V. Bridges    503-53-8350    Marketing    16-04-2008    95000    NULL
EMP004    Denton Q. Dale    858-39-7967    Marketing    03-05-2008    105000    NULL
EMP005    Blossom K. Fox    245-18-5890    Engineering    11-07-2008    90000    NULL
EMP006    Kerry V. David    873-45-8675    Finance    17-07-2008    60000    NULL
EMP007    Melanie X. Baker    190-08-3679    Finance    05-10-2008    87000    NULL
EMP008    Adele M. Fulton    352-36-9553    Engineering    28-10-2008    104000    NULL
EMP009    Justina O. Jensen    645-74-0451    Marketing    05-11-2008    380050    NULL
EMP010    Yoshi J. England    558-53-1475    Marketing    09-12-2008    93000    NULL
Time taken: 0.306 seconds, Fetched: 10 row(s)

We cannot delete/drop the column directly. For that, we have to use REPLACE keyword. We can also change the Names and datatypes of all the columns using REPLACE keyword.
        Syntax:- ALTER TABLE TblName REPLACE COLUMNS (Col1 DataType,Col2 DataType)

hive (Hivedb)> alter table employee replace columns(eid string,name string,ssn string,depart string,startdate string,earning string);
OK
Time taken: 0.303 seconds

hive (Hivedb)> desc employee;
OK
eid                     string                                      
name                    string                                      
ssn                     string                                      
depart                  string                                      
startdate               string                                      
earning                 string                                      
Time taken: 0.077 seconds, Fetched: 6 row(s)

hive (Hivedb)> select * from employee limit 10;
OK
EMP001    Faith K. Macias    845-04-3962    Marketing    27-01-2008    73500
EMP002    Lucian Q. Franklin    345-28-4935    IT/IS    01-03-2008    80000
EMP003    Blaze V. Bridges    503-53-8350    Marketing    16-04-2008    95000
EMP004    Denton Q. Dale    858-39-7967    Marketing    03-05-2008    105000
EMP005    Blossom K. Fox    245-18-5890    Engineering    11-07-2008    90000
EMP006    Kerry V. David    873-45-8675    Finance    17-07-2008    60000
EMP007    Melanie X. Baker    190-08-3679    Finance    05-10-2008    87000
EMP008    Adele M. Fulton    352-36-9553    Engineering    28-10-2008    104000
EMP009    Justina O. Jensen    645-74-0451    Marketing    05-11-2008    380050
EMP010    Yoshi J. England    558-53-1475    Marketing    09-12-2008    93000
Time taken: 0.331 seconds, Fetched: 10 row(s)