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

> Integrate Estuary and ClickHouse with a connector using the native protocol

# Direct materialization from Estuary to ClickHouse

export const PartnerBadge = () => {
  return <div className="PartnerBadge">
            <div className="PartnerBadgeIcon">
                <svg width="16" height="16" viewBox="0 0 16 16" fill="none" xmlns="http://www.w3.org/2000/svg">
                    <polyline points="12.5 9.5 10 12 6 11 2.5 8.5" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" strokeWidth="1" />
                    <polyline points="4.54 4.41 8 3.5 11.46 4.41" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" strokeWidth="1" />
                    <path d="M2.15,3.78 L0.55,6.95 A0.5,0.5 0,0,0 0.77,7.62 L2.5,8.5 L4.54,4.41 L2.82,3.55 A0.5,0.5 0,0,0 2.15,3.78 Z" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" strokeWidth="1" />
                    <path d="M13.5,8.5 L15.23,7.62 A0.5,0.5 0,0,0 15.45,6.95 L13.85,3.78 A0.5,0.5 0,0,0 13.18,3.55 L11.46,4.41 Z" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" strokeWidth="1" />
                    <path d="M11.5,4.5 L9,4.5 L6.15,7.27 A0.5,0.5 0,0,0 6.24,8.05 C7.33,8.74 8.81,8.72 10,7.5 L12.5,9.5 L13.5,8.5" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" strokeWidth="1" />
                    <polyline points="7.75 13.5 5.15 12.85 3.5 11.67" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" strokeWidth="1" />
                </svg>
            </div>
            Partner Integration
        </div>;
};

<PartnerBadge />

Estuary provides a direct materialization connector with ClickHouse that uses ClickHouse's [native protocol](/docs/interfaces/tcp) and [native format](/docs/interfaces/formats/Native).

This allows Estuary to:

* Materialize data to both self-hosted and ClickHouse Cloud instances
* Automatically handle tasks like table creation and schema evolution
* Support soft or hard deletes
* Use `ReplacingMergeTree` for standard merge updates or `MergeTree` for delta updates
* Provide exactly-once delivery

See [Estuary's Kafka ClickPipe integration](/docs/integrations/connectors/data-ingestion/community-integrations/estuary-clickpipes) for a ClickPipes workflow.

<h2 id="setup-guide">
  Setup guide
</h2>

**Prerequisites**

You will need:

* An [Estuary account](https://dashboard.estuary.dev/register)
* One or more [**captures**](https://docs.estuary.dev/concepts/captures/) in Estuary that pull data from your desired sources
* A ClickHouse instance, self-hosted or Cloud account
* A ClickHouse database user with credentials

<Steps>
  <Step title="Configure ClickHouse for integration" id="1-configure-clickhouse">
    To set up Estuary's ClickHouse connector, you will need to gather some information from your ClickHouse instance and configure user permissions.

    1. Copy your database's host endpoint.

       For the port, use **9440** if TLS is enabled or **9000** if TLS is disabled.

       Together, the host and port will form the **address** you need to provide to Estuary.

    2. Grant permissions to the database user that Estuary will access.

       To automatically create and manage tables for you, Estuary will need `CREATE`, `SELECT`, `INSERT`, etc. permissions on your target database as well as permissions for metadata discovery and partition management.

       You can grant all required permissions by running these SQL commands, replacing `<database>` and `<user>` with your own information:

       ```sql theme={null}
       -- Target database access: CREATE TABLE, DROP TABLE, SELECT, INSERT, TRUNCATE, etc.
       GRANT ALL ON <database>.* TO <user>;

       -- System table access for metadata discovery and partition management.
       -- These are NOT covered by the database grant above.
       GRANT SELECT ON system.columns TO <user>;
       GRANT SELECT ON system.parts TO <user>;
       GRANT SELECT ON system.tables TO <user>;
       ```

    3. Optionally restrict user system access to only the target database.

       You can do so with row-level policies. For example:

       ```sql theme={null}
       CREATE ROW POLICY estuary_columns ON system.columns FOR SELECT USING database = '<database>' TO <user>;
       CREATE ROW POLICY estuary_parts ON system.parts FOR SELECT USING database = '<database>' TO <user>;
       CREATE ROW POLICY estuary_tables ON system.tables FOR SELECT USING database = '<database>' TO <user>;
       ```

    You can then move to Estuary to finish setup.
  </Step>

  <Step title="Create an Estuary materialization" id="2-create-an-estuary-materialization">
    1. In Estuary's dashboard, go to the [Destinations](https://dashboard.estuary.dev/materializations) page.
    2. Click **+ New Materialization**.
    3. Select the **ClickHouse** connector.
    4. Fill out the **Materialization Details** section:
       * Provide a unique name for your materialization
       * Choose a data plane (cloud provider and region)
    5. Fill out **Endpoint Config** details so Estuary can connect to your ClickHouse instance:

       * **Address:** the host and port of your instance
       * **Database:** target database name
       * **Authentication:** username and password for the database user

       You can also configure optional settings, such as whether to use hard deletes and the SSL mode to use.
  </Step>

  <Step title="Configure source collections" id="3-configure-source-collections">
    Choose which sources you'd like to materialize into ClickHouse in the **Source Collections** section.

    1. Link an existing **capture** or add individual data collections to materialize to ClickHouse.
    2. Select a data collection from the list to configure further if necessary. Customization options include:
       * Choose a different table name for the collection
       * Select merge behavior for the collection (whether to use delta updates mode)
       * Customize field selection behavior to control which fields are materialized
    3. Once you're happy with how data will be materialized to ClickHouse, click **Next** and **Save and Publish**.

    Estuary will start backfilling data from the selected collections to ClickHouse and then stream updates as they occur.
  </Step>
</Steps>

<h2 id="additional-resources">
  Additional resources
</h2>

For more on setting up a ClickHouse connector with Estuary, see Estuary's documentation:

* Reference Estuary's [ClickHouse materialization docs](https://docs.estuary.dev/reference/Connectors/materialization-connectors/ClickHouse/).
* Besides the UI-based workflow provided in these instructions, you can also manage pipeline setup with Estuary via CLI. See Estuary's [guides on `flowctl`](https://docs.estuary.dev/guides/flowctl/ci-cd/) for more on working with Estuary programmatically.