ClickHouse

Datafly Signal delivers events to ClickHouse for real-time analytics, high-performance columnar storage, and sub-second query response times on billions of rows.

Prerequisites

Before configuring ClickHouse in Signal, you need a ClickHouse server (self-hosted or ClickHouse Cloud) with a database and a MergeTree table.

Set Up a ClickHouse Server

You have two options:

Option A: ClickHouse Cloud (Managed)

Sign up at clickhouse.cloud.
Create a new service and select a region.
Note the Host (e.g. abc123.us-east-1.aws.clickhouse.cloud), Port (default 8443 for HTTPS), and the default Username and Password.

Option B: Self-Hosted

Install ClickHouse using the official guide.
Ensure the HTTP(S) interface is enabled (default port 8443 for HTTPS, 8123 for HTTP).
Note the hostname and port.

Create a Database

Connect to ClickHouse and create a database:

CREATE DATABASE IF NOT EXISTS datafly_events;

Create a MergeTree Table

Create a table using the MergeTree engine family for optimal analytics performance:

CREATE TABLE datafly_events.events (
  event_id String,
  type LowCardinality(String),
  event String,
  anonymous_id String,
  user_id String,
  timestamp DateTime64(3),
  received_at DateTime64(3),
  sent_at DateTime64(3),
  context String,
  properties String,
  traits String,
  source_id String,
  integration_id String
)
ENGINE = MergeTree()
ORDER BY (type, timestamp)
PARTITION BY toYYYYMM(timestamp);

The ORDER BY clause defines the primary sort key and determines the primary index. Ordering by (type, timestamp) optimises queries that filter by event type and time range. LowCardinality(String) improves performance for columns with few unique values.

Create a User for Signal

For self-hosted ClickHouse, create a dedicated user:

CREATE USER datafly_signal IDENTIFIED BY 'your_secure_password';
GRANT INSERT ON datafly_events.events TO datafly_signal;

For ClickHouse Cloud, you can use the default user or create an additional user in the Cloud console.

Configuration

Field	Type	Required	Description
`host`	string	Yes	The ClickHouse server hostname.
`port`	string	Yes	The ClickHouse HTTP(S) port. Defaults to `8443` (HTTPS) or `8123` (HTTP).
`database`	string	Yes	The target database name.
`table`	string	Yes	The target table name to insert rows into.
`username`	string	Yes	The ClickHouse username for authentication.
`password`	secret	Yes	The ClickHouse password for authentication.
`use_tls`	boolean	No	Enable TLS for the connection. Defaults to `true`.

Signal Setup

Quick Setup

Navigate to Integrations in the sidebar.
Open the Integration Library tab.
Find ClickHouse or filter by Cloud Storage.
Click Install, select a variant if available, and fill in the required fields.
Click Install Integration to create the integration with a ready-to-use default blueprint.

API Setup

curl -X POST http://localhost:8084/v1/admin/integration-catalog/clickhouse/install \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "ClickHouse",
    "variant": "default",
    "config": {
      "host": "abc123.us-east-1.aws.clickhouse.cloud",
      "port": "8443",
      "database": "datafly_events",
      "table": "events",
      "username": "datafly_signal",
      "password": "your_secure_password",
      "use_tls": true
    },
    "delivery_mode": "server_side"
  }'

Testing

Enable the integration in Signal and trigger a test event on your website.
Connect to ClickHouse and query the target table:

SELECT * FROM datafly_events.events ORDER BY timestamp DESC LIMIT 10;

Verify that event rows are appearing with correct data.
In Signal, check the Live Events view to confirm delivery status shows as successful.

Troubleshooting

Problem	Solution
Events not appearing in ClickHouse	Verify the host, port, database, and table name are correct.
Connection refused / timeout	Check that the ClickHouse server is running and the HTTP(S) port is accessible. Verify firewall rules or ClickHouse Cloud IP access list.
`Authentication failed`	The username or password is incorrect. Verify the credentials.
`Table not found`	The table does not exist in the specified database. Verify the database and table names.
`INSERT permission denied`	The user lacks INSERT permission. Run `GRANT INSERT ON db.table TO user;`.
TLS errors	If `use_tls` is true, the port should be `8443`. If false, use `8123`. ClickHouse Cloud always requires TLS.
Slow inserts	ClickHouse performs best with batch inserts. Increase the batch size in the integration settings. Avoid inserting one row at a time.
Schema mismatch	The column types must match the event data. Verify column types with `DESCRIBE TABLE datafly_events.events;`.

Visit ClickHouse documentation for full SQL reference, table engine options, and performance tuning guides.

Azure SQL Elasticsearch