ClickHouse Setup
Prerequisites
- ClickHouse Cloud, or ClickHouse Server 21.8 or higher with the HTTP interface enabled (default port 8123).
- Admin privileges to create users and grant permissions.
- Network connectivity from the Supermetal agent to the database.
Cluster
ClickHouse Cluster deployment is not yet supported.
Setup
Visit SQL console
Log in to ClickHouse Cloud to access the SQL console and select your service.
Create a user
CREATE USER IF NOT EXISTS 'supermetal_user' IDENTIFIED WITH sha256_password BY 'strong-password';Password security
Replace 'strong-password' with a unique password and store it securely. You will enter it when configuring the target in Supermetal.
Create database
CREATE DATABASE IF NOT EXISTS target_database;Script variables
Replace target_database with the database to write to.
Grant permissions
GRANT CREATE DATABASE ON target_database.*, CREATE TABLE ON target_database.*, CREATE TEMPORARY TABLE ON *.*, S3 ON *.*, AZURE ON *.*, INSERT ON target_database.*, ALTER TABLE ON target_database.*, SELECT ON target_database.*, TRUNCATE ON target_database.* TO 'supermetal_user';Script variables
Replace target_database with the database created in the previous step.
Connection details
You need the following to configure the target in Supermetal:
- HTTPS URL (e.g.
https://<host>.<region>.clickhouse.cloud:8443) - Username and password you created
IP whitelist
You may need to add the Supermetal agent's IP address under Service Settings, Security, IP Access List.
Create a user
Log in to the ClickHouse server as an admin user.
CREATE USER IF NOT EXISTS 'supermetal_user' IDENTIFIED WITH sha256_password BY 'strong-password';Password security
Replace 'strong-password' with a unique password and store it securely. You will enter it when configuring the target in Supermetal.
Create database
CREATE DATABASE IF NOT EXISTS target_database;Script variables
Replace target_database with the database to write to.
Grant permissions
GRANT CREATE DATABASE ON target_database.*, CREATE TABLE ON target_database.*, CREATE TEMPORARY TABLE ON *.*, S3 ON *.*, AZURE ON *.*, INSERT ON target_database.*, ALTER TABLE ON target_database.*, SELECT ON target_database.*, TRUNCATE ON target_database.* TO 'supermetal_user';Script variables
Replace target_database with the database created in the previous step.
Connection details
You need the following to configure the target in Supermetal:
- HTTP interface URL (e.g.
https://hostname:port) - Username and password you created
Table Engine
The engine setting selects the table engine.
| Engine | Description |
|---|---|
Auto (default) | Tables with primary keys use ReplacingMergeTree with version tracking (_sm_version). Tables without primary keys use MergeTree. |
MergeTree | Append only, without deduplication. Use when deduplication is unneeded or write performance matters more. |
Null | Discards all data. Useful as source tables for Materialized Views. |
Data Retention (TTL)
The ttl_days setting expires rows automatically. When enabled, Supermetal adds a _sm_synced_at column, populated on insert, and ClickHouse drops rows that many days after their sync time.
Table Naming
Target table names carry the source schema as a prefix by default, so public.users becomes public_users. Turning the schema prefix off writes to users instead, which is safe only when table names are unique across all source schemas. Independently, you can add a custom prefix or suffix to every table name (raw_users, users_log).
Nullability
Supermetal creates every column except primary keys as Nullable, regardless of the source schema. This handles CDC edge cases where a change event does not carry every column's value, such as large object types. Two options change the default.
- Preserve source nullability. Columns keep the NOT NULL constraints they have at the source.
- Non nullable columns. Every column is created non
Nullable, and source NULLs land as ClickHouse type defaults (0 for numbers, '' for strings, epoch for dates). Customize the defaults per column withALTER TABLE ... MODIFY COLUMN ... DEFAULT ....
Last updated on