How to Query Remote GeoParquet over HTTP with DuckDB
Problem statement
The dataset is 446 MB and it is on someone else's server. The conventional workflow downloads it, converts it, and queries it — twenty minutes and half a gigabyte of disk before the first question is answered.
DuckDB can query it where it is. With the httpfs extension it issues HTTP range requests and reads only the bytes the query needs. Measured against a byte-counting server, on a 446 MB Parquet file with 13,464,017 rows:
query bytes read share of file requests time
count(*) 0.26 MB 0.06% 2 0.01 s
count(distinct country) 0.27 MB 0.06% 111 2.09 s
where country = 'AD' 0.26 MB 0.06% 4 0.01 s
top 5 by population 2.83 MB 0.64% 52 2.28 s
group by floor(lon), floor(lat) 108.11 MB 24.24% 111 2.07 s
count + max(length(alternatenames)) 115.62 MB 25.92% 111 1.25 s
Three of those six queries read less than one thousandth of the file. That is the difference between downloading a dataset and asking it a question.
Quick answer
Load httpfs and put a URL where a path would go:
import duckdb
con = duckdb.connect()
con.execute("install httpfs; load httpfs;")
con.execute("install spatial; load spatial;")
con.execute("""
select country, count(*) as n
from read_parquet('https://example.org/data/places.parquet')
where country in ('GB', 'FR', 'DE')
group by 1
""").df()For S3-compatible storage:
con.execute("set s3_region = 'us-west-2'")
con.execute("""
select count(*) from read_parquet('s3://bucket/prefix/*.parquet')
where bbox.xmin between -74.02 and -73.93
""").fetchone()Measured against a real public bucket — Overture Maps places, theme=places/type=place — a Manhattan bounding-box count returned 173,023 rows in 6.8 seconds without downloading the dataset.
Step-by-step solution
1. Install httpfs, and know what it enables
httpfs teaches DuckDB to read http://, https:// and s3:// URLs by issuing HTTP range requests. Without it, a URL is just an unreadable path.
con.execute("install httpfs") # one download per DuckDB version
con.execute("load httpfs") # every connectionIt works with read_parquet, read_csv and DuckDB's own database files. ST_Read — the GDAL path — has its own remote handling through GDAL's virtual file systems, which is a different mechanism with different behaviour.
2. Understand what makes remote reads cheap
Three mechanisms, all of them properties of the Parquet format:
- The footer holds the schema and statistics. DuckDB reads it first — a couple of hundred kilobytes — and knows the row count, the columns and each row group's minimum and maximum per column.
- Column pruning. Only the columns the query names are fetched. A
count(*)needs none of them. - Row-group pruning. A filter whose value falls outside a row group's recorded range skips that group entirely.
The count(*) in the opening table read 0.26 MB in two requests: the footer, and nothing else. The row count was already in the metadata.
3. Sort the file if you control it — it changes everything
Row-group pruning only works if the values are clustered. The same 13.5-million-row file, sorted by the filter column and shuffled, answering the same query:
sorted shuffled
bytes read 0.26 MB 10.25 MB
requests 4 222
time 0.01 s 2.28 s
file size 446 MB 741 MB
Thirty-nine times the bytes, two hundred times the time, and a 66% larger file — for identical rows. Sorting on write is the single most effective thing a publisher can do for their readers.
4. Name your columns
select * fetches every column. On the measured file, the difference between a two-column aggregate and one touching the widest column was 108 MB against 116 MB — but against a count(*) at 0.26 MB, both are enormous.
The discipline is the same as for any columnar file and matters much more over a network: every column named is a network transfer.
5. Filter on the partition or the sort key, not on a derived value
A filter DuckDB can push into the pruning logic must compare a column against a constant:
where country = 'AD' -- prunes row groups
where upper(country) = 'AD' -- cannot prune; reads everything
where lat between 51 and 52 -- prunes if sorted or clustered by latWrapping the column in a function defeats the statistics, because the statistics are about the column's values, not the function's.
6. Cache deliberately
DuckDB keeps an external file cache within a connection, so a second identical query can read nothing at all. That is helpful interactively and misleading in a benchmark:
con.execute("set enable_external_file_cache = false") # for honest measurementFor repeated work against a stable remote file, the opposite applies — leave the cache on, or copy the file locally once.
Code examples
Example 1 — measuring what a remote query actually costs
import http.server
import os
import re
import socketserver
import threading
COUNT = {"bytes": 0, "requests": 0}
class ByteCountingHandler(http.server.SimpleHTTPRequestHandler):
"""Serves a directory and counts exactly what each client pulled."""
def log_message(self, *args):
pass
def do_GET(self):
path = self.translate_path(self.path)
if not os.path.isfile(path):
self.send_error(404)
return
size = os.path.getsize(path)
rng = self.headers.get("Range")
if rng:
match = re.match(r"bytes=(\d+)-(\d*)", rng)
start = int(match.group(1))
end = int(match.group(2)) if match.group(2) else size - 1
end = min(end, size - 1)
length = end - start + 1
self.send_response(206)
self.send_header("Content-Range", f"bytes {start}-{end}/{size}")
else:
start, length = 0, size
self.send_response(200)
self.send_header("Content-Length", str(length))
self.send_header("Accept-Ranges", "bytes")
self.end_headers()
with open(path, "rb") as handle:
handle.seek(start)
self.wfile.write(handle.read(length))
COUNT["bytes"] += length
COUNT["requests"] += 1
def serve(directory, port=8899):
handler = lambda *a, **kw: ByteCountingHandler(*a, directory=directory, **kw)
server = socketserver.ThreadingTCPServer(("127.0.0.1", port), handler)
server.allow_reuse_address = True
threading.Thread(target=server.serve_forever, daemon=True).start()
return serverdef measure(sql, url_size):
import duckdb, time
con = duckdb.connect()
con.execute("install httpfs; load httpfs;")
con.execute("set enable_external_file_cache = false") # or the numbers lie
COUNT["bytes"] = COUNT["requests"] = 0
started = time.perf_counter()
result = con.execute(sql).fetchall()
print(f"{COUNT['bytes'] / 1e6:8.2f} MB {COUNT['requests']:4} requests "
f"{time.perf_counter() - started:5.2f}s "
f"({100 * COUNT['bytes'] / url_size:5.2f}% of the file)")
return resultRunning this against your own published files is the only way to know whether they are actually cloud-friendly.
Example 2 — reading from S3-compatible storage
import duckdb
def s3_connection(region="us-east-1", anonymous=True, endpoint=None):
con = duckdb.connect()
con.execute("install httpfs; load httpfs;")
con.execute("install spatial; load spatial;")
con.execute("set enable_progress_bar = false")
con.execute(f"set s3_region = '{region}'")
if endpoint: # MinIO, R2, other S3 APIs
con.execute(f"set s3_endpoint = '{endpoint}'")
con.execute("set s3_url_style = 'path'")
if not anonymous:
con.execute("set s3_access_key_id = getenv('AWS_ACCESS_KEY_ID')")
con.execute("set s3_secret_access_key = getenv('AWS_SECRET_ACCESS_KEY')")
return con
def overture_places_in_bbox(con, xmin, ymin, xmax, ymax,
release="2026-08-19.0"):
"""A bounding-box query against a public multi-terabyte dataset."""
return con.execute(f"""
select count(*) from read_parquet(
's3://overturemaps-us-west-2/release/{release}/theme=places/type=place/*.parquet')
where bbox.xmin between {xmin} and {xmax}
and bbox.ymin between {ymin} and {ymax}
""").fetchone()[0]>>> con = s3_connection(region="us-west-2")
>>> overture_places_in_bbox(con, -74.02, 40.70, -73.93, 40.79)
173023 # 6.8 seconds, nothing downloadedThe bbox struct column is why this is fast: it is a plain numeric column with row-group statistics, so the filter prunes before any geometry is touched.
Example 3 — deciding whether to download
def should_download(con, url, queries_expected=1, bandwidth_mb_s=50):
"""Compare downloading once with reading remotely every time."""
size_bytes = con.execute(
f"select total_compressed_size from parquet_metadata('{url}') limit 1"
).fetchone()
footer = con.execute(f"select count(*) from parquet_metadata('{url}')").fetchone()[0]
import duckdb
file_mb = con.execute(
f"select sum(total_compressed_size) / 1e6 from parquet_metadata('{url}')"
).fetchone()[0]
download_s = file_mb / bandwidth_mb_s
print(f"file about {file_mb:,.0f} MB across {footer} column chunks")
print(f"downloading once: ~{download_s:,.0f}s")
print(f"reading remotely: cheap if your queries touch few columns and "
f"the file is sorted on your filter column")
print(f"rule of thumb: download if you will run more than about "
f"{max(2, int(download_s / 5))} full-column queries")Explanation
Why the footer makes count(*) free
A Parquet file ends with a footer containing the schema, the number of rows, and per-column statistics for every row group. DuckDB fetches the last few kilobytes, reads the footer, and for count(*) it already has the answer.
That is the 0.26 MB in two requests. No column data was transferred at all, because none was needed — the count is metadata.
Why one query read 24% of the file and another read 0.06%
group by floor(lon), floor(lat) needs every value of two columns, so it reads both columns in full: 108 MB. count(distinct country) needs every value of one column too — but that column is dictionary-encoded with 253 distinct values across 13.4 million rows, so its entire storage across 109 row groups is 270 kB.
The lesson is that "reads a whole column" and "transfers a lot of data" are not the same thing. Low-cardinality columns are nearly free; high-cardinality ones are not.
Why sorting matters more remotely than locally
Locally, reading 10 MB instead of 0.26 MB costs a few milliseconds of page-cache access. Over a network it costs 222 round trips and two seconds — and on a high-latency link, considerably more.
That is why the sorted-versus-shuffled measurement is a 200× time difference remotely and a much smaller one locally. Cloud-native performance is dominated by round trips, and pruning is what removes them.
Why this changes how datasets are published
A publisher who sorts by the column readers filter on, uses sensible row-group sizes, and includes a bbox struct for spatial filtering turns a multi-terabyte archive into something a laptop can query in seconds.
The Overture example is exactly that: a public dataset of hundreds of millions of features, queried for a Manhattan bounding box in 6.8 seconds from a Python process, because the file layout was designed for it.
Edge cases or notes
httpfsandspatialare separate extensions — load both for remote spatial work.- The server must support range requests. Without
Accept-Ranges, DuckDB downloads the whole file. - Disable the external file cache when benchmarking, or the second query reads nothing and looks miraculous.
ST_Readon a URL goes through GDAL, nothttpfs, and behaves differently.- Wrapping a filter column in a function defeats pruning.
- Glob patterns over S3 list the prefix first, which can be slow on very large buckets.
- Anonymous S3 access needs the region set and nothing else for public buckets.
- Copy locally if you will scan the whole file repeatedly — remote reads are for selective queries.
Internal links
- Cloud-native geospatial explained — the idea behind range requests
- How to query hive-partitioned GeoParquet in DuckDB — pruning at the file level
- Columnar or row storage: why DuckDB is fast on wide tables — why column pruning works
- How to install and load the DuckDB spatial extension — installing httpfs too
- How to read spatial data from S3 — the GDAL route
- Fixing GDAL that cannot open an S3 URL — when the other route fails
- GeoParquet and columnar storage explained — the format
- How to read a COG from a URL — the raster equivalent
FAQ
Can DuckDB query a Parquet file over HTTP?
Yes, with the httpfs extension. It issues range requests and reads only the bytes the query needs — measured, a count(*) on a 446 MB file read 0.26 MB.
How much data does a remote query actually transfer?
It depends entirely on the query. Measured on one file: 0.06% for a count or a filtered count, 0.64% for a top-five query, and 24% for an aggregate over two full columns.
Why is my remote query slow?
Usually because the file is not sorted on the column you filter by, so no row groups can be pruned. The same query read 0.26 MB from a sorted file and 10.25 MB from a shuffled one.
Do I need to download the file first?
Not for selective queries. Download when you will repeatedly scan whole columns — at that point the transfer happens anyway and local access is faster.
Does this work with S3?
Yes. Set s3_region and use an s3:// URL. A bounding-box count against the public Overture places dataset returned 173,023 rows in 6.8 seconds.
Why did my second query read nothing?
DuckDB caches remote file chunks within a connection. Set enable_external_file_cache = false when measuring, and leave it on in normal use.