Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

File table engine

View as MarkdownEdit this pageReport an issueSet up docs MCP

The File table engine keeps the data in a file in one of the supported file formats (TabSeparated, Native, etc.).

Usage scenarios:

  • Data export from ClickHouse to file.
  • Convert data from one format to another.
  • Updating data in ClickHouse via editing a file on a disk.

Usage in ClickHouse Server

File(Format)

The Format parameter specifies one of the available file formats. To perform SELECT queries, the format must be supported for input, and to perform INSERT queries – for output. The available formats are listed in the Formats section.

ClickHouse does not allow specifying filesystem path for File. It will use folder defined by path setting in server configuration.

When creating table using File(Format) it creates empty subdirectory in that folder. When data is written to that table, it’s put into data.Format file in that subdirectory.

You may manually create this subfolder and file in server filesystem and then ATTACH it to table information with matching name, so you can query data from that file.

Example

1. Set up the file_engine_table table:

CREATE TABLE file_engine_table (name String, value UInt32) ENGINE=File(TabSeparated)

By default ClickHouse will create folder /var/lib/clickhouse/data/default/file_engine_table.

2. Manually create /var/lib/clickhouse/data/default/file_engine_table/data.TabSeparated containing:

$ cat data.TabSeparated
one 1
two 2

3. Query the data:

SELECT * FROM file_engine_table
┌─name─┬─value─┐
│ one  │     1 │
│ two  │     2 │
└──────┴───────┘

Usage in ClickHouse-local

In clickhouse-local File engine accepts file path in addition to Format. Default input/output streams can be specified using numeric or human-readable names like 0 or stdin, 1 or stdout. It is possible to read and write compressed files based on an additional engine parameter or file extension. The supported values are none (no compression), gzip/gz, deflate, brotli/br, lzma/xz, zstd/zst, lz4, bz2, and snappy. For snappy, the wire format is selected by the snappy_mode setting (basic by default).

Example:

$ echo -e "1,2\n3,4" | clickhouse-local -q "CREATE TABLE table (a Int64, b Int64) ENGINE = File(CSV, stdin); SELECT a, b FROM table; DROP TABLE table"

Details of Implementation

  • Multiple SELECT queries can be performed concurrently, but INSERT queries will wait each other.
  • Supported creating new file by INSERT query.
  • If file exists, INSERT would append new values in it, but only for formats that support appending. Formats that do not support it, such as Avro, Arrow, JSON, Npy, ORC and Parquet, reject an INSERT into a non-empty file with CANNOT_APPEND_TO_FILE. For a plain file path, use the engine_file_truncate_on_insert or engine_file_allow_create_multiple_files settings listed below instead; neither applies when writing through a file descriptor, where the caller owns the descriptor.
  • Not supported:
    • ALTER
    • SELECT ... SAMPLE
    • Indices
    • Replication

PARTITION BY

PARTITION BY — Optional. It is possible to create separate files by partitioning the data on a partition key. In most cases, you don’t need a partition key, and if it is needed you generally don’t need a partition key more granular than by month. Partitioning does not speed up queries (in contrast to the ORDER BY expression). You should never use too granular partitioning. Don’t partition your data by client identifiers or names (instead, make client identifier or name the first column in the ORDER BY expression).

For partitioning by month, use the toYYYYMM(date_column) expression, where date_column is a column with a date of the type Date. The partition names here have the "YYYYMM" format.

Virtual columns

  • _path — Path to the file. Type: LowCardinality(String).
  • _file — Name of the file. Type: LowCardinality(String).
  • _size — Size of the file in bytes. Type: Nullable(UInt64). If the size is unknown, the value is NULL.
  • _time — Last modified time of the file. Type: Nullable(DateTime). If the time is unknown, the value is NULL.

Settings

Navigation