Modbus to ClickHouse: Stream PLC Data the Right Way

ClickHouse is a phenomenal store for industrial time-series: columnar, compressed, and fast enough to scan billions of machine samples in milliseconds. The hard part isn’t ClickHouse — it’s everything between the PLC and the insert. Polling Modbus, making sense of raw register values, and loading them at volume usually means a tangle of scripts, a message broker, and a transform layer that drifts over time. This is a practical guide to doing Modbus to ClickHouse cleanly, and how OpexFlow collapses the whole chain.

The naive approach (and why it hurts)

The typical hand-rolled pipeline looks like this:

1. A Python/Node script polls Modbus TCP on an interval.
2. It decodes registers into fields by hand.
3. It publishes to MQTT or writes to a file.
4. Another process reads that, transforms, and bulk-inserts into ClickHouse.

It works for a proof of concept. Then reality hits:

• Register decoding is duplicated in every consumer. A vendor mapping change means editing three scripts.
• No backpressure. A slow ClickHouse insert backs up the broker; a fast poll overruns a slow one.
• No retention on gaps. If the network drops, you lose data silently.
• No observability. When inserts stall, you find out from a stale Grafana panel hours later.

The clean architecture

A robust Modbus → ClickHouse path has four responsibilities, and each should live in one place:

1. Poll Modbus TCP reliably, with per-device config.
2. Normalize raw registers into a stable, typed shape.
3. Transform in flight — filter noise, compute derived fields, convert units.
4. Deliver to ClickHouse with signing and local retention.

1. Poll Modbus TCP

A Modbus source is a device with a host, port, unit id, and a register map. Registers map onto typed fields — axis positions, spindle speed, free-form metadata like temp or vib. OpexFlow’s modbus adapter does this mapping declaratively:

adapters:
  modbus:
    enabled: true
    devices:
      - id: "fanuc-01"
        host: "192.168.1.10"
        port: 502
        registers: [ ... ]

Every poll normalizes to a CanonicalMachineData event — one shape regardless of protocol.

2. Normalize

Normalization happens at the source boundary, once. Downstream code (and your ClickHouse schema) never sees raw register integers; it sees machine.id, status.state, spindle.speed, metadata.temp, and so on. This is the single biggest win over hand-rolled pipelines: decode once, consume everywhere.

3. Transform in flight

This is where most pipelines get brittle. OpexFlow gives you two engines, addressed per source route ({source}:{machine.id}) with a shared default:

• Native DSL for filters and field math — no JSON marshalling, 15–50× faster than a script:

  when metadata.temp > 80 || metadata.vib > 5.0
  set metadata.alert = metadata.temp > 80 || metadata.vib > 5.0
  set metadata.temp_f = metadata.temp_c * 9 / 5 + 32

• Sandboxed JavaScript for anything complex (vendor-field reshaping, regex). Memory-capped and deadline-interrupted, so a bad transform can’t crash the poller.

The AI generator writes either from a description. (Full DSL reference in the docs.)

4. Load ClickHouse

The clickhouse sink fans transformed events out to a ClickHouse table. Because events are already normalized and typed, your table is a clean time-series:

CREATE TABLE machine_samples (
  machine_id   String,
  ts           DateTime64(3),
  state        LowCardinality(String),
  spindle_speed   Nullable(Float64),
  temp            Nullable(Float64),
  vib             Nullable(Float64),
  metadata  String  -- JSON blob for the long tail
) ENGINE = MergeTree
ORDER BY (machine_id, ts);

Per-source routing lets you push noisy machines to a separate lower-frequency table while sending critical assets straight to the hot table.

Don’t forget: retention and signing

Two things every industrial pipeline needs that DIY setups skip:

• Local retention. OpexFlow keeps an SQLite-backed history and HMAC-signs every push, so records survive restarts and network gaps. When ClickHouse comes back online, you don’t have a hole in your data.
• Stall alerts. Native sink-stall detection fires to a webhook or Telegram when inserts stop flowing — you find out in seconds, not hours.

Putting it together

The whole path — poll, normalize, transform, load, retain, alert — is one config file and one binary:

transform:
  enabled: true
  default:
    backend: "dsl"
    script_inline: |
      when !(status.state == "offline")
      set metadata.temp_f = metadata.temp_c * 9 / 5 + 32
    sinks: ["clickhouse", "storage"]

No scripts to babysit, no broker to operate, no duplicated decoding.

When ClickHouse isn’t the only target

Often you want the same stream in more than one place: ClickHouse for the warehouse, Postgres/TimescaleDB for an app, NATS for downstream services. Because transforms fan out to multiple sinks per route, you declare the destinations once and let the engine handle delivery. (See OPC-UA vs Modbus for choosing sources, and the sinks reference.)

The bottom line

Streaming Modbus into ClickHouse is easy to prototype and hard to run. By collapsing poll + normalize + transform + load + retain into one licensed engine, OpexFlow turns a weekend hack into a production data path — one binary, from the PLC to the warehouse.

Read the docs, see how it works, or request a license.