title: Hive delete duplicate records
body: <p>In hive, how can I delete duplicate records ? Below is my case,</p>
  
  <p>First, I load data from product table to products_rcfileformat. There are 25 rows of records on product table</p>
  
  <pre><code>FROM products INSERT OVERWRITE TABLE products_rcfileformat         
  SELECT *;
  </code></pre>
  
  <p>Second, I load data from product table to products_rcfileformat. There are 25 rows of records on product table. But this time I'm NOT using OVERWRITE clause</p>
  
  <pre><code>FROM products INSERT INTO TABLE products_rcfileformat         
  SELECT *;
  </code></pre>
  
  <p>When I query the data it give me total rows = 50 which are right</p>
  
  <p><a href="https://i.stack.imgur.com/wmqQP.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/wmqQP.jpg" alt="enter image description here"></a></p>
  
  <p>Check from hdfs, it seem hdfs make another copy of file xxx_copy_1 instead of append to 000000_0</p>
  
  <p><a href="https://i.stack.imgur.com/HyRdN.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/HyRdN.jpg" alt="enter image description here"></a></p>
  
  <p>Now I want to remove those records that read from xxx_copy_1. How can I achieve this in hive command ? If I'm not mistaken, i can remove xxx_copy_1 file by using hdfs dfs -rm command follow by rerun insert overwrite command. But I want to know whether this can it be done by using hive command example like delete statement?  </p>