...
EXPLAIN Syntax
Hive provides an EXPLAIN
command that shows the execution plan for a query. The syntax for this statement is as follows:
Code Block |
---|
EXPLAIN [EXTENDED|DEPENDENCY|AUTHORIZATION] query |
AUTHORIZATION
is supported from HIVE- 0.14.0 via HIVE-5961.
The use of EXTENDED
in the EXPLAIN
statement produces extra information about the operators in the plan. This is typically physical information like file names.
...
As an example, consider the following EXPLAIN
query:
Code Block |
---|
EXPLAIN FROM src INSERT OVERWRITE TABLE dest_g1 SELECT src.key, sum(substr(src.value,4)) GROUP BY src.key; |
...
- A mapping from table alias to Map Operator Tree - This mapping tells the mappers which operator tree to call in order to process the rows from a particular table or result of a previous map/reduce stage. In Stage-1 in the above example, the rows from src table are processed by the operator tree rooted at a Reduce Output Operator. Similarly, in Stage-2 the rows of the results of Stage-1 are processed by another operator tree rooted at another Reduce Output Operator. Each of these Reduce Output Operators partitions the data to the reducers according to the criteria shown in the metadata.
- A Reduce Operator Tree - This is the operator tree which processes all the rows on the reducer of the map/reduce job. In Stage-1 for example, the Reducer Operator Tree is carrying out a partial aggregation where as the Reducer Operator Tree in Stage-2 computes the final aggregation from the partial aggregates computed in Stage-1
The use of DEPENDENCY
in the EXPLAIN
statement produces extra information about the inputs in the plan. It shows various attributes for the inputs. For example, for a query like:
...
As can be seen, src is being accessed via parents v1 and v4.
The use of AUTHORIZATION in AUTHORIZATION
in the EXPLAIN
statement shows all entities needed to be authorized to execute the query and authorization failures if exists. For example, for a query like:
...
Code Block |
---|
INPUTS: default@srcpart default@src default@srcpart@ds=2008-04-08/hr=11 default@srcpart@ds=2008-04-08/hr=12 default@srcpart@ds=2008-04-09/hr=11 default@srcpart@ds=2008-04-09/hr=12 OUTPUTS: hdfs://localhost:9000/tmp/.../-mr-10000 CURRENT_USER: navis OPERATION: QUERY AUTHORIZATION_FAILURES: Permission denied: Principal [name=navis, type=USER] does not have following privileges for operation QUERY [[SELECT] on Object [type=TABLE_OR_VIEW, name=default.src], [SELECT] on Object [type=TABLE_OR_VIEW, name=default.srcpart]] |
With the FORMATTED
keyword, it will be returned in JSON format.
...