Hive - how to reuse a sub-query in hive with optimal performance

Question

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?

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.

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.

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;

user_2700344 · Answer

There are issues in your query.

1) In the CTE you have three left joins without ON clause. This may cause serious performance problems because joins without ON clause are CROSS JOINS.

2) BTW where b.status = 'Completed' clause converts LEFT join with table b to the inner join though still without ON clause it multiplicates all records from a by all records from b with a where.

3) Most probably you do not need CTE at all. Just join correctly with ON clause and use case when type='User' then response end + aggregate using min() or max() by id:

select a.id
max(case when x.type='User' then y.response end) as user,
max(case when x.type='System' then y.response end) as system,
...

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' --if you want LEFT JOIN add --or b.status is null
group by a.id

Hive - how to reuse a sub-query in hive with optimal performance

1 Answers