Migrating data to ClickStack from Elastic

The equivalent ClickHouse table schema:ClickHouse schema

```
SET enable_json_type = 1;

CREATE TABLE logs_system_syslog
(
    `@timestamp` DateTime,
    `agent` Tuple(
        ephemeral_id String,
        id String,
        name String,
        type String,
        version String),
    `cloud` Tuple(
        account Tuple(
            id String),
        availability_zone String,
        image Tuple(
            id String),
        instance Tuple(
            id String),
        machine Tuple(
            type String),
        provider String,
        region String,
        service Tuple(
            name String)),
    `data_stream` Tuple(
        dataset String,
        namespace String,
        type String),
    `ecs` Tuple(
        version String),
    `elastic_agent` Tuple(
        id String,
        snapshot UInt8,
        version String),
    `event` Tuple(
        agent_id_status String,
        dataset String,
        ingested DateTime,
        module String,
        timezone String),
    `host` Tuple(
        architecture String,
        containerized UInt8,
        hostname String,
        id String,
        ip Array(Variant(IPv4, IPv6)),
        mac Array(String),
        name String,
        os Tuple(
            build String,
            codename String,
            family String,
            kernel String,
            name String,
            platform String,
            type String,
            version String)),
    `input` Tuple(
        type String),
    `log` Tuple(
        file Tuple(
            path String),
        offset Int64),
    `message` String,
    `process` Tuple(
        name String,
        pid Int64),
    `system` Tuple(
        syslog JSON)
)
ENGINE = MergeTree
ORDER BY (`host.name`, `@timestamp`)
```

Note that:

- Tuples are used to represent nested structures instead of dot notation
- Used appropriate ClickHouse types based on the mapping:
  - `keyword` → `String`
  - `date` → `DateTime`
  - `boolean` → `UInt8`
  - `long` → `Int64`
  - `ip` → `Array(Variant(IPv4, IPv6))`. We use a [`Variant(IPv4, IPv6)`](/docs/sql-reference/data-types/variant) here as the field contains a mixture of [`IPv4`](/docs/sql-reference/data-types/ipv4) and [`IPv6`](/docs/sql-reference/data-types/ipv6).
  - `object` → `JSON` for the syslog object whose structure is unpredictable.
- Columns `host.ip` and `host.mac` are explicit `Array` type, unlike in Elasticsearch where all types are arrays.
- An `ORDER BY` clause is added using timestamp and hostname for efficient time-based queries
- `MergeTree`, which is optimal for log data, is used as the engine type

**This approach of statically defining the schema and using the JSON type selectively where required [is recommended](/docs/integrations/data-formats/json/schema#handling-semi-structured-dynamic-structures).**

This strict schema has a number of benefits:

- **Data validation** – enforcing a strict schema avoids the risk of column explosion, outside of specific structures.
- **Avoids risk of column explosion**: although the JSON type scales to potentially thousands of columns, where subcolumns are stored as dedicated columns, this can lead to a column file explosion where an excessive number of column files are created that impacts performance. To mitigate this, the underlying [Dynamic type](/docs/sql-reference/data-types/dynamic) used by JSON offers a [`max_dynamic_paths`](/docs/sql-reference/data-types/newjson#reading-json-paths-as-sub-columns) parameter, which limits the number of unique paths stored as separate column files. Once the threshold is reached, additional paths are stored in a shared column file using a compact encoded format, maintaining performance and storage efficiency while supporting flexible data ingestion. Accessing this shared column file is, however, not as performant. Note, however, that the JSON column can be used with [type hints](/docs/integrations/data-formats/json/schema#using-type-hints-and-skipping-paths). "Hinted" columns will deliver the same performance as dedicated columns.
- **Simpler introspection of paths and types**: although the JSON type supports [introspection functions](/docs/sql-reference/data-types/newjson#introspection-functions) to determine the types and paths that have been inferred, static structures can be simpler to explore e.g. with `DESCRIBE`.

  

Alternatively, you can simply create a table with one `JSON` column.

```
SET enable_json_type = 1;

CREATE TABLE syslog_json
(
 `json` JSON(`host.name` String, `@timestamp` DateTime)
)
ENGINE = MergeTree
ORDER BY (`json.host.name`, `json.@timestamp`)
```

Note

We provide a type hint for the `host.name` and `timestamp` columns in the JSON definition as we use it in the ordering/primary key. This helps ClickHouse know this column won't be null and ensures it knows which sub-columns to use (there may be multiple for each type, so this is ambiguous otherwise).

This latter approach, while simpler, is best for prototyping and data engineering tasks. For production, use `JSON` only for dynamic sub structures where necessary.

For more details on using the JSON type in schemas, and how to efficiently apply it, we recommend the guide ["Designing your schema"](/docs/integrations/data-formats/json/schema).

Install

### Install `elasticdump`[​](#install-elasticdump "Direct link to install-elasticdump")

We recommend [`elasticdump`](https://github.com/elasticsearch-dump/elasticsearch-dump) for exporting data from Elasticsearch. This tool requires `node` and should be installed on a machine with network proximity to both Elasticsearch and ClickHouse. We recommend a dedicated server with at least 4 cores and 16GB of RAM for most exports.

```
npm install elasticdump -g
```

`elasticdump` offers several advantages for data migration:

- It interacts directly with the Elasticsearch REST API, ensuring proper data export.
- Maintains data consistency during the export process using the Point-in-Time (PIT) API - this creates a consistent snapshot of the data at a specific moment.
- Exports data directly to JSON format, which can be streamed to the ClickHouse client for insertion.

Where possible, we recommend running both ClickHouse, Elasticsearch, and `elastic dump` in the same availability zone or data center to minimize network egress and maximize throughput.

Install ClickHouse client

### Install ClickHouse client[​](#install-clickhouse-client "Direct link to Install ClickHouse client")

Ensure ClickHouse is [installed on the server](/docs/install) on which `elasticdump` is located. **Don't start a ClickHouse server** - these steps only require the client.

Stream data

### Stream data[​](#stream-data "Direct link to Stream data")

To stream data between Elasticsearch and ClickHouse, use the `elasticdump` command - piping the output directly to the ClickHouse client. The following inserts the data into our well structured table `logs_system_syslog`.

```
# export url and credentials
export ELASTICSEARCH_INDEX=.ds-logs-system.syslog-default-2025.06.03-000001
export ELASTICSEARCH_URL=
export ELASTICDUMP_INPUT_USERNAME=
export ELASTICDUMP_INPUT_PASSWORD=
export CLICKHOUSE_HOST=
export CLICKHOUSE_PASSWORD=
export CLICKHOUSE_USER=default

# command to run - modify as required
elasticdump --input=${ELASTICSEARCH_URL} --type=data --input-index ${ELASTICSEARCH_INDEX} --output=$ --sourceOnly --searchAfter --pit=true | 
clickhouse-client --host ${CLICKHOUSE_HOST} --secure --password ${CLICKHOUSE_PASSWORD} --user ${CLICKHOUSE_USER} --max_insert_block_size=1000 \
--min_insert_block_size_bytes=0 --min_insert_block_size_rows=1000 --query="INSERT INTO test.logs_system_syslog FORMAT JSONEachRow"
```

Note the use of the following flags for `elasticdump`: