How to Update the Hive tables without deleting the existing data?


I have some data in Hive table (XYZ) which are using for chart visualisations.
Now I have to append the data into XYZ table with out deleting the existing data.

How can i achieve this?

Appreciate your Help.


If you want append data from RDBMS using sqoop, then use import using --append and pointing to xyz dir of Hive. e.g. --target-dir /user/hive/warehouse/xyz

Hi N_Chakote,

Thanks for the response.

Actually we are not using sqoop.
I have updated data in ABC table . which schema is same as XYZ table.
Where XYZ table is using for chart visualisation so we need to update XYZ table only from another Hive ABC table.


There is no straight forward way. Either you can use sqoop merge command or have to write your own script which will generate merged data set.

Hi @syamsri1,

Hive transactions are supported by Hive with latest versions, i am not sure whether it suits your requirement. Please refer below link:
DML operations in hive queries

If it is the case that you cannot upgrade Hive, then please prefer one of the solutions mentioned above by Durga sir.

if you have a column in ABC and XYZ table where its unique and incremental then you can consider using
insert into table XYZ select * from ABC where column>“value” and column <=“value”

Thanks for the reply.

Let me explain my requirement in detail :
I have one table “tweets”

CREATE EXTERNAL TABLE tweets (id BIGINT,entities STRUCT<hashtags:ARRAY<STRUCTtext:STRING>>) ROW FORMAT SERDE ‘com.cloudera.hive.serde.JSONSerDe’ LOCATION ‘/user/cloudera/flume/’;

Table is created and data is look in the table :

Now I want to fetch some hashtag count for that we created two hive views and w.r.t to hashtag_word view for visualisation of chart I created a select query which represent hashtag count (chart_hashtag).

create view hashtags as select id as id,entities.hashtags.text as words from tweets;

create view hashtag_word as select id as id,hashtag from hashtags LATERAL VIEW explode(words) w as hashtag;

create table chart_hashtag as select hashtag,COUNT(hashtag) from hashtag_word group by hashtag ;

Unfortunately in HDFS old data is deleted . But some hashtag count results are stored in chart_hashtag table.
Now with out deleting the chart_hashtag table data. I need to update the new data coming from hashtag_word Hive view ?


You have to run INSERT command with out OVERWRITE clause.