title: To overwrite hive table with an updated file
body: <p>I have a CSv file:</p>
  
  <pre><code>Name,Age,City,Country
  
  SACHIN,44,PUNE,INDIA
  TENDULKAR,45,MUMBAI,INDIA
  SOURAV,45,NEW YORK,USA
  GANGULY,45,CHICAGO,USA
  </code></pre>
  
  <p>I created a HIVE table and loaded the data into it.</p>
  
  <p>I found that the above file is wrong and corrected file is below:</p>
  
  <pre><code>Name,Age,City,Country
  
  SACHIN,44,PUNE,INDIA
  TENDULKAR,45,MUMBAI,INDIA
  SOURAV,45,NEW JERSEY,USA
  GANGULY,45,CHICAGO,USA
  </code></pre>
  
  <p>I need to update my main table with correct file.</p>
  
  <p>I have tried below approaches.</p>
  
  <p>1- Created the main table as a partitioned table on City and dynamically loaded the first file.</p>
  
  <p>Step1- Creating a temp table and loading the old.csv file as it is without partitioning. This step I am doing to insert data in main table dyn dynamically by not creating separate input files per partition.</p>
  
  <pre><code>create table temp(
  name string,
  age int,
  city string,
  country string)
  row format delimited
  fields terminated by ','
  stored as textfile;
  </code></pre>
  
  <p>Step2- Loaded old file into temporary table.</p>
  
  <p><code>load data local inpath '/home/test_data/old.csv' into table temp;</code></p>
  
  <p>Step3- Creating the main partitioned table. </p>
  
  <pre><code>create table dyn(
  name string,
  age int)
  partitioned by(city string,country string)
  row format delimited
  fields terminated by ','
  stored as textfile;
  </code></pre>
  
  <p>Step4- Inserting dynamically the old.csv file into the partitioned table from temporary table. </p>
  
  <pre><code>insert into table dyn
  partition(city,country)
  select name,age,city,country from temp;
  </code></pre>
  
  <p>Old recorded dynamically inserted into main table. In the next steps I am trying to correct the main table <code>dyn</code> with old.csv to new.csv</p>
  
  <p>Step5- Creating another temporary table with new and correct input file.</p>
  
  <pre><code>create table temp1(
  name string,
  age int,
  city string,
  country string)
  row format delimited
  fields terminated by ','
  stored as textfile;
  </code></pre>
  
  <p>Step6- Loading the new and correct input file into second temp table which will then be used to overwrite the main table but only the row whose data was wrong in old.csv. That is for <code>SOURAV,45,NEW YORK,USA</code> to <code>SOURAV,45,NEW JERSEY,USA</code>.</p>
  
  <p><code>load data local inpath '/home/test_data/new.csv' into table temp1;</code></p>
  
  <p>Overwriting the main table but only the row whose data was wrong in old.csv. That is for <code>SOURAV,45,NEW YORK,USA</code> to <code>SOURAV,45,NEW JERSEY,USA</code>.</p>
  
  <p>Final overwrite Step7 attempt 1- </p>
  
  <pre><code>insert overwrite table dyn partition(country='USA' , city='NEW YORK') select city,country from temp1 t where t.city='NEW JERSEY' and t.country='USA';
  </code></pre>
  
  <p>Result:- Inserted NUll in Name column.</p>
  
  <pre><code>NEW JERSEY      NULL    NEW YORK        USA
  </code></pre>
  
  <p>Final overwrite Step7 attempt 2- </p>
  
  <pre><code>insert overwrite table dyn partition(country='USA' , city='NEW YORK') select name,age from temp1 t where t.city='NEW JERSEY' and t.country='USA';
  </code></pre>
  
  <p>Result:- <code>No change in dyn table. Same as before. NEW YORk did not update to NEW JERSEY</code></p>
  
  <p>Final overwrite Step7 attempt3 - </p>
  
  <pre><code>insert overwrite table dyn partition(country='USA' , city='NEW YORK') select * from temp1 t where t.city='NEW JERSEY' and t.country='USA';
  </code></pre>
  
  <p>Error:- <code>FAILED: SemanticException [Error 10044]: Line 1:23 Cannot Insert into target table because column number/types are different. Table insclause-0 has 2 columns,but query has 4 columns</code></p>
  
  <p>What is the correct approach for this problem.</p>