title: Incremental data load to hive table
body: <p>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.</p>
  
  <p>I am using Hive 0.14.</p>
  
  <p>I tried the below queries but could not make it work.</p>
  
  <pre><code>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 &gt; T.FIELD_TIMESTAMP;
  </code></pre>
  
  <p>The above code is taking hours together without giving any result.</p>
  
  <p>I also tried to execute the below query and later found that HIVE does not support subqueries in WHERE clause. (got ParseException)</p>
  
  <pre><code>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 &gt; (Select max(FIELD_TIMESTAMP) from TARGET);
  </code></pre>
  
  <p>Please help me out in selecting only the rows that have been added after my initial load.</p>
  
  <p>Thank you.</p>