How to Query Remote GeoParquet over HTTP with DuckDB
On this page
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,472,195 rows (Example 1):
query bytes read share of file requests time
count(*) 0.26 MB 0.06% 1 0.006 s
count(distinct country) 0.27 MB 0.06% 110 1.246 s
where country = 'AD' 0.26 MB 0.06% 3 0.005 s
top 5 by population 2.76 MB 0.62% 48 1.466 s
group by floor(lon), floor(lat) 108.19 MB 24.24% 110 1.253 s
count + max(length(alternatenames)) 116.11 MB 26.01% 110 2.078 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 224,648 places in 9.1 seconds from the 2026-09-23.1 release, without downloading the dataset (Example 2).
What you get — from The data used on this page
13,472,195 rows; full.parquet 446 MB, shuffled.parquet 741 MB
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.
import duckdb
con = duckdb.connect()
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 one request: 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.26 MB
requests 3 221
time 0.005 s 1.492 s
file size 446 MB 741 MB
Thirty-nine times the bytes, about three hundred times the time, and a 66% larger file — for identical rows (Example 1). 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. Measured, upper(country) = 'AD' made 110 requests — every row group of the column — where country = 'AD' made 3, and took 1.25 s instead of 0.005 s. The bytes stayed small only because the country column is tiny.
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
The data used on this page
The file is GeoNames' full allCountries dump — every named place on Earth, about 13.5 million rows, rebuilt daily at download.geonames.org — written with all 19 of its columns to full.parquet in the order the dump lists them (which groups rows by country), and again with the rows shuffled to shuffled.parquet. Same rows, same schema; only the order differs.
import os
import zipfile
import duckdb
import requests
if not os.path.exists("allCountries.txt"):
if not os.path.exists("allCountries.zip"):
with requests.get("https://download.geonames.org/export/dump/allCountries.zip",
stream=True, timeout=600) as reply: # about 420 MB
reply.raise_for_status()
with open("allCountries.zip", "wb") as out:
for chunk in reply.iter_content(1 << 20):
out.write(chunk)
zipfile.ZipFile("allCountries.zip").extract("allCountries.txt")
COLUMNS = ("geonameid BIGINT, name VARCHAR, asciiname VARCHAR, alternatenames VARCHAR, lat DOUBLE, lon DOUBLE, "
"feature_class VARCHAR, feature_code VARCHAR, country VARCHAR, cc2 VARCHAR, admin1 VARCHAR, "
"admin2 VARCHAR, admin3 VARCHAR, admin4 VARCHAR, population BIGINT, elevation VARCHAR, dem INTEGER, "
"timezone VARCHAR, modified DATE")
TSV = ("read_csv('allCountries.txt', delim = '\\t', header = false, quote = '', columns = {"
+ ", ".join(f"'{c.split()[0]}': '{c.split()[1]}'" for c in COLUMNS.split(", ")) + "})")
if not os.path.exists("full.parquet"):
duckdb.sql(f"copy (select * from {TSV}) to 'full.parquet' (format parquet, compression zstd)")
if not os.path.exists("shuffled.parquet"):
duckdb.sql("select setseed(0.42)")
duckdb.sql("copy (select * from read_parquet('full.parquet') order by random()) "
"to 'shuffled.parquet' (format parquet, compression zstd)")
rows = duckdb.sql("select count(*) from read_parquet('full.parquet')").fetchone()[0]
print(f"{rows:,} rows; full.parquet {os.path.getsize('full.parquet') / 1e6:.0f} MB, "
f"shuffled.parquet {os.path.getsize('shuffled.parquet') / 1e6:.0f} MB")13,472,195 rows; full.parquet 446 MB, shuffled.parquet 741 MB
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
class Server(socketserver.ThreadingTCPServer):
allow_reuse_address = True # must be set before the socket binds, so on the class
daemon_threads = True
def serve(directory, port=0): # 0: let the OS pick a free port
handler = lambda *a, **kw: ByteCountingHandler(*a, directory=directory, **kw)
server = Server(("127.0.0.1", port), handler)
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:6.3f}s "
f"({100 * COUNT['bytes'] / url_size:5.2f}% of the file)")
return resultServing the working directory and running the six queries from the top of the page:
server = serve(".")
BASE = f"http://127.0.0.1:{server.server_address[1]}"
URL = f"{BASE}/full.parquet"
SIZE = os.path.getsize("full.parquet")
SRC = f"read_parquet('{URL}')"
QUERIES = {
"count(*)": f"select count(*) from {SRC}",
"count(distinct country)": f"select count(distinct country) from {SRC}",
"where country = 'AD'": f"select count(*) from {SRC} where country = 'AD'",
"top 5 by population": f"select name, population from {SRC} order by population desc limit 5",
"group by floor(lon), floor(lat)": f"select floor(lon), floor(lat), count(*) from {SRC} group by 1, 2",
"count + max(length(alternatenames))": f"select count(*), max(length(alternatenames)) from {SRC}",
}
spent = {}
for label, sql in QUERIES.items():
print(f"{label:37}", end=" ")
measure(sql, SIZE)
spent[label] = COUNT["bytes"]count(*) 0.26 MB 1 requests 0.006s ( 0.06% of the file)
count(distinct country) 0.27 MB 110 requests 1.246s ( 0.06% of the file)
where country = 'AD' 0.26 MB 3 requests 0.005s ( 0.06% of the file)
top 5 by population 2.76 MB 48 requests 1.466s ( 0.62% of the file)
group by floor(lon), floor(lat) 108.19 MB 110 requests 1.253s (24.24% of the file)
count + max(length(alternatenames)) 116.11 MB 110 requests 2.078s (26.01% of the file)
And the same filtered count against the sorted and the shuffled copy — identical rows, different order:
import time
for name in ("full.parquet", "shuffled.parquet"):
print(f"{name:17}", end=" ")
measure(f"select count(*) from read_parquet('{BASE}/{name}') where country = 'AD'",
os.path.getsize(name))
for name in ("full.parquet", "shuffled.parquet"): # the same query on local disk
started = time.perf_counter()
duckdb.sql(f"select count(*) from read_parquet('{name}') where country = 'AD'").fetchall()
print(f"{name:17} local {time.perf_counter() - started:.3f}s")
print(f"{'upper(country)':17}", end=" ")
measure(f"select count(*) from read_parquet('{URL}') where upper(country) = 'AD'",
os.path.getsize("full.parquet"))full.parquet 0.26 MB 3 requests 0.005s ( 0.06% of the file)
shuffled.parquet 10.26 MB 221 requests 1.492s ( 1.38% of the file)
full.parquet local 0.004s
shuffled.parquet local 0.008s
upper(country) 0.27 MB 110 requests 1.253s ( 0.06% of the file)
Running 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 latest_overture_release():
"""Overture keeps only recent releases in the bucket, so look the newest one up."""
import re
import requests
listing = requests.get("https://overturemaps-us-west-2.s3.amazonaws.com/",
params={"list-type": "2", "prefix": "release/", "delimiter": "/"},
timeout=60).text
return sorted(re.findall(r"<Prefix>release/([^<]+)/</Prefix>", listing))[-1]
def overture_places_in_bbox(con, xmin, ymin, xmax, ymax, release=None):
"""A bounding-box query against a public multi-terabyte dataset."""
release = release or latest_overture_release()
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]import time
con = s3_connection(region="us-west-2")
print("release", latest_overture_release())
started = time.perf_counter()
n = overture_places_in_bbox(con, -74.02, 40.70, -73.93, 40.79) # Manhattan
print(f"{n:,} places in {time.perf_counter() - started:.1f} s")release 2026-09-23.1
224,648 places in 9.1 s
The 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
The footer says how big each column is before anything is fetched, and Example 1 says how many bytes a typical query really pulls. Together they give a break-even: downloading costs the whole file once, reading remotely costs the query's bytes every time.
def should_download(con, url, bytes_per_query):
"""Compare downloading once with reading remotely every time, from the footer alone."""
columns = con.execute(f"""
select path_in_schema as col, sum(total_compressed_size) / 1e6 as mb
from parquet_metadata('{url}') group by 1 order by 2 desc
""").fetchall()
groups = con.execute(f"select count(distinct row_group_id) from parquet_metadata('{url}')").fetchone()[0]
file_mb = sum(mb for _, mb in columns)
print(f"column data {file_mb:,.0f} MB in {len(columns)} columns and {groups} row groups")
for col, mb in columns[:3] + columns[-3:]:
print(f" {col:15} {mb:8.2f} MB")
runs = file_mb / (bytes_per_query / 1e6)
print(f"a query reading {bytes_per_query / 1e6:.2f} MB breaks even with one download "
f"after about {runs:,.0f} runs")con = duckdb.connect()
con.execute("install httpfs; load httpfs;")
should_download(con, URL, spent["group by floor(lon), floor(lat)"])
print("distinct countries:", con.execute(f"select count(distinct country) from read_parquet('{URL}')").fetchone()[0])column data 443 MB in 19 columns and 109 row groups
alternatenames 115.85 MB
name 74.60 MB
asciiname 73.05 MB
timezone 0.60 MB
cc2 0.04 MB
country 0.01 MB
a query reading 108.19 MB breaks even with one download after about 4 runs
distinct countries: 253
The footer also shows why the opening table looks the way it does: alternatenames is 116 MB of the file's 443 MB of column data, and country is 0.01 MB.
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 holds 253 distinct codes, stored dictionary-encoded and in long runs because the file is grouped by country, so its entire storage across 109 row groups is 0.01 MB. The query's 0.27 MB is almost all footer; what it paid for was 110 requests, one per row group.
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 — measured, 0.008 s against 0.004 s. Over a network it costs 221 round trips and 1.5 seconds — and on a high-latency link, considerably more.
That is why the sorted-versus-shuffled measurement is a 300× time difference over HTTP and a 2× one on local disk. 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 9.1 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.62% 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.26 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 224,648 places in 9.1 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.