task1 - Details for Query 5

Details for Query 5

Submitted Time: 2024/12/04 20:19:33
Duration: 42 s
Succeeded Jobs: 7

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

digraph G { 0 [labelType="html" label=" TakeOrderedAndProject "]; subgraph cluster1 { isCluster="true"; label="WholeStageCodegen (2)\n \nduration: total (min, med, max (stageId: taskId))\n563 ms (1 ms, 2 ms, 42 ms (stage 9.0: task 67))"; 2 [labelType="html" label="HashAggregate time in aggregation build total (min, med, max (stageId: taskId)) 26 ms (0 ms, 0 ms, 11 ms (stage 9.0: task 67)) peak memory total (min, med, max (stageId: taskId)) 498.0 MiB (256.0 KiB, 256.0 KiB, 64.3 MiB (stage 9.0: task 66)) number of output rows: 7 avg hash probe bucket list iters (min, med, max (stageId: taskId)): (1, 1, 1 (stage 9.0: task 66))"]; } 3 [labelType="html" label="Exchange shuffle records written: 14 shuffle write time total (min, med, max (stageId: taskId)) 23 ms (0 ms, 0 ms, 11 ms (stage 8.0: task 48)) records read: 14 local bytes read total (min, med, max (stageId: taskId)) 448.0 B (0.0 B, 0.0 B, 64.0 B (stage 9.0: task 66)) fetch wait time total (min, med, max (stageId: taskId)) 3 ms (0 ms, 0 ms, 2 ms (stage 9.0: task 68)) remote bytes read total (min, med, max (stageId: taskId)) 448.0 B (0.0 B, 0.0 B, 64.0 B (stage 9.0: task 66)) local blocks read: 7 remote blocks read: 7 data size total (min, med, max (stageId: taskId)) 336.0 B (0.0 B, 0.0 B, 168.0 B (stage 8.0: task 48)) shuffle bytes written total (min, med, max (stageId: taskId)) 896.0 B (0.0 B, 0.0 B, 448.0 B (stage 8.0: task 48))"]; subgraph cluster4 { isCluster="true"; label="WholeStageCodegen (1)\n \nduration: total (min, med, max (stageId: taskId))\n1.3 m (1.3 s, 4.2 s, 5.4 s (stage 8.0: task 46))"; 5 [labelType="html" label="HashAggregate time in aggregation build total (min, med, max (stageId: taskId)) 1.3 m (1.3 s, 4.2 s, 5.3 s (stage 8.0: task 46)) peak memory total (min, med, max (stageId: taskId)) 5.0 MiB (256.0 KiB, 256.0 KiB, 256.0 KiB (stage 8.0: task 47)) number of output rows: 14"]; 6 [labelType="html" label=" Project "]; 7 [labelType="html" label="Filter number of output rows: 2,202"]; } 8 [labelType="html" label="Scan csv number of files read: 7 metadata time: 1 ms size of files read: 2.2 GiB number of output rows: 22,400,728"]; 2->0; 3->2; 5->3; 6->5; 7->6; 8->7; }

