48.5k
To query a REST API in Python, use chDB to read the JSON response directly into a DataFrame:
from chdb.datastore import DataStore
df = DataStore.from_url("https://api.example.com/orders", format="JSONEachRow")df supports the pandas API you already use (indexing, filtering, groupby, merge), but the work runs on ClickHouse's engine instead of pandas, so it stays fast as files grow. There is no server to start and no separate load step.
pip install chdbPrefer the command line? See how to query a REST API with SQL to do this with clickhouse local.
Read a JSON API response into a DataFrame
The companion example serves sample data over Python's built-in http.server. That stand-in for a real API shows the exact call you would make against any live JSON endpoint.
from chdb.datastore import DataStore
df = DataStore.from_url("http://127.0.0.1:8731/orders.json", format="JSONEachRow")
print(df)
print(df.dtypes)id country status amount labels
0 0 GB open 10.99 [emea, gold]
1 1 AU closed 12.36 [apac, silver]
2 2 IN pending 13.73 [amer, bronze]
3 3 US open 15.10 [emea, gold]
4 4 DE closed 16.47 [apac, silver]
id int64
country str
status str
amount float64
labels object
dtype: objectchDB infers the schema from the response: id comes back as an integer, amount as a float. The nested labels field comes back as an object column (a numpy array per row when you materialize). You declared nothing.
One thing is different from a regular DataFrame: df is not the response loaded into memory. It is a lazy, ClickHouse-backed object that records what you asked for and runs on ClickHouse's engine when you materialize a result, whether you print it, take len(), or call .to_pandas(). That is why it stays fast on large responses.
Filter and aggregate the way you already do
The pandas you write does not change. Filter with a boolean mask, group, and aggregate:
from chdb.datastore import DataStore
df = DataStore.from_url("http://127.0.0.1:8731/orders.json", format="JSONEachRow")
revenue = (
df[df["status"] != "closed"]
.groupby("country")["amount"]
.sum()
.sort_values(ascending=False)
)
print(revenue.to_pandas())country
US 15.10
IN 13.73
GB 10.99
Name: amount, dtype: float64Same syntax as pandas, same result. chDB compiles the whole chain into one optimized query and only materializes what you asked for, rather than pulling the full response into memory first. Joins with merge, computed columns with assign, and the .str and .dt accessors all work the same way.
Hand off to real pandas when you need it
When a library downstream needs an actual pandas DataFrame (scikit-learn, a plotting call, anything that mutates in place), call .to_pandas() to materialize one:
df = DataStore.from_url("http://127.0.0.1:8731/orders.json", format="JSONEachRow")
pdf = df.to_pandas() # a real pandas.DataFrame, in memoryThe usual pattern is to do the heavy filtering and aggregation on the chDB object first, then .to_pandas() the small result and continue in ordinary pandas.
Multiple pages: read each URL, then concat
When an API paginates across predictable URLs, read each page into a DataFrame and concat them:
import pandas as pd
from chdb.datastore import DataStore
page1 = DataStore.from_url("http://127.0.0.1:8731/orders_page1.json", format="JSONEachRow").to_pandas()
page2 = DataStore.from_url("http://127.0.0.1:8731/orders_page2.json", format="JSONEachRow").to_pandas()
pages = pd.concat([page1, page2], ignore_index=True)
print(pages.groupby("country")["amount"].sum().sort_values(ascending=False))country
IN 1.82
US 1.06
AU 0.91
DE 0.53
GB 0.00
Name: amount, dtype: float64Is it faster than requests + json?
On a large response, yes. The same filter-and-aggregate over a 2M-row (~120 MB) JSON endpoint, served on localhost so network latency is out of the picture, best-of-3 with a warm cache on an Apple M4 Pro (14 cores, 24 GB RAM, macOS):
requests + json + manual agg: 2.067s
DataStore.from_url (chDB): 0.162s
speedup: 12.8xAbout 12-13x faster here. The gap is fetch-plus-parse-plus-aggregate cost: chDB streams the response and aggregates as it goes, while the Python loop decodes every line into objects before it can do anything with them. Against a real API the network round trip adds equally to both sides, so it does not change the ratio; it just raises the floor.
The back-to-back ratio is the robust claim; absolute times shift with concurrent load. On a tiny response the comparison can flip, because chDB pays a small fixed per-query cost that dominates when there is barely any data. Reach for DataStore.from_url when the payload is large enough that parsing and aggregating it is the real work.
Hardware: Apple M4 Pro (14 cores, 24 GB RAM, macOS); chDB 4.1.8, Python 3.14; best-of-3, warm.
Works in Jupyter
Printing a chDB object renders it as a table in a notebook, and .to_pandas() feeds straight into .plot(), joins, or any pandas code. The companion folder ships a run.ipynb you can open and run cell by cell.
Run it yourself
The complete, runnable example is here, with generate.sh to create the sample JSON files, plus run.ipynb and a run.py mirror containing the exact code above:
github.com/ClickHouse/examples/tree/main/local-analytics/chdb-rest-api
Working with JSON from other sources? Read a JSON file in Python and read a JSON Lines file in Python use the same pandas drop-in pattern; only the reader changes. The same code scales from a local script to ClickHouse Cloud with no rewrite.
