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

The "DB::Exception: Format is not suitable for output" error in ClickHouse occurs when a SELECT query specifies a format that can only be used for reading (input), not for rendering results (output). The FORMAT_IS_NOT_SUITABLE_FOR_OUTPUT error is less common than its input counterpart, since most formats support output. However, a few specialized formats exist purely for data ingestion.

Impact

The SELECT query will fail without producing any results. No data is modified, and no side effects occur. This is purely a format selection issue that is resolved by choosing an output-capable format.

Common Causes

1. Using a format in a SELECT ... FORMAT clause that is input-only
2. Specifying an input-only format in an INTO OUTFILE statement
3. Using an input-only format in a table function like file() or s3() for writing
4. Confusing a format's name with a similarly named format that does support output
5. Building dynamic queries in application code that incorrectly selects an input-only format for result rendering

Troubleshooting and Resolution Steps

1. Verify the format's output capability:

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

   Formats with is_output = 0 cannot be used in SELECT queries.

2. List all output-capable formats:

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

3. Switch to a format that supports output. The most commonly used output formats are:

   -- Human-readable
   SELECT * FROM your_table FORMAT Pretty;
   SELECT * FROM your_table FORMAT PrettyCompact;
   
   -- Machine-readable
   SELECT * FROM your_table FORMAT JSONEachRow;
   SELECT * FROM your_table FORMAT CSV;
   SELECT * FROM your_table FORMAT TabSeparated;
   SELECT * FROM your_table FORMAT Parquet;
   

4. For file exports, use a format that supports output:

   SELECT * FROM your_table INTO OUTFILE '/tmp/data.parquet' FORMAT Parquet;
   SELECT * FROM your_table INTO OUTFILE '/tmp/data.csv' FORMAT CSV;
   

5. Check your application code if the format is being selected programmatically. Ensure the format selection logic distinguishes between input and output contexts.

Best Practices

• Use system.formats as your reference for format capabilities rather than memorizing which formats support input, output, or both.
• For data export pipelines, standardize on formats like Parquet, JSONEachRow, or CSV which support both input and output.
• Validate format selection in application code before sending queries to ClickHouse.
• When in doubt, TabSeparated and JSONEachRow are safe defaults that work for both input and output.

Frequently Asked Questions

Q: Which formats are input-only in ClickHouse?
A: Very few formats are input-only. The most notable example is Regexp, which allows parsing input using regular expressions but has no output representation. Use SELECT name FROM system.formats WHERE is_input = 1 AND is_output = 0 to get the complete list for your version.

Q: Can I use the same format for both importing and exporting data?
A: Yes, most ClickHouse formats support both directions. Formats like CSV, TabSeparated, JSONEachRow, Parquet, ORC, Avro, Native, and RowBinary all work for both input and output.

Q: Why would a format be input-only?
A: Input-only formats typically exist for specialized parsing scenarios where the input structure does not have a natural output equivalent. For example, Regexp lets you define custom parsing rules using regular expressions, but there is no meaningful way to "output" data in a regex format.