TakeOrderedAndProject(limit=21, orderBy=[pickup_date#113 ASC NULLS FIRST], output=[pickup_date#154,trip_count#155])

HashAggregate(keys=[pickup_date#113], functions=[count(1)])

WholeStageCodegen (2)

Exchange hashpartitioning(pickup_date#113, 200), true, [id=#94]

HashAggregate(keys=[pickup_date#113], functions=[partial_count(1)])

Project [cast(cast(unix_timestamp(tpep_pickup_datetime#40, yyyy-MM-dd HH:mm:ss, Some(GMT)) as timestamp) as date) AS pickup_date#113]

Filter (((((isnotnull(fare_amount#47) AND isnotnull(trip_distance#43)) AND (cast(fare_amount#47 as int) > 50)) AND (cast(trip_distance#43 as int) < 1)) AND (cast(cast(unix_timestamp(tpep_pickup_datetime#40, yyyy-MM-dd HH:mm:ss, Some(GMT)) as timestamp) as date) >= 19389)) AND (cast(cast(unix_timestamp(tpep_pickup_datetime#40, yyyy-MM-dd HH:mm:ss, Some(GMT)) as timestamp) as date) <= 19395))

WholeStageCodegen (1)

FileScan csv [tpep_pickup_datetime#40,trip_distance#43,fare_amount#47] Batched: false, DataFilters: [isnotnull(fare_amount#47), isnotnull(trip_distance#43), (cast(fare_amount#47 as int) > 50), (cas..., Format: CSV, Location: InMemoryFileIndex[s3a://data-repository-bkt/ECS765/nyc_taxi/yellow_tripdata/2023/yellow_tripdata_..., PartitionFilters: [], PushedFilters: [IsNotNull(fare_amount), IsNotNull(trip_distance)], ReadSchema: struct<tpep_pickup_datetime:string,trip_distance:string,fare_amount:string>

Details

== Parsed Logical Plan ==
GlobalLimit 21
+- LocalLimit 21
   +- Project [cast(pickup_date#113 as string) AS pickup_date#154, cast(trip_count#149L as string) AS trip_count#155]
      +- Sort [pickup_date#113 ASC NULLS FIRST], true
         +- Aggregate [pickup_date#113], [pickup_date#113, count(1) AS trip_count#149L]
            +- Filter ((pickup_date#113 >= cast(2023-02-01 as date)) AND (pickup_date#113 <= cast(2023-02-07 as date)))
               +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, PULocationID#44, DOLocationID#45, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, to_date('tpep_pickup_datetime, Some(yyyy-MM-dd HH:mm:ss)) AS pickup_date#113]
                  +- Filter ((cast(fare_amount#47 as int) > 50) AND (cast(trip_distance#43 as int) < 1))
                     +- Relation[tpep_pickup_datetime#40,tpep_dropoff_datetime#41,passenger_count#42,trip_distance#43,PULocationID#44,DOLocationID#45,payment_type#46,fare_amount#47,extra#48,mta_tax#49,tip_amount#50,tolls_amount#51,total_amount#52,congestion_surcharge#53,airport_fee#54,taxi_type#55] csv

== Analyzed Logical Plan ==
pickup_date: string, trip_count: string
GlobalLimit 21
+- LocalLimit 21
   +- Project [cast(pickup_date#113 as string) AS pickup_date#154, cast(trip_count#149L as string) AS trip_count#155]
      +- Sort [pickup_date#113 ASC NULLS FIRST], true
         +- Aggregate [pickup_date#113], [pickup_date#113, count(1) AS trip_count#149L]
            +- Filter ((pickup_date#113 >= cast(2023-02-01 as date)) AND (pickup_date#113 <= cast(2023-02-07 as date)))
               +- Project [tpep_pickup_datetime#40, tpep_dropoff_datetime#41, passenger_count#42, trip_distance#43, PULocationID#44, DOLocationID#45, payment_type#46, fare_amount#47, extra#48, mta_tax#49, tip_amount#50, tolls_amount#51, total_amount#52, congestion_surcharge#53, airport_fee#54, taxi_type#55, to_date('tpep_pickup_datetime, Some(yyyy-MM-dd HH:mm:ss)) AS pickup_date#113]
                  +- Filter ((cast(fare_amount#47 as int) > 50) AND (cast(trip_distance#43 as int) < 1))
                     +- Relation[tpep_pickup_datetime#40,tpep_dropoff_datetime#41,passenger_count#42,trip_distance#43,PULocationID#44,DOLocationID#45,payment_type#46,fare_amount#47,extra#48,mta_tax#49,tip_amount#50,tolls_amount#51,total_amount#52,congestion_surcharge#53,airport_fee#54,taxi_type#55] csv

== Optimized Logical Plan ==
GlobalLimit 21
+- LocalLimit 21
   +- Project [cast(pickup_date#113 as string) AS pickup_date#154, cast(trip_count#149L as string) AS trip_count#155]
      +- Sort [pickup_date#113 ASC NULLS FIRST], true
         +- Aggregate [pickup_date#113], [pickup_date#113, count(1) AS trip_count#149L]
            +- Project [cast(cast(unix_timestamp(tpep_pickup_datetime#40, yyyy-MM-dd HH:mm:ss, Some(GMT)) as timestamp) as date) AS pickup_date#113]
               +- Filter (((((isnotnull(fare_amount#47) AND isnotnull(trip_distance#43)) AND (cast(fare_amount#47 as int) > 50)) AND (cast(trip_distance#43 as int) < 1)) AND (cast(cast(unix_timestamp(tpep_pickup_datetime#40, yyyy-MM-dd HH:mm:ss, Some(GMT)) as timestamp) as date) >= 19389)) AND (cast(cast(unix_timestamp(tpep_pickup_datetime#40, yyyy-MM-dd HH:mm:ss, Some(GMT)) as timestamp) as date) <= 19395))
                  +- Relation[tpep_pickup_datetime#40,tpep_dropoff_datetime#41,passenger_count#42,trip_distance#43,PULocationID#44,DOLocationID#45,payment_type#46,fare_amount#47,extra#48,mta_tax#49,tip_amount#50,tolls_amount#51,total_amount#52,congestion_surcharge#53,airport_fee#54,taxi_type#55] csv

== Physical Plan ==
TakeOrderedAndProject(limit=21, orderBy=[pickup_date#113 ASC NULLS FIRST], output=[pickup_date#154,trip_count#155])
+- *(2) HashAggregate(keys=[pickup_date#113], functions=[count(1)], output=[pickup_date#113, trip_count#149L])
   +- Exchange hashpartitioning(pickup_date#113, 200), true, [id=#94]
      +- *(1) HashAggregate(keys=[pickup_date#113], functions=[partial_count(1)], output=[pickup_date#113, count#159L])
         +- *(1) Project [cast(cast(unix_timestamp(tpep_pickup_datetime#40, yyyy-MM-dd HH:mm:ss, Some(GMT)) as timestamp) as date) AS pickup_date#113]
            +- *(1) Filter (((((isnotnull(fare_amount#47) AND isnotnull(trip_distance#43)) AND (cast(fare_amount#47 as int) > 50)) AND (cast(trip_distance#43 as int) < 1)) AND (cast(cast(unix_timestamp(tpep_pickup_datetime#40, yyyy-MM-dd HH:mm:ss, Some(GMT)) as timestamp) as date) >= 19389)) AND (cast(cast(unix_timestamp(tpep_pickup_datetime#40, yyyy-MM-dd HH:mm:ss, Some(GMT)) as timestamp) as date) <= 19395))
               +- FileScan csv [tpep_pickup_datetime#40,trip_distance#43,fare_amount#47] Batched: false, DataFilters: [isnotnull(fare_amount#47), isnotnull(trip_distance#43), (cast(fare_amount#47 as int) > 50), (cas..., Format: CSV, Location: InMemoryFileIndex[s3a://data-repository-bkt/ECS765/nyc_taxi/yellow_tripdata/2023/yellow_tripdata_..., PartitionFilters: [], PushedFilters: [IsNotNull(fare_amount), IsNotNull(trip_distance)], ReadSchema: struct<tpep_pickup_datetime:string,trip_distance:string,fare_amount:string>