Which types of query optimization do you know in Spark, and how do they work internally?
Use the supplied PostgreSQL tables to provide a hands-on SQL illustration of aggregating stage metrics and identifying completed jobs with high shuffle amplification. Explain how the corresponding Spark SQL plan could benefit from Catalyst optimization, predicate pushdown, partition pruning, join strategy selection, and Adaptive Query Execution.
job_id, application_name, input_gb, shuffle_read_mb, shuffle_write_mb, shuffle_ratio, shuffle_rankshuffle_ratio descending, then job_id ascendingstatus = 'SUCCEEDED'.shuffle_read_mb and shuffle_write_mb.shuffle_ratio as shuffle_write_mb / (input_gb * 1024.0).shuffle_ratio to 3 decimal places.input_gb is NULL or not greater than zero.shuffle_ratio values the same competition rank, leaving gaps after ties.| Column | Type | Description |
|---|---|---|
| job_idPK | INT | Unique Spark job identifier |
| application_name | VARCHAR(100) | Spark application name |
| submitted_at | TIMESTAMP | Job submission timestamp |
| status | VARCHAR(20) | Final job status |
| input_gb | DECIMAL(10,2) | Input data size in gigabytes |
| Column | Type | Description |
|---|---|---|
| stage_idPK | INT | Unique Spark stage identifier |
| job_id | INT | Parent Spark job identifier |
| stage_name | VARCHAR(100) | Stage description |
| shuffle_read_mb | INT | Shuffle data read in megabytes |
| shuffle_write_mb | INT | Shuffle data written in megabytes |