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.
Allow Zenskar to connect to your ClickHouse instance, with SSH tunnel configuration, if required.

Create a dedicated read-only user in ClickHouse (recommended)

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.

🚧
Ensure that the ClickHouse user allotted to Zenskar has read-only access.

Set up a ClickHouse data-source connector via Zenskar dashboard

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 connector.
Click on the SAVE SOURCE button.

Configure the connector

General configuration

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 (default = `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 associated 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 available options are: 1) No Tunnel; 2) SSH Key Authentication; 3) Password Authentication. The default is No Tunnel, which requires no configuration and establishes a direct connection to the database.	Conditional

🚧
You must complete the corresponding conditional table below based on the SSH Tunnel Method you choose.

SSH key authentication configuration

🚧
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

🚧
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 the source to Zenskar’s data infrastructure. The data is periodically synced to maintain freshness. This option is:

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

Set up a ClickHouse data-source connector via API

Refer to the create data-source connector API reference.

💡
The connector_config object is the only part of the request that differs across connector types (such as BigQuery, Snowflake, or Redshift). The create data-source connector API reference provides a generic overview, while this document explains the ClickHouse-specific structure of connector_config.

Request examples

The API request payload depends on the SSH Tunnel Method. The following three tabs exemplify the available options: 1) No Tunnel; 2) SSH Key Authentication; 3) Password Authentication.

curl --location 'https://api.zenskar.com/datasources' \
-H 'x-api-key: <your-api-key>' \
-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"}

ClickHouse-specific connector configuration: No tunnel

The connector_config object contains configuration fields specific to the ClickHouse data source.

Field	Description	Required
`host`	The hostname or IP address of the ClickHouse server.	Yes
`port`	The port used to connect to the ClickHouse server. Default is typically `8123`.	Yes
`database`	The database name to connect to in ClickHouse.	Yes
`username`	Username for authenticating with the ClickHouse server.	Yes
`password`	Password for authenticating with the ClickHouse server.	Yes

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.

📑
SSH credentials and ClickHouse database credentials are separate.