Hive

9 / 18

Hive - DDL - Alter Table




Not able to play video? Try with vimeo

Let's make changes in the table definition. Log into CloudxLab linux console type hive and wait for hive prompt to appear. Use your database. Run use followed by the database name here the string ${env:USER} is replaced by your username automatically. This would work if your username is same as the name of your database. To rename a table x to x1 type alter table x rename to x1 followed by semicolon and press Enter table x is renamed to x1. You can run show tables followed by semicolon to verify if the table has been created. To change the data type of the column a in table x1 to float we can simply type alter table x1 change a a float; and press Enter. The data type of column a is changed to float. You can run describe x1; to verify. To add column b with int data type and column c with float data type use alter table x1 add columns (b int, c float); and press Enter now table x1 has two columns b and c. You can run describe x1; to verify whether these columns have been added or not.

INSTRUCTIONS

Steps:

  • Login to the web console
  • Launch Hive with typing in hive on the console. Run below commands in Hive.
  • Use your database by using the below command. ${env:USER} gets replaced by your username automatically:

    use ${env:USER};
    
  • To rename a table from x to x1

    ALTER TABLE x RENAME TO x1;
    
  • To change the data type of a column

    ALTER TABLE x1 CHANGE a a FLOAT;
    
  • To add columns to an existing table

    ALTER TABLE x1 ADD COLUMNS (b FLOAT, c INT);
    

Loading comments...