Keep clickhouse integration splunk queryable

Splunk pricing scales with ingest volume and indexed field count. ClickHouse^® 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.

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.

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.

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.

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.

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.

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 pattern: queue table, MergeTree destination, materialized view. The full three-object setup matches kafka to ClickHouse example: queue table, materialized view, MergeTree destination.

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.

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.

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):

| 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 at ingest, not at query time:

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:

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.

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 ingestion. Object-storage ingest patterns are covered in ClickHouse integration Amazon S3.

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 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:

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.