CODEWITHSUNDEEP
hadoopapache-sparkhivehiveql
ravi theja
ravi theja

Reputation: 43

Adding a column using HIVE

I have following data table.

ID salary occupation

1  5000   Engineer       
2  6000   Doctor         
3  8000   Pilot          
4  1000   Army           
1  3000   Engineer       
2  4000   Teacher        
3  2000   Engineer       
1  1000   Teacher        
3  1000   Engineer       
1  5000   Doctor

Now I want to add another column flag to this table so that it looks in the following way.

ID salary occupation Flag

1  5000   Engineer   0    
2  6000   Doctor     0    
3  8000   Pilot      0    
4  1000   Army       0    
1  3000   Engineer   1    
2  4000   Teacher    1    
3  2000   Engineer   1    
1  1000   Teacher    2    
3  1000   Engineer   2    
1  5000   Doctor     3

Now how can I update my original table to the above format using HIVE?

Kindly help me.

Upvotes: 2

Views: 6092

Answers (2)

Nishu Tayal
Nishu Tayal

Reputation: 20830

You can definitely add new columns in HIVE table using alter command as told above

hive>Alter table Test ADD COLUMNS (flag TINYINT);

In Hive 0.13 and earlier releases, column will have NULL values but HIVE 0.14.0 and later release, you can update the column values using UPDATE command

Another way is, after adding column using ALTER command, you can overwrite the existing data with the new data(having Flag column)

 hive> LOAD DATA LOCAL INPATH 'flagfile.txt' OVERWRITE INTO TABLE <tablename>;

Upvotes: 0

user3032283
user3032283

Reputation: 373

Provided that you have data in your files for the additional column you can use Add Column clause for Alter Table. In your example do something like this:

Alter table Test ADD COLUMNS (flag TINYINT);

Or you can try REPLACE COLUMNS as well:

Alter Table test REPLACE COLUMNS (id int, salary int, occupation String, flag tinyint);

You might need to load(overwrite) your dataset again though(just a speculation!!!).

Upvotes: 0

Related Questions