Adaptive Query Execution & Power of EXPLAIN command in Spark

Optimization is the key to unlocking the true potential of Apache Spark. EXPLAIN is one of the tools that is available to Spark developers for understanding, fine-tuning, and optimizing Spark queries. In this article, we will explore the importance of the EXPLAIN command and discuss Adaptive Query Execution (AQE) in Spark.

Understanding Execution Plans:

The EXPLAIN command in Spark provides a detailed breakdown of the physical execution plan that Spark intends to execute for a given query. This plan outlines the steps Spark will take to process the data, including tasks such as scanning, filtering, joining, and aggregating. By examining the execution plan, developers gain crucial insights into how Spark will handle their queries, helping them identify potential bottlenecks, optimize data processing, and enhance overall performance.

Let's take an example query:

explain formatted SELECT gender, SUM(salary) AS total_salary
FROM employee_data
WHERE creationDate >= '1900-07-08' and creationDate <= '2022-12-31'
GROUP BY gender

======= Physical Plan ===============
AdaptiveSparkPlan (7)
+- HashAggregate (6)
   +- Exchange (5)
      +- HashAggregate (4)
         +- Project (3)
            +- Filter (2)
               +- Scan csv  (1)


(1) Scan csv 
Output [3]: [gender#24, salary#25, creationDate#26]
Batched: false
Location: InMemoryFileIndex [dbfs:/FileStore/ADE/EmployeeData.csv]
PushedFilters: [IsNotNull(creationDate), GreaterThanOrEqual(creationDate,1900-07-08 00:00:00.0), LessThanOrEqual(creationDate,2022-12-31 00:00:00.0)]
ReadSchema: struct<gender:string,salary:int,creationDate:timestamp>

(2) Filter
Input [3]: [gender#24, salary#25, creationDate#26]
Condition : ((isnotnull(creationDate#26) AND (creationDate#26 >= 1900-07-08 00:00:00)) AND (creationDate#26 <= 2022-12-31 00:00:00))

(3) Project
Output [2]: [gender#24, salary#25]
Input [3]: [gender#24, salary#25, creationDate#26]

(4) HashAggregate
Input [2]: [gender#24, salary#25]
Keys [1]: [gender#24]
Functions [1]: [partial_sum(salary#25) AS sum#551L]
Aggregate Attributes [1]: [sum#550L]
Results [2]: [gender#24, sum#551L]

(5) Exchange
Input [2]: [gender#24, sum#551L]
Arguments: hashpartitioning(gender#24, 200), ENSURE_REQUIREMENTS, [plan_id=540]

(6) HashAggregate
Input [2]: [gender#24, sum#551L]
Keys [1]: [gender#24]
Functions [1]: [finalmerge_sum(merge sum#551L) AS sum(salary#25)#549L]
Aggregate Attributes [1]: [sum(salary#25)#549L]
Results [2]: [gender#24, sum(salary#25)#549L AS total_salary#535L]

(7) AdaptiveSparkPlan
Output [2]: [gender#24, total_salary#535L]
Arguments: isFinalPlan=false
        

Analyzing Cost-Based Optimization

One of the key benefits of using EXPLAIN command is the cost-based optimization. The cost-based optimization evaluates multiple execution plans and selects the one with the lowest estimated cost. This ensures that Spark chooses the most efficient plan based on statistics and distribution of data, contributing to improved query performance.

What is Adaptive Query Execution?

Adaptive Query Execution (AQE) is introduced in Apache Spark 3.0. Unlike traditional query execution strategies that rely on static optimization, AQE introduces adaptability. This means that Spark can dynamically adjust its execution plan during runtime based on the actual characteristics of the data being processed.

Advantages of AQE:

1. Dynamic Partitioning: AQE allows Spark to dynamically adjust the number of partitions based on the size of the data, preventing issues related to skewed data distribution.
2. Runtime Statistics: AQE gathers runtime statistics during query execution, enabling Spark to make informed decisions about optimization strategies. This adaptability is particularly beneficial when dealing with varying data distributions.
3. Optimizing Joins: AQE can dynamically switch between different join algorithms based on the size of the data and pick the correct join for the input data.

Enabling AQE can significantly impact the execution plan generated by Spark. By using the EXPLAIN command on queries with AQE enabled, developers can witness the adaptive nature of the execution plan.

Conclusion

Understanding and utilizing the EXPLAIN command is crucial for any developer working with Apache Spark. Whether to know about static optimization or explore features of the AQE, the EXPLAIN command helps developers to debug and optimise spark jobs better.