ClickHouse Cloud Integration
Relevant source files
The following files were used as context for generating this wiki page:
The ClickHouse Cloud Integration provides a specialized state backend for Meltano, enabling pipeline state persistence—such as incremental bookmarks and full-table markers—within a ClickHouse warehouse. This implementation addresses the specific architectural characteristics of ClickHouse as an OLAP store, which lacks traditional row-level updates and transactional locking primitives.
Sources: README.md:1-7, src/meltano_state_backend_clickhouse/backend.py:1-12
System Architecture
The integration centers on the ClickhouseStateStoreManager, which implements the Meltano StateStoreManager interface. It utilizes the clickhouse-connect library to interact with ClickHouse instances, supporting both standard installations and secure cloud environments via TLS.
Sources: pyproject.toml:25-26, src/meltano_state_backend_clickhouse/backend.py:126-128
Core Components and Class Structure
The following diagram illustrates the relationship between the manager class and its dependencies:
Data Storage Strategy
ClickHouse is optimized for append-only operations. To support state updates, the backend utilizes the ReplacingMergeTree engine. This engine automatically manages versioning by keeping the row with the highest updated_at (for state) or locked_at (for locks) value.
Sources: src/meltano_state_backend_clickhouse/backend.py:180-210, README.md:32-35
Schema Definitions
The backend maintains two primary tables within a designated schema (default: meltano):
| Table Type | Table Name (Default) | Engine | Key Columns | Purpose |
|---|---|---|---|---|
| State Table | meltano.state | ReplacingMergeTree(updated_at) | state_id | Stores serialized JSON state data. |
| Lock Table | meltano.state_lock | ReplacingMergeTree(locked_at) | state_id | Manages best-effort advisory locks. |
Sources: src/meltano_state_backend_clickhouse/backend.py:180-210, README.md:28-28
State Retrieval Logic
When retrieving state, the backend uses the FINAL modifier in SQL queries. This forces ClickHouse to collapse all versions of a state_id and return only the most recent entry, simulating row-level updates.
SELECT partial_state, completed_state
FROM meltano.state FINAL
WHERE state_id = {state_id:String}
LIMIT 1
Sources: src/meltano_state_backend_clickhouse/backend.py:228-232, README.md:35-35
Lock Management and Concurrency
ClickHouse does not support unique constraints or traditional primary key violations for locking. The backend implements a "best-effort" advisory lock using a "settle-then-decide" algorithm to prevent race conditions in distributed environments.
Sources: src/meltano_state_backend_clickhouse/backend.py:7-12, src/meltano_state_backend_clickhouse/backend.py:317-335
Lock Acquisition Flow
The lock process involves inserting a candidate row with a unique lock_id (UUID), waiting for a "settle" period, and then verifying if the current process owns the earliest valid lock.
Timing Parameters
| Parameter | Value | Description |
|---|---|---|
LOCK_SETTLE_SECONDS | 1.0s | Time to wait after insertion to allow concurrent writes to become visible. |
STALE_LOCK_SECONDS | 300s | Period after which a lock is considered stale and eligible for cleanup. |
LOCK_TIMEOUT_SECONDS | 30s | Maximum time a process will attempt to acquire a lock before failing. |
Sources: src/meltano_state_backend_clickhouse/backend.py:46-51
Configuration and Connectivity
The backend is configured via a URI or specific Meltano settings. It translates these into connection parameters for the clickhouse-connect client.
Sources: src/meltano_state_backend_clickhouse/backend.py:129-158, README.md:15-26
Configuration Settings
| Setting Name | Env Var | Type | Description |
|---|---|---|---|
host | CLICKHOUSE_HOST | String | ClickHouse server hostname. |
port | CLICKHOUSE_PORT | Integer | HTTP interface port (default: 8123). |
database | CLICKHOUSE_DATABASE | String | Target database name. |
secure | CLICKHOUSE_SECURE | Boolean | Enables HTTPS/TLS for Cloud connections. |
schema | CLICKHOUSE_SCHEMA | String | Database used for state (default: meltano). |
table | CLICKHOUSE_TABLE | String | Table name (default: state). |
Sources: src/meltano_state_backend_clickhouse/backend.py:59-112, pyproject.toml:34-41
Summary
The ClickHouse Cloud Integration enables Meltano state persistence in high-performance OLAP environments. By leveraging ReplacingMergeTree for versioned storage and a custom "settle-then-decide" locking mechanism, it overcomes ClickHouse's lack of transactional primitives to ensure reliable state management for data pipelines.
Sources: src/meltano_state_backend_clickhouse/backend.py:1-12, README.md:32-40