The File table engine keeps the data in a file in one of the supported file formats (
- Data export from ClickHouse to file.
- Convert data from one format to another.
- Updating data in ClickHouse via editing a file on a disk.
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
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.
Be careful with this functionality, because ClickHouse does not keep track of external changes to such files. The result of simultaneous writes via ClickHouse and outside of ClickHouse is undefined.
1. Set up the
CREATE TABLE file_engine_table (name String, value UInt32) ENGINE=File(TabSeparated)
By default ClickHouse will create folder
2. Manually create
$ cat data.TabSeparated one 1 two 2
3. Query the data:
SELECT * FROM file_engine_table
┌─name─┬─value─┐ │ one │ 1 │ │ two │ 2 │ └──────┴───────┘
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
stdout. It is possible to read and write compressed files based on an additional engine parameter or file extension (
$ 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"
SELECTqueries can be performed concurrently, but
INSERTqueries will wait each other.
- Supported creating new file by
- If file exists,
INSERTwould append new values in it.
- Not supported:
SELECT ... SAMPLE