Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Hive Explain plan Interpretation

avatar
Super Collaborator

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

avatar
hide-solution

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community

Register/Login
6 REPLIES 6

avatar
Rising Star

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

avatar
Super Collaborator

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

avatar
Rising Star

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.

avatar

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

avatar
Expert Contributor
@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)

avatar
hide-solution

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community

Register/Login