Sail examples

Appearance

beginnerView Python source

Explore a year of taxi trips with SQL

This example analyzes every NYC green taxi trip record from 2024, 660,218 records in twelve monthly Parquet files (16 MB). It reads the folder as one table, keeps the plausible trips, and answers the questions an analyst asks first, each with one SQL query.

Run it

Run all commands below from the repository root (sail-examples/):

uv run analytics/explore-taxi-trips/main.py

The first run downloads the files into analytics/explore-taxi-trips/.data/.

Of the 660,218 records, 635,426 pass the checks. Trips peak on weekday evenings: the busiest hour of the week is Thursday at 5 pm. Manhattan has the most pickups, and East Harlem North is its busiest zone.

Output

The newest capture appears first. Older output stays here when a result changes.

Current output · Sail 0.7.3

Captured on Sail 0.7.3. Latest capture: 2026-11-05, Python 3.12.14.

cleaning
+-------+------+-------+
|records|kept  |dropped|
+-------+------+-------+
|660218 |635426|24792  |
+-------+------+-------+

monthly
+-----+-----+----------+--------------------------+
|month|trips|fares     |change_from_previous_month|
+-----+-----+----------+--------------------------+
|1    |54592|916368.74 |NULL                      |
|2    |51757|876578.54 |-0.0519                   |
|3    |55509|956792.12 |0.0725                    |
|4    |54423|952515.09 |-0.0196                   |
|5    |58725|1082830.56|0.079                     |
|6    |52693|986915.97 |-0.1027                   |
|7    |49719|913957.82 |-0.0564                   |
|8    |49608|966984.12 |-0.0022                   |
|9    |52249|1052502.82|0.0532                    |
|10   |54070|1013853.08|0.0349                    |
|11   |50275|903758.94 |-0.0702                   |
|12   |51806|923518.21 |0.0305                    |
+-----+-----+----------+--------------------------+

busiest_hours
+---------+----------+----+-----+
|day      |day_number|hour|trips|
+---------+----------+----+-----+
|Thursday |5         |17  |8921 |
|Wednesday|4         |17  |8559 |
|Friday   |6         |17  |8236 |
|Thursday |5         |18  |8235 |
|Tuesday  |3         |17  |7949 |
+---------+----------+----+-----+

boroughs
+-------------+------+-----------+---------+----------+
|borough      |trips |median_fare|p90_miles|card_share|
+-------------+------+-----------+---------+----------+
|Manhattan    |381224|12.8       |4.91     |0.7397    |
|Queens       |155060|15.6       |7.85     |0.5691    |
|Brooklyn     |89781 |17.7       |7.78     |0.735     |
|Bronx        |8622  |22.5       |9.55     |0.5264    |
|Unknown      |416   |13.75      |6.04     |0.5601    |
|N/A          |296   |50.0       |21.23    |0.4088    |
|Staten Island|23    |33.84      |20.37    |0.4783    |
|EWR          |4     |137.0      |0.63     |0.25      |
+-------------+------+-----------+---------+----------+

top_zones
+-------------+---------------------------+------+----+
|borough      |zone                       |trips |rank|
+-------------+---------------------------+------+----+
|Bronx        |West Concourse             |1872  |1   |
|Bronx        |Melrose South              |651   |2   |
|Brooklyn     |Fort Greene                |21087 |1   |
|Brooklyn     |Downtown Brooklyn/MetroTech|18648 |2   |
|EWR          |Newark Airport             |4     |1   |
|Manhattan    |East Harlem North          |147612|1   |
|Manhattan    |East Harlem South          |90116 |2   |
|N/A          |Outside of NYC             |296   |1   |
|Queens       |Forest Hills               |31792 |1   |
|Queens       |Elmhurst                   |29515 |2   |
|Staten Island|Saint George/New Brighton  |10    |1   |
|Staten Island|Arrochar/Fort Wadsworth    |2     |2   |
+-------------+---------------------------+------+----+
only showing top 12 rows

Python code

main.py170 lines
View main.py on GitHub 
# /// script
# requires-python = ">=3.12"
# dependencies = [
#   "pysail==0.7.3",
#   "pyspark-client==4.1.3",
#   "pyarrow==26.0.0",
#   "pandas==3.0.6",
# ]
# ///
"""Explore a year of real taxi trips with SQL: clean, trend, compare and rank.

The data is every NYC green taxi trip record from 2024 (twelve monthly Parquet files) and the
taxi zone lookup, published by the NYC Taxi and Limousine Commission. The questions are the
ones an analyst asks first; each answer is one SQL query.
"""

import hashlib
import os
import tempfile
import urllib.request
from pathlib import Path

