Hive Explain plan Interpretation

[#1]

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?

[#2]

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

[#3]

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

[#4]

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.

[#5]

@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.

[#6]

@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-pl...

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

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

[#7 · Accepted solution]

@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.