Incremental data load to hive table

Question

I am trying to load incremental data from one hive external table to another hive table. I have a date timestamp field on the source table to identify the newly added rows to it on a daily basis. My task is to extract the rows that are newly added to the source and insert them into the target table.

I am using Hive 0.14.

I tried the below queries but could not make it work.

INSERT INTO TABLE TARGET PARTITION (FIELD_DATE)
SELECT A.FIELD1, A.FIELD2, A.FIELD3,
CASE WHEN LENGTH(A.FIELD4)=0 THEN 0 ELSE 1 END,
CASE WHEN LENGTH(A.FIELD5)=0 THEN 0 ELSE 1 END,
FROM SOURCE A, (Select max(FIELD_TIMESTAMP) from TARGET) T
where A.FIELD_TIMESTAMP > T.FIELD_TIMESTAMP;

The above code is taking hours together without giving any result.

I also tried to execute the below query and later found that HIVE does not support subqueries in WHERE clause. (got ParseException)

INSERT INTO TABLE TARGET PARTITION (FIELD_DATE)
SELECT A.FIELD1, A.FIELD2, A.FIELD3,
CASE WHEN LENGTH(A.FIELD4)=0 THEN 0 ELSE 1 END,
CASE WHEN LENGTH(A.FIELD5)=0 THEN 0 ELSE 1 END,
FROM SOURCE A, TARGET T
where A.FIELD_TIMESTAMP > (Select max(FIELD_TIMESTAMP) from TARGET);

Please help me out in selecting only the rows that have been added after my initial load.

Thank you.

Durga Viswanath Gadiraju Durga Viswanath Gadiraju · Accepted Answer · 2015-12-21T13:30:37

Try this

INSERT INTO TABLE TARGET PARTITION (FIELD_DATE)
SELECT A.FIELD1, A.FIELD2, A.FIELD3,
CASE WHEN LENGTH(A.FIELD4)=0 THEN 0 ELSE 1 END,
CASE WHEN LENGTH(A.FIELD5)=0 THEN 0 ELSE 1 END,
FROM SOURCE A JOIN 
(Select max(FIELD_TIMESTAMP) as FIELD_TIMESTAMP from TARGET) T
on 1=1
where A.FIELD_TIMESTAMP > T.FIELD_TIMESTAMP;

This is the tested query for your reference:

insert into orders_target
select o.* from orders_source o join
(select max(o1.order_date) order_date from orders_target o1) o2
on 1=1
where o.order_date > o2.order_date;

Incremental data load to hive table

1 Answers