ClickHouse Writer operational considerations

The ParallelThreads property distributes work by target table name, and each target table is written by only one writer instance; if a writer targets only one table, increasing ParallelThreads beyond 1 has no effect. To scale writes to one large or high-throughput table beyond a single writer instance, place a Router in front of N independent ClickHouse Writer targets that all write to the same ClickHouse table. The Router evaluates a CASE expression per event and sends each event to exactly one of the N output streams; ClickHouse simply receives inserts from N independent clients. In internal testing, this pattern improved throughput on a single hot table several-fold versus one writer.

Replicated and Distributed table engines are orthogonal to this decision: routing happens on the Striim side, and all N writers still write to the one target table. Replication only copies whatever data parts were produced, and Distributed sharding is a separate, ClickHouse-side fan-out axis — if a Distributed table's sharding key differs from its sorting key, an order-sensitive engine can still see a key out of order, so the hash-by-sorting-key rule above still applies.

Example: hash-partitioning a numeric sorting key

The modulo technique works cleanly only when the sorting key is a single numeric column. This example routes one Oracle source table to five ReplacingMergeTree writer targets, all mapped to the same ClickHouse table analytics.orders, partitioned on the numeric sorting key order_id:

CREATE SOURCE OrdersSource USING Global.DatabaseReader (
    ConnectionURL: 'jdbc:oracle:thin:@//db-host:1521/ORCL',
    Username: 'striim', Password: '********',
    Tables: 'SALES.ORDERS', FetchSize: 10000
) OUTPUT TO RawOrdersStream;

-- Hash/modulo-partition on the SORTING KEY (order_id) -- NOT round-robin.
-- GETDATA reads the after-image; GETBEFORE reads the before-image so a
-- DELETE (whose key lives only in the before-image) still routes to the
-- same writer as that key's other changes.
CREATE OR REPLACE ROUTER Orders_router INPUT FROM RawOrdersStream s
CASE
  WHEN (getIndexOfColumn(s, "order_id") >= 0 AND ABS(TO_LONG(GETDATA(s, "order_id")))   % 5 = 1)
    OR (GETBEFORE(s, "order_id") IS NOT NULL AND ABS(TO_LONG(GETBEFORE(s, "order_id"))) % 5 = 1) THEN ROUTE TO S1,
  WHEN (... % 5 = 2) THEN ROUTE TO S2,
  WHEN (... % 5 = 3) THEN ROUTE TO S3,
  WHEN (... % 5 = 4) THEN ROUTE TO S4,
  -- The '% 5 = 0' branch is essential: without it, those events are discarded.
  WHEN (... % 5 = 0) THEN ROUTE TO S5
;

CREATE TARGET OrdersWriter1 USING Global.ClickHouseWriter (
    connectionProfileName: 'analytics_ch_cp',
    Tables: 'SALES.ORDERS,analytics.orders',
    TableEngine: 'ReplacingMergeTree',
    UploadPolicy: 'eventcount:50000,interval:30s'
) INPUT FROM S1;
-- Repeat for OrdersWriter2..5, each INPUT FROM S2..S5, all with the same
-- connectionProfileName, Tables mapping, and TableEngine.

Composite, GUID, or string sorting keys

Striim's TQL expression language has no hash, checksum, or string-concatenation function (no MD5, SHA, CRC32, generic HASH, or even CONCAT) that a Router CASE expression can call, so there is no pure-TQL way to partition a composite, GUID, or string sorting key. Compute a stable shard identifier upstream instead, in a custom continuous query or Open Processor: derive a deterministic shard ID from the real sorting key (reading it from the before-image for deletes), write it into the event with putUserData(event, '__shard', shardId), and have the Router partition on USERDATA(s, "__shard") % N instead.

Start with three to five writer instances, measure throughput, and increase only if the single table is still the bottleneck. Choose a divisor that spreads your key space evenly; a skewed key distribution produces one hot writer regardless of the routing scheme.

ClickHouse Writer publishes metrics through Striim's standard monitoring features so you can confirm the application is healthy and verify that data is flowing as expected.

This topic describes known issues with ClickHouse Writer, their symptoms, and how to resolve them.

SELECT
    <sorting_key_cols>,
    sum(Sign) AS sign_sum,
    count()   AS rows
FROM <table>
GROUP BY <sorting_key_cols>
HAVING sign_sum < 0 OR sign_sum > 1
ORDER BY sign_sum;