title: Hive query generating multiple small file
body: <p>I have 2 hive table as source. Say</p>
  
  <ul>
  <li>DEV.INPUT_01</li>
  <li>DEV.INPUT_02</li>
  </ul>
  
  <p>I have 1 more table as DEV.TARGET. I want to load data into this table for above 2 input tables. The HQL which I have is:</p>
  
  <pre><code>insert overwrite table DEV.TARGET partition(c30)
  select
     c1
    ,c2
    ,c3
    ,c4
    ,c5
    ,c6
    ,c7
    ,c8
    ,c9
    ,c10
    ,c11
    ,c12
    ,c13
    ,c14
    ,c15
    ,c16
    ,c17
    ,c18
    ,c19
    ,c20
    ,c21
    ,c22
    ,c23
    ,c24
    ,c25
    ,c26
    ,c27
    ,c28
    ,c29
    ,c30
  from
    DEV.SOURCE_01 t1 left join
    DEV.SOURCE_02 t2 on
    t1.tab_id = t2.tab_id;
  </code></pre>
  
  <p>The query is working fine. Number of mapper are 700 and reducers are 400. </p>
  
  <p>The problem is above query is generating 400 files per partition and size of every file is around 200K.</p>
  
  <p>I have tried multiple parameter combinations:</p>
  
  <p><strong>Setting 1</strong>:</p>
  
  <pre><code>set hive.exec.reducers.bytes.per.reducer=256000000;
  </code></pre>
  
  <p><strong>Result 1</strong> Number of reducers decreased to 100 and hence 100 files generated per partition.</p>
  
  <p><strong>Setting 2</strong></p>
  
  <pre><code>set hive.merge.mapredfiles=true;
  set hive.merge.size.per.task=256000000;
  set hive.merge.smallfiles.avgsize=256000000;
  </code></pre>
  
  <p><strong>Result 2</strong> Above setting launched 2 MR steps and result is same.</p>
  
  <p><strong>Setting 3</strong></p>
  
  <pre><code>set mapred.reduce.tasks=40;
  </code></pre>
  
  <p><strong>Result 3</strong> </p>
  
  <ul>
  <li>Number of files are reduced to 40 (which is expected)</li>
  <li>Query performance degraded by 3 folds (original query to 20 mins and with this setting it took 55 mins). </li>
  <li>The another problem is data size with this setting. As data grows this setting start degrading more and hence will be tough to manage.</li>
  </ul>
  
  <p><strong>Question</strong> How can I generate files of size 128M?</p>