Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 38 ยท Reading Files and Formats

Writing Files and Remote Data

Export DuckDB results with COPY to CSV, Parquet and JSON, then reach remote data over HTTPS and S3 with httpfs, use secrets, and read SQLite, Excel and PostgreSQL sources.

Reading is half the story. A data tool earns its keep when it also writes clean output others can use, and when it can reach data where it lives: on a web server, in object storage, or in another database. This lesson covers COPY ... TO for exports, then DuckDB's remote access through the httpfs extension and its database scanners.

Writing files and talking to remote servers cannot happen in the page. All DuckDB code is read-only, and the stand-ins prepare and verify the result sets you would export, using SQLite. Never put real keys or passwords into SQL you share. Use the secrets mechanism below.

COPY ... TO

COPY writes a table, or any query's result, to a file. The format is chosen from the file extension or the FORMAT option:

SQL
-- DuckDB (read-only example)
COPY products TO 'products.csv' (HEADER, DELIMITER ',');
COPY (SELECT name, price FROM products WHERE price > 50) TO 'pricey.parquet' (FORMAT parquet);
COPY (SELECT * FROM orders) TO 'orders.json';

Because the source can be any query, COPY is also the way to transform files: read a CSV, clean it, and write Parquet in one statement:

SQL
-- DuckDB (read-only example)
COPY (
  SELECT id,
         name,
         try_cast(price AS DECIMAL(10, 2)) AS price,
         strptime(added_on, '%d/%m/%Y')::DATE AS added_on
  FROM read_csv('products_raw.csv', all_varchar = true)
  WHERE name IS NOT NULL
) TO 'products_clean.parquet' (FORMAT parquet, COMPRESSION zstd);

CSV options

Option Purpose
HEADER / HEADER false Write a header row (default true)
DELIMITER ';' Field separator
QUOTE '"', ESCAPE '"' Quoting rules
FORCE_QUOTE (name, notes) Quote these columns always
NULL 'NA' Text used for NULL
DATEFORMAT '%d/%m/%Y' Format for dates
COMPRESSION gzip Compress the output

Parquet and JSON options

COMPRESSION (snappy, zstd, gzip), ROW_GROUP_SIZE, and for JSON ARRAY true for a single JSON array instead of one object per line. Sorting the query with ORDER BY before writing Parquet gives better statistics (Parquet files).

Partitioned and multiple files

SQL
-- DuckDB (read-only example)
COPY orders TO 'orders_by_year' (FORMAT parquet, PARTITION_BY (year), OVERWRITE_OR_IGNORE);
COPY orders TO 'chunks' (FORMAT parquet, FILE_SIZE_BYTES '128MB');
COPY orders TO 'out' (FORMAT csv, PER_THREAD_OUTPUT);

PARTITION_BY builds the Hive layout of Many files, globs and Hive partitions. FILE_SIZE_BYTES and PER_THREAD_OUTPUT create several files for parallel writing.

Preparing an export

The best exports are the result of a query you have checked first. Here the stand-in builds a tidy summary table you could hand to a colleague; in DuckDB you would end it with COPY (...) TO 'summary.csv':

SQL Stand-in: build the result you would export ยท database: shop

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

A useful check is to confirm that a row count in the export matches the source. Run a count on both sides:

SQL Stand-in: check an export before writing it ยท database: shop

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

The Excel problem

People still exchange spreadsheets. Newer DuckDB versions include an excel extension with read_xlsx('sheet.xlsx') and can write .xlsx with COPY ... (FORMAT xlsx), while older ones use the spatial extension's st_read. For anything big, export to CSV and let the spreadsheet open that. Be careful with identifiers like 007, which spreadsheets silently turn into numbers.

Reading remote files: httpfs

The httpfs extension teaches DuckDB to read files over HTTP(S) and from S3-compatible storage (Amazon S3, Cloudflare R2, MinIO, Google Cloud Storage through its S3 interface). Install once, load per session (recent versions load it automatically when a URL appears):

