--- title: "Keep clickhouse integration splunk queryable" excerpt: "Dual-index Splunk and ClickHouse®: HEC placement, sourcetype contracts, SPL-to-SQL rollups, and frozen-bucket replay without license blowout." 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" --- Splunk pricing scales with ingest volume and indexed field count. ClickHouse{% sup %}®{% /sup %} pricing scales with stored bytes and query patterns. Most teams that search for **clickhouse integration splunk** are not trying to replace SPL keyword search. They want SQL aggregations, multi-month retention, and product APIs without paying Splunk license rates on every event they only ever aggregate. For architecture patterns at PB scale, see [real-time logs analytics architectures](https://www.tinybird.co/blog/real-time-logs-analytics-architectures). Splunk never forwards to ClickHouse natively. The bridge is always explicit: HTTP Event Collector (HEC) dual-write, universal forwarder cloning, or batch replay from frozen buckets in S3. ## Dual-index: what each system owns Dual-index means Splunk stays the investigation UI. ClickHouse serves SQL analytics, embedded dashboards, and HTTP APIs. Neither system is a dumb mirror of the other. | Workload | Splunk | ClickHouse | | --- | --- | --- | | Ad hoc keyword search on `_raw` | Primary | Fallback only | | Field discovery during incidents | Primary | Predefined schema | | 90-day error rate by index/sourcetype | Expensive (`tstats` over hot/warm) | Cheap with rollups | | Join logs to billing or product tables | Awkward | Native SQL | | Sub-second log API in your product | Not the default path | Tinybird Pipes / direct SQL | | Compliance archive at PB scale | Frozen buckets + license | TTL + object storage tiering | Write a field contract before you wire the bridge. SPL dashboards and ClickHouse rollups must count the same events or on-call will not trust either number during an outage. ## HEC topology: indexers and heavy forwarders only The HTTP Event Collector accepts token-authenticated HTTP POSTs on port **8088** (`/services/collector/event` for JSON, `/services/collector/raw` for raw text). HEC must live on **indexers or heavy forwarders**. It is **not supported on universal forwarders**. Universal forwarders collect via `inputs.conf` and deliver via Splunk-to-Splunk (`tcpout`) on port 9997. They do not run HEC inputs. Teams that point application agents at a UF HEC endpoint get silent failure or misrouted events. ```text App agents ──► HEC on indexer :8088 ──┬──► Splunk index (search, SPL, alerts) └──► HTTP POST ──► ClickHouse / Tinybird Events API Host logs ── universal forwarder ── tcpout ──► indexer │ └── (clone via heavy forwarder bridge) ──► Kafka ──► ClickHouse ``` Validate JSON and raw HEC modes in staging before production traffic. ### HEC JSON envelope vs ClickHouse row shape HEC wraps your payload. Splunk stores the envelope plus extracted fields; ClickHouse should store the inner event as typed columns. | HEC envelope field | Splunk use | ClickHouse handling | | --- | --- | --- | | `time` | Sets `_time` | Map to `event_time` (Unix seconds → `DateTime64`) | | `host` | Index-time host | `host LowCardinality(String)` | | `source` | Source metadata | `source` column | | `sourcetype` | Parsing rules | `sourcetype` column; drives table routing | | `index` | Index routing | `index_name` column | | `event` | Becomes `_raw` / fields | Flatten to typed columns; optional `raw_line` | Token scoping matters: create separate HEC tokens per index when security teams require index-level ACLs. The ClickHouse sink can use one token per environment or route by `index` field in the proxy layer. Batch HEC POSTs when volume exceeds a few thousand events per second per source. Default Splunk receiver settings tolerate bursts, but dual-write adds latency if your app awaits both responses synchronously. Use `Promise.allSettled` or a local buffer queue so Splunk/ClickHouse backpressure does not block request handlers. ## Application logs: dual-write at the producer When apps already POST structured JSON to HEC, add a parallel POST to ClickHouse or Tinybird at emit time. Splunk keeps `_raw` search and field extraction; ClickHouse gets typed columns from day one. ```javascript async function emitLog(event) { const hecPayload = { time: Math.floor(Date.now() / 1000), host: event.host, sourcetype: "json", index: "app_logs", event: event, }; await Promise.allSettled([ fetch("https://splunk-hec.example.com:8088/services/collector/event", { method: "POST", headers: { Authorization: "Splunk HEC_TOKEN", "Content-Type": "application/json" }, body: JSON.stringify(hecPayload), }), fetch("https://api.tinybird.co/v0/events?name=app_logs", { method: "POST", headers: { Authorization: "Bearer TB_INGEST_TOKEN", "Content-Type": "application/json" }, body: JSON.stringify({ event_time: new Date().toISOString(), ...event }), }), ]); } ``` When third-party agents only speak HEC and you cannot change producers, put a lightweight proxy in front: accept HEC, forward unchanged to Splunk, transform the envelope for ClickHouse ingest. ## Infrastructure logs: forwarder clone to Kafka Host and security logs usually arrive through universal forwarders. Forwarders can clone to multiple output groups in `outputs.conf`, but the path to Kafka almost always needs a **heavy forwarder** or stream processor between S2S and JSON topics. Universal forwarders do not natively produce Kafka JSON. ```text Universal forwarder inputs.conf → /var/log, wineventlog, etc. outputs.conf [tcpout:splunk_indexers] → indexer:9997 [tcpout:kafka_bridge] → heavy forwarder → Kafka topic → ClickHouse Kafka engine ``` The [Kafka table engine](https://clickhouse.com/docs/en/engines/table-engines/integrations/kafka) pattern: queue table, MergeTree destination, materialized view. The full three-object setup matches [kafka to ClickHouse example](https://www.tinybird.co/blog/kafka-to-clickhouse-example): queue table, materialized view, MergeTree destination. ```sql CREATE TABLE splunk.logs_kafka ( raw String ) ENGINE = Kafka SETTINGS kafka_broker_list = 'kafka:9092', kafka_topic_list = 'splunk-logs', kafka_group_name = 'ch-splunk', kafka_format = 'JSONEachRow', kafka_num_consumers = 4; CREATE TABLE splunk.logs ( event_time DateTime64(3), host LowCardinality(String), sourcetype LowCardinality(String), index_name LowCardinality(String), level LowCardinality(String), message String, raw_line String ) ENGINE = MergeTree PARTITION BY toYYYYMM(event_time) ORDER BY (index_name, sourcetype, event_time, host); CREATE MATERIALIZED VIEW splunk.logs_mv TO splunk.logs AS SELECT parseDateTime64BestEffort(JSONExtractString(raw, 'event_time'), 3) AS event_time, JSONExtractString(raw, 'host') AS host, JSONExtractString(raw, 'sourcetype') AS sourcetype, JSONExtractString(raw, 'index_name') AS index_name, JSONExtractString(raw, 'level') AS level, JSONExtractString(raw, 'message') AS message, JSONExtractString(raw, 'raw_line') AS raw_line FROM splunk.logs_kafka WHERE JSONHas(raw, 'event_time'); CREATE TABLE splunk.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; CREATE MATERIALIZED VIEW splunk.logs_quarantine_mv TO splunk.logs_quarantine AS SELECT now64(3) AS ingest_time, raw, 'missing_event_time' AS reject_reason FROM splunk.logs_kafka WHERE NOT JSONHas(raw, 'event_time'); ``` Define a JSON schema at the topic boundary and reject malformed rows in the bridge. Monitor `logs_quarantine` row growth; spikes mean the forwarder bridge changed field names without updating the contract. ## Sourcetype as schema contract In Splunk, `sourcetype` drives parsing rules, CIM compliance, and dashboard filters. In ClickHouse, treat it as a schema selector. | Splunk field | ClickHouse column | Transform | | --- | --- | --- | | `_time` | `event_time DateTime64(3)` | UTC always | | `host` | `host LowCardinality(String)` | Lowercase | | `index` | `index_name LowCardinality(String)` | As-is | | `sourcetype` | `sourcetype LowCardinality(String)` | As-is | | `source` | `source LowCardinality(String)` | As-is | | `_raw` | `raw_line String` | Optional; trim if huge | | `level` / `severity` | `level LowCardinality(String)` | Normalize (`warn` → `warning`) | ```sql CREATE TABLE splunk.app_logs ( event_time DateTime64(3), ingest_time DateTime64(3) DEFAULT now64(3), host LowCardinality(String), source LowCardinality(String), sourcetype LowCardinality(String), index_name LowCardinality(String), level LowCardinality(String), service LowCardinality(String), status_code UInt16, trace_id String, message String, raw_line String ) ENGINE = MergeTree PARTITION BY toYYYYMM(event_time) ORDER BY (index_name, sourcetype, event_time, host) TTL event_time + INTERVAL 90 DAY; ``` One sourcetype can map to one table or a shared table with a `sourcetype` column. Prefer shared tables when dashboards cross sourcetypes; split when schemas diverge strongly (JSON app logs vs syslog). Store `raw_line` for audit parity with Splunk `_raw`. Do not build production dashboards on it. Full-text search belongs in Splunk; aggregations belong on typed columns. Field extraction patterns for structured logs are covered in [analyzing nginx logs with ClickHouse](https://www.tinybird.co/blog/analyzing-nginx-logs-with-clickhouse-and-tinybird). ## SPL dashboards vs ClickHouse rollups Splunk `tstats` over indexed fields is fast for hot data. Over 90 days at billions of events, license and search cost dominate. Precompute the same counts in ClickHouse and compare weekly. Splunk (canonical for on-call investigation windows): ```spl | tstats count WHERE index=app_logs sourcetype=json level=error by _time span=1h ``` ClickHouse rollup (canonical for reporting and product APIs). Build rollups as [materialized views](https://www.tinybird.co/blog/clickhouse-create-materialized-view-example) at ingest, not at query time: ```sql CREATE TABLE splunk.logs_1h ( hour DateTime, index_name LowCardinality(String), sourcetype LowCardinality(String), level LowCardinality(String), event_count UInt64 ) ENGINE = SummingMergeTree PARTITION BY toYYYYMM(hour) ORDER BY (index_name, sourcetype, level, hour); CREATE MATERIALIZED VIEW splunk.logs_1h_mv TO splunk.logs_1h AS SELECT toStartOfHour(event_time) AS hour, index_name, sourcetype, level, count() AS event_count FROM splunk.app_logs GROUP BY hour, index_name, sourcetype, level; ``` Drift beyond 1% between `tstats` and ClickHouse counts signals mapping bugs, timezone skew, or a broken bridge. Page on Splunk alerts for short windows; report from ClickHouse for monthly error budgets. Weekly parity query in ClickHouse: ```sql SELECT toStartOfHour(event_time) AS hour, index_name, sourcetype, countIf(level IN ('error', 'critical', 'fatal')) AS errors, count() AS total FROM splunk.app_logs WHERE event_time >= now() - INTERVAL 7 DAY GROUP BY hour, index_name, sourcetype ORDER BY hour, index_name; ``` Compare hourly `errors` to Splunk `tstats` output for the same index/sourcetype window. Filter on `index_name` and time range in every production query per [five rules for faster SQL](https://www.tinybird.co/blog/5-rules-for-writing-faster-sql-queries). ## Frozen buckets and S3 replay Splunk frozen buckets, scheduled search exports, and SIEM archives often land in S3-compatible storage. ClickHouse reads them with `s3()` or continuous [S3Queue](https://clickhouse.com/docs/en/engines/table-engines/integrations/s3queue) ingestion. Object-storage ingest patterns are covered in [ClickHouse integration Amazon S3](https://www.tinybird.co/blog/clickhouse-integration-amazon-s3). ```sql INSERT INTO splunk.logs_archive SELECT parseDateTime64BestEffort(JSONExtractString(line, 'event_time')) AS event_time, JSONExtractString(line, 'host') AS host, JSONExtractString(line, 'sourcetype') AS sourcetype, JSONExtractString(line, 'index_name') AS index_name, JSONExtractString(line, 'level') AS level, JSONExtractString(line, 'message') AS message, line AS raw_line FROM s3( 'https://archive.example.com/splunk-export/{year}/{month}/*.json.gz', 'ACCESS_KEY', 'SECRET_KEY', 'LineAsString' ); ``` Partition object keys by date, compress exports, dedupe with `ReplacingMergeTree` when replays overlap live ingest, and track watermarks in a metadata table. Cold replay is for backfill and compliance analytics, not sub-second serving. ## Retention split that matches license economics Practical split most teams land on: - **Splunk hot/warm:** 14–30 days for analyst investigation, SPL, and security playbooks - **ClickHouse:** 90–365 days with TTL for aggregations, SLO backtests, and product log APIs - **Object storage:** multi-year archive loaded on demand for audits Extract only the fields you need in Splunk indexed fields. Every extra extracted field hits license and slows `tstats`. Hash or drop high-cardinality IDs from rollups in both systems. Security teams correlating across index boundaries often land high-volume auth and firewall logs in ClickHouse for SQL joins while Splunk handles analyst search. See [ClickHouse cybersecurity logs](https://www.tinybird.co/blog/clickhouse-cybersecurity-logs) for correlation table design across sources. ## Acceptance tests before production 1. **HEC placement:** Confirm tokens hit indexer/heavy forwarder endpoints, not universal forwarders 2. **Schema:** Sample events populate `event_time`, `index_name`, `sourcetype`, and `level` without null spikes above 0.1% 3. **Parity:** Hourly error counts match Splunk `tstats` within 1% for top three indexes 4. **Lag:** p95 forwarder-to-ClickHouse latency under your SLO (often 30–120 seconds) 5. **Quarantine:** `logs_quarantine` stays near zero in steady state 6. **Replay:** S3 backfill job completes without duplicate rows when overlapping live ingest ## Tinybird for Splunk-adjacent analytics When ClickHouse should power APIs without operating Kafka engines and insert plumbing, Tinybird is managed ClickHouse with streaming ingest and sub-second SQL APIs. Splunk stays the investigation UI; Tinybird is the SQL and HTTP API layer for aggregations Splunk license economics make expensive at billions of events. ### Match your Splunk bridge to a Tinybird ingest path 1. **HEC dual-write path** — POST the same typed JSON you send to HEC (minus Splunk envelope fields) to the Events API. Default limits: 100 requests per second per Data Source; 10 MB per request on Free plans, 100 MB on paid plans. 2. **Forwarder-to-Kafka path** — Connect the Kafka topic your heavy forwarder bridge writes with the Kafka connector (Confluent Platform and Cloud, Redpanda, AWS MSK). 3. **S3 replay path** — Batch-load frozen bucket exports into a Data Source, then build rollups as Pipes. ### Rollups and APIs aligned to SPL windows Precompute the same counts your SPL dashboards use as SQL Pipes at 1-minute or 5-minute windows. Publish Pipes as HTTP endpoints for apps, Grafana, and security tools: ```sql NODE error_rate_by_index SQL > SELECT index_name, countIf(level IN ('error', 'critical', 'fatal')) AS errors, count() AS total, errors / total AS error_rate FROM app_logs WHERE event_time >= now() - INTERVAL 15 MINUTE GROUP BY index_name ORDER BY error_rate DESC TYPE endpoint ``` Compare Pipe output to Splunk `tstats` weekly. Drift beyond 1% means your sourcetype mapping or timezone normalization is wrong. For multi-tenant product log explorers, use JWT fixed parameters to scope rows by `organization_id` or `index_name` without exposing cross-tenant data in URL parameters. Monitor ingest volume and query latency 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. Use Splunk for live investigation. Use Tinybird when product and security teams need SQL over the same event stream with longer retention. ## 5 operational mistakes on Splunk ClickHouse integrations ### 1. Putting HEC on a universal forwarder UFs forward via S2S; they do not accept HEC POSTs. **Fix:** HEC on indexers or heavy forwarders. UFs stay on `tcpout` → indexer. ### 2. Shipping `_raw` only with no typed columns ClickHouse query cost tracks bytes read. Billion-row `raw_line` scans time out. **Fix:** Extract `level`, `service`, `status_code`, and `trace_id` at ingest. Splunk for grep; columns for aggregates. ### 3. Ignoring timestamp and timezone skew Splunk `_time` and producer clocks diverge when forwarders parse local timestamps. **Fix:** Normalize to UTC at ingest. Reject events N minutes ahead of `now()`. ### 4. Assuming Splunk pushes to ClickHouse There is no native forward target. Every bridge is custom. **Fix:** Choose dual-write, forwarder clone, or S3 replay per source. Monitor lag on each bridge. ### 5. Cardinality explosion in both systems Extracting every JSON key into Splunk fields and ClickHouse columns spikes license and merge cost. **Fix:** Allowlist indexed fields. Aggregate before either index. ## What the integration comes down to Splunk integration with ClickHouse is a license-aware dual-index design. Splunk keeps keyword search, analyst workflows, and SPL inside its retention window. ClickHouse holds aggregations, long retention, and SQL APIs that Splunk economics make painful at billions of events. Pick HEC dual-write for application logs, forwarder-to-Kafka for infrastructure volume, and S3 replay for historical backfill. The bridge is always explicit; plan it before volume doubles. ## Frequently Asked Questions (FAQs) ### Can ClickHouse replace Splunk? For keyword search and analyst-driven investigation, Splunk remains the better tool. ClickHouse wins for high-volume aggregations, long retention economics, and SQL APIs. Most teams run dual-index rather than rip-and-replace. ### Does Splunk forward directly to ClickHouse? Not natively. You need dual-write to HEC and HTTP ingest, a forwarder clone to Kafka, or batch export to object storage. ### Why is HEC not on universal forwarders? HEC is a data input for HTTP POSTs. Universal forwarders are lightweight collectors that forward via S2S. Splunk documents HEC on indexers and heavy forwarders only. ### What is the default HEC port? 8088 for HEC traffic. Port 8089 is the management/REST interface, not event ingest. ### How do I map Splunk sourcetypes to ClickHouse tables? Shared table with a `sourcetype` column when dashboards cross sourcetypes; separate tables when schemas diverge strongly. ### Should I store Splunk `_raw` in ClickHouse? Keep `raw_line` for audit and replay, but build dashboards on typed columns extracted at ingest. ### How do I backfill historical Splunk data? Export frozen buckets or scheduled searches to S3 as newline-delimited JSON, then batch insert with `s3()` or S3Queue. Dedupe with `ReplacingMergeTree` if live ingest overlaps the backfill window. ### What retention split works in practice? Splunk 14–30 days hot for investigation, ClickHouse 90–365 days with TTL for analytics and compliance exports. {% cta title="SQL analytics on Splunk-scale logs" text="Tinybird ingests log streams over HTTP or Kafka into managed ClickHouse. Publish sub-second SQL APIs without operating the pipeline yourself." button={href: "https://cloud.tinybird.co/signup", target: "_blank", text: "Try Tinybird free"} /%}