To enable DPP at the session level, use the Hive SET command:

SET hive.spark.dynamic.partition.pruning.map.join.only=true;
          

Use Cloudera Manager to enable DPP as a service-wide default:

1. In the Cloudera Manager Admin Console, go to the Hive service.
2. In the Hive service page, click the Configuration tab.
3. On the Configuration page, click the HiveServer2 scope and click the Performance category.
4. Search for Hive on Spark Dynamic Partition Pruning for MapJoins, and select the check box.
5. Click Save Changes.

In this example, TPC-DS benchmark data is used with the query generated from query3.tpl in their downloadable package. It demonstrates how you can use the EXPLAIN command to verify that DPP is being triggered. For more information about the TPC-DS benchmark data and queries, see www.tpc.org/tpcds/.

First, set the following properties which instruct Hive to use Spark as its execution engine and turns on DPP for map joins:

SET hive.execution.engine=spark;
SET hive.spark.dynamic.partition.pruning.map.join.only=true;
        

Then run the following commands, which tell Hive to use the testing_example_db database and to show (EXPLAIN) the query plan for the query that follows:

USE testing_example_db;

EXPLAIN
SELECT dt.d_year
       ,item.i_brand_id brand_id
       ,item.i_brand brand
       ,sum(ss_ext_sales_price) sum_agg
FROM date_dim dt
       ,store_sales
       ,item
WHERE dt.d_date_sk = store_sales.ss_sold_date_sk
  AND store_sales.ss_item_sk = item.i_item_sk
  AND item.i_manufact_id = 436
  AND dt.d_moy=12
GROUP BY dt.d_year
      ,item.i_brand
      ,item.i_brand_id
ORDER BY dt.d_year
         ,sum_agg desc
         ,brand_id
LIMIT 100;
        

The EXPLAIN command returns the query plan for the TPC-DS query. An excerpt from that query plan is included below. Look for the Spark HashTable Sink Operator and the Spark Partition Pruning Sink Operator, which are in bold font in the following output. Presence of these sink operators in the query plan indicate that DPP is being triggered for the query.

+----------------------------------------------------+--+
|                      Explain                       |
+----------------------------------------------------+--+
| STAGE DEPENDENCIES:                                |
|   Stage-2 is a root stage                          |
|   Stage-1 depends on stages: Stage-2               |
|   Stage-0 depends on stages: Stage-1               |
|                                                    |
| STAGE PLANS:                                       |
|   Stage: Stage-2                                   |
|     Spark                                          |
|       DagName: hive_20170908151313_f478b7d3-89b8-4c6d-b98c-4ef3b8e25bf7:964 |
|       Vertices:                                    |
|         Map 1                                      |
|             Map Operator Tree:                     |
|                 TableScan                          |
|                   alias: dt                        |
|                   filterExpr: (d_date_sk is not null and (d_moy = 12)) (type: boolean) |
|                   Statistics: Num rows: 73049 Data size: 2045372 Basic stats: COMPLETE Column stats: NONE |
|                   Filter Operator                  |
|                     predicate: (d_date_sk is not null and (d_moy = 12)) (type: boolean) |
|                     Statistics: Num rows: 18262 Data size: 511336 Basic stats: COMPLETE Column stats: NONE |
|                     Spark HashTable Sink Operator  |
|                       keys:                        |
|                         0 d_date_sk (type: bigint) |
|                         1 ss_sold_date_sk (type: bigint) |
|                     Select Operator                |
|                       expressions: d_date_sk (type: bigint) |
|                       outputColumnNames: _col0     |
|                       Statistics: Num rows: 18262 Data size: 511336 Basic stats: COMPLETE Column stats: NONE |
|                       Group By Operator            |
|                         keys: _col0 (type: bigint) |
|                         mode: hash                 |
|                         outputColumnNames: _col0   |
|                         Statistics: Num rows: 18262 Data size: 511336 Basic stats: COMPLETE Column stats: NONE |
|                         Spark Partition Pruning Sink Operator |
|                           partition key expr: ss_sold_date_sk |
|                           tmp Path: hdfs://<server_name>.<domain>.com:8020/tmp/hive/hive/a8939414-8311-4b06-bbd6-5afc9c3b2d3d/hive_2017-09-08_15-13-54_861_527211251736847122-4/-mr-10003/2/1 |
|                           Statistics: Num rows: 18262 Data size: 511336 Basic stats: COMPLETE Column stats: NONE |
|                           target column name: ss_sold_date_sk |
|                           target work: Map 2       |
|             Local Work:                            |
|               Map Reduce Local Work                |
|         Map 5                                      |
|             Map Operator Tree:                     |
|                 TableScan                          |
|                   alias: item                      |
|                   filterExpr: (i_item_sk is not null and (i_manufact_id = 436)) (type: boolean) |
|                   Statistics: Num rows: 102000 Data size: 2244000 Basic stats: COMPLETE Column stats: NONE |
|                   Filter Operator                  |
|                     predicate: (i_item_sk is not null and (i_manufact_id = 436)) (type: boolean) |
|                     Statistics: Num rows: 25500 Data size: 561000 Basic stats: COMPLETE Column stats: NONE |
|                     Spark HashTable Sink Operator  |
|                       keys:                        |
|                         0 _col32 (type: bigint)    |
|                         1 i_item_sk (type: bigint) |
|             Local Work:                            |
|               Map Reduce Local Work                |
|
...
          

  Note: There are a few map join patterns that are not supported by DPP. For DPP to be triggered, the Spark Partition Pruning Sink Operator must have a target Map Work in a child stage. For example, in the above query plan, the Spark Partition Pruning Sink Operator resides in Stage-2 and has a target work: Map 2. So for DPP to be triggered, Map 2 must reside in either Stage 1 or Stage 0 because both are dependent on Stage 2, thus they are both children of Stage 2. See the STAGE DEPENDENCIES at the top of the query plan to see the stage hierarchy. If Map 2 resides in Stage 2, DPP is not triggered because Stage 2 is the root stage and therefore cannot be a child stage.

