NYC Yellow Taxi Data Analysis - Details for Query 5

Details for Query 5

Submitted Time: 2024/12/16 22:49:38
Duration: 1.0 min
Succeeded Jobs: 8 9 10 11 12

Show the Stage ID and Task ID that corresponds to the max metric

digraph G { 0 [labelType="html" label=" CollectLimit "]; subgraph cluster1 { isCluster="true"; label="WholeStageCodegen (9)\n \nduration: total (min, med, max (stageId: taskId))\n441 ms (0 ms, 2 ms, 8 ms (stage 38.0: task 1170))"; 2 [labelType="html" label=" Project "]; 3 [labelType="html" label="Filter number of output rows: 22"]; 4 [labelType="html" label="HashAggregate time in aggregation build total (min, med, max (stageId: taskId)) 302 ms (0 ms, 1 ms, 7 ms (stage 38.0: task 1170)) peak memory total (min, med, max (stageId: taskId)) 9.7 GiB (256.0 KiB, 64.3 MiB, 64.3 MiB (stage 28.0: task 1099)) number of output rows: 323 avg hash probe bucket list iters (min, med, max (stageId: taskId)): (1, 1, 1 (stage 28.0: task 1099))"]; } 5 [labelType="html" label="Exchange shuffle records written: 8,528 shuffle write time total (min, med, max (stageId: taskId)) 660 ms (0 ms, 3 ms, 11 ms (stage 22.0: task 952)) records read: 7,876 local bytes read total (min, med, max (stageId: taskId)) 374.1 KiB (0.0 B, 1419.0 B, 7.4 KiB (stage 28.0: task 1098)) fetch wait time total (min, med, max (stageId: taskId)) 52 ms (0 ms, 0 ms, 4 ms (stage 38.0: task 1146)) remote bytes read total (min, med, max (stageId: taskId)) 312.9 KiB (0.0 B, 1502.0 B, 7.3 KiB (stage 38.0: task 1180)) local blocks read: 3,655 remote blocks read: 3,041 data size total (min, med, max (stageId: taskId)) 523.5 KiB (0.0 B, 2.3 KiB, 7.3 KiB (stage 22.0: task 921)) shuffle bytes written total (min, med, max (stageId: taskId)) 744.9 KiB (0.0 B, 3.4 KiB, 10.1 KiB (stage 22.0: task 921))"]; subgraph cluster6 { isCluster="true"; label="WholeStageCodegen (8)\n \nduration: total (min, med, max (stageId: taskId))\n24.8 s (1 ms, 16 ms, 1.4 s (stage 22.0: task 995))"; 7 [labelType="html" label="HashAggregate time in aggregation build total (min, med, max (stageId: taskId)) 23.7 s (0 ms, 10 ms, 1.4 s (stage 22.0: task 995)) peak memory total (min, med, max (stageId: taskId)) 50.0 MiB (256.0 KiB, 256.0 KiB, 256.0 KiB (stage 22.0: task 898)) number of output rows: 8,528"]; 8 [labelType="html" label=" Project "]; } 9 [labelType="html" label="SortMergeJoin number of output rows: 22,400,728"]; subgraph cluster10 { isCluster="true"; label="WholeStageCodegen (5)\n \nduration: total (min, med, max (stageId: taskId))\n25.3 s (2 ms, 20 ms, 1.5 s (stage 22.0: task 995))"; 11 [labelType="html" label="Sort sort time total (min, med, max (stageId: taskId)) 137 ms (0 ms, 0 ms, 18 ms (stage 22.0: task 1008)) peak memory total (min, med, max (stageId: taskId)) 10.8 GiB (64.0 KiB, 64.3 MiB, 256.0 MiB (stage 22.0: task 984)) spill size total (min, med, max (stageId: taskId)) 0.0 B (0.0 B, 0.0 B, 0.0 B (stage 22.0: task 898))"]; } 12 [labelType="html" label="Exchange shuffle records written: 22,400,728 shuffle write time total (min, med, max (stageId: taskId)) 1.5 s (0 ms, 6 ms, 39 ms (stage 21.0: task 784)) records read: 22,400,728 local bytes read total (min, med, max (stageId: taskId)) 188.0 MiB (0.0 B, 66.1 KiB, 11.3 MiB (stage 22.0: task 984)) fetch wait time total (min, med, max (stageId: taskId)) 0 ms (0 ms, 0 ms, 0 ms (stage 22.0: task 898)) remote bytes read total (min, med, max (stageId: taskId)) 195.7 MiB (0.0 B, 76.4 KiB, 12.0 MiB (stage 22.0: task 984)) local blocks read: 8,849 remote blocks read: 8,445 data size total (min, med, max (stageId: taskId)) 2.3 GiB (0.0 B, 152.6 KiB, 154.1 MiB (stage 21.0: task 784)) shuffle bytes written total (min, med, max (stageId: taskId)) 383.7 MiB (0.0 B, 41.6 KiB, 24.5 MiB (stage 21.0: task 784))"]; subgraph cluster13 { isCluster="true"; label="WholeStageCodegen (4)\n \nduration: total (min, med, max (stageId: taskId))\n23.4 s (0 ms, 10 ms, 1.6 s (stage 21.0: task 784))"; 14 [labelType="html" label=" Project "]; } 15 [labelType="html" label="SortMergeJoin number of output rows: 22,400,728"]; subgraph cluster16 { isCluster="true"; label="WholeStageCodegen (1)\n \nduration: total (min, med, max (stageId: taskId))\n24.3 s (2 ms, 13 ms, 1.6 s (stage 21.0: task 784))"; 17 [labelType="html" label="Sort sort time total (min, med, max (stageId: taskId)) 140 ms (0 ms, 0 ms, 21 ms (stage 21.0: task 784)) peak memory total (min, med, max (stageId: taskId)) 11.0 GiB (64.0 KiB, 64.1 MiB, 256.0 MiB (stage 21.0: task 784)) spill size total (min, med, max (stageId: taskId)) 0.0 B (0.0 B, 0.0 B, 0.0 B (stage 21.0: task 698))"]; } 18 [labelType="html" label="Exchange shuffle records written: 22,400,728 shuffle write time total (min, med, max (stageId: taskId)) 835 ms (25 ms, 42 ms, 51 ms (stage 20.0: task 689)) records read: 22,400,728 local bytes read total (min, med, max (stageId: taskId)) 226.1 MiB (0.0 B, 16.4 KiB, 15.1 MiB (stage 21.0: task 795)) fetch wait time total (min, med, max (stageId: taskId)) 5 ms (0 ms, 0 ms, 5 ms (stage 21.0: task 698)) remote bytes read total (min, med, max (stageId: taskId)) 224.0 MiB (0.0 B, 17.6 KiB, 17.1 MiB (stage 21.0: task 784)) local blocks read: 1,435 remote blocks read: 1,414 data size total (min, med, max (stageId: taskId)) 2.2 GiB (34.9 MiB, 126.4 MiB, 126.5 MiB (stage 20.0: task 679)) shuffle bytes written total (min, med, max (stageId: taskId)) 450.1 MiB (7.3 MiB, 25.6 MiB, 25.6 MiB (stage 20.0: task 684))"]; 19 [labelType="html" label="Scan csv number of files read: 7 metadata time: 0 ms size of files read: 2.2 GiB number of output rows: 22,400,728"]; subgraph cluster20 { isCluster="true"; label="WholeStageCodegen (3)\n \nduration: total (min, med, max (stageId: taskId))\n13.7 s (1 ms, 4 ms, 1.4 s (stage 21.0: task 784))"; 21 [labelType="html" label="Sort sort time total (min, med, max (stageId: taskId)) 0 ms (0 ms, 0 ms, 0 ms (stage 21.0: task 698)) peak memory total (min, med, max (stageId: taskId)) 9.1 GiB (64.0 KiB, 64.1 MiB, 64.1 MiB (stage 21.0: task 698)) spill size total (min, med, max (stageId: taskId)) 0.0 B (0.0 B, 0.0 B, 0.0 B (stage 21.0: task 698))"]; } 22 [labelType="html" label="Exchange shuffle records written: 265 shuffle write time total (min, med, max (stageId: taskId)) 24 ms (11 ms, 12 ms, 12 ms (stage 19.0: task 676)) records read: 530 local bytes read total (min, med, max (stageId: taskId)) 16.1 KiB (0.0 B, 72.0 B, 126.0 B (stage 22.0: task 998)) fetch wait time total (min, med, max (stageId: taskId)) 894 ms (0 ms, 0 ms, 28 ms (stage 22.0: task 984)) remote bytes read total (min, med, max (stageId: taskId)) 15.8 KiB (0.0 B, 0.0 B, 151.0 B (stage 21.0: task 721)) local blocks read: 203 remote blocks read: 195 data size total (min, med, max (stageId: taskId)) 9.0 KiB (4.4 KiB, 4.6 KiB, 4.6 KiB (stage 19.0: task 675)) shuffle bytes written total (min, med, max (stageId: taskId)) 16.0 KiB (7.8 KiB, 8.2 KiB, 8.2 KiB (stage 19.0: task 675))"]; subgraph cluster23 { isCluster="true"; label="WholeStageCodegen (2)\n \nduration: total (min, med, max (stageId: taskId))\n113 ms (55 ms, 58 ms, 58 ms (stage 19.0: task 676))"; 24 [labelType="html" label=" Project "]; 25 [labelType="html" label="Filter number of output rows: 265"]; 26 [labelType="html" label="Scan ExistingRDD number of output rows: 265"]; } subgraph cluster27 { isCluster="true"; label="WholeStageCodegen (7)\n \nduration: total (min, med, max (stageId: taskId))\n14.7 s (0 ms, 12 ms, 1.1 s (stage 22.0: task 984))"; 28 [labelType="html" label="Sort sort time total (min, med, max (stageId: taskId)) 0 ms (0 ms, 0 ms, 0 ms (stage 22.0: task 898)) peak memory total (min, med, max (stageId: taskId)) 9.1 GiB (64.0 KiB, 64.1 MiB, 64.1 MiB (stage 22.0: task 898)) spill size total (min, med, max (stageId: taskId)) 0.0 B (0.0 B, 0.0 B, 0.0 B (stage 22.0: task 898))"]; } 2->0; 3->2; 4->3; 5->4; 7->5; 8->7; 9->8; 11->9; 12->11; 14->12; 15->14; 17->15; 18->17; 19->18; 21->15; 22->21; 24->22; 25->24; 26->25; 28->9; 22->28; }

