Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Hive Explain plan Interpretation

Solved Go to solution

Hive Explain plan Interpretation

Expert Contributor

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?

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Hive Explain plan Interpretation

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

6 REPLIES 6

Re: Hive Explain plan Interpretation

Contributor

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

Expert Contributor

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

Re: Hive Explain plan Interpretation

New Contributor

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.

Highlighted

Re: Hive Explain plan Interpretation

@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

Rising Star
@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)

Re: Hive Explain plan Interpretation

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

Don't have an account?
Coming from Hortonworks? Activate your account here