A simple way to check whether DPP is triggered for a query is to use the EXPLAIN command as shown above in Verifying Your DPP Configuration in Hive on Spark. If the query plan contains a Spark Partition Pruning Sink Operator, DPP will be triggered for the query. If it does not contain this operator, DPP will not be triggered for the query.

Use the HiveServer2 logs to debug the compile time phase of DPP and use the Hive on Spark Remote Driver logs to debug the runtime phase of DPP:

• HiveServer2 Logs

  The HiveServer2 logs print debugging information from the Java class DynamicPartitionPruningOptimization. This class looks at the query and checks if it can benefit from DPP. If the query can benefit from DPP, the class modifies the query plan to include DPP-specific operators, such as the Spark Partition Pruning Sink Operator. When the class runs, it prints out information related to whether or not it is enabling DPP for a particular clause in the query.

  For example, if the following message appears in the HiveServer2 log, it means that DPP will be triggered and that partitions will be dynamically pruned from the partitioned_table table, which is in bold text in the following example:

  INFO  org.apache.hadoop.hive.ql.optimizer.DynamicPartitionPruningOptimization:
        [HiveServer2-Handler-Pool: Thread-xx]: Dynamic partitioning: default@partitioned_table.partition_column
                

  To access these log files in Cloudera Manager, select Hive > HiveServer2 > Log Files > Role Log File.

• Hive on Spark Remote Driver Logs

  The Hive on Spark (HoS) Remote Driver logs print debugging information from the Java class SparkDynamicPartitionPruner. This class does the actual pruning of the partitioned table. Because pruning happens at runtime, the logs for this class are located in the HoS Remote Driver logs instead of the HiveServer2 logs. These logs print which partitions are pruned from the partitioned table, which can be very useful for troubleshooting.

  For example, if the following message appears in the HoS Remote Driver log, it means that the partition partition_column=1 is being pruned from the table partitioned_table, both of which are in bold text in the following example:

  INFO spark.SparkDynamicPartitionPruner:Pruning path:
  hdfs://<namenode_uri>/user/hive/warehouse/partitioned_table/partition_column=1
                

  To access these log files in Cloudera Manager, select SPARK_ON_YARN > History Server Web UI > <select_an_application> > Executors > executor id = driver > stderr.