Query Glue Catalog with Redshift Spectrum

After the external schema is created and duplicate errors are fixed, we can query the data.

Sample Query

SELECT *
FROM taxi_processed.processed_yellow_taxi_trip_data
LIMIT 30;

Query limit 30

Query by Partition

SELECT vendorid, tpep_pickup_datetime, passenger_count, trip_distance, total_amount, trip_duration_min
FROM taxi_processed.processed_yellow_taxi_trip_data
WHERE year = '2025' AND month = '01'
LIMIT 20;

Statistical Query

SELECT year, month, COUNT(*) AS total_trips
FROM taxi_processed.processed_yellow_taxi_trip_data
GROUP BY year, month
ORDER BY year, month;

Analytics Query

SELECT year, month, payment_type, COUNT(*) AS total_trips,
       ROUND(SUM(total_amount), 2) AS total_revenue,
       ROUND(AVG(total_amount), 2) AS avg_revenue,
       ROUND(AVG(trip_distance), 2) AS avg_trip_distance,
       ROUND(AVG(trip_duration_min), 2) AS avg_trip_duration_min
FROM taxi_processed.processed_yellow_taxi_trip_data
GROUP BY year, month, payment_type
ORDER BY year, month, payment_type;

Query results

Redshift Spectrum allows direct queries of Parquet data from S3 through Glue metadata without loading data into Redshift native tables.