Environment Variable Configuration

Relevant source files

The following files were used as context for generating this wiki page:

• README.md
• pyproject.toml
• src/meltano_state_backend_clickhouse/backend.py
• src/meltano_state_backend_clickhouse/init.py
• tests/test_backend.py

Environment variable configuration in meltano-state-backend-clickhouse allows users to define how Meltano connects to a ClickHouse instance to persist pipeline state. The configuration supports both a unified URI-based approach and granular, individual setting overrides.

The backend uses these variables to initialize a ClickhouseStateStoreManager, which manages the state table (storing incremental bookmarks) and a lock table (managing advisory locks). By default, the state is stored in the meltano.state table within ClickHouse.

Sources: README.md:1-24, src/meltano_state_backend_clickhouse/backend.py:80-136

Core Connection Settings

Connection settings are primarily defined through the MELTANO_STATE_BACKEND_URI. This URI follows the format clickhouse://user:password@host:port/database. Individual components of the connection can be overridden using specific environment variables or meltano.yml settings.

Configuration Hierarchy

The following diagram illustrates how connection parameters are derived from the URI and optional overrides.

The system first parses the URI and then applies any explicit overrides provided via settings.

Sources: src/meltano_state_backend_clickhouse/backend.py:141-197, README.md:23-28

Connection Parameter Summary

Setting Name	Environment Variable	Description	Default
host	MELTANO_STATE_BACKEND_CLICKHOUSE_HOST	ClickHouse server hostname	localhost
port	MELTANO_STATE_BACKEND_CLICKHOUSE_PORT	HTTP interface port	8123
database	MELTANO_STATE_BACKEND_CLICKHOUSE_DATABASE	ClickHouse database name	N/A (Required)
user	MELTANO_STATE_BACKEND_CLICKHOUSE_USER	ClickHouse username	N/A (Required)
password	MELTANO_STATE_BACKEND_CLICKHOUSE_PASSWORD	ClickHouse password	""
secure	MELTANO_STATE_BACKEND_CLICKHOUSE_SECURE	Use HTTPS/TLS	False

Sources: src/meltano_state_backend_clickhouse/backend.py:80-136, src/meltano_state_backend_clickhouse/backend.py:192-195, pyproject.toml:31-38

Storage and Table Settings

Beyond connectivity, environment variables control where within ClickHouse the state data is physically persisted. This is governed by two primary settings: schema and table.

• Schema: Defined by state_backend.clickhouse.schema. It defaults to meltano. In ClickHouse, this refers to the database name where the tables reside.
• Table: Defined by state_backend.clickhouse.table. It defaults to state.

The backend automatically creates two tables based on these settings:

1. State Table: <schema>.<table> (e.g., meltano.state).
2. Lock Table: <schema>.<table>_lock (e.g., meltano.state_lock).

Sources: src/meltano_state_backend_clickhouse/backend.py:61-62, src/meltano_state_backend_clickhouse/backend.py:228-232, README.md:30-30

Table Schema and Data Model

The state storage relies on the ReplacingMergeTree engine. This engine is critical because ClickHouse does not support standard row-level updates. Instead, the updated_at column acts as a version, and the state_id acts as the sorting key.

The LOCK_TABLE includes a TTL (Time To Live) configured by STALE_LOCK_SECONDS (defaulting to 300 seconds or 5 minutes) to ensure abandoned locks are reaped.

Sources: src/meltano_state_backend_clickhouse/backend.py:64-66, src/meltano_state_backend_clickhouse/backend.py:246-267

Implementation in Code

The mapping between environment variables and the backend logic is defined via Meltano SettingDefinition objects and entry points in pyproject.toml.

Setting Definitions

CLICKHOUSE_HOST = SettingDefinition(
    name="state_backend.clickhouse.host",
    label="ClickHouse Host",
    description="ClickHouse server hostname",
    kind=SettingKind.STRING,
    env_specific=True,
)

Sources: src/meltano_state_backend_clickhouse/backend.py:80-86

Entry Points mapping

In pyproject.toml, these settings are registered so Meltano can discover them:

[project.entry-points."meltano.settings"]
host = "meltano_state_backend_clickhouse.backend:CLICKHOUSE_HOST"
port = "meltano_state_backend_clickhouse.backend:CLICKHOUSE_PORT"
database = "meltano_state_backend_clickhouse.backend:CLICKHOUSE_DATABASE"
# ... other settings

Sources: pyproject.toml:31-40

Summary

The ClickHouse state backend for Meltano utilizes environment variables primarily to establish secure, authenticated connections to ClickHouse clusters and to define the namespace for state persistence. By utilizing ReplacingMergeTree and specific settings like secure and database, it provides a robust alternative to traditional transactional databases for Meltano state management.