JupySQL is a Python library that lets you run SQL in Jupyter notebooks and the IPython shell.
In this guide, we're going to learn how to query data using chDB and JupySQL.
We're going to use the New York City taxi dataset, which contains around 3 million taxi rides along with the fare, tip, and pickup neighborhood of each one.
The trips are split across several TSV files, so let's start by downloading those:
from urllib.request import urlretrieve
base = "https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi"
for n in range(3):
_ = urlretrieve(
f"{base}/trips_{n}.gz",
f"trips_{n}.gz",
)
If we look back at the schema, a few of the money-related columns — trip_distance, fare_amount, and tip_amount — were inferred as String rather than a numeric type.
We'll clean those up when we import the data into a table.
Now we're going to store the data from these TSV files in a table.
The default database doesn't persist data on disk, so we need to create another database first:
%sql CREATE DATABASE taxi
And now we're going to create a table called trips whose schema will be derived from the structure of the data in the TSV files.
We'll use the REPLACE clause to cast the money-related columns to Float64, and the transform function to turn the numeric pickup_borocode column into a human-readable borough name:
%%sql
CREATE TABLE taxi.trips
ENGINE = MergeTree
ORDER BY pickup_datetime AS
SELECT * REPLACE (
toFloat64OrZero(trip_distance) AS trip_distance,
toFloat64OrZero(fare_amount) AS fare_amount,
toFloat64OrZero(tip_amount) AS tip_amount,
toFloat64OrZero(total_amount) AS total_amount
),
transform(pickup_borocode, [1, 2, 3, 4, 5],
['Manhattan', 'Bronx', 'Brooklyn', 'Queens', 'Staten Island'],
'Unknown') AS pickup_borough
FROM file('trips_*.gz')
SETTINGS schema_inference_make_columns_nullable=0
Just over 3 million trips — let's also bring in a second table.
New York City's Taxi & Limousine Commission divides the city into taxi zones, and a lookup file maps each zone to its borough.
Let's download that file:
And then create a table called zones based on the content of the CSV file:
%%sql
CREATE TABLE taxi.zones
ENGINE = MergeTree
ORDER BY LocationID AS
SELECT * FROM file('taxi_zone_lookup.csv')
SETTINGS schema_inference_make_columns_nullable=0
Once that's finished running, we can have a look at the data we've ingested:
%sql SELECT * FROM taxi.zones LIMIT 5
+------------+---------------+-------------------------+--------------+
| LocationID | Borough | Zone | service_zone |
+------------+---------------+-------------------------+--------------+
| 1 | EWR | Newark Airport | EWR |
| 2 | Queens | Jamaica Bay | Boro Zone |
| 3 | Bronx | Allerton/Pelham Gardens | Boro Zone |
| 4 | Manhattan | Alphabet City | Yellow Zone |
| 5 | Staten Island | Arden Heights | Boro Zone |
+------------+---------------+-------------------------+--------------+
Data ingestion is done, now it's time for the fun part - querying the data!
Each borough is divided into a different number of taxi zones.
We're going to write a query that joins the two tables to find out how many trips were picked up in each borough, and how many trips that works out to per taxi zone:
%%sql
SELECT pickup_borough AS borough,
zone_count,
count() AS trips,
round(count() / zone_count) AS trips_per_zone
FROM taxi.trips
JOIN (
SELECT Borough, count() AS zone_count
FROM taxi.zones
GROUP BY Borough
) AS zones ON pickup_borough = zones.Borough
GROUP BY borough, zone_count
ORDER BY trips DESC
We can save queries using the --save parameter on the same line as the %%sql magic.
The --no-execute parameter means that query execution will be skipped.
%%sql --save tips_by_neighborhood --no-execute
SELECT pickup_ntaname AS neighborhood,
count() AS trips,
round(avg(tip_amount), 2) AS avg_tip
FROM taxi.trips
WHERE fare_amount > 0 AND pickup_ntaname != ''
GROUP BY neighborhood
ORDER BY avg_tip DESC
When we run a saved query it will be converted into a Common Table Expression (CTE) before executing.
In the following query we compute the neighborhoods with the highest average tip:
%sql SELECT * FROM tips_by_neighborhood ORDER BY avg_tip DESC LIMIT 5
We can also use parameters in our queries.
Parameters are just normal variables:
min_trips = 10000
And then we can use the {{variable}} syntax in our query.
The following query finds the neighborhoods with the highest average tip among those with more than 10,000 trips:
%%sql
SELECT * FROM tips_by_neighborhood
WHERE trips >= {{min_trips}}
ORDER BY avg_tip DESC
LIMIT 10
JupySQL also has limited charting functionality.
We can create box plots or histograms.
We're going to create a histogram, but first let's write (and save) a query that returns the distance of each trip under 20 miles.
We'll be able to use this to create a histogram that counts how many trips fall into each distance bucket:
%%sql --save trip_distances --no-execute
SELECT trip_distance
FROM taxi.trips
WHERE trip_distance > 0 AND trip_distance < 20
We can then create a histogram by running the following:
