Elasticsearch to ClickHouse: How to Move Your Data

Moving Elasticsearch to ClickHouse takes documents built for search and puts them in a store built for aggregation. Elasticsearch is excellent at finding the records matching a query and comparatively expensive at counting, grouping, and trending across hundreds of millions of them. ClickHouse does the opposite job well.

Airbyte connects the two with no code, discovering your indices automatically and writing them into typed ClickHouse tables. One characteristic of the source shapes every decision you make, so it is worth seeing up front.

Why does full refresh shape the design?

The Elasticsearch source supports full refresh only. There is no incremental mode and no cursor, so every sync reads every document in the indices you selected.

Two things follow. Use full refresh overwrite, which replaces the ClickHouse table each run and leaves you with a snapshot that matches the source, including the absence of documents that were deleted. Full refresh append would accumulate a fresh copy of every document on every sync, which is almost never what you want here.

And set a sync frequency you can defend. Re-reading a large index costs time on both sides, and Elasticsearch calls may be rate limited depending on how your deployment is configured. If your indices are large, a daily or twice-daily schedule is usually more sensible than hourly.

There is a useful consequence of working this way. Because you are always writing a complete snapshot rather than merging changes, the deduplication machinery in the ClickHouse destination does not come into play. That means you do not need the FINAL operator in your queries, which matters a great deal when replicating a database into ClickHouse and not at all here. A plain SELECT returns what you expect.

Setting up the Elasticsearch source in Airbyte

The source needs very little: an endpoint URL and, optionally, credentials. Once connected, it automatically discovers every index in the domain and offers all of them as streams.

That convenience is worth handling carefully. A working Elasticsearch domain typically contains far more than the indices you care about, including internal and monitoring indices created by the cluster itself. Selecting everything on the connection screen means syncing operational noise into ClickHouse and paying for it on every full refresh. Pick the specific indices that hold your data and leave the rest deselected.

If your indices roll over by date, remember that each dated index appears as its own stream, so a rollover pattern will keep producing new streams you need to select as they appear.

Setting up the ClickHouse destination

Create a dedicated user rather than reusing an existing one. The ClickHouse destination needs grants to create and manage databases, to create, alter, drop and truncate tables, and to insert and select data. Server 21.8.10.19 or later is required.

One setting produces a failure whose error message points nowhere useful. If async_insert is enabled on your instance, disable it for the Airbyte user with ALTER USER airbyte_user SETTINGS async_insert = 0; Leave it on and the connection check complains that rows did not reach the check table, while writing nothing at all.

Enter the hostname without a protocol prefix, the port (8123 for HTTP or 8443 for HTTPS), the database, and credentials. Enable the JSON option if your ClickHouse version supports it, which matters here because Elasticsearch documents are frequently nested. With it on, object fields land as the native JSON type instead of strings.

How do Elasticsearch documents map to ClickHouse columns?

Version 2.0.0 of the destination writes directly into typed columns matching the source schema rather than dumping JSON blobs into raw tables, so your indices arrive queryable.

One Elasticsearch characteristic is worth understanding before you model anything downstream. Elasticsearch has no dedicated array type. Any field can hold zero or more values by default, provided they share a data type, which means a field that looks scalar in one document may hold several values in another without any schema declaring it. Since arrays always land as String in ClickHouse regardless of the JSON setting, plan to parse those with ClickHouse JSON functions.

On the rest of the mapping, timestamps become DateTime64 with millisecond precision, numerics become Decimal(38, 9), and objects follow the JSON option you set above.

Frequently asked questions

Can I sync only new documents from Elasticsearch?

No. The source supports full refresh only, with no incremental mode and no cursor. Every sync reads the whole index, so choose your schedule and your selected indices with that in mind.

Which sync mode should I use?

Full refresh overwrite. It replaces the ClickHouse table each run, so deleted documents disappear and you keep a clean snapshot. Append would stack a fresh copy of every document on every sync.

Do I need the FINAL operator in my queries?

Not for this pipeline. FINAL matters when ReplacingMergeTree is deduplicating behind the scenes, which happens with incremental dedup modes. Because this source is full refresh only, a plain SELECT returns what you expect.

Why are there so many streams to choose from?

The source discovers every index in the domain automatically, including internal and monitoring indices. Select only the ones holding data you actually want, since each selected index is re-read in full on every sync.

Why is my connection check failing on ClickHouse?

Almost always async_insert. Disable it for the Airbyte user and the check passes. The error refers to rows not arriving in a check table, which gives no hint that an insert-mode setting is responsible.

Get your Elasticsearch data into ClickHouse

Select your indices deliberately, use overwrite, disable async_insert, and let Airbyte keep ClickHouse current on the schedule you set. Replicating a database into the same store instead? See our guides to PostgreSQL to ClickHouse and MongoDB to ClickHouse.

Start syncing now →