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

hive
#1

Hi,

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.

Thanks,
Syam.

0 Likes

#2

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

0 Likes

#3

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.

Thanks,
Syam.

0 Likes

#4

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.

0 Likes

#5

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.

0 Likes

#6

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”

0 Likes

#7

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 ?

Thanks,
Syam.

0 Likes

#8

You have to run INSERT command with out OVERWRITE clause.

0 Likes