How to Export a ClickHouse Table to CSV

Galaxy Glossary

How do I export a ClickHouse table to CSV?

Export a ClickHouse table’s rows to a client-side CSV file with one command.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why export a ClickHouse table to CSV?

CSV is the simplest way to move ClickHouse data into spreadsheets, BI tools, or other databases. It keeps column order, supports large result sets, and avoids vendor lock-in.

What is the fastest command?

Run clickhouse-client --query "SELECT * FROM db.table FORMAT CSV" > file.csv. The client streams rows directly to stdout, bypassing intermediate files for maximum throughput.

How do I include column headers?

Add the setting --output_format_csv_with_names=1.Example: clickhouse-client --query "SELECT * FROM Orders FORMAT CSV" --output_format_csv_with_names=1 > orders.csv.

Can I filter rows before export?

Yes. Any valid WHERE, ORDER BY, or LIMIT clause works, e.g., SELECT * FROM Orders WHERE order_date >='2023-01-01' FORMAT CSV.

How to export only specific columns?

List the columns explicitly: SELECT id, name FROM Customers FORMAT CSV.This trims the file size and reduces downstream parsing.

What about large 100M+ row tables?

Use --max_block_size to tune memory usage and pipe through gzip to compress on the fly: clickhouse-client --max_block_size=50000 --query "SELECT * FROM OrderItems FORMAT CSV" | gzip > order_items.csv.gz.

Best practice: keep types intact

When re-importing, store date/time columns as ISO strings to avoid parsing issues in other tools.

Best practice: use UTC timestamps

Set --use_client_time_zone=0 so all dates export in UTC, eliminating confusion across teams.

How to export from inside SQL?

Use INTO OUTFILE when connected via HTTP interface: SELECT * FROM Products INTO OUTFILE 'products.csv' FORMAT CSV.The file is created on the server host.

How to automate in Bash?

Create a reusable function:
export_csv(){ clickhouse-client --query "$1 FORMAT CSV" --output_format_csv_with_names=1 > "$2"; }
Call with export_csv "SELECT * FROM Customers" customers.csv.

Why How to Export a ClickHouse Table to CSV is important

How to Export a ClickHouse Table to CSV Example Usage


-- Export top 1,000 high-value orders to CSV with headers
airflow@etl$ clickhouse-client --query "SELECT * FROM Orders WHERE total_amount > 1000 ORDER BY total_amount DESC LIMIT 1000 FORMAT CSV" --output_format_csv_with_names=1 > high_value_orders.csv

How to Export a ClickHouse Table to CSV Syntax


clickhouse-client [--host HOST] [--port PORT] [--database DB]
              [--user USER] [--password PASS]
              --query "SELECT column_list FROM [DB.]table
                      [WHERE condition] [ORDER BY cols] [LIMIT n]
                      FORMAT CSV"
              [--output_format_csv_with_names=1]
              [--use_client_time_zone=0]
              [--max_block_size=ROWS]
> path/to/file.csv

# E-commerce example: export Orders created this month with headers
clickhouse-client --database ecommerce \
  --query "SELECT id, customer_id, order_date, total_amount FROM Orders \
          WHERE order_date >= toStartOfMonth(today()) FORMAT CSV" \
  --output_format_csv_with_names=1 \
  > orders_this_month.csv

Common Mistakes

Omitting FORMAT CSV: Running just SELECT redirects the native format, producing binary gibberish. Always append "FORMAT CSV".
Forgetting --output_format_csv_with_names=1: Without it the first row lacks headers, confusing spreadsheet imports. Add the flag to include column names.