Keep clickhouse integration elastic stack queryable

Elasticsearch is built for inverted-index search. Kibana is built for operator workflows on those indices. ClickHouse^® is built for columnar SQL over months of events at a fraction of the storage cost. A clickhouse integration elastic stack project fails when teams treat ClickHouse as "Elasticsearch with cheaper disks" instead of translating Elastic Common Schema (ECS) into MergeTree tables with the right partition and sort keys.

The Elastic Stack does not ship a ClickHouse output. Elastic Agent and Beats write to Elasticsearch or Kafka. Logstash fans out. Kibana reads. Your job is to decide where the parallel stream splits off and how ECS fields land as typed columns before anyone runs a 90-day aggregation. Pipeline failure modes at Kafka scale are covered in why Kafka pipelines fail.

Where to split the pipeline

Most broken integrations scroll Elasticsearch into ClickHouse on a cron. That pattern overloads the cluster, misses events during index rollovers, and never catches up at Fleet scale. Fan out at collection time instead.

                    ┌──► Elasticsearch data stream ──► Kibana / Elastic Security
Elastic Agent / Beats ┤
                    └──► Kafka topic ──► ClickHouse MergeTree ──► SQL / APIs

Logstash (optional middle layer)
  input beats ── filter (ECS normalize) ──┬── elasticsearch output
                                          └── kafka or http output ──► ClickHouse

Use this decision tree:

Scrolling Elasticsearch is a backfill tool. It is not a production ingest path for high-volume logs.

ECS to MergeTree: the field contract

ECS uses dotted field names (service.name, log.level, @timestamp). ClickHouse wants flat, typed columns. Write the mapping once and enforce it at the Kafka materialized view or Logstash filter stage.

