ClickHouse

This page shows you how to use ClickHouse as an analytical store for Tenzir pipelines: write normalized security telemetry to ClickHouse with to_clickhouse and read tables or SQL query results back into Tenzir with from_clickhouse.

ClickHouse is an open-source analytical database built for fast SQL over large event volumes. Tenzir sits in front of ClickHouse as a programmable security data pipeline: it collects telemetry, parses source formats, maps events to OCSF, enriches or reduces volume, and writes structured tables that analysts and detection systems can query.

With from_clickhouse, ClickHouse can also feed later pipeline stages. Use this path to backfill historical events, export query results, materialize detection outputs, or route subsets to another tool without adding another ingestion path.

Choose an integration path

Use the path that matches the role ClickHouse plays in your deployment:

Goal	ClickHouse role	Tenzir building blocks
Build a security data lake	Destination for OCSF-normalized telemetry	`ocsf::cast`, `to_clickhouse`
Keep a schema you manage in ClickHouse	Existing MergeTree table with explicit types, TTLs, projections, or materialized views	`to_clickhouse` with `mode="append"`
Query retained telemetry	Source table or SQL query result	`from_clickhouse`
Run lake-backed hunts or detections	SQL engine for filtering, grouping, and sorting large event sets	`from_clickhouse`, `where`, `publish`
Export or fan out query results	Query result source for downstream tools or object storage	`from_clickhouse`, output operators such as `to_s3` or `to_http`
Inspect available tables and schemas	`SHOW`, `DESCRIBE`, or `system.*` metadata queries	`from_clickhouse` with `sql=...`

Connect to ClickHouse

Tenzir connects to ClickHouse through the native ClickHouse TCP protocol using the official clickhouse-cpp client library. The operators don’t use ClickHouse’s HTTP interface or a local IPC mechanism.

Use either a ClickHouse URI or explicit connection arguments:

from_clickhouse uri="clickhouse://default:secret@localhost:9000/security",
                table="events",
                tls=false

You can also pass the connection details as separate arguments:

from_clickhouse table="security.events",
                host="localhost",
                port=9000,
                user="default",
                password=secret("CLICKHOUSE_PASSWORD"),
                tls=false

Use the same connection arguments with to_clickhouse. If a URI selects a database and table is unqualified, Tenzir uses that database. In create modes, to_clickhouse also creates the selected database if it doesn’t exist.

Set up ClickHouse

Use ClickHouse Cloud when you want ClickHouse managed separately from Tenzir but still reachable through the native protocol. Create a service with the ClickHouse Cloud quick start, copy the native endpoint, port, user, and database from the connection details, and keep TLS enabled.

Store the password in Tenzir’s secret store and pass the Cloud connection details to either ClickHouse operator:

from_clickhouse table="security.events",
                host="abc123.us-east-1.aws.clickhouse.cloud",
                port=9440,
                password=secret("CLICKHOUSE_PASSWORD")

The examples later use tls=false only because they assume a local self-managed ClickHouse server without TLS.

If you need a local or self-managed ClickHouse deployment, start with the ClickHouse OSS quick start. Tenzir connects to a self-managed server the same way, using the native host and port you configure in from_clickhouse and to_clickhouse.

Examples

These examples assume that ClickHouse runs on the same host as Tenzir and allows non-TLS connections, which is why the pipelines use tls=false. For ClickHouse Cloud, use your service endpoint and credentials, and keep TLS enabled.

Land OCSF telemetry in ClickHouse

Use this path when ClickHouse is your security data lake. Tenzir can create tables from incoming events, and ocsf::cast keeps the ClickHouse schema stable by casting events to the selected OCSF class, encoding variant fields, and filling missing fields with typed nulls.

from_file "ocsf_network_activity.json"
ocsf::cast encode_variants=true, null_fill=true
to_clickhouse table=f"ocsf.{class_name.replace(" ","_")}",
              primary=time,
              tls=false

When creating a table, the to_clickhouse operator uses the first event to determine the schema. Make sure the first event doesn’t contain untyped nulls or empty records. ocsf::cast helps because it gives expected OCSF fields explicit types before the table is created.

After the data lands, query it directly in ClickHouse:

SELECT
  dst_endpoint.ip,
  count() AS events,
  median(traffic.bytes_in) AS median_bytes_in
FROM ocsf.Network_Activity
WHERE time > now() - INTERVAL 1 DAY
GROUP BY dst_endpoint.ip
ORDER BY events DESC
LIMIT 20;

Append to a table you manage

Create the ClickHouse table first when you need explicit types, table engines, TTL policies, projections, materialized views, or partitioning that should stay under ClickHouse control.

Create the table in ClickHouse:

CREATE DATABASE IF NOT EXISTS security;

CREATE TABLE security.alerts (
  time DateTime64(9),
  rule_name String,
  severity Int64,
  src_ip Nullable(IPv6),
  message String
) ENGINE = MergeTree()
ORDER BY (time, rule_name);

Ingest data from Tenzir:

from {
  time: 2026-06-01T12:00:00Z,
  rule_name: "failed-login-burst",
  severity: 5,
  src_ip: 10.0.1.12,
  message: "More than 20 failed logons from one source in 5 minutes",
}
to_clickhouse table="security.alerts", mode="append", tls=false

mode="append" makes the pipeline fail if the table doesn’t already exist, so ClickHouse remains the source of truth for the schema.

Read data from ClickHouse

Use table mode when you want Tenzir to read a ClickHouse table as structured events. Filters after from_clickhouse can be pushed into ClickHouse in table mode, which reduces the amount of data transferred into the pipeline.

from_clickhouse table="ocsf.Network_Activity", tls=false
where time > now() - 1d and severity_id >= 3
publish "clickhouse-network-activity"

Use SQL mode when ClickHouse should project, aggregate, sort, or otherwise shape the result before Tenzir receives it:

from_clickhouse sql="SELECT time, host, severity, message FROM security.events WHERE severity >= 3 ORDER BY time DESC",
                tls=false
publish "clickhouse-findings"

Inspect tables and schemas

Use SQL mode for ClickHouse metadata queries:

from_clickhouse sql="SHOW TABLES FROM ocsf", tls=false

To inspect columns for a specific table, run DESCRIBE TABLE:

from_clickhouse sql="DESCRIBE TABLE ocsf.Network_Activity", tls=false

Export query results

Because from_clickhouse turns query results into regular Tenzir events, you can route them to any supported destination. For example, export a seven-day slice to S3 as Parquet:

from_clickhouse sql="SELECT * FROM ocsf.Network_Activity WHERE time >= now() - INTERVAL 7 DAY",
                tls=false
to_s3 "s3://security-exports/clickhouse/network_activity_{uuid}.parquet" {
  write_parquet
}