SQL
-- DuckDB (read-only example)
INSTALL httpfs;
LOAD httpfs;

SELECT count(*) FROM 'https://example.org/data/trips.parquet';
SELECT * FROM read_csv('https://example.org/data/prices.csv') LIMIT 10;

For Parquet files, DuckDB uses HTTP range requests: it fetches the footer first, then only the byte ranges of the columns and row groups the query needs. So SELECT avg(price) FROM 'https://.../huge.parquet' downloads a small part of a very large file. The same pushdown you learned for local files saves bandwidth over the network. This is why Parquet and remote storage fit together so well.

S3 and credentials

For private buckets you provide credentials as a secret, kept in the session (or stored persistently) instead of pasted into queries:

SQL
-- DuckDB (read-only example)
CREATE SECRET my_s3 (
  TYPE s3,
  PROVIDER credential_chain      -- use your environment, config files or IAM role
);

SELECT * FROM 's3://my-bucket/logs/2026/*.parquet' LIMIT 5;

-- explicit keys (placeholders, never real ones in shared code)
CREATE SECRET other (TYPE s3, KEY_ID 'AKIA...', SECRET 'xxxx', REGION 'eu-west-1');

Rules for staying safe:

  • Prefer credential_chain or environment variables over typing keys in a script.
  • Keep secrets out of version control; CREATE PERSISTENT SECRET stores them in a local file for your user.
  • Use read-only keys for analysis.

Writing back to S3 uses the same syntax: COPY (...) TO 's3://my-bucket/out/result.parquet' (FORMAT parquet);

Reading other databases

DuckDB can attach other databases and query their tables as if they were its own, via extensions (read-only):

SQL
-- DuckDB (read-only example)
INSTALL sqlite;   LOAD sqlite;
ATTACH 'app.sqlite' AS app (TYPE sqlite);
SELECT * FROM app.orders LIMIT 5;

INSTALL postgres; LOAD postgres;
ATTACH 'host=localhost dbname=shop user=reader' AS pg (TYPE postgres, READ_ONLY);
SELECT count(*) FROM pg.public.orders;

Filters are pushed down so Postgres does the filtering, which keeps the transfer small. This turns DuckDB into a bridge: join a live PostgreSQL table with a Parquet file from S3, then write the answer to CSV, in a single SQL statement. MySQL has a similar scanner. You will see more on extensions in the Extensions chapter.

An end-to-end pattern

flowchart LR
  A["Remote Parquet (S3)"] --> D["DuckDB query"]
  B["PostgreSQL table"] --> D
  C["Local CSV"] --> D
  D --> E["COPY TO clean.parquet"]
  D --> F["COPY TO report.csv"]

Many pipelines look exactly like this: several inputs, one SQL statement that joins and cleans them, one or two output files.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which statement writes a query's result to a Parquet file?

    COPY (query) TO 'file' (FORMAT ...) writes any query result in CSV, Parquet or JSON.
  2. 2. Why can DuckDB query a huge remote Parquet file without downloading all of it?

    Range requests plus Parquet's layout let DuckDB fetch only the bytes that a query needs.
  3. 3. How should you supply S3 credentials?

    Secrets keep credentials out of your SQL text, and credential_chain reuses your environment or IAM role.
  4. 4. What does ATTACH 'app.sqlite' AS app (TYPE sqlite) allow?

    The sqlite extension attaches a SQLite database so you can query and join its tables.
  5. 5. Which option writes a Hive-style folder layout?

    PARTITION_BY creates key=value folders for each distinct value of the chosen columns.

Summary

  • COPY (query) TO 'file' writes CSV, Parquet or JSON and doubles as a transformation tool.
  • Options control headers, delimiters, compression, row groups and partitioning.
  • httpfs reads HTTP(S) and S3 files, fetching only the byte ranges a query needs.
  • Use CREATE SECRET for credentials; never paste real keys into shared SQL.
  • Scanner extensions attach SQLite, PostgreSQL and MySQL so one query can combine files and databases.

Next: The DuckDB Python API Basics