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

# ClickHouse

> Configuring your ClickHouse destination.

## Prerequisites

* If your ClickHouse security posture requires IP whitelisting, have our static IP available during the following steps. It will be required in Step 1.

<Steps>
  <Step title="Allow access">
    <Warning>
      **SSH Tunneling Not Supported**

      SSH Tunneling is currently unsupported for Clickhouse destinations. Please ensure your Clickhouse destination is accessible over the public internet.
    </Warning>

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

    1. incoming connections to your host and port (usually `9440`) from the static IP.
    2. outgoing connections from ports `1024` to `65535` to the static IP.

    <Note>
      **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 support for the static egress IP.
    </Note>
  </Step>

  <Step title="Create writer user">
    Create a database user to perform the writing of the data.

    1. Open a connection to your ClickHouse database.
    2. Create a user for the data transfer by executing the following SQL command.

    ```sql title="Create user" icon="database" theme={null}
    CREATE USER <username>@'%' IDENTIFIED BY '<some-password>';
    ```

    <Note>
      **Password Rules**

      Passwords may only include alphanumeric characters (A-Z, a-z, 0-9), dashes (-), and underscores (\_).
    </Note>

    3. Grant user required privileges on the database.

    ```sql title="Grant privileges" icon="database" expandable 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>@'%';
    ```

    <Note>
      **Understanding the `CREATE TEMPORARY TABLE, S3` permissions**

      The `CREATE TEMPORARY TABLE` and `S3` permissions are required to efficiently transfer data to ClickHouse. Under the hood, these permissions are used to stage data in object storage as compressed files, COPY INTO temporary tables, and finally merge into the target tables. By definition, the temporary table will not exist outside of the session.
    </Note>
  </Step>

  <Step title="Setup staging bucket">
    ClickHouse sources require a staging bucket to efficiently transfer data. Configure your staging bucket using one of the following types of ClickHouse supported object storage:

    * S3
    * GCS
    * Implicit

    <Note>
      **Using the `implicit` bucket option**

      ClickHouse supports the ability to configure staging resources with [environment credentials](https://clickhouse.com/docs/en/integrations/s3#managing-credentials). If this setting is enabled on your ClickHouse cluster, you may choose to use the configured implicit staging resources using the `implicit` option for the staging bucket selection.
    </Note>
  </Step>

  <Step title="Add your destination">
    <Note>
      **Connection Protocol**

      Use the ClickHouse TCP native protocol, not HTTPS. This is commonly exposed on port 9000.
    </Note>

    Use the following details to complete the connection setup: **host name**, **port**, **cluster**, **database name**, **schema name**, **username**, **password**, and staging bucket details.

    <Note>
      **Understanding the `database` vs. `schema` fields (`connection database` vs. `write database`)**

      Depending on the version of your integration, you may be asked for both a `database` and `schema`, or a `connection database` and `write database`.

      * `database` (also referred to as `connection_database`): is the **database** used to establish the connection with ClickHouse.
      * `schema` (also referred to as `write_database`): is the **database/schema** within which data will be written

      These can be (and often are) the same values, but do not need to be.
    </Note>
  </Step>
</Steps>

## Using the ClickHouse data

<Note>
  **Querying ClickHouse data without duplicates**

  The resulting ClickHouse tables use the [ReplacingMergeTree](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/replacingmergetree) table engine in order to efficiently upsert changes. To properly query this data, the `FINAL` keyword must be used when selecting from these tables guarantee duplicates are removed. For example:

  ```sql title="Query without duplicates" icon="database" expandable theme={null}
  SELECT
    *
  FROM
    schema.table FINAL
  WHERE
    foo = bar
  ORDER BY foo
  LIMIT 10;
  ```
</Note>