Getting data out of ClickHouse® should be the easy part, and yet for years my process was: run the query in the terminal, squint at the output, copy it, paste it into a spreadsheet, and spend ten minutes fixing the columns that did not line up. There is a better way, and it is worth a short post because picking the right export format saves the person on the other end a lot of grief.
The copy-paste problem
When you run a query in clickhouse-client, the result is formatted for a terminal, not for reuse. Wide tables wrap. Tabs and spaces get mixed up. Special characters in your data break the alignment. If you copy that into Excel, you spend more time cleaning it than you spent writing the query.
The CHOps SQL editor gives you the result as a real table, and an export button that writes a clean file in the format you choose. No copy-paste, no cleanup. That alone is reason enough to stop exporting through the terminal.
CSV: for spreadsheets and humans
CSV is the format you want when a human is going to open the file, usually in Excel or Google Sheets. It is universally understood, and every spreadsheet tool imports it cleanly.
If you do want to produce CSV directly from SQL for a pipeline, ClickHouse® supports it natively with a format clause:
SELECT
event_date,
count() AS events,
uniqExact(user_id) AS users
FROM analytics.events
WHERE event_date >= today() - 30
GROUP BY event_date
ORDER BY event_date
FORMAT CSVWithNames;CSVWithNames includes the header row, which is almost always what you want when a person is going to open the file. In the editor you get the same thing with a click, no format clause needed.
JSON: for code and APIs
When the data is going into another program, JSON is usually the right call. It preserves types better than CSV, it handles nested structures, and most languages parse it without any extra work. If you are feeding query results into a script, a dashboard, or another service, export JSON.
ClickHouse® has rich JSON support, and the editor's JSON export gives you an array of objects that any language can read directly. No guessing whether a column was a number or a string, which is the classic CSV headache.
TSV: for pipelines and big dumps
TSV, tab-separated values, is the quiet workhorse. It is ClickHouse®'s most natural text format, it is faster to parse than CSV because there is no quoting logic to worry about, and it handles values with commas without escaping gymnastics. When I am moving a large result between systems or loading it back into another ClickHouse® table, TSV is what I reach for.
A quick rule of thumb
Pick CSV when a person will open it in a spreadsheet. Pick JSON when code will read it and types matter. Pick TSV when you are moving a lot of rows between systems or back into ClickHouse®. When in doubt, CSV with headers is the safe default for sharing with people, and JSON is the safe default for sharing with software.
The real point is smaller than the format debate: exporting should be one click from where you ran the query, in a format the recipient can actually use, with no manual cleanup. Once that is true, getting data out of ClickHouse® stops being a chore. The SQL editor feature page covers the rest of what the editor does once your query is written.
