Where do people catch taxis in New York?
Start with a Parquet file of taxi trips and a CSV of pickup zones. Join them with SQL to find the busiest places. Sail runs the queries on your laptop, without loading a database or setting up a cluster.
These are the top five zones in the January 2024 NYC green taxi file. The script prints ten. East Harlem North leads, and the median trip distance is 1.79 miles. One record claims a trip of 201,421.68 miles: a useful reminder to check real data.
Run it
You need uv.
Run all commands below from the repository root (sail-examples/):
uv run quickstart/query-files-with-sql/main.py
The first run installs the pinned packages and downloads a 1.4 MB Parquet file
and a 12 KB CSV into quickstart/query-files-with-sql/.data/. No account, API key or GPU is needed.
The output includes an overview, the busiest pickup zones and payment types.
Look for 56551 in the overview's trips column and East Harlem North at the
top of the pickup-zone table. The source code also returns hourly
counts and the file's schema for you to explore.
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.2, 0.7.3. Latest capture: 2026-11-05, Python 3.12.14.
+-----+-------------------+-------------------+------------+---------+
|trips|first_pickup |last_pickup |median_miles|max_miles|
+-----+-------------------+-------------------+------------+---------+
|56551|2023-12-31 14:38:47|2024-01-31 23:57:29|1.79 |201421.68|
+-----+-------------------+-------------------+------------+---------+
+---------------------------+---------+-----+
|pickup_zone |borough |trips|
+---------------------------+---------+-----+
|East Harlem North |Manhattan|12141|
|East Harlem South |Manhattan|8458 |
|Forest Hills |Queens |2915 |
|Central Park |Manhattan|2765 |
|Morningside Heights |Manhattan|2649 |
|Elmhurst |Queens |2623 |
|Central Harlem |Manhattan|2589 |
|Fort Greene |Brooklyn |1898 |
|Downtown Brooklyn/MetroTech|Brooklyn |1624 |
|Astoria |Queens |1521 |
+---------------------------+---------+-----+
+------------+-----+---------+
|payment |trips|tip_share|
+------------+-----+---------+
|Credit card |36660|0.226 |
|Cash |15913|0.0 |
|Not recorded|3415 |0.11 |
|No charge |434 |0.003 |
|Dispute |128 |0.0 |
|Unknown |1 |0.0 |
+------------+-----+---------+
Python code
main.py138 lines
# /// script
# requires-python = ">=3.12"
# dependencies = [
# "pysail==0.7.3",
# "pyspark-client==4.1.3",
# "pyarrow==26.0.0",
# "pandas==3.0.6",
# ]
# ///
"""Query Parquet and CSV files with SQL: no tables to load, no cluster, no Java.
The data is real: every NYC green taxi trip from January 2024 (one Parquet file) and the taxi
zone lookup table (one CSV file), both published by the NYC Taxi and Limousine Commission.
"""
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"
FILES = {
# name: (url, SHA-256 of the file this example was written against)
"green_tripdata_2024-01.parquet": (
f"{SOURCE}/trip-data/green_tripdata_2024-01.parquet",
"1512a953bed564ac68dcb622827424dfb4f68ad93c2a4572e50ef6ab0b09c1ad",
),
"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]:
trips_file, zones_file = download("green_tripdata_2024-01.parquet"), download("taxi_zone_lookup.csv")
# 1. Query a file directly: name the format and the path in the FROM clause.
overview = spark.sql(f"""
SELECT count(*) AS trips,
min(lpep_pickup_datetime) AS first_pickup,
max(lpep_pickup_datetime) AS last_pickup,
median(trip_distance) AS median_miles,
max(trip_distance) AS max_miles
FROM parquet.`{trips_file}`
""")
# 2. Give files a name with temporary views. The CSV view declares its column types.
spark.sql(f"CREATE OR REPLACE TEMPORARY VIEW trips USING parquet OPTIONS (path '{trips_file}')")
spark.sql(f"""
CREATE OR REPLACE TEMPORARY VIEW zones
(LocationID INT, Borough STRING, Zone STRING, service_zone STRING)
USING csv OPTIONS (path '{zones_file}', header 'true')
""")
schema = spark.sql("DESCRIBE TABLE trips")
# 3. Join, group and sort, as in any SQL database.
top_zones = spark.sql("""
SELECT z.Zone AS pickup_zone, z.Borough AS borough, count(*) AS trips
FROM trips t JOIN zones z ON t.PULocationID = z.LocationID
GROUP BY z.Zone, z.Borough
ORDER BY trips DESC, pickup_zone
LIMIT 10
""")
by_hour = spark.sql("""
SELECT hour(lpep_pickup_datetime) AS hour, count(*) AS trips, round(avg(fare_amount), 2) AS avg_fare
FROM trips
GROUP BY 1
ORDER BY 1
""")
# 4. Decode the payment codes (from the TLC data dictionary) and compare tips. The data records
# card tips only, so the tip share is the average tip as a fraction of the fare.
payments = spark.sql("""
SELECT CASE payment_type
WHEN 0 THEN 'Flex Fare trip' WHEN 1 THEN 'Credit card' WHEN 2 THEN 'Cash'
WHEN 3 THEN 'No charge' WHEN 4 THEN 'Dispute' WHEN 5 THEN 'Unknown'
WHEN 6 THEN 'Voided trip' ELSE 'Not recorded'
END AS payment,
count(*) AS trips,
round(avg(CASE WHEN fare_amount > 0 THEN tip_amount / fare_amount END), 3) AS tip_share
FROM trips
GROUP BY 1
ORDER BY trips DESC, payment
""")
return {
"overview": overview,
"schema": schema,
"top_zones": top_zones,
"by_hour": by_hour,
"payments": payments,
}
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 in ("overview", "top_zones", "payments"):
results[name].show(truncate=False)
spark.stop()
server.stop()
The query to keep
Once the script has named the two files trips and zones, this is the query
that answers the question:
SELECT z.Zone AS pickup_zone, z.Borough AS borough, count(*) AS trips
FROM trips t JOIN zones z ON t.PULocationID = z.LocationID
GROUP BY z.Zone, z.Borough
ORDER BY trips DESC, pickup_zone
LIMIT 10
Sail reads the files, joins the rows and computes the counts. You write ordinary Spark SQL; the Python script starts the local engine and prints the results.
Try one change: only Queens
quickstart/query-files-with-sql/main.py, top_zones:
Linemain.py · original lines 90 to 96 top_zones = spark.sql("""
SELECT z.Zone AS pickup_zone, z.Borough AS borough, count(*) AS trips
FROM trips t JOIN zones z ON t.PULocationID = z.LocationID
+ WHERE z.Borough = 'Queens'
GROUP BY z.Zone, z.Borough
ORDER BY trips DESC, pickup_zone
LIMIT 10
""")
From the repository root (sail-examples/), run the edited script:
uv run quickstart/query-files-with-sql/main.py
Output after this change
Current output · Sail 0.7.3
Captured on Sail 0.7.3. Latest capture: 2026-11-06, Python 3.14.10.
+-----+-------------------+-------------------+------------+---------+
|trips|first_pickup |last_pickup |median_miles|max_miles|
+-----+-------------------+-------------------+------------+---------+
|56551|2023-12-31 14:38:47|2024-01-31 23:57:29|1.79 |201421.68|
+-----+-------------------+-------------------+------------+---------+
+----------------+-------+-----+
|pickup_zone |borough|trips|
+----------------+-------+-----+
|Forest Hills |Queens |2915 |
|Elmhurst |Queens |2623 |
|Astoria |Queens |1521 |
|Jackson Heights |Queens |1398 |
|Jamaica |Queens |1339 |
|Woodside |Queens |981 |
|Kew Gardens |Queens |649 |
|Elmhurst/Maspeth|Queens |523 |
|Flushing |Queens |380 |
|Steinway |Queens |370 |
+----------------+-------+-----+
+------------+-----+---------+
|payment |trips|tip_share|
+------------+-----+---------+
|Credit card |36660|0.226 |
|Cash |15913|0.0 |
|Not recorded|3415 |0.11 |
|No charge |434 |0.003 |
|Dispute |128 |0.0 |
|Unknown |1 |0.0 |
+------------+-----+---------+
How it works
- Read a file directly.
FROM parquet.`path`queries a Parquet file without an import step. - Give files names. Temporary views let later queries refer to
tripsandzones. The CSV view declares its types and skips the header. - Join and summarize. A pickup-zone ID connects each trip to a place name. Grouping counts the trips there.
- Compare payment types. The script translates payment codes into names and calculates recorded tip shares. The source records card tips but excludes cash tips, so a zero cash-tip share does not mean people never tip in cash.
Use your own files
Change the input paths. A path can be a folder or a glob such as
data/2024-*.parquet to read several files as one table.
The preview counts the supplied records as they are. It does not remove implausible trips. The taxi ETL guide shows how to validate records and retain rejects.
Sail and Spark details
Declaring CSV types keeps results consistent between Sail and Apache Spark.
With inferSchema, Sail reads whole-number columns as BIGINT, while Spark reads
them as INT. A direct csv.`path` query keeps the header as a data row;
use the declared view shown in the script for this file.
Data
NYC TLC Trip Record Data,
green taxi trips for January 2024 and the taxi zone lookup. Field definitions
are in the green taxi data dictionary.
The preview shows the top_zones output from the pinned example.