ClickHouse DB::Exception: Cassandra internal error

The "DB::Exception: Cassandra internal error" is raised when ClickHouse encounters a failure while communicating with an Apache Cassandra cluster through the Cassandra table engine. The CASSANDRA_INTERNAL_ERROR code wraps errors from the Cassandra C++ driver, including connection failures, query execution errors, and data serialization problems.

Impact

This error prevents ClickHouse from reading data from Cassandra-backed tables. Queries against Cassandra engine tables will fail, and dictionaries sourced from Cassandra will not load or refresh. Since Cassandra is typically used as an external data source for lookups or enrichment, the impact extends to any ClickHouse query that joins with or references a Cassandra table.

Common Causes

1. Cassandra cluster is unreachable — wrong contact points, port, or the cluster is down.
2. Authentication failure — wrong username or password, or the Cassandra user does not exist.
3. The specified keyspace or table does not exist in Cassandra.
4. Schema mismatch between the ClickHouse table definition and the actual Cassandra table structure.
5. Cassandra query timeout — the query exceeds the server-side timeout.
6. Network connectivity issues or firewall blocking the native transport port (default 9042).
7. TLS/SSL handshake failure when connecting to a Cassandra cluster that requires encryption.

Troubleshooting and Resolution Steps

1. Check the ClickHouse server log for the Cassandra driver error:

   grep -i "CASSANDRA_INTERNAL_ERROR\|Cassandra\|cassandra" /var/log/clickhouse-server/clickhouse-server.log | tail -20
   

2. Verify the Cassandra cluster is reachable:

   nc -zv cassandra-host 9042
   cqlsh cassandra-host 9042 -u cassandra_user -p password -e "DESCRIBE KEYSPACES;"
   

3. Confirm the keyspace and table exist:

   -- In cqlsh
   USE my_keyspace;
   DESCRIBE TABLE my_table;
   

4. Review the ClickHouse table definition for correctness:

   CREATE TABLE cassandra_table
   (
       id UInt64,
       name String,
       value Float64
   )
   ENGINE = Cassandra
   SETTINGS
       cassandra_host = 'cassandra-host',
       cassandra_port = 9042,
       cassandra_keyspace = 'my_keyspace',
       cassandra_table = 'my_table',
       cassandra_username = 'user',
       cassandra_password = 'pass';
   

5. Verify that column types are compatible between ClickHouse and Cassandra. For example, Cassandra uuid maps to ClickHouse UUID, and text maps to String.

6. If queries are timing out, check Cassandra's read_request_timeout_in_ms setting and consider increasing it or optimizing the query.

7. For TLS connections, ensure the Cassandra driver configuration includes the correct certificate paths.

Best Practices

• Specify multiple contact points in the Cassandra configuration for cluster discovery and failover.
• Use a dedicated Cassandra user for ClickHouse with read-only access to the required keyspaces.
• Match ClickHouse column types carefully to Cassandra types to avoid serialization errors.
• Test Cassandra connectivity and queries with cqlsh from the ClickHouse host before creating tables.
• Monitor Cassandra cluster health and node availability alongside ClickHouse operations.
• Set appropriate consistency levels for ClickHouse reads based on your data accuracy requirements.

Frequently Asked Questions

Q: What Cassandra versions does ClickHouse support?
A: ClickHouse uses the DataStax C++ driver, which supports Cassandra 2.1 and later, as well as DataStax Enterprise. Cassandra 3.x and 4.x are the most commonly used versions with ClickHouse.

Q: Can ClickHouse write data to Cassandra?
A: The Cassandra table engine in ClickHouse is primarily designed for reading. INSERT operations are not supported through this engine. For writing to Cassandra, use external tools or application-level logic.

Q: Why do I get CASSANDRA_INTERNAL_ERROR with a timeout message?
A: Cassandra queries that scan large amounts of data or hit many partitions can exceed the server-side timeout. Add more specific WHERE conditions, create appropriate secondary indexes in Cassandra, or increase the timeout on the Cassandra server.

Q: Does ClickHouse push down WHERE clauses to Cassandra?
A: Yes, ClickHouse can push down simple conditions on the partition key and clustering columns to Cassandra, which significantly reduces the amount of data transferred. Conditions on non-key columns are evaluated on the ClickHouse side.