Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 38 ยท Extensions and Performance

Remote Data: HTTP, S3 and Cloud Storage

Query Parquet and CSV files over HTTP and S3 with DuckDB httpfs: range requests, projection and filter pushdown, secrets, globs and writing results back to buckets.

A big attraction of DuckDB is that your data does not have to be on your laptop. With the httpfs extension a query can read a Parquet file on a web server or in an S3 bucket, and transfer only the bytes it needs. There is no download step and no cluster: just a FROM 's3://...'.

This site cannot reach the network from the SQL runner, so the DuckDB statements below are read-only. Runnable stand-ins show the planning idea (reading less data) with portable SQL.

Reading over HTTP

SQL
INSTALL httpfs; LOAD httpfs;      -- usually autoloaded

SELECT count(*), avg(fare)
FROM read_parquet('https://data.example.com/trips/2026-03.parquet')
WHERE fare > 20;

How can this be fast on a multi-gigabyte file? Parquet stores a footer with the schema and per-column-chunk statistics. DuckDB first reads the footer with a small HTTP range request, then:

  1. Projection pushdown: it fetches only the column chunks for fare, not the other 30 columns.
  2. Filter pushdown: row groups whose min/max statistics prove fare > 20 cannot match are skipped without being downloaded.
  3. Parallel reads: several range requests run at once, one per row group.
flowchart LR
    Q["SELECT avg(fare) ... WHERE fare > 20"] --> F["Range request: file footer"]
    F --> P["Plan: need column fare, skip row groups with max <= 20"]
    P --> R["Range requests for just those column chunks"]
    R --> A["Aggregate locally"]

Only a small fraction of the file crosses the network.

CSV and JSON over HTTP cannot be skipped this way, because they have no statistics, so DuckDB must read them from the start. Prefer Parquet for remote data.

S3 and compatible storage

SQL
CREATE SECRET my_s3 (
    TYPE s3,
    KEY_ID 'AKIA...',
    SECRET '...',
    REGION 'eu-central-1'
);

SELECT * FROM 's3://my-bucket/events/2026/03/*.parquet' LIMIT 10;

A secret is DuckDB's managed credential object. CREATE SECRET stores it for the session; CREATE PERSISTENT SECRET writes it to disk (unencrypted, in your home folder, so mind permissions). For AWS you can use PROVIDER credential_chain to pick up environment variables or role credentials automatically instead of pasting keys. Never commit keys to source control or print them in notebooks.

For S3-compatible services (Cloudflare R2, MinIO, Backblaze B2, Google Cloud Storage via its S3 API) set ENDPOINT, plus URL_STYLE 'path' when the service needs path-style addressing. Dedicated extensions (azure, aws) add native Azure Blob support and extra credential chains.

Globs and many files

Data usually arrives as many files. Globs and lists read them as one table:

SQL
SELECT * FROM read_parquet('s3://logs/2026/*/*.parquet', filename = true);
SELECT * FROM read_csv(['a.csv', 'b.csv'], union_by_name = true);

filename = true adds a column with the source path, handy for debugging and for deriving dates from folder names. Hive partitioning (folders like year=2026/month=03/) is recognized with hive_partitioning = true (or automatically), and the folder values become columns. A filter on those columns skips entire folders:

SQL
SELECT count(*) FROM read_parquet('lake/*/*/*.parquet', hive_partitioning = true)
WHERE year = 2026 AND month = 3;     -- reads only lake/year=2026/month=3/

Partition pruning is covered again in incremental loads and partitioned output.

Writing to remote storage

COPY can write straight to a bucket:

SQL
COPY (SELECT * FROM staging WHERE day = DATE '2026-03-01')
TO 's3://my-bucket/clean/day=2026-03-01/data.parquet'
(FORMAT parquet, COMPRESSION zstd);

DuckDB writes the object when the copy finishes; there are no row-level updates in object storage, so the unit of change is a whole file. That fits append-only pipelines nicely.

Caching and cost

Every query repeats the network reads, and you may pay for requests and egress. Options:

  • Enable the object cache for metadata (SET enable_object_cache = true in older versions; recent releases cache Parquet metadata by default).
  • Enable the external file cache in newer versions, or cache_httpfs from the community repository, to keep hot ranges on local disk.
  • Copy a working subset into a local DuckDB table (CREATE TABLE trips AS SELECT ...) when you will query it repeatedly.
  • Run DuckDB in the same cloud region as the bucket.

Stand-in: pushdown in action

A plain table cannot show network savings, but it can show why filtering on a partition-like column and selecting few columns are the winning habits. This query reads just two columns and only rows that match:

SQL Stand-in: select few columns, filter early

Press Run (or Ctrl + Enter) to see the result here.

On Parquet in a bucket, the year and month conditions would skip whole folders and fare > 20 would skip row groups.

The next one builds a file path from a partition value, the string handling you will do when writing partitioned output:

SQL Stand-in: derive partition paths

Press Run (or Ctrl + Enter) to see the result here.

Practical advice

  • Always inspect first: DESCRIBE SELECT * FROM read_parquet('...') reads only the footer.
  • Use LIMIT while exploring; for Parquet it cuts the read short.
  • Prefer larger Parquet files (100 MB to 1 GB) and row groups of about 100,000 to 1,000,000 rows; thousands of tiny files make you pay per request.
  • Set http_retries, http_timeout and http_keep_alive for flaky links.
  • Check licences and terms before querying someone else's public data in bulk.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why can DuckDB query a huge remote Parquet file quickly?

    Footer statistics and column chunks let DuckDB fetch just the columns and row groups required.
  2. 2. What is a Hive-partitioned folder layout?

    DuckDB turns key=value folder names into columns and skips folders that cannot match your filter.
  3. 3. What object holds credentials for S3 in DuckDB?

    CREATE SECRET stores credentials; a credential_chain provider can fetch them from the environment instead.
  4. 4. Why prefer Parquet to CSV for remote data?

    CSV has to be read from the beginning; Parquet lets the engine skip irrelevant columns and row groups.

Summary

  • httpfs lets queries read HTTP and S3 files with range requests, so little data moves.
  • Parquet enables projection and filter pushdown; CSV does not.
  • Secrets hold credentials; use credential chains and never hard-code keys.
  • Globs and Hive partitions treat many files as one table and prune folders.
  • Cache or copy hot data locally and keep files reasonably large.

Next: Performance: The Vectorized Engine