Quickstart Guide
Relevant source files
The following files were used as context for generating this wiki page:
The meltano-state-backend-clickhouse extension allows Meltano to persist pipeline state—such as incremental bookmarks and full-table markers—directly within a ClickHouse warehouse. This backend provides parity for ClickHouse users with existing Meltano state backends like Postgres or Snowflake.
This guide covers the installation, configuration, and architectural nuances of using ClickHouse as a state store, specifically addressing how the system manages state updates and locking in an OLAP environment.
Sources: README.md:1-10, src/meltano_state_backend_clickhouse/backend.py:1-12
Installation
The package can be installed via pip directly from the Git repository. It requires Python 3.10 or higher and depends on clickhouse-connect and meltano.
pip install "meltano-state-backend-clickhouse @ git+https://github.com/Meltano/meltano-state-backend-clickhouse.git%22
Sources: README.md:12-16, pyproject.toml:13-30
Configuration
Meltano must be instructed to use the ClickHouse backend by setting the MELTANO_STATE_BACKEND_URI.
URI Format
The connection string follows this pattern:
clickhouse://user:password@host:port/database
Configuration Methods
You can configure the backend using environment variables or the meltano.yml project file.
| Method | Syntax |
|---|---|
| Environment Variable | export MELTANO_STATE_BACKEND_URI="clickhouse://default:password@localhost:8123/meltano" |
| meltano.yml | state_backend: { uri: clickhouse://user:pass@host:8123/db } |
Sources: README.md:18-32, src/meltano_state_backend_clickhouse/backend.py:115-135
Detailed Settings
Individual settings can override values parsed from the URI. These are registered as Meltano settings via entry points.
| Setting | Description | Default |
|---|---|---|
host | ClickHouse server hostname | localhost |
port | HTTP interface port | 8123 |
database | Target database for state | (Required) |
user | ClickHouse username | (Required) |
schema | ClickHouse database used for storage | meltano |
table | Table name for state storage | state |
Sources: pyproject.toml:34-42, src/meltano_state_backend_clickhouse/backend.py:70-112, src/meltano_state_backend_clickhouse/backend.py:157-160
Architecture and Data Flow
The backend is implemented in the ClickhouseStateStoreManager class. Because ClickHouse is an OLAP store, it does not support traditional row-level updates or primary key constraints.
State Persistence Logic
State is managed using a ReplacingMergeTree engine. This allows the backend to handle "updates" by inserting new rows with a newer timestamp (updated_at).
The use of the FINAL modifier during selection ensures that only the latest state version for a given state_id is returned.
Sources: src/meltano_state_backend_clickhouse/backend.py:192-205, src/meltano_state_backend_clickhouse/backend.py:220-235, README.md:38-40
Lock Management
Since ClickHouse lacks native row-level locking or unique constraints, the backend implements a best-effort advisory lock.
- Stale Cleanup: Removes locks older than 5 minutes (
STALE_LOCK_SECONDS). - Insert Candidate: Inserts a row with a unique
lock_id. - Settle: Waits for 1 second (
LOCK_SETTLE_SECONDS) to allow network/scheduling jitter to clear. - Verify: Queries all candidates for the
state_id. The requester wins only if itslock_idis the lexicographical minimum.
Data Schema
The backend automatically creates two tables within the specified schema:
State Table (meltano.state)
| Column | Type | Description |
|---|---|---|
state_id | String | Unique identifier for the pipeline state |
partial_state | Nullable(String) | JSON string of partial state |
completed_state | Nullable(String) | JSON string of completed state |
updated_at | DateTime64(3) | Versioning key for ReplacingMergeTree |
Lock Table (meltano.state_lock)
| Column | Type | Description |
|---|---|---|
state_id | String | ID being locked |
lock_id | String | UUID of the lock requester |
locked_at | DateTime | Timestamp of lock acquisition |
Sources: src/meltano_state_backend_clickhouse/backend.py:175-208
Usage Summary
The ClickhouseStateStoreManager integrates with Meltano's internal state management. Developers can interact with it through the standard StateStoreManager API, which includes methods like get(), set(), delete(), and acquire_lock().
# Example initialization from backend.py logic
from meltano_state_backend_clickhouse.backend import ClickhouseStateStoreManager
manager = ClickhouseStateStoreManager(uri="clickhouse://default@localhost:8123/meltano")
with manager.acquire_lock("my-pipeline"):
state = manager.get("my-pipeline")
# ... update state ...
manager.set(state)
Sources: src/meltano_state_backend_clickhouse/backend.py:141-168, tests/test_backend.py:75-85