ClickHouse DB::Exception: MongoDB error

The "DB::Exception: MongoDB error" is a general-purpose error code raised when ClickHouse encounters a failure while interacting with MongoDB that is not specifically an authentication problem. The MONGODB_ERROR code covers connection failures, query execution errors, schema mapping issues, and unexpected responses from the MongoDB server.

Impact

This error causes queries against MongoDB-backed tables and table functions to fail. Dictionaries sourced from MongoDB will not load or refresh if this error occurs during the data retrieval phase. Since the error is general, the actual impact depends on the underlying cause — it may affect a single query or all MongoDB-related operations on the server.

Common Causes

1. MongoDB server is unreachable — wrong hostname, port, or the mongod process is not running.
2. Network connectivity issues, firewall rules, or security groups blocking the connection.
3. The specified database or collection does not exist on the MongoDB server.
4. Schema mismatch between the ClickHouse table definition and the actual MongoDB document structure.
5. MongoDB query timeout — the query takes too long and is killed by the MongoDB server.
6. Unsupported MongoDB data types that ClickHouse cannot map to its own type system.
7. MongoDB replica set or sharded cluster is in an unhealthy state.
8. Connection pool exhaustion on the MongoDB server.

Troubleshooting and Resolution Steps

1. Examine the ClickHouse server log for the detailed MongoDB driver error:

   grep -i "MONGODB_ERROR\|MongoDB\|mongo" /var/log/clickhouse-server/clickhouse-server.log | tail -30
   

2. Verify that the MongoDB server is running and reachable:

   mongosh --host mongodb-host --port 27017 --eval "db.runCommand({ ping: 1 })"
   

3. Check network connectivity from the ClickHouse host:

   nc -zv mongodb-host 27017
   

4. Confirm the database and collection exist:

   use mydb
   db.getCollectionNames()
   

5. Verify the schema mapping. ClickHouse column types must be compatible with MongoDB field types. For example, MongoDB's ObjectId maps to ClickHouse String, and nested documents may need to be mapped to String (as JSON):

   CREATE TABLE mongo_table
   (
       _id String,
       name String,
       age Int32,
       metadata String  -- nested document stored as JSON string
   )
   ENGINE = MongoDB('mongodb-host:27017', 'mydb', 'mycollection', 'user', 'pass');
   

6. If the query is timing out, check the MongoDB server logs for slow queries and consider adding indexes to the MongoDB collection on frequently filtered fields.

7. For replica set issues, verify the replica set status:

   rs.status()
   

Best Practices

• Define ClickHouse table schemas that closely match the MongoDB document structure to avoid type conversion errors.
• Use the mongodb() table function for ad-hoc exploration before creating permanent MongoDB engine tables.
• Monitor MongoDB server health and connection pool utilization to prevent connection exhaustion.
• Add appropriate indexes on the MongoDB side for fields that ClickHouse will filter on, as ClickHouse can push down simple WHERE conditions.
• Test the MongoDB connection parameters independently before creating ClickHouse tables.
• Use named_collections to centralize MongoDB connection settings and simplify credential management.

Frequently Asked Questions

Q: What MongoDB versions does ClickHouse support?
A: ClickHouse supports MongoDB 3.6 and later through its MongoDB table engine. Using a more recent MongoDB version (5.0+) is recommended for better compatibility and performance.

Q: Can ClickHouse push down filters to MongoDB?
A: Yes, ClickHouse can push simple equality and comparison filters to MongoDB, which reduces the amount of data transferred. However, complex expressions and ClickHouse-specific functions are evaluated on the ClickHouse side after data retrieval.

Q: Why do I get MONGODB_ERROR when querying nested documents?
A: ClickHouse maps MongoDB nested documents to String columns containing JSON. If you try to map a nested document to a structured ClickHouse type without proper conversion, the query will fail. Map nested fields to String and use ClickHouse JSON functions to extract values.

Q: How is MONGODB_ERROR different from MONGODB_CANNOT_AUTHENTICATE?
A: MONGODB_CANNOT_AUTHENTICATE is specifically for authentication failures during the connection handshake. MONGODB_ERROR covers all other MongoDB integration failures, including connectivity, query execution, and data-related errors.