ClickHouse to Elasticsearch: How to Index Your Data

Sending data from ClickHouse to Elasticsearch turns analytical rows into searchable documents, so that data your analytics engine already holds can be found by keyword, filtered in a search UI, or surfaced in Kibana. Airbyte will build the pipeline without code. One constraint should shape your expectations first, though: the Elasticsearch destination is a Marketplace-tier connector available on Airbyte Core and PyAirbyte, but not on the Standard, Plus, Pro, or Enterprise Flex plans.

If you are on a paid Cloud tier, this connector will not be in your catalogue, and everything below assumes a self-managed deployment.

Why index ClickHouse data in Elasticsearch?

The two systems answer different questions. ClickHouse is exceptional at scanning a column across billions of rows and returning an aggregate. Elasticsearch is exceptional at finding the handful of documents matching a phrase, with relevance ranking and fuzzy matching that a SQL engine does not attempt.

So the sensible reason to run this pipeline is that you want text search or a search-driven interface over data that already lives in ClickHouse. The wrong reason is wanting faster aggregates, because Elasticsearch will not beat ClickHouse at that and the index will cost you considerably more storage.

Expect the indexed footprint to be a multiple of what the same data occupies in ClickHouse. ClickHouse compresses by column, storing similar values adjacent to one another; Elasticsearch stores each row as a document and adds an inverted index on top. That expansion is inherent to the conversion, and the cheapest way to control it is to select only the columns you will actually search or display.

How do you avoid duplicate documents?

The Elasticsearch destination supports full refresh overwrite, full refresh append, and incremental append. Neither deduped mode is available, which matters as soon as you run a second sync.

The mechanism that compensates is a configuration option called Upsert Records. With it enabled, the connector indexes using each record's identifier, so re-indexing the same record replaces the existing document rather than creating a second copy. This is how you get idempotent re-runs without a deduped sync mode, and for a cursor-based source that may re-read overlapping ranges it is usually what you want.

Set it during initial configuration rather than later, because switching it on an index that already holds data changes what a re-sync does to the documents already there.

Writes are performed in batches, and the documentation notes that large records may affect performance, with variable batch sizing listed as a wanted enhancement. ClickHouse tables with wide rows or large nested structures are worth testing before a full load.

Define the index mapping before the first sync

The connector maps Airbyte types onto Elasticsearch field types fairly coarsely: text to string, date to date-time, object to object, array to array, boolean to boolean, and both integer and number onto numeric types.

ClickHouse's type system is more precise than what survives. Its Decimal types carry explicit precision and scale, and its integer widths are distinct. Those distinctions flatten, so if you are indexing financial values, check what actually lands before trusting an aggregation over the index.

The bigger risk is letting Elasticsearch infer the index mapping from the first documents it sees. Dynamic mapping guesses from a sample, and a field that is null early and populated later, or numeric early and string later, produces a mapping that either rejects subsequent documents or silently coerces them. Define the mapping explicitly up front. It costs ten minutes and prevents the failure where a sync succeeds for a fortnight and then starts rejecting records with no change on your side.

Pay particular attention to ClickHouse Array and Nested columns here. They are commonly used for tags and event properties, and they are precisely the fields where a search index either becomes genuinely useful or becomes an unqueryable blob, depending on how you map them.

Setting up both ends

On the source side you need ClickHouse Server 21.3.10.1 or later and a dedicated read-only user with SELECT on the relevant database. The ClickHouse source supports full refresh and cursor-based incremental sync, and lists both incremental deletes and logical replication as coming soon, so deletions in ClickHouse will not remove documents from your index.

Note that a single source cannot span multiple ClickHouse databases. Configure a separate source per schema, each connecting to the same instance.

On the destination side you need Elasticsearch 7.x or above and an endpoint URL. Authentication is optional, but where used, the account needs permission to create an index if one does not exist and to create documents. Basic auth and API key ID with secret are both supported, along with a CA certificate.

Both connectors support SSH tunnelling to a bastion host, which is common when the ClickHouse cluster and the search cluster both sit on private networks. On each, remember the SSH login username is the operating system user on the bastion rather than a database or Elasticsearch account.

Frequently asked questions

Can I run this on a paid Airbyte Cloud plan?

The Elasticsearch destination shows as available on Core and PyAirbyte only, not on Standard, Plus, Pro, or Enterprise Flex. Plan on a self-managed deployment.

How do I stop duplicate documents building up?

Enable Upsert Records so the connector replaces documents by identifier. There is no deduped sync mode on this destination, so that setting is the mechanism.

Will deletions in ClickHouse remove documents from the index?

No. The ClickHouse source lists incremental deletes as coming soon, so a periodic full refresh overwrite is the way to bring the index back in line.

Why is my index far bigger than the ClickHouse table?

ClickHouse compresses by column while Elasticsearch stores documents plus an inverted index. The expansion is expected. Reduce it by indexing only the columns you will search or display.

Get your ClickHouse data into Elasticsearch

Define the mapping first, enable upserts, and index only the columns search actually needs. For the reverse direction, see our guide to Elasticsearch to ClickHouse. For indexing from a different source, see n8n to Elasticsearch.

Start syncing now →