How the ClickHouse RabbitMQ table engine ingests queue messages into ClickHouse tables
Messages sit in RabbitMQ queues and need to land in ClickHouse where analysts can query them. The supported method is the built-in RabbitMQ table engine, which reads from a queue, paired with a materialized view that moves each batch into a MergeTree table. The engine table itself stores nothing. Its job is to consume, and the view is what writes the rows. Collection starts in the background once the materialized view is attached, so the order of creation matters: create the target MergeTree table first, then the engine table, then the view. Before you build anything, check the RabbitMQ table engine page in the ClickHouse documentation for the version you run, because settings names, defaults and parsing behavior can change between releases.
Each consumed message is parsed according to the data format declared on the engine table, so the format setting and the column list must match the message body exactly. Decide early how the engine maps to your queues: the exchange, routing key and queue names define which messages reach ClickHouse at all, and a queue that several consumers read will split its messages between them. Test with a copy of production traffic in a pre-production cluster. Confirm how the engine handles a message that fails to parse, whether the batch is retried or the message is acknowledged, and how many rows the engine accumulates before the view flushes them. Those answers determine whether a bad producer can stall ingestion or silently drop data.
What you need in place before connecting RabbitMQ to ClickHouse
The RabbitMQ table engine is only as dependable as the versions, network paths, credentials and test environment underneath it. Confirm each of these before a consumer touches a production queue, because a gap in any one of them shows up later as stalled consumption or lost messages.
Confirm the ClickHouse line
The ClickHouse security policy lists 26.9, 26.8, 26.7 and 26.3 as versions receiving security updates, and all other versions as unsupported. Pick a line from that list and record it in your change request. Check the GitHub releases page for the current stable and LTS builds on that line before you pin a version, and read the changelog for any backward-incompatible changes that apply to your schema or queries.
Choose a RabbitMQ line with support in view
RabbitMQ 4.3 receives community support through January 31, 2027. Community support for 4.2 ended on July 31, 2026, so a 4.2 broker now depends on a valid commercial support license for fixes. Ask your team which line you will run for the full lifetime of this integration, and confirm the commercial lifecycle dates with the vendor on the Broadcom support portal if you rely on 4.2.
Map the network path between the systems
The ClickHouse server must open outbound connections to the RabbitMQ AMQP port on every node it consumes from, and the firewall rules must allow that path in both directions for connection setup. Document which ClickHouse hosts run the consumer, which broker nodes they reach, and whether traffic crosses a VLAN, a Kubernetes network policy or a cloud security group. Test the path from each ClickHouse host, not only from one jump box.
Encrypt the broker connection
Decide whether messages will cross the network in clear text, and treat that as a security decision rather than a default. Enable TLS on the RabbitMQ listener, supply the CA certificate to ClickHouse, and confirm the broker certificate names match the hostnames ClickHouse connects to. Keep credentials and certificate paths in a managed configuration location so they are not repeated in table definitions that many people can read.
Scope a dedicated RabbitMQ account
Create a RabbitMQ user for ClickHouse rather than reusing an application or administrator account. Grant it configure, write and read permissions only on the vhost, exchange and queue it needs, and confirm it can declare or bind the queues your table definition expects. Test the permissions with a message round trip before you add the materialized view, since a permission error can look like an empty queue.
Build a non-production test environment
Stand up a ClickHouse and RabbitMQ pair that matches the production versions, and load it with message sizes, rates and payload shapes taken from production. Use the test run to observe consumer throughput, the effect of block size settings and how the broker behaves under backlog. Ask your implementation partner or internal team to write down the acceptance criteria before the test starts, so the go-live decision rests on measured results.
Designing exchanges, bindings and queue types before the engine is created
Decide the exchange, routing key and binding layout before you create the RabbitMQ table engine, because the engine only sees what lands in the queue it consumes. A direct or topic exchange, bound to a queue with a specific routing key or pattern, determines which messages reach ClickHouse at all. A fanout exchange sends every message to every bound queue, which is useful when several consumers need the same stream but easy to get wrong when a message is routed into a queue that feeds an ingestion table it was never meant for. Write the routing table down: which producer publishes with which routing key, which bindings carry it, and which queue each ingestion table reads.
Give each ClickHouse ingestion table its own dedicated queue. When several tables or consumers share a queue, the backlog, acknowledgements and redelivery behavior of one pipeline affect the others, and it becomes hard to tell whether a growing queue means slow inserts, a bad message or a stopped materialized view. A dedicated queue lets you read queue depth and unacknowledged counts as a single pipeline's health signal. Confirm with the ClickHouse RabbitMQ table engine documentation how the engine names and declares its queues and exchanges in your version, so you know which objects it creates and which you must create yourself.
Choose replicated queue types for anything you cannot afford to lose. Quorum queues are durable, replicated queues based on the Raft consensus algorithm, and they are the default recommendation for new work in current RabbitMQ releases. Classic queue mirroring was removed starting with RabbitMQ 4.0, so a design that assumes mirrored classic queues will not carry over. Streams are the other replicated structure and suit high-throughput consumption, but confirm that the ClickHouse engine supports the queue type you plan to use in your exact version before committing, since engine support for each queue type is not something to assume. Test the chosen type with a representative message rate and a broker node restart before you build the materialized view around it.
Configuring RabbitMQ, ClickHouse and the materialized view in sequence
This sequence wires a RabbitMQ queue into a ClickHouse MergeTree table through the RabbitMQ table engine and a materialized view, and confirms that messages are consumed and stored as expected. Each step ends with a check you can run before moving on.
Create the exchange, queue and bindings
The RabbitMQ administrator creates the exchange, the queue and the binding that routes messages between them, using a dedicated user that has permission only on that virtual host, exchange and queue. Choose the exchange type and routing key with the producing applications, and decide whether the queue is a quorum queue, since quorum queues are durable and replicated on the Raft consensus algorithm. Confirm the setup by publishing a test message from the producer side and checking that it appears in the queue with the expected routing key. Do not reuse an administrator account for the ClickHouse connection.
Create the target MergeTree table
The ClickHouse administrator creates the destination table using a MergeTree engine, with column types and an ordering key chosen for the queries the table will serve. Columns must match the fields in the message payload, so agree on the message format with the producers before this step. Run the statement on one node of the cluster or on a standalone server, and confirm it exists with a query against the system tables. The table should be empty at this point, which gives you a clean baseline for the row counts checked later.
Create the RabbitMQ engine table
Create a table with the RabbitMQ engine that names the broker host, the exchange, the routing key and the message format, and that uses the same credentials as the dedicated RabbitMQ user. The engine table is a consumer definition, not storage, so no rows are kept in it. Keep the credentials in a named collection or in a configuration file rather than in plain SQL where your security policy requires it. Check the definition by querying the table definition in the system tables, then confirm that the table can connect by running a select and expecting no connection errors in the server log.
Set persistence and dead-letter handling
Set rabbitmq_persistent to 1 where messages must survive a broker restart, and define how unprocessable messages are handled. When the queue defines an x-dead-letter-exchange, ClickHouse enables reject_unhandled_messages automatically, so failed messages are negatively acknowledged and routed to the dead-letter target rather than being dropped. Confirm the setting by reading the engine definition back, and by publishing one deliberately malformed message in a test environment and checking that it appears in the dead-letter queue. Agree with your team who reviews that queue and how often.
Attach the materialized view
Create a materialized view that selects from the RabbitMQ engine table and inserts into the MergeTree table, with column mapping written explicitly so that schema drift is visible. Attaching the view starts background consumption, so do this after the previous steps are verified. Check that the view exists and points at the correct source and target. Then query the target table with a count after a short period and confirm that rows are arriving in step with messages published from the producer.
Confirm consumption on both sides
Compare the queue depth in RabbitMQ with the row count in the target table over a short observation window. The queue depth should fall or stay near zero, and the row count should rise by the number of messages published. Confirm that the queue shows no unacknowledged messages left behind once processing settles, and that no ClickHouse errors are logged for the consumer. If the counts diverge, check the dead-letter queue and the server log before changing any settings.
Tune block size and record the baseline
Review the block size and flush behavior of the RabbitMQ engine against the message rate you measured, adjusting only after a baseline exists. Record the queue depth, consumption rate, row counts and any dead-lettered messages as the reference for later checks. Confirm the baseline by repeating the publish test with the final settings and getting consistent results. Document the configuration in version control so that changes during upgrades or incidents can be compared with a known state.
Mapping RabbitMQ message fields to ClickHouse columns
This table maps each RabbitMQ message field to a candidate ClickHouse column, with notes on typing, defaults and what to keep for replay. Read it as a starting design: confirm every format name and setting against the RabbitMQ table engine documentation before you build the materialized view, because the engine's data format setting decides how the message body is parsed.
| Message field | Suggested ClickHouse column | Type and parsing note | Why keep it |
|---|---|---|---|
| Message body | payload (String) or parsed typed columns | Parsed only if the engine's data format matches the body; confirm the format name in the engine docs | Raw payload lets you re-parse after a schema fix |
| Routing key | routing_key (LowCardinality(String)) | Set from the delivery; useful when one queue carries several event types | Filters and debugging by route |
| Exchange name | exchange_name (LowCardinality(String)) | Shows which exchange and binding delivered the message | Confirms routing after topology changes |
| Timestamp property | message_ts (DateTime64) | Producer-set; may be absent, so decide whether a default or ingestion time applies | Event time for partitioning and ordering |
| Headers | headers (Map(String, String)) | Header values often arrive as mixed types; convert to strings or extract specific keys | Tenant, source and trace identifiers |
| Message ID and correlation ID | message_id (Nullable(String)) | Nullable when producers omit it; do not treat it as unique without checking producer behavior | Deduplication and tracing across systems |
| Redelivery and consumer state | Not mapped by default; confirm availability in the engine docs | Handle through dead-letter queues instead of assumed columns | Troubleshooting failed batches |
Choosing delivery, rejection and tuning options for each workload
The rabbitmq_persistent setting marks messages that ClickHouse inserts into RabbitMQ as persistent, which asks the broker to write them to disk. That protects data while it sits in a queue, but it adds disk I/O on the broker and can slow throughput under heavy publishing. The right setting depends on whether the source system can republish after a loss and how much latency your queues already carry.
- Durable queues first: Persistence only matters if the queue itself survives a broker restart, so confirm the queue is durable and, for production, a quorum queue.
- Publisher side: Persistence on the ClickHouse insert path does not cover messages published by other applications, which must set their own delivery mode.
- Broker disk headroom: Persistent writes grow the broker's disk and IO load, so measure queue depth and disk alarms at peak publish rate.
- Replay sources: If upstream systems can replay events, the cost of occasional loss may be lower than the throughput cost of persistence.
- Load test: Publish a representative peak for an hour with the setting on and off, then compare broker disk latency, consumer lag and any message loss after a forced broker restart.
Before you go live, confirm with your broker team which queue type you run and check that the persistence choice matches your recovery objectives.
The reject_unhandled_messages setting controls what happens when ClickHouse cannot process a message. With it enabled, ClickHouse sends a negative acknowledgement, so the broker can redeliver or dead-letter the message instead of treating it as handled. The setting is enabled automatically when the queue defines an x-dead-letter-exchange, so the decision is really about whether you want failed rows to leave the main queue at all.
- Acknowledgement semantics: A negative acknowledgement lets the broker requeue or route the message, so a bad payload can loop unless a dead-letter route or retry limit ends the cycle.
- Parse errors versus outages: A malformed payload will fail every time, while a ClickHouse outage is transient, so the two need different handling.
- Redelivery pressure: Repeated requeues of a poison message can starve the queue and inflate consumer work, so track redelivery counts.
- Unknown defaults: Confirm in the engine documentation how your version behaves when a materialized view write fails partway through a block.
- Load test: Inject a controlled share of malformed messages and a short ClickHouse outage, then check that good messages keep flowing and that failed ones appear where you expect.
Record the expected behavior for each failure class in your runbook before the first production run.
A dead-letter queue holds messages that the pipeline cannot process, so operators can inspect, fix and replay them without blocking the main flow. Designing it means choosing where failed messages go, what metadata they keep and who owns the queue. The tradeoff is between a simple single catch-all queue and separate routes for different failure types, which cost more to operate but make root cause analysis faster.
- Routing: Define the dead-letter exchange and its binding on the source queue, and confirm the broker accepts the arguments before ClickHouse consumes.
- Failure classes: Separate schema errors from transient errors if your team needs different owners or replay windows.
- Retention and capacity: Set a maximum queue length or message age on the dead-letter queue so a bad release cannot fill the broker disk.
- Replay path: Document how a corrected message is republished to the source exchange and who approves it.
- Load test: Route a batch of known-bad messages through the full path, then replay them after a schema fix and confirm rows appear once, with no duplicates in the target table.
Ask your team to name an owner for the dead-letter queue and to set an alert on its depth before the pipeline handles production events.
Block size controls how many messages ClickHouse gathers before it flushes a batch into the target table. Larger blocks create fewer, bigger parts, which generally suits ClickHouse merge behavior, but they delay rows appearing in queries. Smaller blocks reduce latency and increase the number of small inserts, which can strain merges. The right value sits where your freshness requirement and insert rate meet what the cluster can merge.
- Freshness target: Write down the longest acceptable delay between a message being published and its row being queryable.
- Insert frequency: Watch how many inserts per second the cluster receives, since many small blocks multiply part creation.
- Flush triggers: Check the engine documentation for the settings that control when a block is flushed by size or by time, and test both limits.
- Merge health: Monitor the count of active parts per partition during the test, since rising counts signal that batches are too small.
- Load test: Run the expected peak with two or three block settings, recording end-to-end latency, part counts and CPU during merges, then pick the setting that meets the freshness target with stable part counts.
Confirm the exact setting names and defaults against the RabbitMQ engine documentation for your ClickHouse version before you start the test.
Securing the RabbitMQ to ClickHouse connection
The RabbitMQ to ClickHouse pipeline has two credentials that matter: a broker identity that can only read the queues it consumes, and a ClickHouse identity that can only write the target table. Each card below covers one control on either side of that pipeline, plus the transport and secret handling that connect them.
Dedicated RabbitMQ consumer user
Create a RabbitMQ user used only by ClickHouse, with read permissions limited to the required vhost and queues. Avoid reusing an application or administrator account, because the engine needs consume access and nothing else. Review the permission pattern for each queue name, and confirm with your RabbitMQ administrator how the user is created and rotated in your environment. Check that the user cannot publish or manage exchanges.
TLS on the broker connection
Encrypt the connection between ClickHouse and the RabbitMQ broker where your deployment supports it, so credentials and message payloads do not cross the network in clear text. Confirm the exact TLS settings for your ClickHouse version in the engine documentation, including how certificates and CA files are referenced. Verify that the broker's certificate chain is trusted by the ClickHouse hosts and that certificate expiry is monitored.
Keeping credentials out of DDL
A CREATE TABLE statement for the RabbitMQ engine contains connection details, so writing the password directly into it leaves the secret in table metadata and in any saved query text. Where the engine supports named collections, move the host, user and password into a collection and reference it by name. Confirm which options can be stored this way in your version, and restrict who can read or alter named collections.
Restricted ClickHouse ingestion user
Create a dedicated ClickHouse user with only the grants the ingestion needs: creating or reading the RabbitMQ source table, inserting into the target MergeTree table, and the materialized view that connects them. The default user starts with all permissions, so disable or restrict it and do not use it for pipeline jobs. Use roles so the grant set is reviewable, and confirm the exact privileges the engine and view need in your version.
Separating read and write access
Keep the users who query analytics results separate from the ingestion identity, so a dashboard account cannot alter the source table, the materialized view or the target table. Use settings profiles or quotas to limit query resource use for analysts. Check that row policies, if used for tenant or team isolation, are applied to the target table that analysts read rather than to the ingestion path.
Rotation and access review
Plan how the RabbitMQ and ClickHouse credentials are rotated, who approves changes, and how a rotated password reaches the named collection or user without a long outage. Record which identities can read the stored secrets and review that list on a fixed schedule. Confirm with your security team whether secrets must come from a vault in your environment, and whether ClickHouse external authenticators are needed for human access alongside this pipeline.
Common RabbitMQ to ClickHouse failures and how to fix them
This table maps the failures teams most often hit when consuming RabbitMQ queues into ClickHouse, with the likely cause and the first check for each. Read it from the symptom you see, then work down to the setting that explains it. Most failures trace back to a missing materialized view, delivery or rejection settings left at their defaults, or a format that does not match the message body.
| Symptom | Likely cause | Fix | First check |
|---|---|---|---|
| Messages pile up in the queue and no rows arrive | The materialized view that reads from the RabbitMQ engine table was never created or was dropped, so no consumer collects messages | Create the materialized view that selects from the RabbitMQ table into a MergeTree target table, then confirm the queue depth falls | Query system.tables and confirm the materialized view exists and points at the RabbitMQ engine table |
| Rows are missing after a broker or server restart | Messages were published without persistence, so the broker or the queue lost them before ClickHouse consumed them | Set rabbitmq_persistent = 1 in the engine settings and publish with persistent delivery mode, and use durable queues on the broker | Check the delivery mode on a sample published message and the durability flag on the queue definition |
| Messages are redelivered again and again | Errors were not negatively acknowledged, so the broker keeps requeueing the same message | Enable reject_unhandled_messages so failed messages are rejected, and define a dead-letter exchange to hold them | Review the ClickHouse server log for parse or insert errors tied to the same message batch |
| Unprocessable messages block the queue | No dead-letter exchange is defined, so bad payloads stay in the consumer path with no place to go | Define an x-dead-letter-exchange on the queue, which enables rejection automatically, then inspect and replay dead-lettered messages after fixing the schema | Confirm the queue arguments include the dead-letter exchange and that a dead-letter queue is bound to it |
| Ingestion is slow with many small inserts | Blocks are flushed before they reach a useful size, so ClickHouse writes many small parts | Tune the block size and flush settings on the engine so each insert carries more rows, and check merge pressure on the target table | Compare rows per insert in the query log with the block size settings on the engine table |
| Insert fails with parse errors | The message format setting does not match the payload, such as JSON sent to a CSV-style format or a column order change | Set the format that matches the producer output, align column names and types with the target table, and test with a sample message before going live | Take one raw message from the queue and parse it against the chosen format outside production |
| Some messages are consumed but values look wrong or empty | Fields are missing or typed differently from the target columns, so defaults are written silently | Map each field explicitly in the materialized view query and cast types where the producer varies | Count rows with null or default values in the target table for the last ingestion window |
| Two or more ClickHouse nodes consume the same data twice or split it unevenly | The engine was attached on more than one replica without a plan for how consumers share the queue | Confirm the consumer topology against the RabbitMQ engine documentation before scaling, and test duplicate handling in a pre-production environment | List every node with the RabbitMQ engine table and the queue bindings each one uses |
Questions buyers ask before connecting RabbitMQ to ClickHouse
How much effort does a RabbitMQ to ClickHouse integration take, and what drives it?
The effort depends on how many queues feed the cluster, how varied the message schemas are, and how much failure handling the business expects. A single queue with one stable JSON schema and a simple dead-letter path is a small piece of work. Many queues with different payloads, several exchanges with routing rules, and strict rules for unprocessable messages add design, build and test time. Message volume matters too, because it sets the block size tuning and the load the ClickHouse servers must absorb. Ask for the effort to be split into design, build, load testing and handover, so each part can be checked against the written scope. Any duration you are given should trace back to those inputs.
Can our own team build this integration without outside help?
Yes, many teams can, provided they have people who understand both systems. The build needs working knowledge of RabbitMQ exchanges, queues, bindings and acknowledgements, plus ClickHouse table engines, materialized views and MergeTree target tables. Someone also needs to own the dead-letter design, the replay path for rejected messages and the monitoring of consumer behavior. If one of those skills is missing, the risk shows up as duplicated rows, silently dropped messages or a queue that fills without anyone noticing. A short internal proof of concept against a non-production broker is a sensible way to test that skill set before committing to the full build.
What do we need to provide before the integration work starts?
The work needs access and samples more than hardware. Typically the team should provide:
- Read access to the RabbitMQ management view and the exchange, queue and binding definitions for the flows in scope
- Sample messages for each queue, including malformed examples that show what fails today
- A non-production ClickHouse cluster and a non-production RabbitMQ environment with representative traffic
- Named owners for the broker, the ClickHouse cluster and the source applications, plus a change window for production steps
Agree these items in the written scope, because missing samples or late access to a test broker are common causes of schedule slippage.
What do RabbitMQ's MPL 2.0 license and support dates mean for our plan?
RabbitMQ server and its tier 1 core plugins are licensed under MPL 2.0, with some OCF files under Apache 2.0, so you can run the broker in your own infrastructure without a license fee for that software. The support status is a separate question. RabbitMQ 4.3.6 was released on September 16, 2026 with community support until January 31, 2027, and 4.2 community support ended on July 31, 2026. Support beyond those dates for some lines is available only with a valid commercial support license, for example commercial support for 4.2 runs to June 30, 2030. Confirm the version you run, its community end date and whether you need a commercial agreement, using the official release information and the Broadcom support portal.
Which RabbitMQ and ClickHouse versions should the integration target?
Target versions that are still supported on both sides at the time of build and planned operation. On the ClickHouse side, the security policy lists 26.9, 26.8, 26.7 and 26.3 as supported with security updates, and stable releases are recommended for most production use. On the RabbitMQ side, check the release information for the community end date of your line before choosing. Test the RabbitMQ table engine behavior on the exact versions you plan to run, because engine options and defaults can change between releases. Read the changelog before any upgrade, since minor backward-incompatible changes can occur.
What dead-letter and failure risks should we plan for before go-live?
The main risk is a message that the ClickHouse insert cannot accept, such as a schema mismatch, a bad type or a malformed payload. With reject_unhandled_messages enabled, errors send negative acknowledgements, and defining an x-dead-letter-exchange turns that behavior on automatically. Plan where rejected messages go, who reviews them, how they are replayed after a fix and how queue depth is alerted. Also test what happens during a ClickHouse outage, because messages must not be lost or duplicated when the consumer restarts. Ask for these failure cases to be written into the test plan with expected results.
What should a quote for this integration include?
A useful quote lists the scope in writing before the price. It should name the exchanges, queues and routing keys in scope, the target ClickHouse tables and materialized views, the dead-letter and replay design, the message samples used for testing, and the environments involved. It should also state the test depth, including load and failure tests, the handover documentation and any follow-on support. Separate one-time build work from recurring items such as monitoring changes, version upgrades or a support retainer. Ask which items depend on your own access or decisions, because those are the common sources of cost change. DeployOpen does not publish a price list, and the quote follows from the written scope.
How do we keep access and credentials secure during the integration?
Give the integration its own RabbitMQ user with permissions limited to the queues and exchanges in scope, and give ClickHouse a dedicated user rather than the default user, which starts with all permissions. Restrict that user's rights to the target tables and use roles so access can be reviewed. Store broker credentials and TLS material in a managed secrets location, and keep test data free of production personal information where possible. Confirm with your security team how the broker connection is encrypted and which network paths are allowed between the two systems.
How DeployOpen works on this
DeployOpen plans and builds the RabbitMQ to ClickHouse pipeline inside your own infrastructure, starting from a written scope that names queues, message formats and failure handling. Engagements are fixed-scope, and ongoing support is available as a retainer once the pipeline is live.
How we scope
- Inventory the exchanges, queues and routing keys that feed ClickHouse, and the consumers that depend on them today.
- Agree the message format, target table layout and which messages count as unprocessable before any build starts.
- Record the RabbitMQ version, license position and quorum queue design in the written scope, with items to confirm listed.
How we build
- Configure the RabbitMQ table engine, materialized view and persistence settings in a pre-production ClickHouse environment first.
- Wire a dead-letter exchange and test rejected messages end to end, including replay of messages moved aside for review.
- Tune block size and flush behavior against representative message volumes, and document the results alongside the configuration.
How we hand over
- Deliver the configuration, dead-letter runbook and restore-tested backup steps as documentation your team can maintain.
- Walk your operators through monitoring for consumer lag, rejected messages and insert errors, with alert thresholds you agree.
- Provide a short acceptance test script so your team can repeat the verification after future changes.
How we support
- Offer a retainer covering ClickHouse and RabbitMQ upgrades, reviewed against the changelog before each version change.
- Review queue growth, dead-letter volumes and table sizing on a schedule agreed in the retainer scope.
- Confirm the commercial support terms for your RabbitMQ release with the vendor, since community support dates differ by version.
