ClickHouse

ClickHouse is a fast, open-source, column-oriented database management system built for real-time analytics on large volumes of data. It works with both ClickHouse Cloud and self-hosted deployments.

You can use ClickHouse as a destination in Weld to load data from your sources into ClickHouse, use it as the data warehouse behind your Weld Models, and use it as a source for Reverse-ETL syncs.

Setup Guide

  1. Fill in the required fields

    Host: The hostname of your ClickHouse server (for ClickHouse Cloud, this looks like abcd1234.eu-central-1.aws.clickhouse.cloud).
    Port: The port number of your ClickHouse server. Defaults to 8443 for HTTPS and 8123 for HTTP.
    Protocol: https or http. Defaults to https.
    Username: The username to connect with.
    Password: The password for the user.

Where do I find my ClickHouse Cloud credentials?

In the ClickHouse Cloud console, open your service and click Connect, then select HTTPS. This shows your host, port, and default username - use the password you set when the service was created (or reset it from the same screen).

Host cannot be changed later

The host is used to identify the connection, so it can't be changed after the connection has been created. If your ClickHouse host changes, you'll need to set up a new connection.

  1. Permissions

    The user you connect with needs enough privileges, on any database Weld will manage, to:

    • Create and drop databases and tables
    • Insert and select data
    • Run ALTER TABLE ... UPDATE mutations (used to soft-delete removed rows)

Loading data into ClickHouse

Weld creates one ClickHouse database per integration you sync (named after the destination schema you configure for the sync), since ClickHouse doesn't have a nested schema concept within a database the way most other destinations do.

Tables are created as a ReplacingMergeTree ordered by the stream's primary key(s). Incremental syncs load new rows by inserting a newer version of each row, which ClickHouse merges together in the background - so when querying these tables yourself, use the FINAL modifier to see a fully deduplicated result.

Tables are always (re)created from your source's schema, and ClickHouse doesn't yet support adding/removing columns on an existing destination table. Because of this, any schema change on the source (e.g. a column being added or removed) requires a full resync rather than an incremental update.

Using ClickHouse as a data warehouse

You can also set ClickHouse as the data warehouse behind your Weld Models, so that your SQL transformations run directly against ClickHouse. Weld creates a dedicated database for your models automatically the first time it's needed.

Using ClickHouse with Reverse-ETL

Once connected, ClickHouse can be used as the source for Reverse-ETL syncs - build a SQL model against your ClickHouse data and sync the results out to any of Weld's destinations on a schedule.

Was this page helpful?