ClickHouse DB::Exception: Unknown format

The "DB::Exception: Unknown format" error in ClickHouse occurs when a query specifies a data format that the server does not recognize. The UNKNOWN_FORMAT error is raised when the format name provided in a FORMAT clause, INSERT ... FORMAT, or in a table function like file() or s3() does not match any of ClickHouse's built-in or registered formats.

Impact

The query that references the unknown format will fail immediately. No data is read, written, or modified. This is a client-side configuration error that has no impact on existing data or other running queries.

Common Causes

1. Typo in the format name (e.g., Parqet instead of Parquet, JSOM instead of JSON)
2. Using a format name from another database system that ClickHouse does not support
3. The ClickHouse version does not include support for the requested format (some formats were added in newer releases)
4. Case sensitivity issues -- ClickHouse format names are case-sensitive
5. Using an abbreviated or unofficial format name instead of the full ClickHouse format name
6. Copy-pasting queries from documentation written for a different ClickHouse version

Troubleshooting and Resolution Steps

1. Check the format name spelling. Commonly used formats in ClickHouse include:

   • TabSeparated, CSV, JSONEachRow, JSON, Parquet, ORC, Avro, Arrow, Native, RowBinary, Values, TSV, JSONCompactEachRow, Protobuf, MsgPack, TSKV

2. List all supported formats on your ClickHouse instance:

   SELECT * FROM system.formats ORDER BY name;
   

3. Verify case sensitivity. Format names are case-sensitive:

   -- Correct
   SELECT * FROM your_table FORMAT JSONEachRow;
   -- Incorrect
   SELECT * FROM your_table FORMAT jsoneachrow;
   

4. Check your ClickHouse version for format availability:

   SELECT version();
   

   Some formats (e.g., One, Prometheus, SQLInsert) were added in more recent versions.

5. Use the closest available format if the one you need is not supported. For example:

   • Instead of a generic JSON array format, use JSONEachRow for line-delimited JSON
   • Instead of TSV, use TabSeparated (though TSV is a valid alias)

6. For table functions, ensure the format parameter is correctly placed:

   -- Correct
   SELECT * FROM file('data.csv', 'CSV', 'col1 UInt32, col2 String');
   -- Incorrect format parameter
   SELECT * FROM file('data.csv', 'csv_format', 'col1 UInt32, col2 String');
   

Best Practices

• Refer to the system.formats table when unsure about available formats on your specific ClickHouse version.
• Use consistent format names across your codebase and document your preferred formats.
• When writing client applications, validate format names against the target ClickHouse version's supported list.
• Prefer widely supported formats like JSONEachRow, CSV, and TabSeparated for maximum compatibility across versions.

Frequently Asked Questions

Q: Are format names case-sensitive in ClickHouse?
A: Yes. You must use the exact casing as documented. For example, JSONEachRow is correct, while jsoneachrow or JSONEACHROW will trigger the UNKNOWN_FORMAT error.

Q: Can I add custom formats to ClickHouse?
A: ClickHouse does not support user-defined data formats at runtime. All formats must be compiled into the binary. However, ClickHouse provides a very large number of built-in formats that cover most use cases.

Q: Is TSV the same as TabSeparated?
A: Yes, TSV is an alias for TabSeparated in ClickHouse. Similarly, TSKV is the key-value variant. Both forms are accepted.