clickhouse integration apache nifi — Flows, Back Pressure, and Insert Shape

Apache NiFi is a pressure-sensitive nervous system for data movement. ClickHouse^® is a high-throughput analytical stomach. Connect them wrong and NiFi's queues swell while ClickHouse® chokes on a storm of single-row inserts.

A solid clickhouse integration apache nifi design is less about finding a magic processor and more about flow topology + batch shape + failure relationships.

Pick a topology before you pick a processor

Three topologies cover almost every production case:

1. Stream edge → NiFi → ClickHouse®

Kafka / MQTT / ListenHTTP → enrich → batch → sink

Use when NiFi is already the enterprise ingress fabric and ClickHouse® is the analytical store.

2. Files → NiFi → object storage → ClickHouse® pull

GetFile / ListS3 → validate → PutS3Object → (later) ClickHouse® s3()

Use for multi-GB drops. Do not shove gigabytes through InvokeHTTP bodies on the NiFi heap.

3. NiFi → Tinybird Events API

origin → enrich → MergeContent → InvokeHTTP (Tinybird)

Use when the sink must also be an API platform, not only a database.

Back pressure is a feature. Design for it.

NiFi will slow upstream when the sink lags. That is good. Fighting it with huge concurrent tasks is how you create insert storms.

Practical defaults to start from:

If Kafka lag grows, scale batch size and ClickHouse® insert capacity before you blindly raise NiFi concurrency.

Record path: PutDatabaseRecord + JDBC

Best when your cluster already lives in Record land (JsonTreeReader, AvroReader, schema registries).

DBCPConnectionPool:

URL:    jdbc:clickhouse://ch.internal:8443/otel?ssl=true
Driver: com.clickhouse.jdbc.ClickHouseDriver
Jar:    /opt/nifi/drivers/clickhouse-jdbc-all.jar
User:   nifi_writer

PutDatabaseRecord:

Statement Type:     INSERT
Table Name:         spans_raw
Maximum Batch Size: 2000+
Translate Field Names: true

Table tuned for trace analytics:

CREATE TABLE otel.spans_raw (
    trace_id    String,
    span_id     String,
    service     LowCardinality(String),
    operation   LowCardinality(String),
    start_time  DateTime64(9),
    duration_ns UInt64,
    status      LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toDate(start_time)
ORDER BY (service, operation, start_time, trace_id);

Route the failure relationship to a retry / DLQ flow. A red sink processor with nowhere to go is how data vanishes from provenance charts.

Content path: MergeContent + InvokeHTTP

Best for ClickHouse® Cloud and teams that do not want JDBC jars on every node.

ConsumeKafka
  → EvaluateJsonPath (optional filters)
  → MergeContent (correlate by topic / schema)
  → InvokeHTTP POST JSONEachRow
  → RetryFlowFile on 429/5xx

InvokeHTTP remote URL:

https://ch.cloud:8443/?query=INSERT%20INTO%20otel.spans_raw%20FORMAT%20JSONEachRow

Headers:

Content-Type: application/x-ndjson
X-ClickHouse-User: nifi_writer
X-ClickHouse-Key: ${ch_password}

Keep the Parameter Context secret. Do not paste keys into processor properties that export cleanly to XML templates.

Provenance: your replay button

NiFi's advantage over a pile of scripts is replay. Use it.

• Retain provenance long enough to cover your worst ClickHouse® outage window
• Tag FlowFiles with batch.id / kafka.offset attributes before the sink
• On bad deploys, replay from the last good attribute watermark, not from "I think Tuesday"

ClickHouse® ReplacingMergeTree helps if replay duplicates keys. Pure append logs need either idempotent keys or tolerance for duplicate events in metrics.

Kafka-specific notes

NiFi in front of Kafka → ClickHouse® is optional. Sometimes the better design is:

Kafka → ClickHouse® Kafka engine table

and NiFi only for side routes (PII scrubbing to a different system, partner webhooks, quarantine).

Use NiFi on the hot path when you need:

• Multi-destination fan-out from one consumer
• Record-level enrichment against lookup services
• Protocol translation (MQTT → NDJSON, etc.)

Otherwise you are paying NiFi CPU to be a very elaborate kcat.

Anti-patterns

1. One FlowFile, one INSERT. Death by parts.
2. Unlimited concurrent sink tasks. Death by connections.
3. Schema inference every FlowFile. Cache/readers with stable schemas.
4. Using event_time = now() only. Analytics needs source time in ORDER BY.
5. Ignoring bulletins. Sink auth failures hide in noise until Kafka lag pages you.

Terminal sink: Tinybird

If product teams will query this data over HTTP, end the flow at Tinybird instead of self-managed ClickHouse®.

MergeContent → InvokeHTTP
  POST https://api.tinybird.co/v0/events?name=spans_raw
  Authorization: Bearer ${tinybird_token}

Then publish:

NODE p95_by_service
SQL >
    SELECT
        service,
        quantile(0.95)(duration_ns) AS p95_ns,
        count() AS spans
    FROM spans_raw
    WHERE start_time >= {{ DateTime(start_time, '2026-07-01 00:00:00') }}
    GROUP BY service
    ORDER BY p95_ns DESC

TYPE endpoint

NiFi keeps doing routing and policy. Tinybird keeps doing ClickHouse® storage + user-facing analytics APIs. Clean ownership boundary.

Validation loop before production

1. Load a captured Kafka slice through the flow in a lower environment
2. Confirm batch sizes in provenance (not just "FlowFiles out")
3. Compare ClickHouse® / Tinybird row counts to Kafka offsets
4. Fail the sink on purpose; confirm DLQ + replay
5. Only then raise throughput

FAQ

Do I need a custom ClickHouse® NiFi processor?

No. PutDatabaseRecord + JDBC, or InvokeHTTP + HTTP INSERT, is enough for production.

How fresh can NiFi → ClickHouse® be?

Seconds, if MergeContent max bin age is low and ClickHouse® keeps up. Sub-second usually means bypassing NiFi on the hottest path.

Why are my jobs "successful" but tables empty?

Empty merges, filters that drop everything, or inserts into the wrong database. Count attributes before the sink; validate count() after.

JDBC or HTTP?

JDBC if Record processors and pools are standard ops. HTTP if Cloud TLS simplicity matters more than UI column mapping.