How ClickHouse reads MongoDB collections for analytics
ClickHouse can read MongoDB collections directly through the MongoDB table engine, but that engine is read-only and is best treated as a way to look at operational data, not as the analytics store. Teams running a self-hosted ClickHouse cluster that want MongoDB data for dashboards or investigations should plan for two paths: a live read through the engine for ad hoc checks, and a scheduled copy into a MergeTree table for anything that runs repeatedly. The engine reads from the source collection on each query, so every query puts load on the MongoDB deployment you are already running for the application.
Before building anything, confirm three constraints. The engine supports only MongoDB 7 and later, so check the server version in your environment before you write the table definition. It does not yet support mongodb+srv seed lists, so connection strings must use explicit host lists. And when a document lacks a column defined in the table, the engine fills in the column's default value or NULL, which means a schema change in the application can show up as silent gaps in your results rather than as an error. Ask the MongoDB team which collections carry optional fields and how often the document shape changes, because that determines how much of the NULL and default handling you will need to write into downstream queries.
Most analytics teams end up copying the data into a MergeTree table. A MergeTree table lets you choose an ordering key for your common filters, apply TTL retention, and keep query load away from the operational database. A typical pattern is a scheduled insert from the MongoDB table into a MergeTree target, with the copy's watermark recorded so reruns do not duplicate rows. Treat the copy as data you must back up and restore-test with the rest of the cluster, because the MongoDB source will not protect it. Whether that copy is refreshed in batches or streamed is a design decision to make with your data owners before the first load.
What you need before connecting ClickHouse to MongoDB
The MongoDB table engine has a few hard requirements that you should settle before any DDL runs. The cards below cover the server version, the ClickHouse release line, the network path, a read-only account and TLS, so the engine can be tested in a pre-production environment first.
MongoDB 7 or later
The MongoDB table engine supports only MongoDB 7 and later. Confirm the exact server version in each environment, including replica set members you might read from, before writing any table definition. If your MongoDB estate runs an older major version, plan an upgrade on the MongoDB side as a separate change, because the engine will not connect to it. Record the version in the design document so the constraint is visible to the people who approve the rollout.
ClickHouse release line
Confirm that the ClickHouse version you run ships the MongoDB table engine in the release line your team supports, and check the release notes for that line before you pin it. Use the security policy to confirm the line still receives security updates. Do not assume a version has the engine because a neighboring release does. Test the same version in pre-production that you plan to run in production.
Network paths to MongoDB hosts
Every ClickHouse node that will run queries against the engine needs a route to the MongoDB hosts on the MongoDB port, and the route must be allowed by firewalls, security groups and any proxy in between. Note that queries run from individual nodes, so a cluster with several replicas needs the path from each of them. Document the source addresses so the MongoDB team can allow only those hosts.
Dedicated read-only MongoDB user
Create a MongoDB user used only by ClickHouse, granted read access to the specific databases and collections it needs and no write or administrative roles. The table engine is read-only on the ClickHouse side, but the credentials still grant the MongoDB server access, so limit them at the source. Store the password in a secrets system or named collection rather than in table DDL that gets copied into shared scripts.
TLS certificates for MongoDB connections
Decide whether connections to MongoDB will use TLS, then obtain the CA certificate chain that ClickHouse nodes must trust and confirm the hostnames on the MongoDB servers match the certificates. Test the handshake from a ClickHouse node with the same user and network path the engine will use, because certificate errors often appear only from the database host. Keep certificate renewal on the same calendar as other internal services.
Pre-production test data
Before you point analysts at the engine, load a representative sample of collections into a non-production cluster and check that the schema inference, ObjectId values and missing fields look as expected. The engine fills a default or NULL when a document lacks a column, so sample documents with sparse fields are the cases most likely to surprise a team. Record the results so the production design rests on evidence.
Configuring the MongoDB table engine step by step
This sequence takes a self-hosted ClickHouse cluster from a MongoDB user with read access to one collection to a verified table that returns the same documents the source holds. Each step ends with a check, so a fault surfaces before analysts query the table.
Create a read-only MongoDB user
The MongoDB database administrator creates a dedicated user with read-only privileges on the single database and collection ClickHouse needs, and no write or administrative roles. Keep the credentials out of shell history and in your secrets store. Confirm the role names and scopes against the MongoDB documentation for your version. The step is done when the new user authenticates from a ClickHouse host and can read one document from the collection, while an attempted insert is refused.
Confirm the MongoDB server version
The platform team checks that the MongoDB deployment runs version 7 or later, which is the minimum the ClickHouse MongoDB table engine supports. Record the exact version string from the server, including for each member of a replica set that you might connect to. Also confirm that the connection string you plan to use is a standard host and port form, because the engine does not yet accept mongodb+srv seed lists. The step is done when the version and connection format are written into the change record.
Test connectivity from every ClickHouse node
Each ClickHouse node opens its own connection to MongoDB, so test from every node rather than only from one host. Run a simple authenticated read from each server through the network path it will use, and check firewall rules, DNS resolution and any TLS settings your MongoDB deployment requires. Failures that appear on a single node usually point to routing, DNS or firewall differences between hosts. The step is done when every node returns the same document count for the collection in a test read.
Create the table with explicit column types
The engineer creates a table with the MongoDB table engine and declares every column with an explicit ClickHouse type instead of relying on inference. Map the fields you need, and mark a column as Nullable when documents may omit it, because the engine fills a default value or NULL for a missing field. Check in the engine documentation how ObjectId values are presented in your version, and test one document with an ObjectId before you rely on the mapping. The step is done when DESCRIBE TABLE shows the expected types for each column.
Run a bounded test query
Run a query with a LIMIT clause and a filter on a field that is selective in the collection, so the test does not scan the whole collection on the source database. Use a small result set and read the query log to confirm which filter conditions reached MongoDB and which were applied by ClickHouse. Note any query that fails or runs slowly for review before you widen it. The step is done when the bounded query returns the expected rows and the server log shows no errors for the connection.
Reconcile results against the source collection
Compare the ClickHouse table with the source collection using counts for the full collection and for a few filtered slices, then compare sample documents field by field. Count how many documents lack each mapped field so you know which defaults or NULL values to expect in analysis. If the counts or values differ, identify whether the cause is a type mapping, a missing field or a timing gap in the source data. The step is done when the differences are either zero or documented with an explanation that the data owner has accepted.
Mapping MongoDB fields and ObjectId values to ClickHouse columns
This table compares common MongoDB fields, ObjectId keys and sparse documents with the ClickHouse column choices they require when read through the MongoDB table engine. Read each row as a decision about column type and nullability. The main takeaway is that a missing field fills with a default or NULL, so gaps can pass silently into totals unless you test sparse documents first.
| Field or concern | What to plan in ClickHouse | What to confirm |
|---|---|---|
| Strings and text fields | Map to String columns, and reserve LowCardinality for repeated values such as status codes | How the engine returns string fields for your MongoDB 7 or later version |
| Numeric fields | Choose integer or floating-point types from the value ranges in a sample of documents | Type coercion when one collection holds both integers and decimals |
| ObjectId keys | Keep the hexadecimal text form in a String column so it matches keys in MergeTree copies | How the engine presents _id values and whether you can filter on them directly |
| Dates and timestamps | Map BSON dates to DateTime or DateTime64, and set the timezone and precision explicitly | Precision and timezone handling in your version, checked with a test collection |
| Missing fields | A document without a column fills with the column default or NULL, so counts can drift silently | Default behavior for each type, tested with documents that omit fields |
| Nullable columns | Use Nullable only where NULL means unknown, since it adds storage and query overhead | Whether the engine returns NULL or a default for your chosen column types |
| Nested documents and arrays | Decide early whether to flatten sub-fields, store them as JSON text, or skip them in the copy step | Support for nested and array types in the engine docs for your version |
| Type drift across documents | The same field may hold different types in different documents, so sample and enforce types before loading | Engine behavior on mixed-type fields, tested with a deliberately inconsistent collection |
Querying MongoDB live or copying it into MergeTree
Live queries through the MongoDB table engine suit small, occasional lookups, such as checking one order, confirming a customer record or spot-checking a recent document while investigating an issue. Each query goes to MongoDB at the moment it runs, so the result reflects the collection as it stands. Scheduled copies into a MergeTree table suit dashboards that refresh repeatedly, large scans over months of documents, and joins with other ClickHouse data, because the data is already stored in ClickHouse's columnar layout and sorted for analytical reads.
- Lookup volume: a handful of queries per hour from an analyst is a live-query pattern; a dashboard refreshing for many users is a copy pattern.
- Scan size: queries that read most of a large collection put the cost on MongoDB, not on ClickHouse.
- Joins and aggregations: combining documents with other ClickHouse tables is simpler once the data is local.
- Repeatability: a copied table gives the same answer to the same dashboard query until the next load.
- Ownership: someone must own the copy job, its schedule and its failure alerts.
Check your query log for the number of MongoDB reads per day and the size of the collections involved before deciding.
A live query is as fresh as the MongoDB read it performs. A copied table is only as fresh as its last successful load, so the refresh interval becomes a business decision: a nightly job suits monthly reporting, while operational monitoring may need loads every few minutes. Between loads, the copy can miss documents that were inserted, updated or deleted, and deletions are the easiest to overlook because a plain append job never removes rows.
- Refresh interval: agree the maximum acceptable staleness with the people reading the dashboards.
- Late writes: documents written with an old timestamp after a load window closes can be missed unless the job re-reads a lookback window.
- Deletes: decide whether deleted documents must disappear from analytics, and how the copy learns about them.
- Load timing: a long job that overlaps the next run creates duplicate or partial results.
- Reporting: label dashboards with the time of the last successful load so readers know what they see.
Confirm with the data owner how late a document may arrive and whether deletes affect reported figures.
Both options read from MongoDB, so both add work to the cluster that serves the application. A live query runs whenever a user asks for it, which makes its load hard to predict. A scheduled copy runs at known times, so it can be placed in a low-traffic window, throttled, or pointed at a secondary read preference if your MongoDB deployment and consistency needs allow it. Full collection scans during business hours are the usual cause of trouble on either path.
- Read preference: confirm with the MongoDB team whether reads may go to secondaries and what lag that implies.
- Query shape: a copy that filters on an indexed field, such as an update timestamp, reads far less than a full scan.
- Concurrency: limit how many live queries can run at once, or route them through a dedicated account with restricted rights.
- Timing: schedule full reloads outside peak application hours.
- Monitoring: watch MongoDB operation counts, replication lag and slow query logs while the copy runs.
Ask the MongoDB owner for current primary utilization and the indexes available on the fields you intend to filter.
MongoDB collections do not enforce one schema, so documents in the same collection can carry different fields, types or nested structures. The MongoDB table engine fills a default value or NULL when a document lacks a column, which keeps queries running but can hide a field that was renamed or never written. A MergeTree copy adds a further step: the column types are fixed when the table is created, so new fields or changed types require a deliberate schema change and a backfill decision.
- Missing fields: a default or NULL can mean either "not set" or "set to nothing", so define which one your reports use.
- Type changes: a field that changes from string to number will break typed columns unless the copy casts or routes it.
- New fields: live queries expose new fields only if the table definition includes them; copies need an explicit migration.
- ObjectId handling: decide how identifiers are stored and compared, and keep the same convention in both paths.
- Documentation: record the expected schema for each collection and who approves changes.
Sample documents from each collection over several weeks to measure how often fields appear, disappear or change type.
A copy job is idempotent when running it twice, or rerunning it after a failure, leaves the target table in the same state as running it once. Without that property, retries create duplicate rows and partial loads become hard to detect. The common approach is to key each row on a stable document identifier, load a bounded window, and replace or merge the rows for that window rather than appending blindly. Each run should also record its window and row counts so operators can confirm completeness.
- Stable keys: use the MongoDB document identifier as the ordering or deduplication key in the target table.
- Bounded windows: load by update time or batch range so a rerun touches a known slice of data.
- Replace semantics: rebuild a partition or window instead of appending, and keep the previous state until the new load succeeds.
- Run record: store start time, end time, source filter and row counts in a control table.
- Failure handling: alert on failed runs and on row counts that drop unexpectedly.
Run the job twice against a test collection and compare row counts and checksums before using it for reporting.
Securing the MongoDB connection and the copied data
The MongoDB engine reads live from a source system that holds production records, so the connection, the credentials and the ClickHouse users who can query the engine all need deliberate limits. Personal fields should be removed or masked before they land in analytics tables, where access rules are usually looser.
Dedicated least-privilege MongoDB role
Create a MongoDB user used only by ClickHouse, granted read access to the specific database and collections the analytics team needs and nothing else. Do not reuse an application account or an administrative account. Confirm with your MongoDB administrators which read roles apply to your deployment, and review the grants whenever a new collection is added to the integration. A narrow role limits the damage if the ClickHouse side is compromised.
TLS on the MongoDB connection
Encrypt the connection between the ClickHouse servers and MongoDB so that credentials and document contents do not cross the network in clear text. Confirm the TLS options the MongoDB table engine accepts in your ClickHouse version, and make sure the MongoDB server certificate chains to a CA that the ClickHouse hosts trust. Test the connection with a non-production user first, since a certificate mismatch often shows up only as a generic connection failure.
Credentials kept out of DDL
Avoid writing the MongoDB username and password directly into the CREATE TABLE statement, where they can appear in schema listings, logs and backups. Store them in a named collection managed by administrators, or source them from your secrets store if your deployment supports that pattern. Restrict who can read or alter named collections, and rotate the MongoDB password on a schedule your security team sets.
Restricting who can read the engine table
The MongoDB engine table queries the source system directly on every read, so anyone allowed to select from it can drive load on MongoDB and see the raw documents. Grant SELECT on the engine table only to named roles, and give analysts access to the copied MergeTree tables instead where possible. Use settings profiles or quotas to limit resource use by those roles. Refer to the access control documentation for the users, roles and quotas model.
Restricting the default user
A fresh ClickHouse installation starts with a default user that has all permissions. Before connecting MongoDB, create administrative users through the SQL workflow, assign roles, and disable or lock down the default user so it cannot reach the MongoDB engine or change access rules. Confirm the exact steps for your deployment method, since the default user is also used by some installation tooling.
Masking personal fields before analytics
Documents in MongoDB often hold names, contact details, identifiers and free-text notes that analytics users do not need. Drop these fields in the materialized view or INSERT SELECT that fills the MergeTree table, or replace them with hashed or truncated values where a join key is still required. Document which fields are excluded, and confirm with your data protection owner whether the remaining fields are personal data under your policies.
Common MongoDB integration failures and how to fix them
This table lists the failures analytics teams most often hit when reading MongoDB collections from ClickHouse, with the likely cause and the fix for each. Use it as a triage checklist before changing configuration, and confirm each fix against your own MongoDB and ClickHouse versions.
| Failure | Likely cause | Fix |
|---|---|---|
| Engine fails to connect or returns version errors | MongoDB server is older than 7, which the MongoDB table engine does not support | Confirm the server version first. Upgrade the MongoDB deployment to 7 or later, or copy the data out of the older source by another path. |
| Connection string with mongodb+srv fails | The table engine does not yet support seed lists from mongodb+srv connection strings | Use a standard mongodb:// URI that lists the hosts explicitly, and confirm current support in the ClickHouse docs before relying on SRV records. |
| Missing fields return defaults or NULL | Documents in the collection lack a column defined in the table, so the engine fills a default or NULL | Decide per column whether NULL or a default is correct, make the column Nullable where needed, and check for schema drift in source documents. |
| ObjectId filters match nothing | ObjectId values are compared as text against a string that does not match the stored value or type | Declare the column type that matches what the engine returns, compare against the same representation, and test a known _id before running large queries. |
| Queries time out on large collections | Full collection scans run without selective filters, or results are pulled in one large batch | Filter on indexed MongoDB fields where the engine can push predicates down, narrow the query, or copy the collection into a MergeTree table on a schedule. |
| Authentication or permission errors | The MongoDB user lacks read privileges on the database or collection, or the authSource is wrong | Create a dedicated read-only MongoDB user with privileges on the target collections only, set authSource to the database that holds the user, and retest the connection. |
Questions teams ask before connecting ClickHouse to MongoDB
What drives the cost of connecting ClickHouse to MongoDB?
The cost is driven by how much analysis work sits around the connection, not by the engine itself. The ClickHouse MongoDB table engine is part of the open-source server, so the software line item is zero, but the work is not. Cost rises with the number of collections, how deeply nested documents must be flattened into typed columns, how many MongoDB versions and environments must be covered, and how much history must be reconciled. A quote should separate one-time design and build work from any recurring work such as schema changes, pipeline monitoring and restore tests. Ask each provider to state which items are fixed in a written scope and which are billed as they arise.
How long does a first MongoDB connection and copy pipeline take?
Duration depends on the work involved, so the honest answer is to scope it by component. A first read-only connection to a small number of collections is mostly access setup, column mapping and validation against known documents. A copy pipeline adds initial backfill, incremental loading, a deduplication plan, monitoring and a documented restore path. Each collection with irregular fields, or each source with high write volume, adds time. DeployOpen plans and delivers work under a written scope with agreed timelines, so ask for a milestone plan that lists the collections, environments and acceptance checks before work starts. Do not accept a duration that does not name those inputs.
What must our team provide before the work starts?
Three groups of inputs decide whether the work can start on time. First, MongoDB access: a read-only database user, the target collections, and a MongoDB version that is 7 or later, since the table engine supports only MongoDB 7 and later. Second, network rules: a path from the ClickHouse hosts to the MongoDB servers, and TLS settings your security team approves. Note that mongodb+srv seed lists are not yet supported, so connection strings must use standard host lists. Third, data owners: named people who can confirm field meanings, retention expectations and which collections contain personal or regulated data.
Can our internal team connect ClickHouse to MongoDB alone?
An internal team with ClickHouse and MongoDB experience can often build a simple read-only connection alone. The table engine itself is documented and the first query is quick to test. The harder parts are the ones teams underestimate: ObjectId handling in downstream queries, defaults for fields missing from some documents, schema drift as application teams change documents, and a restore plan for the copied data. Check whether your team has someone who owns ClickHouse operations, someone who owns MongoDB, and time to test restores. If any of those three is missing, a fixed-scope engagement can fill the gap while your team keeps ownership of the running system.
When is a MongoDB copy pipeline worth building?
A copy pipeline is worth building when analysts run repeated aggregations over large or wide collections, when queries would otherwise load the production MongoDB cluster, or when the team needs stable typed columns and a retention policy. Live reads through the table engine suit occasional lookups, small collections and exploration. Copying into MergeTree tables gives ClickHouse-native compression, ordering keys and TTL retention, at the cost of a pipeline to run, monitor and reconcile. Illustrative example: a team that dashboards order events from a collection read every few minutes would usually copy them. A team that checks a handful of configuration documents once a week may not need to.
What does the engagement cover, and what does it not?
A well-written scope covers the MongoDB connection design, the table definitions and column mapping, the missing-field and ObjectId rules, any copy pipeline and its monitoring, validation queries, and the backup and restore procedure for the ClickHouse side. DeployOpen plans, implements, documents and supports self-hosted open-source platforms inside your own infrastructure, and most engagements are fixed-scope with agreed deliverables. DeployOpen does not issue audit reports or certifications, so compliance evidence is prepared for your independent auditor. Confirm in writing whether changes to MongoDB source schemas, application code and data-protection sign-off are inside or outside the scope.
What should we confirm about MongoDB versions and upgrades?
Confirm three things before you commit to a design. The ClickHouse MongoDB table engine supports only MongoDB 7 and later, so any older source cluster needs an upgrade or a different ingestion path first. Confirm which ClickHouse version your cluster runs against the version in the official documentation for the engine, because engine behavior can change between releases. Also confirm your ClickHouse upgrade cadence: stable releases ship roughly monthly, and the production guidance recommends testing with representative data and reading the changelog before upgrading, since minor backward-incompatible changes can occur. Record the tested pairing of ClickHouse and MongoDB versions in the runbook.
Testing the integration and keeping it running
Start by proving the copy matches the source before any dashboard depends on it. Compare row counts between the MongoDB collection and the ClickHouse MergeTree table for the same time window, then sample documents by _id and compare field values for each column, including the ones that are often missing. Expect differences where the engine fills a default value or NULL for an absent field, and record which fields are sparse so analysts read zeros and NULLs correctly. Repeat the comparison after each scheduled load, not only at go-live, because a load that silently skips documents is the failure most likely to go unnoticed.
Test every schema change on a pre-production cluster loaded with representative data before it reaches production. A new field, a changed type or a renamed key in MongoDB can change what the table engine returns, so run the same count and sampling checks against the staging copy and the staging MongoDB instance. Confirm your MongoDB version is 7 or later, since the engine supports only that line, and check the ClickHouse release notes for the version you run. Ask your team to confirm the exact ClickHouse version line and upgrade path against the ClickHouse production FAQ before you schedule a change.
Watch query load on the MongoDB side, because the read-only engine queries your operational database directly. Track connection counts, query latency and replication lag on the MongoDB deployment during each load window, and confirm which read preference the connection uses. Then plan a backup and restore test for every MergeTree table holding copied data. The ClickHouse backup documentation warns that a backup never test-restored will probably not restore properly when needed and recommends regular restore tests on spare clusters. Schedule that test, record its result and treat a failed restore as an incident.
How DeployOpen scopes and delivers the MongoDB integration
DeployOpen plans, builds, tests and documents ClickHouse and MongoDB integrations inside infrastructure you control. We start from a written scope that names the collections, the MongoDB version, the target MergeTree schema and the restore tests the work must pass before handover.
How we scope
- The written scope lists the MongoDB collections, fields and document shapes that analytics teams need.
- We confirm your MongoDB server runs version 7 or later and that you use a standard connection string, not mongodb+srv.
- The scope records how missing fields, ObjectId values and nested documents will map to ClickHouse column types and defaults.
How we build
- We build the read-only MongoDB table engine for exploration and a scheduled copy into MergeTree tables for analytics.
- We test ObjectId conversion, missing-field defaults and NULL handling against a representative sample of real documents.
- We set up a least-privilege MongoDB user and ClickHouse access controls, and check that queries stay within planned load.
How we hand over
- You receive the table definitions, load jobs, credentials handling and a runbook describing how each copy is refreshed.
- We document the restore test procedure so your team can prove that the ClickHouse copies can be recovered.
- Handover includes a walkthrough with the engineers who will operate the integration after the engagement ends.
How we support
- Engagements are fixed-scope, with deliverables and timelines agreed in writing before work starts.
- Ongoing support, upgrades and continuous testing for the integration are available as a retainer.
- Retainer work covers reviewing MongoDB and ClickHouse release notes before version changes that could affect the integration.
