How to Export Data from ClickHouse to a File

Learn various methods to export data from ClickHouse, including INTO OUTFILE, the File table engine, and command-line redirection.

Using INTO OUTFILE Clause

Add an INTO OUTFILE clause to your query.

For example:

SELECT * FROM table INTO OUTFILE 'file'

By default, ClickHouse uses the file extension of the filename to deteremine the output format and compression. For example, all of the rows in nyc_taxi will be exported to the nyc_taxi.parquet using the Parquet format:

SELECT *
FROM nyc_taxi
INTO OUTFILE 'taxi_rides.parquet'

And the following file will be a compressed, tab-separated file:

SELECT *
FROM nyc_taxi
INTO OUTFILE 'taxi_rides.tsv.gz'

If ClickHouse can not determine the format from the file extension, then the output format defaults to TabSeparated for output data. To specify the output format, use the FORMAT clause.

For example:

SELECT *
FROM nyc_taxi
INTO OUTFILE 'taxi_rides.txt'
FORMAT CSV

Using the File table engine

Another option is to use the File table engine, where ClickHouse uses the file to store the data. You can perform queries and inserts directly on the file.

For example:

CREATE TABLE my_table (
   x UInt32,
   y String,
   z DateTime
)
ENGINE = File(Parquet)

Insert a few rows:

INSERT INTO my_table VALUES
   (1, 'Hello', now()),
   (2, 'World', now()),
   (3, 'Goodbye', now())

The file is stored in the data folder of your ClickHouse server - specifically in /data/default/my_table in a file named data.Parquet.

Using the File table engine is incredibly handy for creating and querying files on your file system, but keep in mind that File tables are not MergeTree tables, so you don’t get all the benefits that come with MergeTree. Use File for convenience when exporting data out of ClickHouse in convenient formats.

Using Command-Line Redirection

$ clickhouse-client --query "SELECT * from table" --format FormatName > result.txt

See clickhouse-client.