Data export is a common requirement in modern analytics workflows. Whether you need to generate reports, share datasets with external systems, create backups, or migrate data to another platform, ClickHouse® provides several efficient methods for exporting data in a variety of formats.
This article explores the different ways to export data from ClickHouse® and highlights the formats and tools available to support diverse data integration and reporting needs.
Why Export Data from ClickHouse®?
Organizations export data from ClickHouse® for many reasons, including:
- Creating reports and dashboards
- Sharing analytical results with stakeholders
- Migrating data to other databases or systems
- Building data pipelines and integrations
- Archiving historical information
- Performing backup and recovery operations
Because ClickHouse® is designed to handle large volumes of analytical data, it offers optimized export capabilities that can efficiently process and transfer datasets of varying sizes.
Supported Export Formats
ClickHouse® supports multiple output formats, allowing users to choose the most suitable option for their use case.
Common export formats include:
- CSV
- TSV
- JSON
- JSONEachRow
- Parquet
- XML
- SQL Inserts
- Native ClickHouse® format
Each format serves different purposes. For example, CSV is widely used for spreadsheet applications, while Parquet is often preferred for big data and analytics workflows due to its columnar storage structure.
ClickHouse® supports numerous input and output formats. The formats covered in this article represent some of the most commonly used options for exporting data in analytics and data engineering workflows.
Sample Dataset
The examples throughout this article use a sample table named sales_data.
CREATE TABLE sales_data
(
id UInt32,
product String,
amount Float64,
sale_date Date
)
ENGINE = MergeTree
ORDER BY id;Exporting Data Using a SELECT Query
The most common way to export data from ClickHouse® is by executing a SELECT query and specifying an output format using the FORMAT clause. ClickHouse® supports a wide range of output formats, enabling seamless integration with analytics tools, data lakes, applications, and external storage systems.
A SELECT query combined with the FORMAT clause enables ClickHouse® to serialize query results into a specific output format. This approach is commonly used when exporting data to files, integrating with external tools, or transferring data between systems.
Basic example:
SELECT * FROM sales_data FORMAT CSV;Common Export Formats
CSV
CSV (Comma-Separated Values) is one of the most widely used formats for data exchange and spreadsheet applications.
SELECT * FROM sales_data
FORMAT CSV;TSV
TSV (Tab-Separated Values) provides tab-delimited output and is often used for large-scale data processing.
SELECT * FROM sales_data
FORMAT TSV;JSON
JSON is commonly used for APIs, web applications, and data interchange.
SELECT * FROM sales_data
FORMAT JSON;JSONEachRow
Outputs each row as an individual JSON object, making it suitable for streaming and data pipeline workflows.
SELECT * FROM sales_data
FORMAT JSONEachRow;Parquet
Parquet is a columnar file format optimized for analytical workloads and data lake architectures.
SELECT * FROM sales_data
FORMAT Parquet;XML
XML output can be useful when integrating with legacy systems and enterprise applications.
SELECT * FROM sales_data
FORMAT XML;Exporting Query Results to a File
ClickHouse® supports exporting query results directly to a file using the INTO OUTFILE clause. The output can be written in any supported format.
Example:
SELECT * FROM sales_data
INTO OUTFILE 'sales_export.csv'
FORMAT CSV;Example using Parquet:
SELECT * FROM sales_data
INTO OUTFILE 'sales_export.parquet'
FORMAT Parquet;This approach is useful when query results need to be stored directly on the server without requiring additional processing steps.
Exporting Data Using clickhouse-client
The clickhouse-client utility allows query results to be redirected to files directly from the command line. This method is frequently used in automation scripts, scheduled exports, and ETL processes.
CSV export:
clickhouse-client --query="
SELECT * FROM sales_data
FORMAT CSV " > sales_export.csvParquet export:
clickhouse-client --query="
SELECT * FROM sales_data
FORMAT Parquet " > sales_export.parquetJSON export:
clickhouse-client --query="
SELECT * FROM sales_data
FORMAT JSONEachRow " > sales_export.jsonChoosing the Right Export Format
| Format | Best For |
|---|---|
| CSV | Spreadsheets, reporting, general data exchange |
| TSV | Large datasets and data processing workflows |
| JSON | APIs and application integration |
| JSONEachRow | Streaming and ETL pipelines |
| Parquet | Data lakes, analytics platforms, cloud storage |
| XML | Legacy enterprise integrations |
Selecting the appropriate format depends on the target system, performance requirements, and intended use of the exported data.
Exporting Compressed Data
For large datasets, exporting data in a compressed format can reduce storage requirements and improve transfer efficiency. ClickHouse® supports exporting compressed files using various compression methods.
Example:
SELECT * FROM sales_data
INTO OUTFILE 'sales_data.csv.gz'
COMPRESSION 'gzip'
FORMAT CSV;This approach is particularly useful when transferring large datasets across networks or storing exported data for long-term archival purposes.
Best Practices for Data Export
When exporting data from ClickHouse®, consider the following recommendations:
- Export only the required columns instead of using
SELECT *. - Apply filters to reduce unnecessary data transfer.
- Use efficient formats such as Parquet for analytical workloads.
- Compress large exports whenever possible.
- Schedule large exports during off-peak hours.
- Validate exported data before importing it into downstream systems.
- Avoid exporting unnecessary historical data when only recent records are required
- Consider exporting data in batches when working with very large datasets
Following these practices can help improve export performance and reduce resource consumption.
Common Use Cases
- Reporting and Business Intelligence – Export data for visualization, reporting, and dashboarding tools.
- Data Migration – Move datasets between ClickHouse® and other database systems.
- Data Sharing – Provide datasets to business teams, partners, or external applications.
- Backup and Archival – Store exported data for disaster recovery and long-term retention purposes.
- Data Lake Integration – Export data in formats such as Parquet for use in modern analytics ecosystems and cloud storage platforms.
Things to Consider
When exporting data from ClickHouse®, consider the size of the dataset, the target system, and the selected output format. For large analytical datasets, columnar formats such as Parquet can provide better storage efficiency and interoperability with modern data platforms. For interoperability with spreadsheets and simple reporting tools, CSV remains a popular choice.
Additionally, organizations should evaluate storage requirements, network bandwidth, and downstream processing needs before selecting an export strategy.
Exploring ClickHouse® for Your Analytics?
At Quantrail Data, we help teams run ClickHouse® reliably for real-time analytics – from Kubernetes deployments and migrations to performance tuning in production.
We see these challenges firsthand while supporting demanding analytics workloads. In one recent engagement, a customer achieved near bare-metal performance with ClickHouse® in production – a story we’ve shared here:
Success Story: Quantrail Bare-Metal ClickHouse® Deployment
If you’re evaluating ClickHouse® or trying to get more out of an existing setup, we’re happy to share practical lessons from real-world deployments.
Contact
Quantrail Data
Conclusion
ClickHouse® provides multiple methods for exporting data, ranging from simple query-based exports to file-based and command-line approaches. With support for numerous output formats, including CSV, JSON, TSV, and Parquet, ClickHouse® enables seamless integration with reporting tools, analytics platforms, data lakes, and external applications.
By selecting the appropriate export method and format, organizations can efficiently share, migrate, archive, and analyze data while maintaining the high-performance characteristics of ClickHouse®.