CollectLimit 21

Project [cast(Month#405 as string) AS Month#604, route#383, cast(total_tip_amount#556 as string) AS total_tip_amount#606, cast(total_passenger_count#558 as string) AS total_passenger_count#607, cast(round((total_tip_amount#556 / total_passenger_count#558), 2) as string) AS average_tip_per_passenger#608]

Filter (round((total_tip_amount#556 / total_passenger_count#558), 2) = 0.0)

HashAggregate(keys=[Month#405, route#383], functions=[sum(cast(tip_amount#65 as double)), sum(cast(passenger_count#57 as double))])

WholeStageCodegen (9)

Exchange hashpartitioning(Month#405, route#383, 200), true, [id=#449]

HashAggregate(keys=[Month#405, route#383], functions=[partial_sum(cast(tip_amount#65 as double)), partial_sum(cast(passenger_count#57 as double))])

Project [passenger_count#57, tip_amount#65, concat_ws( to , Pickup_Borough#147, Borough#15) AS route#383, month(cast(tpep_pickup_datetime#55 as date)) AS Month#405]

WholeStageCodegen (8)

SortMergeJoin [cast(DOLocationID#60 as int)], [LocationID#14], LeftOuter

Sort [cast(DOLocationID#60 as int) ASC NULLS FIRST], false, 0

WholeStageCodegen (5)

Exchange hashpartitioning(cast(DOLocationID#60 as int), 200), true, [id=#403]

Project [tpep_pickup_datetime#55, passenger_count#57, DOLocationID#60, tip_amount#65, Borough#15 AS Pickup_Borough#147]

WholeStageCodegen (4)

SortMergeJoin [cast(PULocationID#59 as int)], [LocationID#14], LeftOuter

Sort [cast(PULocationID#59 as int) ASC NULLS FIRST], false, 0

WholeStageCodegen (1)

Exchange hashpartitioning(cast(PULocationID#59 as int), 200), true, [id=#370]

FileScan csv [tpep_pickup_datetime#55,passenger_count#57,PULocationID#59,DOLocationID#60,tip_amount#65] Batched: false, DataFilters: [], Format: CSV, Location: InMemoryFileIndex[s3a://data-repository-bkt/ECS765/nyc_taxi/yellow_tripdata/2023/yellow_tripdata_..., PartitionFilters: [], PushedFilters: [], ReadSchema: struct<tpep_pickup_datetime:string,passenger_count:string,PULocationID:string,DOLocationID:string...

Sort [LocationID#14 ASC NULLS FIRST], false, 0

WholeStageCodegen (3)

Exchange hashpartitioning(LocationID#14, 200), true, [id=#395]

Project [LocationID#14, Borough#15]

Filter isnotnull(LocationID#14)

Scan ExistingRDD[LocationID#14,Borough#15,Zone#16,service_zone#17]

WholeStageCodegen (2)

Sort [LocationID#14 ASC NULLS FIRST], false, 0

WholeStageCodegen (7)

Details

== Parsed Logical Plan ==
GlobalLimit 21
+- LocalLimit 21
   +- Project [cast(Month#405 as string) AS Month#604, cast(route#383 as string) AS route#605, cast(total_tip_amount#556 as string) AS total_tip_amount#606, cast(total_passenger_count#558 as string) AS total_passenger_count#607, cast(average_tip_per_passenger#563 as string) AS average_tip_per_passenger#608]
      +- Filter (average_tip_per_passenger#563 = cast(0 as double))
         +- Project [Month#405, route#383, total_tip_amount#556, total_passenger_count#558, round((total_tip_amount#556 / total_passenger_count#558), 2) AS average_tip_per_passenger#563]
            +- Aggregate [Month#405, route#383], [Month#405, route#383, round(sum(cast(tip_amount#65 as double)), 2) AS total_tip_amount#556, sum(cast(passenger_count#57 as double)) AS total_passenger_count#558]
               +- Project [tpep_pickup_datetime#55, tpep_dropoff_datetime#56, passenger_count#57, trip_distance#58, payment_type#61, fare_amount#62, extra#63, mta_tax#64, tip_amount#65, tolls_amount#66, total_amount#67, congestion_surcharge#68, airport_fee#69, taxi_type#70, Pickup_Borough#147, Pickup_Zone#168, Pickup_service_zone#189, Dropoff_Borough#294, Dropoff_Zone#317, Dropoff_service_zone#340, route#383, month(cast(tpep_pickup_datetime#55 as date)) AS Month#405]
                  +- Project [tpep_pickup_datetime#55, tpep_dropoff_datetime#56, passenger_count#57, trip_distance#58, payment_type#61, fare_amount#62, extra#63, mta_tax#64, tip_amount#65, tolls_amount#66, total_amount#67, congestion_surcharge#68, airport_fee#69, taxi_type#70, Pickup_Borough#147, Pickup_Zone#168, Pickup_service_zone#189, Dropoff_Borough#294, Dropoff_Zone#317, Dropoff_service_zone#340, concat_ws( to , Pickup_Borough#147, Dropoff_Borough#294) AS route#383]
                     +- Project [tpep_pickup_datetime#55, tpep_dropoff_datetime#56, passenger_count#57, trip_distance#58, payment_type#61, fare_amount#62, extra#63, mta_tax#64, tip_amount#65, tolls_amount#66, total_amount#67, congestion_surcharge#68, airport_fee#69, taxi_type#70, Pickup_Borough#147, Pickup_Zone#168, Pickup_service_zone#189, Dropoff_Borough#294, Dropoff_Zone#317, Dropoff_service_zone#340]
                        +- Project [tpep_pickup_datetime#55, tpep_dropoff_datetime#56, passenger_count#57, trip_distance#58, DOLocationID#60, payment_type#61, fare_amount#62, extra#63, mta_tax#64, tip_amount#65, tolls_amount#66, total_amount#67, congestion_surcharge#68, airport_fee#69, taxi_type#70, Pickup_Borough#147, Pickup_Zone#168, Pickup_service_zone#189, LocationID#14, Dropoff_Borough#294, Dropoff_Zone#317, service_zone#17 AS Dropoff_service_zone#340]
                           +- Project [tpep_pickup_datetime#55, tpep_dropoff_datetime#56, passenger_count#57, trip_distance#58, DOLocationID#60, payment_type#61, fare_amount#62, extra#63, mta_tax#64, tip_amount#65, tolls_amount#66, total_amount#67, congestion_surcharge#68, airport_fee#69, taxi_type#70, Pickup_Borough#147, Pickup_Zone#168, Pickup_service_zone#189, LocationID#14, Dropoff_Borough#294, Zone#16 AS Dropoff_Zone#317, service_zone#17]
                              +- Project [tpep_pickup_datetime#55, tpep_dropoff_datetime#56, passenger_count#57, trip_distance#58, DOLocationID#60, payment_type#61, fare_amount#62, extra#63, mta_tax#64, tip_amount#65, tolls_amount#66, total_amount#67, congestion_surcharge#68, airport_fee#69, taxi_type#70, Pickup_Borough#147, Pickup_Zone#168, Pickup_service_zone#189, LocationID#14, Borough#15 AS Dropoff_Borough#294, Zone#16, service_zone#17]
                                 +- Join LeftOuter, (cast(DOLocationID#60 as int) = LocationID#14)
                                    :- Project [tpep_pickup_datetime#55, tpep_dropoff_datetime#56, passenger_count#57, trip_distance#58, DOLocationID#60, payment_type#61, fare_amount#62, extra#63, mta_tax#64, tip_amount#65, tolls_amount#66, total_amount#67, congestion_surcharge#68, airport_fee#69, taxi_type#70, Pickup_Borough#147, Pickup_Zone#168, Pickup_service_zone#189]
                                    :  +- Project [tpep_pickup_datetime#55, tpep_dropoff_datetime#56, passenger_count#57, trip_distance#58, PULocationID#59, DOLocationID#60, payment_type#61, fare_amount#62, extra#63, mta_tax#64, tip_amount#65, tolls_amount#66, total_amount#67, congestion_surcharge#68, airport_fee#69, taxi_type#70, LocationID#14, Pickup_Borough#147, Pickup_Zone#168, service_zone#17 AS Pickup_service_zone#189]
                                    :     +- Project [tpep_pickup_datetime#55, tpep_dropoff_datetime#56, passenger_count#57, trip_distance#58, PULocationID#59, DOLocationID#60, payment_type#61, fare_amount#62, extra#63, mta_tax#64, tip_amount#65, tolls_amount#66, total_amount#67, congestion_surcharge#68, airport_fee#69, taxi_type#70, LocationID#14, Pickup_Borough#147, Zone#16 AS Pickup_Zone#168, service_zone#17]
                                    :        +- Project [tpep_pickup_datetime#55, tpep_dropoff_datetime#56, passenger_count#57, trip_distance#58, PULocationID#59, DOLocationID#60, payment_type#61, fare_amount#62, extra#63, mta_tax#64, tip_amount#65, tolls_amount#66, total_amount#67, congestion_surcharge#68, airport_fee#69, taxi_type#70, LocationID#14, Borough#15 AS Pickup_Borough#147, Zone#16, service_zone#17]
                                    :           +- Join LeftOuter, (cast(PULocationID#59 as int) = LocationID#14)
                                    :              :- Relation[tpep_pickup_datetime#55,tpep_dropoff_datetime#56,passenger_count#57,trip_distance#58,PULocationID#59,DOLocationID#60,payment_type#61,fare_amount#62,extra#63,mta_tax#64,tip_amount#65,tolls_amount#66,total_amount#67,congestion_surcharge#68,airport_fee#69,taxi_type#70] csv
                                    :              +- LogicalRDD [LocationID#14, Borough#15, Zone#16, service_zone#17], false
                                    +- LogicalRDD [LocationID#14, Borough#15, Zone#16, service_zone#17], false

== Analyzed Logical Plan ==
Month: string, route: string, total_tip_amount: string, total_passenger_count: string, average_tip_per_passenger: string
GlobalLimit 21
+- LocalLimit 21
   +- Project [cast(Month#405 as string) AS Month#604, cast(route#383 as string) AS route#605, cast(total_tip_amount#556 as string) AS total_tip_amount#606, cast(total_passenger_count#558 as string) AS total_passenger_count#607, cast(average_tip_per_passenger#563 as string) AS average_tip_per_passenger#608]
      +- Filter (average_tip_per_passenger#563 = cast(0 as double))
         +- Project [Month#405, route#383, total_tip_amount#556, total_passenger_count#558, round((total_tip_amount#556 / total_passenger_count#558), 2) AS average_tip_per_passenger#563]
            +- Aggregate [Month#405, route#383], [Month#405, route#383, round(sum(cast(tip_amount#65 as double)), 2) AS total_tip_amount#556, sum(cast(passenger_count#57 as double)) AS total_passenger_count#558]
               +- Project [tpep_pickup_datetime#55, tpep_dropoff_datetime#56, passenger_count#57, trip_distance#58, payment_type#61, fare_amount#62, extra#63, mta_tax#64, tip_amount#65, tolls_amount#66, total_amount#67, congestion_surcharge#68, airport_fee#69, taxi_type#70, Pickup_Borough#147, Pickup_Zone#168, Pickup_service_zone#189, Dropoff_Borough#294, Dropoff_Zone#317, Dropoff_service_zone#340, route#383, month(cast(tpep_pickup_datetime#55 as date)) AS Month#405]
                  +- Project [tpep_pickup_datetime#55, tpep_dropoff_datetime#56, passenger_count#57, trip_distance#58, payment_type#61, fare_amount#62, extra#63, mta_tax#64, tip_amount#65, tolls_amount#66, total_amount#67, congestion_surcharge#68, airport_fee#69, taxi_type#70, Pickup_Borough#147, Pickup_Zone#168, Pickup_service_zone#189, Dropoff_Borough#294, Dropoff_Zone#317, Dropoff_service_zone#340, concat_ws( to , Pickup_Borough#147, Dropoff_Borough#294) AS route#383]
                     +- Project [tpep_pickup_datetime#55, tpep_dropoff_datetime#56, passenger_count#57, trip_distance#58, payment_type#61, fare_amount#62, extra#63, mta_tax#64, tip_amount#65, tolls_amount#66, total_amount#67, congestion_surcharge#68, airport_fee#69, taxi_type#70, Pickup_Borough#147, Pickup_Zone#168, Pickup_service_zone#189, Dropoff_Borough#294, Dropoff_Zone#317, Dropoff_service_zone#340]
                        +- Project [tpep_pickup_datetime#55, tpep_dropoff_datetime#56, passenger_count#57, trip_distance#58, DOLocationID#60, payment_type#61, fare_amount#62, extra#63, mta_tax#64, tip_amount#65, tolls_amount#66, total_amount#67, congestion_surcharge#68, airport_fee#69, taxi_type#70, Pickup_Borough#147, Pickup_Zone#168, Pickup_service_zone#189, LocationID#14, Dropoff_Borough#294, Dropoff_Zone#317, service_zone#17 AS Dropoff_service_zone#340]
                           +- Project [tpep_pickup_datetime#55, tpep_dropoff_datetime#56, passenger_count#57, trip_distance#58, DOLocationID#60, payment_type#61, fare_amount#62, extra#63, mta_tax#64, tip_amount#65, tolls_amount#66, total_amount#67, congestion_surcharge#68, airport_fee#69, taxi_type#70, Pickup_Borough#147, Pickup_Zone#168, Pickup_service_zone#189, LocationID#14, Dropoff_Borough#294, Zone#16 AS Dropoff_Zone#317, service_zone#17]
                              +- Project [tpep_pickup_datetime#55, tpep_dropoff_datetime#56, passenger_count#57, trip_distance#58, DOLocationID#60, payment_type#61, fare_amount#62, extra#63, mta_tax#64, tip_amount#65, tolls_amount#66, total_amount#67, congestion_surcharge#68, airport_fee#69, taxi_type#70, Pickup_Borough#147, Pickup_Zone#168, Pickup_service_zone#189, LocationID#14, Borough#15 AS Dropoff_Borough#294, Zone#16, service_zone#17]
                                 +- Join LeftOuter, (cast(DOLocationID#60 as int) = LocationID#14)
                                    :- Project [tpep_pickup_datetime#55, tpep_dropoff_datetime#56, passenger_count#57, trip_distance#58, DOLocationID#60, payment_type#61, fare_amount#62, extra#63, mta_tax#64, tip_amount#65, tolls_amount#66, total_amount#67, congestion_surcharge#68, airport_fee#69, taxi_type#70, Pickup_Borough#147, Pickup_Zone#168, Pickup_service_zone#189]
                                    :  +- Project [tpep_pickup_datetime#55, tpep_dropoff_datetime#56, passenger_count#57, trip_distance#58, PULocationID#59, DOLocationID#60, payment_type#61, fare_amount#62, extra#63, mta_tax#64, tip_amount#65, tolls_amount#66, total_amount#67, congestion_surcharge#68, airport_fee#69, taxi_type#70, LocationID#14, Pickup_Borough#147, Pickup_Zone#168, service_zone#17 AS Pickup_service_zone#189]
                                    :     +- Project [tpep_pickup_datetime#55, tpep_dropoff_datetime#56, passenger_count#57, trip_distance#58, PULocationID#59, DOLocationID#60, payment_type#61, fare_amount#62, extra#63, mta_tax#64, tip_amount#65, tolls_amount#66, total_amount#67, congestion_surcharge#68, airport_fee#69, taxi_type#70, LocationID#14, Pickup_Borough#147, Zone#16 AS Pickup_Zone#168, service_zone#17]
                                    :        +- Project [tpep_pickup_datetime#55, tpep_dropoff_datetime#56, passenger_count#57, trip_distance#58, PULocationID#59, DOLocationID#60, payment_type#61, fare_amount#62, extra#63, mta_tax#64, tip_amount#65, tolls_amount#66, total_amount#67, congestion_surcharge#68, airport_fee#69, taxi_type#70, LocationID#14, Borough#15 AS Pickup_Borough#147, Zone#16, service_zone#17]
                                    :           +- Join LeftOuter, (cast(PULocationID#59 as int) = LocationID#14)
                                    :              :- Relation[tpep_pickup_datetime#55,tpep_dropoff_datetime#56,passenger_count#57,trip_distance#58,PULocationID#59,DOLocationID#60,payment_type#61,fare_amount#62,extra#63,mta_tax#64,tip_amount#65,tolls_amount#66,total_amount#67,congestion_surcharge#68,airport_fee#69,taxi_type#70] csv
                                    :              +- LogicalRDD [LocationID#14, Borough#15, Zone#16, service_zone#17], false
                                    +- LogicalRDD [LocationID#14, Borough#15, Zone#16, service_zone#17], false

== Optimized Logical Plan ==
GlobalLimit 21
+- LocalLimit 21
   +- Project [cast(Month#405 as string) AS Month#604, route#383, cast(total_tip_amount#556 as string) AS total_tip_amount#606, cast(total_passenger_count#558 as string) AS total_passenger_count#607, cast(round((total_tip_amount#556 / total_passenger_count#558), 2) as string) AS average_tip_per_passenger#608]
      +- Filter (round((total_tip_amount#556 / total_passenger_count#558), 2) = 0.0)
         +- Aggregate [Month#405, route#383], [Month#405, route#383, round(sum(cast(tip_amount#65 as double)), 2) AS total_tip_amount#556, sum(cast(passenger_count#57 as double)) AS total_passenger_count#558]
            +- Project [passenger_count#57, tip_amount#65, concat_ws( to , Pickup_Borough#147, Borough#15) AS route#383, month(cast(tpep_pickup_datetime#55 as date)) AS Month#405]
               +- Join LeftOuter, (cast(DOLocationID#60 as int) = LocationID#14)
                  :- Project [tpep_pickup_datetime#55, passenger_count#57, DOLocationID#60, tip_amount#65, Borough#15 AS Pickup_Borough#147]
                  :  +- Join LeftOuter, (cast(PULocationID#59 as int) = LocationID#14)
                  :     :- Project [tpep_pickup_datetime#55, passenger_count#57, PULocationID#59, DOLocationID#60, tip_amount#65]
                  :     :  +- Relation[tpep_pickup_datetime#55,tpep_dropoff_datetime#56,passenger_count#57,trip_distance#58,PULocationID#59,DOLocationID#60,payment_type#61,fare_amount#62,extra#63,mta_tax#64,tip_amount#65,tolls_amount#66,total_amount#67,congestion_surcharge#68,airport_fee#69,taxi_type#70] csv
                  :     +- Project [LocationID#14, Borough#15]
                  :        +- Filter isnotnull(LocationID#14)
                  :           +- LogicalRDD [LocationID#14, Borough#15, Zone#16, service_zone#17], false
                  +- Project [LocationID#14, Borough#15]
                     +- Filter isnotnull(LocationID#14)
                        +- LogicalRDD [LocationID#14, Borough#15, Zone#16, service_zone#17], false

== Physical Plan ==
CollectLimit 21
+- *(9) Project [cast(Month#405 as string) AS Month#604, route#383, cast(total_tip_amount#556 as string) AS total_tip_amount#606, cast(total_passenger_count#558 as string) AS total_passenger_count#607, cast(round((total_tip_amount#556 / total_passenger_count#558), 2) as string) AS average_tip_per_passenger#608]
   +- *(9) Filter (round((total_tip_amount#556 / total_passenger_count#558), 2) = 0.0)
      +- *(9) HashAggregate(keys=[Month#405, route#383], functions=[sum(cast(tip_amount#65 as double)), sum(cast(passenger_count#57 as double))], output=[Month#405, route#383, total_tip_amount#556, total_passenger_count#558])
         +- Exchange hashpartitioning(Month#405, route#383, 200), true, [id=#449]
            +- *(8) HashAggregate(keys=[Month#405, route#383], functions=[partial_sum(cast(tip_amount#65 as double)), partial_sum(cast(passenger_count#57 as double))], output=[Month#405, route#383, sum#586, sum#587])
               +- *(8) Project [passenger_count#57, tip_amount#65, concat_ws( to , Pickup_Borough#147, Borough#15) AS route#383, month(cast(tpep_pickup_datetime#55 as date)) AS Month#405]
                  +- SortMergeJoin [cast(DOLocationID#60 as int)], [LocationID#14], LeftOuter
                     :- *(5) Sort [cast(DOLocationID#60 as int) ASC NULLS FIRST], false, 0
                     :  +- Exchange hashpartitioning(cast(DOLocationID#60 as int), 200), true, [id=#403]
                     :     +- *(4) Project [tpep_pickup_datetime#55, passenger_count#57, DOLocationID#60, tip_amount#65, Borough#15 AS Pickup_Borough#147]
                     :        +- SortMergeJoin [cast(PULocationID#59 as int)], [LocationID#14], LeftOuter
                     :           :- *(1) Sort [cast(PULocationID#59 as int) ASC NULLS FIRST], false, 0
                     :           :  +- Exchange hashpartitioning(cast(PULocationID#59 as int), 200), true, [id=#370]
                     :           :     +- FileScan csv [tpep_pickup_datetime#55,passenger_count#57,PULocationID#59,DOLocationID#60,tip_amount#65] Batched: false, DataFilters: [], Format: CSV, Location: InMemoryFileIndex[s3a://data-repository-bkt/ECS765/nyc_taxi/yellow_tripdata/2023/yellow_tripdata_..., PartitionFilters: [], PushedFilters: [], ReadSchema: struct<tpep_pickup_datetime:string,passenger_count:string,PULocationID:string,DOLocationID:string...
                     :           +- *(3) Sort [LocationID#14 ASC NULLS FIRST], false, 0
                     :              +- Exchange hashpartitioning(LocationID#14, 200), true, [id=#395]
                     :                 +- *(2) Project [LocationID#14, Borough#15]
                     :                    +- *(2) Filter isnotnull(LocationID#14)
                     :                       +- *(2) Scan ExistingRDD[LocationID#14,Borough#15,Zone#16,service_zone#17]
                     +- *(7) Sort [LocationID#14 ASC NULLS FIRST], false, 0
                        +- ReusedExchange [LocationID#14, Borough#15], Exchange hashpartitioning(LocationID#14, 200), true, [id=#395]