ClickHouse DB::Exception: Illegal syntax for codec type

The "DB::Exception: Illegal syntax for codec type" error in ClickHouse occurs when a column's CODEC clause contains a syntactically invalid codec specification. The ILLEGAL_SYNTAX_FOR_CODEC_TYPE error is raised during DDL operations such as CREATE TABLE or ALTER TABLE when ClickHouse's parser encounters a codec expression it cannot interpret. This is strictly a syntax issue with how the codec is specified, not a problem with the data itself.

Impact

This error prevents the DDL statement from executing. No table or column modification takes place. Since it is a parse-time error, there is no impact on existing data or running queries -- it simply blocks the schema change you are attempting to make.

Common Causes

1. Misspelled codec name in the CODEC() clause (e.g., CODEC(ZSD) instead of CODEC(ZSTD))
2. Missing parentheses or incorrect nesting in a codec chain (e.g., CODEC(Delta, ZSTD) instead of CODEC(Delta, ZSTD))
3. Using an expression or function call where a codec name is expected
4. Passing the wrong type of argument to a codec (e.g., a string instead of a number)
5. Copying DDL from documentation formatted for a different ClickHouse version with different syntax rules
6. Extra commas or trailing syntax errors within the CODEC() specification

Troubleshooting and Resolution Steps

1. Review the exact CODEC clause in your DDL statement. The correct syntax is:

   column_name DataType CODEC(CodecName1(param), CodecName2(param))
   

   For example:

   value Float64 CODEC(Delta, ZSTD(1))
   

2. Check the codec name spelling. Valid built-in codecs include:

   • NONE, LZ4, LZ4HC(level), ZSTD(level), Delta(bytes), DoubleDelta, Gorilla, FPC, T64

3. Verify parameter syntax. Codec parameters must be integer literals:

   -- Correct
   CODEC(ZSTD(3))
   -- Incorrect
   CODEC(ZSTD('3'))
   CODEC(ZSTD(high))
   

4. Check for codec chaining syntax. Multiple codecs are separated by commas within a single CODEC() clause:

   -- Correct: preprocessing codec followed by compression codec
   CODEC(Delta(4), ZSTD(1))
   -- Incorrect: multiple CODEC clauses
   CODEC(Delta(4)) CODEC(ZSTD(1))
   

5. Test with a minimal example to isolate the syntax issue:

   CREATE TABLE test_codec_syntax (
       x UInt64 CODEC(ZSTD(1))
   ) ENGINE = MergeTree() ORDER BY x;
   

6. Consult the documentation for your specific ClickHouse version to verify supported codec syntax, as codec support has evolved across releases.

Best Practices

• Copy codec specifications from the official ClickHouse documentation for your version rather than writing them from memory.
• Test DDL statements in a development environment before applying them to production.
• Keep codec chains simple -- a preprocessing codec (like Delta) followed by a general-purpose compressor (like ZSTD) is the most common and well-tested pattern.
• Use explicit codec parameters rather than relying on defaults to make your schema self-documenting.

Frequently Asked Questions

Q: What is the difference between ILLEGAL_SYNTAX_FOR_CODEC_TYPE and UNKNOWN_CODEC?
A: ILLEGAL_SYNTAX_FOR_CODEC_TYPE means the syntax structure is wrong -- ClickHouse cannot parse the codec expression at all. UNKNOWN_CODEC means the syntax is valid but the codec name is not recognized. Think of it as a parsing error vs. a lookup error.

Q: Can I use expressions or variables as codec parameters?
A: No. Codec parameters must be constant integer literals. You cannot use settings, variables, or expressions as codec parameters in a column definition.

Q: Is codec chaining order important?
A: Yes. Preprocessing codecs like Delta, DoubleDelta, and Gorilla should come before general-purpose compression codecs like ZSTD or LZ4. ClickHouse applies them left to right during compression and right to left during decompression.