from pyspark.sql import DataFrame, SparkSession

DATA = Path(__file__).resolve().parent / ".data"
SOURCE = "https://d37ci6vzurychx.cloudfront.net"
# SHA-256 of each file this example was written against.
MONTHS = {
    "01": "1512a953bed564ac68dcb622827424dfb4f68ad93c2a4572e50ef6ab0b09c1ad",
    "02": "cb7eb405ee7e795c9fd6b8ad1b291f46f4e0a1cd60a57067b0aaca2420e8bc5d",
    "03": "43dedd66dd556acfc38da679a174b6adc32e01dd6dccad14bdfdf1521a14abed",
    "04": "af174977258a02f8f44c37534abc9a8b4eaa5ec6d2f9c2bc49782327628b608c",
    "05": "aa8b1965ed36f0bda1b342930d5e6f5a89f8fe56d0c6c6d77d9c156b408aa0ea",
    "06": "47e7c37dd39069c474364aee3d76c9ddc887a3f714e9bb550b5866fe0b89b9cd",
    "07": "ce0d7591316050bea21da49e383eb733411e98ae17e114afcc7fbe4b36b116f2",
    "08": "733210fb27cbe8cf8b0ad9f5d6fff03dfafcc8ff7c2beb1c1b707825d72f0376",
    "09": "8f6aa8f3e8a2040fc161c7fbcb60e5418238b27e7e95998363572a4c977b69ce",
    "10": "6e737b7c4edac91f865aa210f7110d0118a9992b3a96720936e090277c24edaa",
    "11": "fde255b43d4158124921b7c085d9abf15de5c5a9128c2ce0fb4e2372bc1e0d4a",
    "12": "1d708f58d29b0795a54ca11a36db6df61262986d122985f0fb52a56acc064bac",
}
FILES = {
    f"trips/green_tripdata_2024-{m}.parquet": (f"{SOURCE}/trip-data/green_tripdata_2024-{m}.parquet", sha)
    for m, sha in MONTHS.items()
}
FILES["taxi_zone_lookup.csv"] = (
    f"{SOURCE}/misc/taxi_zone_lookup.csv",
    "1a99e105092230f8620f301edcca7f80d3080642ff404d28ed957d3fa222c8ed",
)


def download(name: str) -> Path:
    """Reuse or download a file only when its SHA-256 matches the pinned input."""
    url, expected = FILES[name]
    path = DATA / name
    if not path.exists():
        path.parent.mkdir(parents=True, exist_ok=True)
        temporary = None
        try:
            with (
                urllib.request.urlopen(url, timeout=120) as response,
                tempfile.NamedTemporaryFile(dir=path.parent, prefix=f".{path.name}.", delete=False) as out,
            ):
                temporary = Path(out.name)
                while chunk := response.read(1024 * 1024):
                    out.write(chunk)
            with temporary.open("rb") as downloaded:
                if hashlib.file_digest(downloaded, "sha256").hexdigest() != expected:
                    raise ValueError(f"SHA-256 mismatch: {name}")
            os.replace(temporary, path)
        finally:
            if temporary is not None:
                temporary.unlink(missing_ok=True)
    with path.open("rb") as cached:
        if hashlib.file_digest(cached, "sha256").hexdigest() != expected:
            raise ValueError(f"SHA-256 mismatch: {name}")
    return path


