Shellfs

Architecture

The Shellfs extension registers a DuckDB FileSystem subclass that recognizes two patterns:

• Path ending in | — DuckDB calls popen(cmd, "r") and reads bytes from the resulting pipe.
• Path beginning with | — DuckDB calls popen(cmd, "w") and writes bytes to the pipe’s stdin.

Everything else goes to the next filesystem in the chain (local disk, httpfs, aws, etc. as usual). The integration is one sentence of glue: any DuckDB function that takes a filename — read_csv, read_json, read_parquet, COPY ... TO, COPY ... FROM — accepts pipe paths transparently.

Why this exists

DuckDB ships with a strong built-in toolbox — HTTP, S3, parquet, JSON, etc. But the long tail is endless: a private API with custom auth, a tool that emits a proprietary text format, a one-off script that walks a directory tree. Rather than building a DuckDB extension for each, the Shellfs extension lets the existing Unix toolchain be the adapter.

Performance

Pipes are streaming — DuckDB starts processing rows as soon as the first chunk arrives, not after the upstream command finishes. For a seq 1 1_000_000_000 | you can LIMIT 100 and the upstream seq is killed via SIGPIPE without producing the rest.

popen overhead per pipeline is small (one fork+exec). The bottleneck is whatever your shell pipeline is doing, not the bridge.

Compared to alternatives

• copy ... from program 'cmd' in PostgreSQL — same idea, more cumbersome syntax. The Shellfs extension slips into any DuckDB filename context.
• Python subprocess + DataFrames — much heavier setup; you give up DuckDB’s vectorized execution against the streamed data.
• DuckDB built-ins — use those first. Reach for the Shellfs extension only when the data lives behind a tool DuckDB can’t speak natively.

Security

Read a pipeline

-- Numbers 1..100 containing a "2"
SELECT * FROM read_csv('seq 1 100 | grep 2 |');

Chain multiple commands:

-- First two multiples of 7 in [1, 35]
SELECT * FROM read_csv('seq 1 35 | awk "\$1 % 7 == 0" | head -n 2 |');

Curl an HTTP API

For custom headers, query params, retries, or pre-processing — pipe through curl:

SELECT abbreviation, unixtime
FROM read_json('curl -s http://worldtimeapi.org/api/timezone/Etc/UTC |');

For programmatic HTTP from SQL without shelling out, see the http_client extension.

Drive a custom script

Any program that prints CSV (or JSON, or Parquet, etc.) to stdout becomes a queryable source — useful when you need bespoke parsing, schema-shaping, or to wrap an API DuckDB doesn’t speak natively:

# test-csv.py
print("counter1,counter2")
for i in range(10_000_000):
    print(f"{i},{i}")

SELECT COUNT(DISTINCT counter1) FROM read_csv('./test-csv.py |');

Pipe out of DuckDB

The | prefix turns COPY into a streaming export:

-- Stream a query result through gzip
COPY (SELECT * FROM big_table) TO '| gzip > out.csv.gz';

-- Stream into a downstream program
COPY (SELECT * FROM events) TO '| ./load_into_kafka.sh';