ClickHouse Integration via API

Purpose

This guide explains how to configure Logsign USO to collect rows from a ClickHouse database, typically application or audit logs that your own systems already write into ClickHouse tables. Logsign runs a query you define on a schedule and turns every new row into a Logsign event. It works with self-managed ClickHouse servers and with ClickHouse Cloud.

The integration talks to the ClickHouse HTTP interface (port 8123 for HTTP, 8443 for HTTPS). It does not use JDBC, so no driver file has to be uploaded to Logsign and it is configured separately from the generic JDBC data source.

How Collection Works

You provide a query and the name of a position column. On each polling cycle Logsign wraps your query and only asks for rows whose position column is greater than the last value it has already collected, ordered by that column. After a batch has been sent, the highest position value is saved and the next cycle continues from there. Rows that share the same position value are always collected together in the same batch, so a tie at the batch boundary does not cause missing rows.

ClickHouse does not always make inserted rows visible in the order of their position value (asynchronous or batched inserts, several writers in parallel). To avoid skipping a row that becomes visible late, Logsign leaves the most recent seconds of data for a later cycle. This is the Safety Lag setting (60 seconds by default, minimum 5). It only delays collection; each row is still collected once and no duplicates are produced. Safety Lag does not apply to integer positions of type Sequence, because those values carry no time information.

Prerequisites

• The ClickHouse HTTP interface must be reachable from Logsign USO: port 8123 (HTTP) or 8443 (HTTPS) by default. ClickHouse Cloud services are reached over HTTPS on 8443. If your server uses different ports, or ClickHouse is published behind a reverse proxy (for example on 443), you can enter that port in the form.
• A ClickHouse user whose password Logsign will use. The user only needs SELECT on the tables referenced in your query. A read-only user (a profile with readonly=1) is supported: Logsign detects it and runs its queries without session settings.
• A position column in the query result whose type is DateTime, DateTime64 or an integer type (Int8 to Int256, UInt8 to UInt256). Nullable(...) and LowCardinality(...) wrappers around these types are accepted. Date, String and other types are rejected by the connection check.

Step 1: Prepare ClickHouse

Create a user for Logsign

Create a dedicated user and grant it read access to the tables you want to collect. For example:

CREATE USER logsign IDENTIFIED BY '<strong-password>';
GRANT SELECT ON applogs.events TO logsign;

Replace applogs.events with your own database and table names, and repeat the grant for every table your query reads, including tables used in joins.

Choose a position column

The position column must grow in the order rows are inserted. Good choices are an insert time column or an increasing ID. An application event time that can arrive out of order (for example a timestamp set by the client) is not a good choice, because rows with an older value that arrive after Logsign has moved past them will not be collected.

If the table only has an application event time, add an insert time column and fill it for existing rows:

ALTER TABLE applogs.events ADD COLUMN inserted_at DateTime64(6) DEFAULT now64(6);
ALTER TABLE applogs.events MATERIALIZE COLUMN inserted_at;

Then use inserted_at as the position column.

Step 2: Configure the Integration in Logsign USO

Go to Settings > Data Collection, click + Device, choose API, and select ClickHouse from the list. Fill in the connection fields:

A few rules for the query. It may contain WHERE conditions and joins. Do not add ORDER BY, LIMIT or FORMAT to it: Logsign adds its own filter, ordering, batch limit and output format around your query, and a LIMIT inside your query would cap the rows Logsign can ever see. A trailing semicolon is removed automatically. If you select specific columns instead of *, include the position column in the list.

Click Check Connection. On success Logsign shows the ClickHouse version and the detected type of the position column, and the remaining fields appear:

Complete the common fields (Period, EPS, Device Name, Group and the others) as for any other data source and click Save. Save stays disabled until the connection check has succeeded, and changing any connection field, the query or the position column requires running the check again.

What Gets Collected

Each row returned by the query becomes one Logsign event. The row's columns are stored under ClickhouseAPI.<column name>, so a column named message is searchable as ClickhouseAPI.message. Values are stored as text. Map, JSON and named Tuple values are flattened into dotted keys (for example ClickhouseAPI.attributes.user) up to three levels deep; deeper objects and arrays are kept as a JSON string. Columns that are NULL in a row are left out of that event. A single event holds at most 100 fields; when a row has more, the extra columns are stored as one JSON string or, if there is no room left, dropped.

The event time (Time.Generated) is the time Logsign collected the row, not a timestamp column from your table. If you need the original time for searches or correlation, keep it as a column in the query so it is available under ClickhouseAPI.*.

Troubleshooting