def run(spark: SparkSession) -> dict[str, DataFrame]:
    for name in FILES:
        download(name)

    # A folder of Parquet files reads as one table.
    spark.sql(f"CREATE OR REPLACE TEMPORARY VIEW raw_trips USING parquet OPTIONS (path '{DATA / 'trips'}')")
    spark.sql(f"""
        CREATE OR REPLACE TEMPORARY VIEW zones
            (LocationID INT, Borough STRING, Zone STRING, service_zone STRING)
        USING csv OPTIONS (path '{DATA / "taxi_zone_lookup.csv"}', header 'true')
    """)

    # 1. Keep plausible trips: picked up in 2024, 1 to 180 minutes, up to 100 miles, no negative fare.
    spark.sql("""
        CREATE OR REPLACE TEMPORARY VIEW trips AS
        SELECT *, timestampdiff(MINUTE, lpep_pickup_datetime, lpep_dropoff_datetime) AS minutes
        FROM raw_trips
        WHERE year(lpep_pickup_datetime) = 2024
          AND timestampdiff(MINUTE, lpep_pickup_datetime, lpep_dropoff_datetime) BETWEEN 1 AND 180
          AND trip_distance BETWEEN 0 AND 100
          AND fare_amount >= 0
    """)
    cleaning = spark.sql("""
        SELECT (SELECT count(*) FROM raw_trips) AS records,
               (SELECT count(*) FROM trips) AS kept,
               (SELECT count(*) FROM raw_trips) - (SELECT count(*) FROM trips) AS dropped
    """)

    # 2. Trend: trips and fares per month, with the change from the month before.
    monthly = spark.sql("""
        SELECT month, trips, fares,
               round((trips - lag(trips) OVER (ORDER BY month)) / lag(trips) OVER (ORDER BY month), 4)
                   AS change_from_previous_month
        FROM (SELECT month(lpep_pickup_datetime) AS month, count(*) AS trips, round(sum(fare_amount), 2) AS fares
              FROM trips GROUP BY 1)
        ORDER BY month
    """)

    # 3. When: the five busiest hours of the week. dayofweek() numbers Sunday 1 to Saturday 7.
    busiest_hours = spark.sql("""
        SELECT date_format(lpep_pickup_datetime, 'EEEE') AS day, dayofweek(lpep_pickup_datetime) AS day_number,
               hour(lpep_pickup_datetime) AS hour, count(*) AS trips
        FROM trips
        GROUP BY 1, 2, 3
        ORDER BY trips DESC, day_number, hour
        LIMIT 5
    """)

    # 4. Where: exact medians and percentiles per pickup borough.
    boroughs = spark.sql("""
        SELECT z.Borough AS borough, count(*) AS trips,
               median(t.fare_amount) AS median_fare,
               round(percentile(t.trip_distance, 0.9), 2) AS p90_miles,
               round(avg(CASE WHEN t.payment_type = 1 THEN 1.0D ELSE 0.0D END), 4) AS card_share
        FROM trips t JOIN zones z ON t.PULocationID = z.LocationID
        GROUP BY z.Borough
        ORDER BY trips DESC, borough
    """)

    # 5. Rank: the top two pickup zones within each borough, with a window function.
    top_zones = spark.sql("""
        SELECT borough, zone, trips, rank
        FROM (SELECT z.Borough AS borough, z.Zone AS zone, count(*) AS trips,
                     rank() OVER (PARTITION BY z.Borough ORDER BY count(*) DESC) AS rank
              FROM trips t JOIN zones z ON t.PULocationID = z.LocationID
              GROUP BY z.Borough, z.Zone)
        WHERE rank <= 2
        ORDER BY borough, rank, zone
    """)
    return {
        "cleaning": cleaning,
        "monthly": monthly,
        "busiest_hours": busiest_hours,
        "boroughs": boroughs,
        "top_zones": top_zones,
    }


if __name__ == "__main__":
    from pysail.spark import SparkConnectServer

    server = SparkConnectServer()
    server.start()
    _, port = server.listening_address
    spark = SparkSession.builder.remote(f"sc://localhost:{port}").getOrCreate()
    results = run(spark)
    for name, frame in results.items():
        print(name)
        frame.show(12, truncate=False)
    spark.stop()
    server.stop()

How it works

  1. A folder is a table. CREATE TEMPORARY VIEW raw_trips USING parquet OPTIONS (path 'trips') reads all twelve files as one table.
  2. Clean before you count. A second view keeps trips picked up in 2024 that last 1 to 180 minutes, cover at most 100 miles, and have no negative fare. Real records include a trip of 201,421 miles and pickups dated 2008 and 2025, so averages without this step mislead. The cleaning result shows how many records the checks kept and dropped.
  3. Trend with a window function. Trips and fares are grouped by month, and lag(trips) OVER (ORDER BY month) gives the previous month, so each row shows its change.
  4. Peak hours. Trips are grouped by day of the week and hour. dayofweek() numbers Sunday 1 to Saturday 7, and date_format(ts, 'EEEE') gives the day's name.
  5. Exact statistics. median() and percentile() compute exact values per borough, and a CASE inside avg gives the share of card payments (payment type 1 in the TLC data dictionary).
  6. Rank within groups. rank() OVER (PARTITION BY borough ORDER BY count(*) DESC) numbers the zones inside each borough, and the outer query keeps the top two.

The zone lookup includes the boroughs Unknown and N/A (outside NYC), so they appear in the results too.

On Sail

  • Use dayofweek() for the day of the week. EXTRACT(DOW FROM ts) numbers Sunday 0 on Sail and 1 on Apache Spark.
  • Use percentile() and median() when results must be exact. percentile_approx can return different approximations on Sail and on Apache Spark.

Data

NYC TLC Trip Record Data, green taxi trips for January to December 2024, and the taxi zone lookup table. Codes are defined in the TLC's data dictionary for green taxi trips.