ClickHouse
Supermetal replicates from ClickHouse over its HTTP interface. Selected ClickHouse tables are exported as Parquet files to an S3 or Azure object store buffer.
Prerequisites
- ClickHouse Cloud, ClickHouse Cluster, or ClickHouse Server 25 or 26 with the HTTP interface enabled.
- Admin privileges to create users and grant permissions.
- Network connectivity from the Supermetal agent to ClickHouse.
- S3, S3 compatible, or Azure Blob Storage reachable from ClickHouse and the Supermetal agent.
Azure credentials
Azure object storage requires an account key or SAS token. Managed identity and bearer token credentials are not supported for this source.
Shared object keys
The ClickHouse source, Supermetal, and any target that reads files directly must resolve the same object keys. Use the same bucket or container, endpoint, and prefix for each component.
Setup
Visit SQL console
Log in to ClickHouse Cloud, select your service, and open the SQL console.
Create a user
CREATE USER IF NOT EXISTS 'supermetal_user'
IDENTIFIED WITH sha256_password BY 'Strong-password1!';Password security
Replace 'Strong-password1!' with a unique password and store it securely. You will enter it when configuring the source in Supermetal.
Grant source permissions
These permissions let Supermetal read the selected database and stop an interrupted export before the next snapshot begins.
GRANT SELECT ON source_database.* TO 'supermetal_user';
GRANT SELECT ON system.databases TO 'supermetal_user';
GRANT SELECT ON system.tables TO 'supermetal_user';
GRANT SELECT ON system.columns TO 'supermetal_user';
GRANT SELECT ON system.processes TO 'supermetal_user';
GRANT KILL QUERY ON *.* TO 'supermetal_user';
GRANT REMOTE ON *.* TO 'supermetal_user';
GRANT CLUSTER ON *.* TO 'supermetal_user';Script variables
Replace source_database with the database to replicate.
Grant object storage permission
ClickHouse requires permission to create temporary tables when exporting through an object storage table function.
GRANT CREATE TEMPORARY TABLE ON *.* TO 'supermetal_user';Grant the table function permission for your object storage provider.
GRANT S3 ON *.* TO 'supermetal_user';GRANT AZURE ON *.* TO 'supermetal_user';Connection details
You need the following to configure the source in Supermetal.
- HTTPS URL, such as
https://<host>.<region>.clickhouse.cloud:8443. - Username and password created above.
- Name of the source database.
IP whitelist
You may need to add the Supermetal agent 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-password1!';Password security
Replace 'Strong-password1!' with a unique password and store it securely. You will enter it when configuring the source in Supermetal.
Grant source permissions
These permissions let Supermetal read the selected database and stop an interrupted export before the next snapshot begins.
GRANT SELECT ON source_database.* TO 'supermetal_user';
GRANT SELECT ON system.databases TO 'supermetal_user';
GRANT SELECT ON system.tables TO 'supermetal_user';
GRANT SELECT ON system.columns TO 'supermetal_user';
GRANT SELECT ON system.processes TO 'supermetal_user';
GRANT KILL QUERY ON *.* TO 'supermetal_user';Script variables
Replace source_database with the database to replicate.
Grant object storage permission
ClickHouse requires permission to create temporary tables when exporting through an object storage table function.
GRANT CREATE TEMPORARY TABLE ON *.* TO 'supermetal_user';Grant the table function permission for your object storage provider.
GRANT S3 ON *.* TO 'supermetal_user';GRANT AZURE ON *.* TO 'supermetal_user';Connection details
You need the following to configure the source in Supermetal.
- HTTP interface URL, such as
https://hostname:8443. - Username and password created above.
- Name of the source database.
Use one stable server endpoint
The URL for a self managed source must connect to the same ClickHouse node for exports, query inspection, and cancellation. Do not use a load balancer or proxy that can route these requests to different nodes. ClickHouse Cloud service URLs are supported because Supermetal inspects and cancels queries across the Cloud service replicas.
Table Engines
| Engine | Snapshot behavior |
|---|---|
ReplacingMergeTree, CollapsingMergeTree, SummingMergeTree, AggregatingMergeTree | Plain sorting key columns become the table key. |
MergeTree, Memory, TinyLog, Log, StripeLog, File | The table is copied without a key. |
The corresponding Replicated and Shared variants are supported. A sorting key that contains an expression cannot become the table key, so the table is copied without one.
Views
Views and materialized views are not supported as source objects. Exclude them from the catalog selection and select their underlying tables instead.
Data Types Mapping
| ClickHouse Type(s) | Apache Arrow DataType | Notes |
|---|---|---|
Int8 | Int8 | |
Int16 | Int16 | |
Int32 | Int32 | |
Int64 | Int64 | |
Int128 | FixedSizeBinary(16) | Recreated as Int128 on a ClickHouse target. |
Int256 | FixedSizeBinary(32) | Recreated as Int256 on a ClickHouse target. |
UInt8 | UInt8 | |
UInt16 | UInt16 | |
UInt32 | UInt32 | |
UInt64 | UInt64 | |
UInt128 | FixedSizeBinary(16) | Recreated as UInt128 on a ClickHouse target. |
UInt256 | FixedSizeBinary(32) | Recreated as UInt256 on a ClickHouse target. |
Float32 | Float32 | |
Float64 | Float64 | |
Decimal(p, s) where p ≤ 38 | Decimal128(p, s) | Precision and scale are preserved. |
| ClickHouse Type(s) | Apache Arrow DataType | Notes |
|---|---|---|
Bool, Boolean | Boolean |
| ClickHouse Type(s) | Apache Arrow DataType | Notes |
|---|---|---|
Date | UInt16 / Date32 | Transport type depends on the ClickHouse version. Recreated as Date on a ClickHouse target. |
Date32 | Date32 | |
DateTime([tz]) | UInt32 / Timestamp(ms, [tz]) | Transport type depends on the ClickHouse version. Recreated with its timezone on a ClickHouse target. |
DateTime64(p, [tz]) where p ≤ 3 | Timestamp(ms, [tz]) | The original precision and timezone are preserved for ClickHouse targets. |
DateTime64(p, [tz]) where 4 ≤ p ≤ 6 | Timestamp(µs, [tz]) | The original precision and timezone are preserved for ClickHouse targets. |
| ClickHouse Type(s) | Apache Arrow DataType | Notes |
|---|---|---|
String | Utf8 | |
FixedString(n) | FixedSizeBinary(n) | Recreated with the same length on a ClickHouse target. |
Enum8, Enum16 | Int8, Int16 / Utf8 | Transport type depends on the ClickHouse version. Labels and numeric values are preserved for ClickHouse targets. |
LowCardinality(T) | Mapping for T | Recreated with LowCardinality on a ClickHouse target. Nullability is inherited recursively from T. |
| ClickHouse Type(s) | Apache Arrow DataType | Notes |
|---|---|---|
IPv4 | UInt32 | Recreated as IPv4 on a ClickHouse target. |
IPv6 | FixedSizeBinary(16) | Recreated as IPv6 on a ClickHouse target. |
| ClickHouse Type(s) | Apache Arrow DataType | Notes |
|---|---|---|
Nullable(T) | Nullable mapping for T | Supported when T is supported. |
Array(T) | List<T> | Supported when T is supported. |
Tuple(...) | Struct | Supported when every field is supported. |
Map(K, V) | Map<K, V> | Supported when both types are supported. |
SimpleAggregateFunction(F, T) | Mapping for T | Supported when T is supported. Nullability is inherited recursively from T. |
| ClickHouse Type(s) | Apache Arrow DataType | Notes |
|---|---|---|
Decimal(p, s) where p > 38 | Unsupported | |
DateTime64(p, [tz]) where p > 6 | Unsupported | |
UUID, JSON | Unsupported | |
Nested | Unsupported | |
Point, Ring, Polygon, MultiPolygon | Unsupported |
Remove unsupported columns from the table selection before starting the connector.
ClickHouse to ClickHouse replication
When both the source and target are ClickHouse, Supermetal recreates supported columns with their original ClickHouse types. This includes recursive nullability, LowCardinality, SimpleAggregateFunction, enum definitions, decimal precision, and temporal precision.
Changelog
Last updated on