> ## Documentation Index
> Fetch the complete documentation index at: https://docs2.zenskar.com/llms.txt
> Use this file to discover all available pages before exploring further.
# ClickHouse
## Overview
[ClickHouse](https://clickhouse.com/) 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:
```sql theme={null}
-- 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
1. Log into your Zenskar dashboard.
2. In the left side bar, click **Usage > Data Sources**.
3. In the top-right corner, click **+ ADD DATA SOURCE**.
4. On the **Add New Data Source** page, [configure the connector](https://docs.zenskar.com/docs/data-source-connector-for-clickhouse#configure-the-connector).
5. 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¶m2=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
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](https://www.zenskar.com/reference/create-data-source-connector) 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.
```shell theme={null}
curl --location 'https://api.zenskar.com/datasources' \
-H 'x-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 |
```shell theme={null}
curl --location 'https://api.zenskar.com/datasources' \
-H 'x-api-key: ' \
-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":"database_host"},
"source_definition_id":"bad83517-5e54-4a3d-9b53-63e85fbd4d7c"}
```
#### ClickHouse-specific connector configuration: SSH key authentication
The `connector_config` object contains configuration fields specific to the ClickHouse data source.
| Field | Description | Required |
| ------------- | ------------------------------------------------------ | -------- |
| `host` | Hostname or IP address of the database server. | Yes |
| `port` | Database port (e.g., `8123` for ClickHouse). | Yes |
| `database` | Name of the database to connect to. | Yes |
| `username` | Database username for authentication. | Yes |
| `password` | Database password for authentication. | Yes |
| `tunnel_host` | Hostname or IP address of the SSH bastion/jump server. | Yes |
| `tunnel_port` | SSH port on the bastion/jump server (default `22`). | Yes |
| `tunnel_user` | SSH user to authenticate with on the tunnel server. | Yes |
| `ssh_key` | Private SSH key used for secure tunnel authentication. | Yes |
```shell theme={null}
curl --location 'https://api.zenskar.com/datasources' \
-H 'x-api-key: ' \
-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"}
```
#### ClickHouse-specific connector configuration
The `connector_config` object contains configuration fields specific to the ClickHouse data source.
| Field | Description | Required |
| ---------------------- | ------------------------------------------------------ | -------- |
| `host` | Hostname or IP address of the database server. | Yes |
| `port` | Database port (e.g., `8123` for ClickHouse). | Yes |
| `database` | Name of the database to connect to. | Yes |
| `username` | Database username for authentication. | Yes |
| `password` | Database password for authentication. | Yes |
| `tunnel_host` | Hostname or IP address of the SSH bastion/jump server. | Yes |
| `tunnel_port` | SSH port on the bastion/jump server (default `22`). | Yes |
| `tunnel_user` | Username for SSH authentication on the tunnel server. | Yes |
| `tunnel_user_password` | Password for SSH authentication on the tunnel 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:
```bash theme={null}
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:
```bash theme={null}
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.