ClickHouse DB::Exception: Format is not suitable for input

The "DB::Exception: Format is not suitable for input" error in ClickHouse occurs when you attempt to read or import data using a format that only supports output (writing). The FORMAT_IS_NOT_SUITABLE_FOR_INPUT error is ClickHouse's way of telling you that the specified format can render query results but cannot parse incoming data. Several ClickHouse formats are designed exclusively for displaying results in human-readable or specialized output forms.

Impact

The INSERT or SELECT ... FROM query using the unsupported input format will fail. No data is read or inserted. This is a usage error that is resolved by selecting an appropriate input format. Existing data and other operations remain unaffected.

Common Causes

1. Using Pretty, PrettyCompact, PrettySpace, or other Pretty-family formats in an INSERT statement
2. Attempting to read from a file or URL using Vertical format, which is output-only
3. Using Markdown, SQLInsert, or XML as input formats, which are only supported for output
4. Specifying an output-only format in a table function like file(), url(), or s3() for reading
5. Copy-pasting a FORMAT clause from a SELECT query into an INSERT context without changing the format

Troubleshooting and Resolution Steps

1. Identify the format in your query and determine if it is output-only. Common output-only formats include:

   • Pretty, PrettyCompact, PrettyCompactMonoBlock, PrettyNoEscapes, PrettySpace
   • Vertical
   • Markdown
   • SQLInsert
   • XML
   • Null (output-only, discards data)

2. Check format capabilities using the system table:

   SELECT name, is_input, is_output
   FROM system.formats
   WHERE name = 'Pretty';
   

   Formats with is_input = 0 cannot be used for reading data.

3. Switch to an equivalent input-capable format:

   -- Instead of Pretty (output-only), use TabSeparated or CSV for input
   INSERT INTO your_table FORMAT CSV
   1,"hello"
   2,"world"
   

4. Common format substitutions for input:

   • Instead of Pretty -> use TabSeparated or CSV
   • Instead of Vertical -> use JSONEachRow or TabSeparated
   • Instead of XML -> use JSONEachRow
   • Instead of SQLInsert -> use Values

5. List all input-capable formats on your server:

   SELECT name FROM system.formats WHERE is_input = 1 ORDER BY name;
   

Best Practices

• Separate your input and output format choices. Use human-readable formats like Pretty for interactive queries and machine-readable formats like JSONEachRow or CSV for data ingestion.
• When building data pipelines, always verify that the chosen format supports both reading and writing if the pipeline involves both directions.
• Document the input/output capabilities of formats used in your ETL processes.

Frequently Asked Questions

Q: Why doesn't ClickHouse support Pretty format for input?
A: Pretty formats add decorative elements like borders, alignment padding, and formatting that make the output human-readable but structurally ambiguous for parsing. They are designed for display, not for data exchange.

Q: Is there a format that works for both input and output?
A: Yes, many formats support both directions. TabSeparated, CSV, JSONEachRow, JSONCompactEachRow, Values, Native, RowBinary, Parquet, ORC, Avro, and Arrow all support both input and output. Check system.formats for the complete list.

Q: Can I use INSERT ... FORMAT JSON?
A: Yes, JSON format supports input as of recent ClickHouse versions. However, JSONEachRow is more commonly used for line-by-line JSON ingestion as it does not require the entire dataset to be wrapped in a JSON object.