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

# ClickHouse

## Overview

Stigg's integration with ClickHouse allows you to export your product catalog, customer, and subscription data to ClickHouse for high-performance analytical queries.

<Note>
  Stigg's native data export integrations are included in the Scale plan, and are also available as an optional add-on to the Growth plan. See Stigg's pricing for more details.
</Note>

<Card title="View the full entity schema" icon="sitemap" href="./schema">
  See every table and column exported to your destination, organized by entity group.
</Card>

## Setting up the integration

### Prerequisites

* A ClickHouse instance (self-hosted or ClickHouse Cloud) with administrative access
* An S3 or GCS bucket for staging data before it's loaded into ClickHouse (or use **Implicit** staging if credentials are already configured on your ClickHouse instance)

<Note>
  If your security posture requires IP allowlisting, Stigg connects from a static IP: `35.192.85.117/32` (US Cloud) or `104.199.49.149/32` (EU Cloud). For private or self-hosted deployments, [contact Stigg support](mailto:support@stigg.io) for the egress IP.
</Note>

<Warning>
  SSH tunneling isn't currently supported for ClickHouse destinations.
</Warning>

<Steps>
  <Step title="Allow network access">
    Whitelist Stigg's static IP in your firewall or security group:

    * Incoming connections to your ClickHouse host and port (typically `9440`) from Stigg's static IP
    * Outgoing connections from ports `1024-65535` to Stigg's static IP
  </Step>

  <Step title="Create a database user">
    Connect to your ClickHouse instance with a SQL client and create a user for Stigg:

    <CodeGroup>
      ```sql create-user.sql theme={null}
      CREATE USER <username>'%' IDENTIFIED BY '<some-password>';
      ```
    </CodeGroup>

    <Note>
      Passwords may only contain alphanumeric characters, dashes, and underscores.
    </Note>

    Grant the permissions Stigg needs to stage and load data:

    <CodeGroup>
      ```sql grant.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>'%';
      ```
    </CodeGroup>
  </Step>

  <Step title="Configure the staging bucket">
    Choose an S3 or GCS bucket for staging, or select **Implicit** if credentials are already configured as environment variables on your ClickHouse instance. Optionally add a lifecycle rule to delete staged objects after 2 days.

    <Note>
      For ClickHouse Cloud using S3 role-based access, add a trust policy on your S3 role allowing ClickHouse's IAM ARN to assume it:

      <CodeGroup>
        ```json trust-policy.json theme={null}
        {
            "Effect": "Allow",
            "Principal": {"AWS": "<CLICKHOUSE_IAM_ARN>"},
            "Action": "sts:AssumeRole"
        }
        ```
      </CodeGroup>

      Find the ARN in ClickHouse Cloud under **Settings > Network security information > Service role ID**.
    </Note>
  </Step>

  <Step title="Connect Stigg with ClickHouse">
    In [Stigg](https://app.stigg.io/), navigate to **Integrations > Apps > ClickHouse**.

    Enter the following information in the connection form:

    | Field               | Description                                                                                          |
    | ------------------- | ---------------------------------------------------------------------------------------------------- |
    | Host                | The hostname of your ClickHouse server                                                               |
    | Port                | The port for the native protocol. Use `9000` (not the HTTPS port).                                   |
    | Cluster             | The ClickHouse cluster name (if using a clustered deployment)                                        |
    | Database            | The connection database                                                                              |
    | Schema              | The database where Stigg will write tables (the write database). Can be the same as **Database**.    |
    | Username            | The ClickHouse user created above                                                                    |
    | Password            | The password for the ClickHouse user                                                                 |
    | Staging bucket type | **S3**, **GCS**, or **Implicit**                                                                     |
    | Staging bucket      | The bucket name, region, and credentials used for staging. Not required if **Implicit** is selected. |

    Click **Test & connect**.
  </Step>

  <Step title="Select entities to export">
    After entering your connection details, expand the **Entities to export** section to choose which entity groups to include in the sync. All groups are selected by default.

    See [Exported entities](./overview#exported-entities) for a description of each group.
  </Step>
</Steps>

## Querying exported data

Stigg's tables in ClickHouse use the `ReplacingMergeTree` engine, which deduplicates rows asynchronously in the background. Use the `FINAL` keyword to force deduplication at query time:

<CodeGroup>
  ```sql query.sql theme={null}
  SELECT * FROM schema.table FINAL WHERE foo = bar;
  ```
</CodeGroup>


## Related topics

- [Exported entities](/documentation/importing-and-exporting-data/export/overview.md#exported-entities)
- [Sync process, schedule, manual sync, and sync history](/documentation/importing-and-exporting-data/export/overview.md#sync-process)