CREATE TABLE logs_ecs
(
    event_time    DateTime64(3),
    stream_type   LowCardinality(String),
    dataset       LowCardinality(String),
    namespace     LowCardinality(String),
    service_name  LowCardinality(String),
    host_name     LowCardinality(String),
    log_level     LowCardinality(String),
    trace_id      String,
    message       String,
    ecs_json      String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (service_name, log_level, event_time)
TTL event_time + INTERVAL 90 DAY;

Do not embed rolling Elasticsearch index names (.ds-logs-application-production-2026.08.12-000001) in the sort key. Data streams roll backing indices automatically; ClickHouse partitions on event_time and stores dataset + namespace as dimensions.

Fleet Agent through Kafka

Elastic Agent does not ship a ClickHouse sink. The durable pattern is Kafka in the middle, then the ClickHouse Kafka engine or a managed connector on the far side. The three-table pattern is walkthrough-ready in kafka to ClickHouse example.

Fleet integration output (or standalone Filebeat):

output.kafka:
  hosts: ["kafka-broker:9092"]
  topic: "logs-ecs"
  partition.round_robin:
    reachable_only: false
  required_acks: 1
  compression: gzip
  codec.json:
    pretty: false

Kafka engine queue + materialized view:

CREATE TABLE logs_kafka (raw String) ENGINE = Kafka
SETTINGS
    kafka_broker_list = 'kafka-broker:9092',
    kafka_topic_list = 'logs-ecs',
    kafka_group_name = 'clickhouse_logs_consumer',
    kafka_format = 'JSONAsString',
    kafka_num_consumers = 2;

CREATE MATERIALIZED VIEW logs_ecs_mv TO logs_ecs AS
SELECT
    parseDateTime64BestEffort(JSONExtractString(raw, '@timestamp'), 3) AS event_time,
    JSONExtractString(raw, 'data_stream', 'type') AS stream_type,
    JSONExtractString(raw, 'data_stream', 'dataset') AS dataset,
    JSONExtractString(raw, 'data_stream', 'namespace') AS namespace,
    JSONExtractString(raw, 'service', 'name') AS service_name,
    JSONExtractString(raw, 'host', 'name') AS host_name,
    JSONExtractString(raw, 'log', 'level') AS log_level,
    JSONExtractString(raw, 'trace', 'id') AS trace_id,
    JSONExtractString(raw, 'message') AS message,
    raw AS ecs_json
FROM logs_kafka
WHERE JSONHas(raw, '@timestamp');

Partition Kafka by service.name or environment, not by host. One topic per host creates thousands of underfilled partitions and uneven consumer lag.

Consumer lag tuning

Add a quarantine path for events missing @timestamp:

CREATE TABLE logs_quarantine
(
    ingest_time DateTime64(3) DEFAULT now64(3),
    raw String,
    reject_reason LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY ingest_time
TTL ingest_time + INTERVAL 7 DAY;

Alert when quarantine row rate exceeds baseline. Silent drops in the MV are worse than visible quarantine growth.

Logstash dual output without silent drops

When Logstash already sits between Beats and Elasticsearch, add a second output instead of deploying another collector tier.

output {
  elasticsearch {
    hosts => ["https://es.example.com:9200"]
    data_stream => "true"
    data_stream_type => "logs"
    data_stream_dataset => "application"
    data_stream_namespace => "production"
    user => "elastic"
    password => "${ES_PASSWORD}"
  }

  kafka {
    bootstrap_servers => "kafka-broker:9092"
    topic_id => "logs-ecs"
    codec => "json"
  }
}

The data_stream => "true" flag writes to a data stream instead of dated index patterns. The Kafka branch feeds the ClickHouse path above.

Critical ops detail: enable persistent queues on outputs. When ClickHouse or Kafka slows during a burst, Logstash drops the ClickHouse branch first if queues are in-memory only. Elasticsearch stays current while ClickHouse falls hours behind with no alert.

For direct HTTP insert (small volume), use the ClickHouse HTTP interface with JSONEachRow. Prefer Kafka when bursts exceed single-request limits.

Backfill from Elasticsearch data streams

When logs already live in Elasticsearch and you need ClickHouse history without re-instrumenting hosts, use the Logstash elasticsearch input on a schedule. This is migration latency (minutes), not streaming.

input {
  elasticsearch {
    hosts => ["https://es.example.com:9200"]
    data_stream => "true"
    data_stream_type => "logs"
    data_stream_dataset => "application"
    data_stream_namespace => "production"
    schedule => "*/5 * * * *"
    query => '{ "sort": ["@timestamp"], "query": { "range": { "@timestamp": { "gte": ":sql_last_value" } } } }'
    docinfo => true
    docinfo_fields => ["_index", "_id"]
  }
}

Store es_index and es_id in ClickHouse. Use ReplacingMergeTree when overlapping fetches are possible. Track cursor state in :sql_last_value or a dedicated metadata table so restarts do not skip windows or duplicate rows. Log pipeline design at scale is in real-time logs analytics architectures.

Kibana stays on Elasticsearch; ClickHouse serves SQL

Kibana queries Elasticsearch. It does not push logs to ClickHouse and cannot read MergeTree tables natively. Keep Kibana for Discover, Elastic-native dashboards, and Elastic Security workflows.

Use ClickHouse for:

• 90-day error rates and SLO backtests
• Joins with product, billing, or user tables already in your warehouse
• Customer-facing log explorers backed by HTTP SQL APIs
• Rollups that would scan terabytes of inverted-index storage in Elasticsearch

Export ClickHouse metrics to Grafana or your app. Do not try to wire Kibana to ClickHouse directly.

Join Elastic logs to product events

Share trace_id, service_name, and tenant IDs across tables:

SELECT
    l.service_name,
    count() AS error_count,
    p.plan_tier,
    count(DISTINCT p.organization_id) AS affected_orgs
FROM logs_ecs l
INNER JOIN product_events p ON l.trace_id = p.trace_id
WHERE l.event_time >= now() - INTERVAL 24 HOUR
  AND l.log_level = 'error'
GROUP BY l.service_name, p.plan_tier
ORDER BY error_count DESC;

Join at query time or in a Tinybird Pipe. Do not copy product tables into Elasticsearch for the join.

Hourly rollups for long-range dashboards

Raw ECS logs grow fast. Per MergeTree docs, query cost tracks bytes read. Point 30-day charts at an hourly rollup built as a materialized view; keep raw logs_ecs for incident drill-down with TTL.

CREATE TABLE logs_1h
(
    hour          DateTime,
    service_name  LowCardinality(String),
    log_level     LowCardinality(String),
    event_count   UInt64
)
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(hour)
ORDER BY (service_name, log_level, hour);

CREATE MATERIALIZED VIEW logs_1h_mv TO logs_1h AS
SELECT
    toStartOfHour(event_time) AS hour,
    service_name,
    log_level,
    count() AS event_count
FROM logs_ecs
GROUP BY hour, service_name, log_level;

Acceptance tests before production

1. Schema: Agent events populate @timestamp, service.name, and log.level without null spikes
2. Parity: Hourly row counts match Elasticsearch data stream within known lag (often under 2%)
3. Lag: p95 Beat-to-ClickHouse latency under SLO (30–120 seconds typical)
4. Quarantine: logs_quarantine near zero in steady state
5. Dual output: ClickHouse ingest failure triggers Logstash queue growth or alerts
6. Joins: Sample trace_id join to product table returns rows in under 500 ms

Structured log parsing examples for access-log shaped ECS datasets are in analyzing nginx logs with ClickHouse.

Tinybird on the Elastic fan-out

Operating Kafka consumers, merge tuning, and API auth at Elastic ingest volume is its own job. Tinybird is managed ClickHouse with streaming ingest and sub-second SQL APIs. Elasticsearch and Kibana stay the operator layer; Tinybird is the analytical store and HTTP API layer for long-retention ECS logs.

Wire the same fan-out you already built

Point the Kafka topic from Fleet or Logstash at Tinybird instead of self-hosting the Kafka engine consumer chain:

1. Events API — For Logstash http output or low-volume dual-write. Default limits: 100 requests per second per Data Source; 10 MB per request on Free plans, 100 MB on Developer, SaaS, and Enterprise plans.
2. Kafka connector — For Fleet-scale volume. Confluent Platform and Cloud, Redpanda, and AWS MSK are supported connector setups.

Events land in MergeTree Data Sources within seconds. You skip consumer group tuning, insert batch sizing, and merge backlog firefighting on your own cluster.

ECS-shaped Data Sources and Pipes

Map your ECS contract directly to Tinybird Data Sources: service_name, log_level, dataset, namespace, and event_time as typed columns; park the long tail in ecs_json until promoted.

Publish log aggregation Pipes as HTTP endpoints. Example error rate by service:

NODE service_error_rate
SQL >
    SELECT
        service_name,
        countIf(log_level IN ('error', 'critical')) AS errors,
        count() AS total,
        errors / total AS error_rate
    FROM logs_ecs
    WHERE event_time >= now() - INTERVAL 15 MINUTE
    GROUP BY service_name
    ORDER BY error_rate DESC

TYPE endpoint

For tenant-scoped product UIs, use JWT fixed parameters so organization_id cannot be overridden from the URL.

Safe schema changes when Fleet updates integrations

Test ECS field mappings on a Branch before Fleet rolls out an integration update. Schema iteration supports safe migrations with zero downtime when new ECS fields need typed columns.

Monitor workspace ingest and query health via Service Data Sources. Canva reports 3.6 PB processed per month and 54 ms p99 query latency on Tinybird's product page. Resend reports 100 TB processed per month and 62 ms p90 query latency without relying on cache. Tinybird is SOC 2 Type II certified.

5 operational mistakes on Elastic Stack ClickHouse integrations

1. Treating Kibana as the ingest path

Kibana reads Elasticsearch only. Saved searches are not a pipeline.

Fix: Fan out at Agent, Beats, or Logstash. ClickHouse receives the same events in parallel.

2. Ignoring data stream semantics

Elasticsearch rolls backing indices daily. One unpartitioned ClickHouse table without TTL becomes a merge nightmare.

Fix: PARTITION BY toYYYYMM(event_time) plus TTL. Name tables after dataset/namespace, not backing index names.

3. Storing full ECS JSON without filter columns

Every dashboard parses JSON at read time and times out.

Fix: Extract service.name, log.level, host.name, and @timestamp at ingest. Keep ecs_json for ad hoc fields.

4. Logstash dual output without persistent queues

ClickHouse backpressure drops the analytics branch silently while Elasticsearch stays healthy.

Fix: Persistent queues on outputs. Alert on ClickHouse consumer lag and Logstash plugin failures.

5. Sort key optimized for document lookup

ORDER BY on document IDs behaves like Elasticsearch. Aggregations by service.name over 30 days scan everything.

Fix: ORDER BY (service_name, log_level, event_time). Split tables if you need both full-text search and columnar analytics.

What the integration comes down to

Elastic Stack integration with ClickHouse is an ECS translation problem at the fan-out point. Elasticsearch and Kibana stay search and operator UI. ClickHouse holds months of structured logs for SQL, joins, and product APIs. Fleet-to-Kafka is the default for new deployments; Logstash dual output fits when parsing already centralizes there; Elasticsearch scroll is for backfill only.

Write the ECS-to-column mapping before you wire the second destination. Without it, every new Fleet integration update breaks ClickHouse inserts or silently drops fields.

Frequently Asked Questions (FAQs)

Should Elasticsearch or ClickHouse be canonical for logs?

Elasticsearch for Elastic Stack search, Kibana, and Elastic Security. ClickHouse for long-retention SQL, product joins, and customer-facing log APIs. Dual-write from Agent or Logstash keeps both aligned.

Can Elastic Agent write directly to ClickHouse?

No native output exists. Use Kafka from Agent, Logstash dual output, or an OTel collector exporter.

How do data streams change ClickHouse schema design?

Map type, dataset, and namespace to LowCardinality columns. Partition on time, not on rolling index names.

How do I join Elastic logs with product events in ClickHouse?

Share trace.id, service.name, and tenant IDs across tables. Join in SQL or Tinybird Pipes at query time.

Does Tinybird replace Elasticsearch or Kibana?

No. Tinybird is managed ClickHouse with ingest and SQL APIs. Elasticsearch and Kibana remain the operator layer.

What request size limits apply to HTTP ingest from Logstash?

Tinybird Events API: 10 MB/request on Free, 100 MB on paid plans, 100 req/sec per Data Source by default. Batch events or use the Kafka connector at higher volume.