Skip to main content

​
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:
CREATE USER <username>@'%' IDENTIFIED BY '<some-password>';
🚧 Password rules Passwords can only include alphanumeric characters (A-Z, a-z, 0-9), dashes (-), and underscores (_).
  1. Grant the user required privileges on the database:
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 setup guide.
  • GCS — Follow the GCS staging bucket 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 table engine to efficiently upsert changes. Use the FINAL keyword when selecting from these tables to remove duplicates. 
SELECT
  *
FROM
  schema.table FINAL
WHERE
  foo = bar
ORDER BY foo
LIMIT 10;