--- title: "Keep clickhouse integration elastic stack queryable" excerpt: "Map ECS fields and data streams from Elastic Agent or Logstash into ClickHouse®. Fleet, Kafka, and dual-output patterns that stay queryable." authors: "Tinybird" categories: "AI Resources" createdOn: "2026-08-16 00:00:00" publishedOn: "2026-08-16 00:00:00" updatedOn: "2026-08-16 00:00:00" status: "published" --- Elasticsearch is built for inverted-index search. Kibana is built for operator workflows on those indices. ClickHouse{% sup %}®{% /sup %} 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](https://www.tinybird.co/blog/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. ```text ┌──► 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: | Your setup | Split here | Why | | --- | --- | --- | | Fleet-managed agents everywhere | Kafka output on Agent | No second collector; replay buffer | | Central Logstash already parses logs | Logstash dual output | One filter path, two destinations | | Logs only exist in ES today | Logstash elasticsearch input | Migration/backfill only; not live ingest | | OTel standardization in progress | Collector exporters beside Elastic APM | Same trace IDs across sources; see [ClickHouse integration OpenTelemetry](https://www.tinybird.co/blog/clickhouse-integration-opentelemetry) | 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. | ECS field | ClickHouse column | Type | Notes | | --- | --- | --- | --- | | `@timestamp` | `event_time` | `DateTime64(3)` | Always UTC | | `data_stream.type` | `stream_type` | `LowCardinality(String)` | Usually `logs` | | `data_stream.dataset` | `dataset` | `LowCardinality(String)` | e.g. `nginx.access` | | `data_stream.namespace` | `namespace` | `LowCardinality(String)` | e.g. `production` | | `service.name` | `service_name` | `LowCardinality(String)` | Primary filter column | | `host.name` | `host_name` | `LowCardinality(String)` | | | `log.level` | `log_level` | `LowCardinality(String)` | Normalize case | | `trace.id` | `trace_id` | `String` | Join key, not sort key | | `message` | `message` | `String` | | | (everything else) | `ecs_json` | `String` | Park until promoted | ```sql 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](https://clickhouse.com/docs/en/engines/table-engines/integrations/kafka) or a managed connector on the far side. The three-table pattern is walkthrough-ready in [kafka to ClickHouse example](https://www.tinybird.co/blog/kafka-to-clickhouse-example). Fleet integration output (or standalone Filebeat): ```yaml 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: ```sql 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 | Signal | Likely cause | Fix | | --- | --- | --- | | `kafka_num_consumers` lag rising linearly | Under-provisioned consumers | Increase consumers up to partition count | | Spiky lag after index rollover | ES scroll backfill competing | Isolate backfill topic from live topic | | Insert failures in MV | ECS schema drift | Route bad rows to quarantine table | | Part count explosion | Small inserts, no batching | Raise `kafka_max_block_size`, tune MV batch | Add a quarantine path for events missing `@timestamp`: ```sql 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. ```ruby 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. ```ruby 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](https://www.tinybird.co/blog/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: ```sql 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](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree), query cost tracks bytes read. Point 30-day charts at an hourly rollup built as a [materialized view](https://www.tinybird.co/blog/clickhouse-create-materialized-view-example); keep raw `logs_ecs` for incident drill-down with TTL. ```sql 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](https://www.tinybird.co/blog/analyzing-nginx-logs-with-clickhouse-and-tinybird). ## 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: ```sql 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. {% cta title="Elastic-scale logs without cluster ops" text="Tinybird is managed ClickHouse with the Events API, Kafka connector, and sub-second SQL APIs. Ingest ECS logs and query them from one platform." button={href: "https://cloud.tinybird.co/signup", target: "_blank", text: "Try Tinybird free"} /%}