ClickHouse MergeTree Support

[Pipboyguy]

Support for MergeTree Engine in ClickHouse Destination

Problem

The ClickHouse destination in dlt 0.4.11 currently only supports the ReplicatedMergeTree table engine. However, many users have requested support for the standard non-replicated MergeTree engine to enable local development and testing deployments.

Desired Behavior

The ClickHouse destination should correctly handle ENGINE=MergeTree and create the appropriate table structure in ClickHouse.

Proposed Solution

Enhance the ClickHouse destination adapter with the following:

1. Automatically detect whether the connected ClickHouse instance is a Cloud instance (replicated) or a local self-managed instance.
   • For ClickHouse Cloud, default to ReplicatedMergeTree engine
   • For non-cloud instances, default to standard MergeTree

SELECT value
FROM system.settings
WHERE name = 'cloud_mode';

2. Allow users to explicitly specify the desired engine (MergeTree or ReplicatedMergeTree) in the destination configuration. This will override the automatic detection.

3. Properly interpolate the provided engine into the CREATE TABLE statement executed against ClickHouse.

Note: To limit scope, this enhancement will not include support for specifying replication, zookeeper or shard details for the ReplicatedMergeTree engine. Users requiring those customizations can continue to specify the full engine definition in their configuration.

Considerations

• Thoroughly test the solution against both ClickHouse Cloud and various self-managed ClickHouse versions
• Update documentation to explain the new behavior and configuration options
• Consider any impacts to downstream systems or processes relying on the current hard-coded ReplicatedMergeTree behavior

[Pipboyguy]

The engine should be configurable via clickhouse_adapter:

clickhouse_adapter(data, table_engine_type="merge_tree")

[Pipboyguy]

It seems that on ClickHouse Cloud, the ReplicatedMergeTree table engine family is automatically replaced by the SharedMergeTree engine family:

https://clickhouse.com/docs/en/cloud/reference/shared-merge-tree

This happened recently btw. This makes our life easier since just MergeTree engine will now work across both cloud and on-prem.

Self managed will still have the option of using ReplciatedMergeTree.