ClickHouse

Overview

ClickHouse is a high-performance, column-oriented OLAP database designed for real-time analytics at scale. In Zenskar, you can use ClickHouse as a data source to stream and process event data for metering, billable metrics, and analytical dashboards.

Prerequisites

General

ClickHouse version 21.3.10.1 or later.
Access to your ClickHouse server and credentials.
Networking access to allow Zenskar to connect to your ClickHouse instance, with optional SSH tunnel configuration if required.

(Recommended) A dedicated read-only user in ClickHouse

To create a dedicated ClickHouse user and grant privileges to the required tables for replication, do the following:

  -- Create user
CREATE USER zenskar_user IDENTIFIED WITH plaintext_password BY 'your_password';

-- Grant access to database (or specific tables)
GRANT SELECT ON db_name.* TO zenskar_user;

⚠️
You may restrict access to specific tables instead of the entire database.

Set up a ClickHouse data-source connector via Zenskar dashboard

Once the prerequisites are complete, connect ClickHouse as a data source in Zenskar.

Log into your Zenskar dashboard.
In the left side bar, click Usage > Data Sources.
In the top-right corner, click + ADD DATA SOURCE.
On the Add New Data Source page, configure the ClickHouse connector

General details

Field	Description	Required
Source Name	Enter a unique name for this data source.	Yes
Source Type	Select ClickHouse from the dropdown menu.	Yes

Connector configuration

Field	Description	Required
Host	The host endpoint of the Clickhouse cluster.	Yes
Port	The port of the database (defaut = `8123`)	Yes
Database	The name of the database.	Yes
Username	The username that has access to the required tables in the database.	Yes
Password	The password associatd with the username.	Yes
JDBC URL Parameters (Advanced)	Optional key-value properties that can be added to the JDBC connection string to modify connection behavior. Enter them as `key=value` pairs separated by `&` (for example: `param1=value1&param2=value2`). Refer to your database's JDBC documentation for supported parameters.	Optional
SSH Tunnel Method	Whether to initiate an SSH tunnel before connecting to the database, and if so, which kind of authentication to use. The default is No Tunnel, which requires no configuration and establishes a direct connection to the database.	Yes

SSH key authentication configuration (conditional fields)

These fields are required only if SSH Tunnel Method is set to SSH Key Authentication

Field	Description	Required
SSH Tunnel Jump Server Host	Bastion hostname/IP	Yes
SSH Connection Port	Port for SSH to bastion. Default: `22` (unless changed).	Yes
SSH Login Username	OS username on bastion (not ClickHouse user)	Yes
SSH Private Key	RSA private key in PEM format. Paste full key text. The private key field in Zenskar must include the full PEM block: `-----BEGIN RSA PRIVATE KEY-----` … `-----END RSA PRIVATE KEY-----`	Yes

SSH password authentication configuration (conditional fields)

These fields are required only if SSH Tunnel Method is set to Password Authentication

Field	Description	Required
SSH Tunnel Jump Server Host	Bastion hostname/IP	Yes
SSH Connection Port	Port for SSH to bastion. Default: `22`	Yes
SSH Login Username	OS username on bastion (not ClickHouse user)	Yes
SSH Password	Password for bastion OS user (not ClickHouse password)	Yes

Data source access mode (read-only)

Zenskar replicates data directly from your ClickHouse databse to Zenskar’s data infrastructure. The data is periodically synced to maintain freshness. This option is:

Ideal for small-mid size database (less than 30GB)
Ideal for periodical data ingestion
Optimised for quicker invoice generation

Click on the SAVE SOURCE button

Set up a ClickHouse data-source connector via API

Refer to the create data-source connector API reference.

Request examples

No tunnel

curl --location 'https://api.zenskar.com/datasources' \
-H 'Authorization: Bearer <your_token>' \
-H 'apiversion: 20240301' \
-H 'Content-Type: application/json' \
-d '{
  "name": "abc",
  "connector_type": "ClickHouse",
  "destination": "ClickHouse",
  "status": "active",
  "connector_config":{
    "port":8123,
    "database":"database_name",
    "username":"acme",
    "password":"password",
    "host":"name_of_the_host"},
  "source_definition_id":"bad83517-5e54-4a3d-9b53-63e85fbd4d7c"}

SSH key authentication

curl --location 'https://api.zenskar.com/datasources' \
-H 'Authorization: Bearer <your_token>' \
-H 'apiversion: 20240301' \
-H 'Content-Type: application/json' \
-d '{
  "name": "abc",
  "connector_type": "ClickHouse",
  "destination": "ClickHouse",
  "status": "active",
  "connector_config":  {
      "port":8123,
      "tunnel_port":22,
      "database":"database_name",
      "username":"acme",
      "password":"password",
      "tunnel_host":"host_of_the_tunnel",
      "tunnel_user":"user_of_the_tunnel",
      "ssh_key":"key_of_ssh",
      "host":"host_of_the_tunnel"},
  "source_definition_id":"bad83517-5e54-4a3d-9b53-63e85fbd4d7c"}

SSH password authentication

curl --location 'https://api.zenskar.com/datasources' \
-H 'Authorization: Bearer <your_token>' \
-H 'apiversion: 20240301' \
-H 'Content-Type: application/json' \
-d '{
  "name": "abc",
  "connector_type": "ClickHouse",
  "destination": "ClickHouse",
  "status": "active",
  "connector_config":{
      "port":8123,
      "tunnel_port":22,
      "database":"database",
      "username":"acme",
      "password":"password",
      "tunnel_user":"user_of_the_tunnel",
      "tunnel_user_password":"passord",
      "host":"host",
      "tunnel_host":"host_of_the_tunnel"},
  "source_definition_id":"bad83517-5e54-4a3d-9b53-63e85fbd4d7c"}

Addendum

SSH key setup

When using SSH key authentication, you will generate a key pair that contains:

File	Description	Where to place it
Private Key	Secret key used by Zenskar to authenticate to the bastion server	Paste into the SSH Private Key field in Zenskar
Public Key	Key the bastion server uses to recognize and trust Zenskar	Add to the `~/.ssh/authorized_keys` file on the bastion server for the user you are connecting as

This setup lets Zenskar securely connect to your bastion host without a password.

Generate the key pair

Run the following command to generate a PEM-formatted RSA key pair:

ssh-keygen -t rsa -b 4096 -m PEM -f zenskar_bastion_key

This creates:

zenskar_bastion_key (private key)
zenskar_bastion_key.pub (public key)

Install the public key on the bastion server

Add the public key to the authorized_keys file for the SSH user:

cat zenskar_bastion_key.pub >> ~/.ssh/authorized_keys

This must be done for the same Linux user that you will configure as the SSH Login Username in Zenskar.

Notes

📘
The simplest way to verify connectivity is to configure the data source in the Zenskar UI.

🚧
Ensure that the ClickHouse user used by Zenskar has read-only access.

📘
SSH credentials and ClickHouse database credentials are separate.