How to Export ClickHouse Data to CSV (5 Practical Methods)

If you work with ClickHouse, sooner or later you'll need to get data out of it and into a CSV file — for a report, a handoff to another system, or just a quick backup of an aggregated result. ClickHouse makes this easy once you know which method fits your situation. Here's a fast, practical rundown of the main ways to do it.

1. The simplest way: clickhouse-client with --format

The most common approach is running a query through the command-line client and telling it to output CSV:

clickhouse-client \
  --host your-clickhouse-host \
  --user default \
  --password your_password \
  --query "SELECT order_id, customer_id, total_amount, created_at FROM orders WHERE created_at >= yesterday()" \
  --format CSVWithNames \
  > orders_export.csv

The key part here is --format CSVWithNames. Swap in plain CSV if you don't want a header row, or use CSVWithNamesAndTypes if you also want column data types included — handy when a downstream tool needs to know the schema without guessing.

2. Exporting over HTTP

If you don't have clickhouse-client installed, or you're triggering the export from a script or another service, ClickHouse's built-in HTTP interface works just as well:

curl -G 'http://your-clickhouse-host:8123/' \
  --data-urlencode "query=SELECT order_id, customer_id, total_amount, created_at FROM orders WHERE created_at >= yesterday() FORMAT CSVWithNames" \
  -H 'X-ClickHouse-User: default' \
  -H 'X-ClickHouse-Key: your_password' \
  -o orders_export.csv

Notice the format is specified inside the query itself with FORMAT CSVWithNames rather than as a separate flag. This is a small but easy-to-miss difference between the CLI and HTTP approaches.

3. Choosing a different delimiter

CSV isn't always the right fit — sometimes you need tab-separated values for a spreadsheet import, or a custom delimiter for a legacy pipeline. ClickHouse supports both:

# Tab-separated
clickhouse-client \
  --query "SELECT device_id, sensor_type, reading_value, recorded_at FROM sensor_readings" \
  --format TabSeparatedWithNames \
  > sensor_readings.tsv

For anything more unusual (pipes, semicolons, etc.), the CustomSeparated format lets you define your own separator via a format schema.

4. Compressing the output

Large exports can get big fast. Rather than exporting first and compressing afterward, pipe the output straight into gzip so you never write an uncompressed file to disk:

clickhouse-client \
  --query "SELECT * FROM orders" \
  --format CSVWithNames \
  | gzip > orders.csv.gz

This is a good default for anything you're archiving or shipping over a network — it saves both disk space and transfer time.

5. Handling tables too big for one export

If a table is large enough that a single query times out or eats too much memory, split the export by date range (or any other natural partition key) and loop through it:

for month in 2026-07 2026-08 2026-09; do
  clickhouse-client \
    --query "SELECT * FROM orders WHERE toYYYYMM(created_at) = replace('${month}', '-', '')" \
    --format CSVWithNames \
    > "orders_${month}.csv"
done

This keeps each individual query small and predictable, and gives you resumability — if one month fails, you only need to re-run that chunk, not the whole export.

Common gotchas

• Quoting and special characters: CSV output escapes commas and quotes automatically, but if a downstream tool is picky about encoding, double-check with CSVWithNamesAndTypes to see the raw types being emitted.
• Large result sets and memory: if a query is timing out, that's usually a sign to add a WHERE clause or chunk the export (see method 5), not to increase timeouts indefinitely.
• Permissions: the HTTP interface requires the same read permissions as the CLI — a common source of "empty file" confusion is actually an auth failure being silently written to the output file instead of raising an error you'd notice.

Wrapping up

For a one-off query, clickhouse-client --format CSVWithNames is the fastest path. For automation or environments without the CLI installed, the HTTP interface does the same job. Compress large exports with gzip, chunk anything too big to fit in one query, and consider exporting directly to S3 if that's where the file needs to end up anyway.