title: Hive SQL coding style: intermediate tables?
body: <p>Should I be creating and dropping intermediate tables in hive?</p>
  
  <p>I can write something like (much simplified):</p>
  
  <pre><code>drop table if exists tmp1;
  create table tmp1 as
  select a, b, c
  from input1
  where a &gt; 1 and b &lt; 3;
  
  drop table if exists tmp2;
  create table tmp2 as
  select x, y, z
  from input2
  where x &lt; 6;
  
  drop table if exists output;
  create table output as
  select x, a, count(*) as count
  from tmp1 join tmp2 on tmp1.c = tmp2.z
  group by tmp1.b;
  drop table tmp1;
  drop table tmp2;
  </code></pre>
  
  <p>or I can roll everything into one statement:</p>
  
  <pre><code>drop table if exists output;
  create table output as
  select x, a, count(*) as count
  from (select a, b, c
      from input1
      where a &gt; 1 and b &lt; 3) t1
  join (select x, y, z
      from input2
      where x &lt; 6) t2
  on t1.c = t2.z
  group by t1.b;
  </code></pre>
  
  <p>Obviously, if I reuse the intermediate tables more than once, it makes perfect sense to create them.
  However, when they are used just once, I have a choice.</p>
  
  <p>I tried both and the second one is <strong>6%</strong> <em>faster</em> as measured by the wall time, but <strong>4%</strong> <em>slower</em> as measured by the <code>MapReduce Total cumulative CPU time</code> log output.
  This difference is probably within the random margin of error (caused by other processes &amp;c).
  However, is it possible that combining queries could result in a dramatic speedup?</p>
  
  <p>Another question is: are intermediate tables, which are used just once, a normal occurrence in hive code, or should they be avoided when possible?</p>