how to add columns to existing hive external table?

Question

create external table demotable(
column1 string,
column2 string,
column3 string)
row format delimited fields terminated by '|' 
location '/data/demotable';

I create external table 'demotable' and the data in '/data/demotable' is like

aaa|bbb|ccc
ddd|eee|fff
www|ttt|uuu
...
yyy|uuu|kkk

Now I want to add two more columns in my data and it is going to be like

aaa|bbb|ccc
ddd|eee|fff
www|ttt|uuu
...
yyy|uuu|kkk|ppp|lll
vvv|mmm|zzz|ttt|hhh

Is there any way to :

1.add new columns in my table(for new data)

2.keep the old data(just mark the last two columns as 'NULL') ?

Arsine

Possible duplicate of Add a column in a table in HIVE QL – Affairs

@Affairs They are not the same clearly. – Arsine

Answer 1

You can new columns to HIVE external table using the below command:

ALTER TABLE demotable ADD columns ( column4 string, column5 string );

Tinkle

Answer 2

Since it's an external table, you can just drop the table and recreate with additional columns placed at the end. Dropping external table, doesn't ideally remove the files. When you query the table, output will be NULL for those rows where those columns doesn't have any data.

Snath