question Re: Hive Explain plan Interpretation in Support Questions

Hive Explain plan Interpretation

sreeviswa_athic — Fri, 23 Jun 2017 01:33:51 GMT

Am trying to understand Hive Explain plan. Sample explain plan for my query is as below

Vertex dependency in root stage Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 2 (SIMPLE_EDGE), Map 3 (BROADCAST_EDGE) Reducer 3 <- Map 4 (SIMPLE_EDGE), Map 5 (BROADCAST_EDGE), Reducer 2 (SIMPLE_EDGE) Reducer 4 <- Map 6 (SIMPLE_EDGE), Map 7 (BROADCAST_EDGE), Map 8 (BROADCAST_EDGE), Reducer 3 (SIMPLE_EDGE).

Can some one help me in understanding what is SIMPLE_EDGE and BROADCAST_EDGE.

What should I interpret from BROADCAST_EDGE and SIMPLE_EDGE?

Re: Hive Explain plan Interpretation

rreddy — Fri, 23 Jun 2017 01:58:43 GMT

You probably are using Hive on Tez. There is user-level explain for Hive on Tez users. Apply below setting and then run 'explain' query to see much more clearly readable tree of operations. This is also available for Hive on Spark and setting is called 'hive.spark.explain.user'

set hive.explain.user=true

Re: Hive Explain plan Interpretation

sreeviswa_athic — Fri, 23 Jun 2017 02:32:25 GMT

after adding this setting, am getting the same explain plan. Nothing additional

Re: Hive Explain plan Interpretation

fwelsch — Sat, 24 Jun 2017 05:01:07 GMT

If you are using Hive from HDP 2.6.0 or later, you might get help understanding the query execution by using the visual explain plan feature in Hive Views 2.0 of Ambari. Open the Query Tab documentation of the Ambari Views Guide, and search for the "Visual Explain Plan" section.

Re: Hive Explain plan Interpretation

SQLShaw — Fri, 30 Jun 2017 19:50:05 GMT

@Viswa, the best way to influence performance and optimize the explain plan is to make sure you have updated table statistics. Hive doesn't provide an auto update stat option so if there are significant table changes, you'll want to periodically update the statistics. Also be sure you've turned on the Cost Based Optimizer (CBO). Hive has a CBO and a rule based optimizer - you'll want both. Finally, another benefit to analyze table is if you are using LLAP then ANALYZE table will cache the table.

Also, a broadcast edge means there was a broadcast join.

Re: Hive Explain plan Interpretation

christopher_w_m — Fri, 30 Jun 2017 20:22:35 GMT

@Viswa

Can you post your query and full explain plan? Looks like not all the output is there so hard for anyone to explain what it is doing.

In the meantime, here is a pretty helpful presentation about reading Hive explain plans: https://www.slideshare.net/HadoopSummit/how-to-understand-and-analyze-apache-hive-query-execution-plan-for-performance-debugging

Assuming you're using the new Hive explain plan (hive.explain.user=true), some general quick tips:

Data flows from the bottom of the explain plan to the top
Operators can have multiple children (ex: to do a MAPJOIN you might need to do a MAP and a FILTER)

Re: Hive Explain plan Interpretation

dineshc — Sat, 01 Jul 2017 03:20:05 GMT

@Viswa

In Tez, there are following types of DataMovements that take place between 2 vertex and is represented via an Edge in the DAG.

BROADCASTOutput on this edge produced by any source task is available to all destination tasks.

CUSTOMCustom routing defined by the user.

ONE_TO_ONEOutput on this edge produced by the i-th source task is available to the i-th destination task.

SCATTER_GATHERThe i-th output on this edge produced by all source tasks is available to the same destination task.

To answer your question: SIMPLE_EDGE refers to data movement type - SCATTER_GATHER (example - SHUFFLE JOIN ) BROADCAST_EDGE refers to data movement type - BROADCAST (example - MAP JOIN)

I drew the above inference from createEdgeProperty() in source code

Hope this helps.