> ## Documentation Index
> Fetch the complete documentation index at: https://docs.staging.metronome.com/llms.txt
> Use this file to discover all available pages before exploring further.

# ClickHouse

## Prerequisites

* If your ClickHouse security posture requires IP whitelisting, have the data-syncing service's static IP available. You need it in Step 1.

> 🚧 SSH tunneling not supported
>
> ClickHouse destinations don't support SSH tunneling. Your ClickHouse instance must be accessible over the public internet.

## Step 1: Allow access

Create a rule in a security group or firewall settings to whitelist:

* Incoming connections to your host and port (usually `9440` for TLS or `9000` for TCP) from the static IP.
* Outbound connections from ports `1024` to `65535` to the static IP.

> 📘 Network allowlisting
>
> * **Cloud Hosted (US):** `35.192.85.117/32`
> * **Cloud Hosted (EU):** `104.199.49.149/32`
>
> If private-cloud or self-hosted, contact your Metronome representative for the static egress IP.

## Step 2: Create writer user

Create a database user to write the exported data.

1. Open a connection to your ClickHouse database.
2. Run the following SQL command to create a user for the data transfer:

```sql theme={null}
CREATE USER <username>@'%' IDENTIFIED BY '<some-password>';
```

> 🚧 Password rules
>
> Passwords can only include alphanumeric characters (`A-Z`, `a-z`, `0-9`), dashes (`-`), and underscores (`_`).

3. Grant the user required privileges on the database:

```sql theme={null}
GRANT SELECT ON information_schema.columns TO <username>;
GRANT CREATE, INSERT, DROP, ALTER, OPTIMIZE, SHOW, TRUNCATE ON <database>.* TO <username>@'%';
GRANT CREATE TEMPORARY TABLE, S3 ON *.* TO <username>@'%';
```

> 📘 Understanding the `CREATE TEMPORARY TABLE` and `S3` permissions
>
> The data-syncing service uses these permissions to efficiently transfer data to ClickHouse. It stages data in object storage as compressed files, copies it into temporary tables with `COPY INTO`, and merges it into the target tables. The temporary table doesn't persist beyond the session.

## Step 3: Set up staging bucket

ClickHouse destinations require a staging bucket to efficiently transfer data. Configure your staging bucket using one of the following options:

* **S3** — Follow the [S3 staging bucket](/guides/reporting-insights/data-export/destinations/staging-s3) setup guide.
* **GCS** — Follow the [GCS staging bucket](/guides/reporting-insights/data-export/destinations/staging-gcs) setup guide.
* **Implicit** — Use your ClickHouse cluster's built-in staging resources (see below).

> 📘 Using the implicit bucket option
>
> ClickHouse supports configuring staging resources with environment credentials. If this setting is enabled on your ClickHouse cluster, you can choose the **implicit** option during destination setup to use the cluster's configured staging resources.

> 📘 Optional: Add a short retention lifecycle policy
>
> If using S3 or GCS for staging, you can configure a lifecycle rule on the bucket to automatically delete objects older than 2 days. Transfer logic cleans up files after completion, so this step is optional.

## Step 4: Add your destination

> 🚧 Connection protocol
>
> Use the ClickHouse **TCP native protocol**, not HTTPS. This is commonly exposed on port `9000`.

Use the following details to complete the connection setup:

* **Host name**
* **Port**
* **Cluster**
* **Database name**
* **Schema name**
* **Username**
* **Password**
* **Staging bucket details**

> 📘 Understanding the database vs. schema fields
>
> Depending on the version of your integration, the setup asks for both a database and schema, or a connection database and write database.
>
> * **Database** (also referred to as `connection_database`): the database used to establish the connection with ClickHouse.
> * **Schema** (also referred to as `write_database`): the database or schema where the service writes data.
>
> These can be the same value, but don't need to be.

## Querying ClickHouse data

The resulting ClickHouse tables use the [ReplacingMergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/replacingmergetree) table engine to efficiently upsert changes. Use the `FINAL` keyword when selecting from these tables to remove duplicates.

```sql theme={null}
SELECT
  *
FROM
  schema.table FINAL
WHERE
  foo = bar
ORDER BY foo
LIMIT 10;
```