title: Hive - how to reuse a sub-query in hive with optimal performance
body: <p>What is the best way to structure/write a query in Hive when I have a complex sub-query that is repeated multiple times throughout the select statement?</p>
  
  <p>I originally created a temporary table for the sub-query which was refreshed before each run. Then I began to use a CTE as part of the original query (discarding the temp table) for readability and noticed degraded performance. This made me curious about which implementation methods are best with respect to performance when needing to reuse sub-queries.</p>
  
  <p>The data I am working with contains upwards of 10 million records. Below is an example of the query I wrote that made use of a CTE.</p>
  
  <pre><code>with temp as (
     select
        a.id,
        x.type,
        y.response
     from sandbox.tbl_form a
     left outer join sandbox.tbl_formStatus b
     on a.id = b.id
     left outer join sandbox.tbl_formResponse y
     on b.id = y.id
     left outer join sandbox.tbl_formType x
     on y.id = x.typeId
     where b.status = 'Completed'
  )
  select
     a.id,
     q.response as user,
     r.response as system,
     s.response as agent,
     t.response as owner
  from sandbox.tbl_form a
  left outer join (
     select * from temp x
     where x.type= 'User'
  ) q
  on a.id = q.id
  left outer join (
     select * from temp x
     where x.type= 'System'
  ) r
  on a.id = r.id
  left outer join (
     select * from temp x
     where x.type= 'Agent'
  ) s
  on a.id = s.id
  left outer join (
     select * from temp x
     where x.type= 'Owner'
  ) t
  on a.id = t.id;
  </code></pre>