CODEWITHSUNDEEP
hadoophive
Charith Ellepola
Charith Ellepola

Reputation: 302

Deleting rows with specific timekey in hive table

enter image description hereI have a table with entries of specific time key .Every month table should update with results from previous three months . Records pervious to that should be deleted .the scrip automatically run every month .Is there a way to achieve this in hive ?

Upvotes: 0

Views: 1310

Answers (2)

Ajay Kharade
Ajay Kharade

Reputation: 1525

As far as delete updates are concern Hive will not support such operations, but with new hive version transactions are supported. But you need to create transaction hive table for that.

Below is syntax you can have while creating this transaction table,

CREATE TABLE hello_acid (key int, value int)
PARTITIONED BY (load_date date)
CLUSTERED BY(key) INTO 3 BUCKETS
STORED AS ORC TBLPROPERTIES ('transactional'='true');

You can refer below link for more details, https://hortonworks.com/tutorial/using-hive-acid-transactions-to-insert-update-and-delete-data/

Hope this will help.

Upvotes: 0

Vijiy
Vijiy

Reputation: 1197

if you want to do it via query.

insert overwrite table <tablename>
select 
col1,
col2,
....
from tablename where timekey >(select from_unixtime(unix_timestamp(add_months(current_date(),-3), 'yyyy-MM-dd'), 'yyyyMM'));

above query will read the table data and insert only last 3 months records

Upvotes: 1

Related Questions