ClickHouse DB::Exception: Protobuf bad cast

The PROTOBUF_BAD_CAST error occurs when ClickHouse cannot convert data between a ClickHouse column type and the corresponding Protobuf field type. This typically surfaces during data import or export using the Protobuf format, and it means the type mapping between your .proto schema and your ClickHouse table definition is incompatible.

Impact

Queries that read from or write to Protobuf-formatted data will fail when this error is raised. Any INSERT or SELECT operation involving the Protobuf format will be aborted for the affected rows. If you are using Kafka or another streaming engine with Protobuf serialization, the consumer will stall on the problematic messages until the schema mismatch is resolved.

Common Causes

1. Incompatible type pairing -- A ClickHouse column type has no valid conversion path to or from the Protobuf field type. For example, mapping a Date column to a Protobuf bool field.
2. Precision or range overflow -- The ClickHouse type can hold values outside the range of the Protobuf type, such as mapping a UInt64 column to an int32 Protobuf field.
3. Enum mismatches -- A ClickHouse Enum8 or Enum16 column is paired with a Protobuf field whose enum values do not align.
4. Schema evolution gone wrong -- The .proto file was updated (e.g., changing a field from string to int64) without updating the corresponding ClickHouse table schema.
5. Nested type confusion -- A ClickHouse Tuple or Nested column is mapped to a Protobuf scalar field instead of a nested message type.

Troubleshooting and Resolution Steps

1. Identify the offending column and field: The error message usually includes the ClickHouse column name and the Protobuf field name that caused the cast failure. Read the full error text carefully to pinpoint the pair.

2. Review your type mappings: Consult the ClickHouse Protobuf format documentation for the supported type mapping table. Ensure every column-to-field pair uses a compatible combination. For example:

   • UInt32 maps to uint32
   • String maps to string or bytes
   • Float64 maps to double

3. Check your .proto schema file:

   cat /path/to/your/schema.proto
   

   Compare each field type against the corresponding ClickHouse column type in your table definition:

   DESCRIBE TABLE your_table;
   

4. Adjust the ClickHouse column type or the Protobuf field type: If the ClickHouse column is UInt64 but the Protobuf field is int32, either change the Protobuf field to uint64 or alter the ClickHouse column:

   ALTER TABLE your_table MODIFY COLUMN your_column UInt32;
   

5. Handle enum mappings explicitly: When using enums, ensure that the numeric values and names in the Protobuf enum definition match the ClickHouse Enum definition. ClickHouse maps enums by value, not by name.

6. Test with a small dataset:

   SELECT * FROM your_table LIMIT 10 FORMAT Protobuf
       SETTINGS format_schema = 'your_schema:YourMessage';
   

   This will surface the cast error quickly without processing the entire table.

Best Practices

• Keep your .proto schema file and ClickHouse table schema in sync -- ideally managed through the same version control repository.
• Use a CI check that validates type compatibility between your Protobuf definitions and ClickHouse DDL.
• Prefer wider numeric types in ClickHouse (e.g., Int64 over Int32) when the Protobuf field is int64, to avoid range-related cast errors.
• Document your type mapping conventions so that team members do not introduce mismatches when modifying schemas.
• When evolving schemas, update both the .proto file and the ClickHouse table in the same deployment step.

Frequently Asked Questions

Q: Which ClickHouse types are compatible with Protobuf string?
A: ClickHouse String, FixedString, UUID, IPv4, IPv6, and date/time types can generally be serialized to a Protobuf string field. Numeric types cannot.

Q: Can I map a Nullable column to a Protobuf field?
A: Protobuf 3 does not natively support null values. ClickHouse will serialize NULL as the default value for the Protobuf field type (e.g., 0 for integers, empty string for strings). If you need explicit null handling, consider using a wrapper message or a separate boolean flag in your .proto schema.

Q: The error appeared after upgrading ClickHouse. What changed?
A: Newer versions of ClickHouse may enforce stricter type casting rules for Protobuf serialization. Review the release notes for any changes to Protobuf format handling and adjust your type mappings accordingly.

Q: How do I debug which row is causing the cast failure?
A: Use input_format_allow_errors_num and input_format_allow_errors_ratio settings to skip bad rows and log them. Alternatively, export a small batch and inspect the data manually to find values that fall outside the target type's range.