question Re: Optimize a long running hive query - has a join with same table in Support Questions

Optimize a long running hive query - has a join with same table

sooraj_antony — Mon, 14 Dec 2015 04:23:11 GMT

The below query takes a lot of time to execute. It is run with tez execution engine.

SELECT STG.EMP_TYPE,DEPT,COUNT(DISTINCT EMP_ID) AS COUNT, A.TOTAL_COUNT
FROM STAGE_SOURCE STG 
LEFT OUTER JOIN 
(SELECT EMP_TYPE,COUNT(DISTINCT EMP_ID) AS TOTAL_COUNT FROM STAGE_SOURCE GROUP BY EMP_TYPE) A
ON STG.EMP_TYPE = A.EMP_TYPE
GROUP BY STG.EMP_TYPE,DEPT,A.TOTAL_COUNT;

Is there any rewrite option or optimization strategy which can improve the query performance?

The subquery with alias "A" itself takes 2-3hrs to execute.

Attaching the explain plan of just the join subquery "A"

explain-plan.txt.

Re: Optimize a long running hive query - has a join with same table

nsabharwal — Mon, 14 Dec 2015 08:22:44 GMT

@Sooraj Antony Please attach explain plan. You can copy the plan in text and upload in pdf format.

Re: Optimize a long running hive query - has a join with same table

jpp — Thu, 17 Dec 2015 02:35:46 GMT

The COUNT(DISTINCT) could be the bottleneck if it is not being parallelized. Can you share the explain plan ?

Re: Optimize a long running hive query - has a join with same table

sooraj_antony — Fri, 18 Dec 2015 03:33:26 GMT

@Neeraj Sabharwal @Jean-Philippe Player Explain Plan file is attached..

Re: Optimize a long running hive query - has a join with same table

aervits — Thu, 24 Dec 2015 11:53:31 GMT

some obvious optimizations would be to convert the tables to ORC, you're using text, the tables are pretty large and ORC can help by skipping batches of rows that don't match the criteria. Another thing to look at is table ordering, here's more info https://cwiki.apache.org/confluence/display/Hive/OuterJoinBehavior and https://cwiki.apache.org/confluence/pages/viewpage.action?pageId=34015666

Re: Optimize a long running hive query - has a join with same table

sachin_mca25 — Sat, 26 Dec 2015 21:39:08 GMT

please check, if set hive.map.aggr=true or not.

Please change the table to ORC format and try to run only subquery. I hope, results should come fast.

Please check, if table can be bucketed on emp_type or not. try to implement SMB join.

Re: Optimize a long running hive query - has a join with same table

sooraj_antony — Sun, 27 Dec 2015 04:25:08 GMT

Table

STAGE_SOURCE

is already in ORC format.

Re: Optimize a long running hive query - has a join with same table

sooraj_antony — Sun, 27 Dec 2015 04:28:33 GMT

The table is in ORC and haven't tried SMB, but the process is getting stuck at the last reducer and the whole processing runs in a single node while other nodes are idle.

Can you explain the function of "hive.map.aggr" parameter?

Re: Optimize a long running hive query - has a join with same table

gbraccialli3 — Tue, 29 Dec 2015 03:54:17 GMT

@Sooraj Antony

I believe the problem is most of records has same value for EMP_TYPE (aka skewed records), that would cause all records from same EMP_TYPE value to be sent to same reducer and would cause the last reducer to take a long time to finish.

Assuming you have small number of different values of EMP_TYPE (and the result fit in memory), try solution below:

set hive.ignore.mapjoin.hint=false;

SELECT /*+ MAPJOIN(A) */ STG.EMP_TYPE,DEPT,COUNT(DISTINCT EMP_ID) AS COUNT, A.TOTAL_COUNT
FROM STAGE_SOURCE STG 
LEFT OUTER JOIN 
(SELECT EMP_TYPE,COUNT(DISTINCT EMP_ID) AS TOTAL_COUNT FROM STAGE_SOURCE GROUP BY EMP_TYPE) A
ON STG.EMP_TYPE = A.EMP_TYPE
GROUP BY STG.EMP_TYPE,DEPT,A.TOTAL_COUNT;

Re: Optimize a long running hive query - has a join with same table

aervits — Wed, 03 Feb 2016 10:41:53 GMT

@Sooraj Antony has this been resolved? Can you post your solution or accept the best answer?