Ecto.Adapters.ClickHouse.DDL (clickhouse_adapter_ecto v0.2.0)

Copy Markdown

DDL generation for Ecto.Migration support.

Only CREATE TABLE [IF NOT EXISTS] (from Ecto.Migration.Table plus a column list of plain {:add, name, type, opts} commands) and DROP TABLE [IF EXISTS] are implemented. That's enough for Ecto.Migrator to create and manage the schema_migrations table, and for a migration's own create table(...)/drop table(...) to work for simple, single-statement tables.

:alter (adding/removing/modifying columns on an existing table), indexes, and constraints are not implemented -- ClickHouse has no unique/foreign-key constraints at all, and its ALTER TABLE semantics don't map cleanly onto Ecto.Migration.Table's :alter subcommands. Use a raw SQL string via execute/1 for anything beyond a one-shot CREATE/DROP TABLE.

change/0 auto-reversal

Ecto synthesizes a migration's down direction from change/0. That's only safe for synchronous, metadata-only operations:

  • create table(...) / drop table(...) -- implemented, safe.
  • add :col, :type / remove :col -- conceptually safe (ClickHouse's ADD COLUMN/DROP COLUMN are synchronous metadata changes), but not yet implemented here (:alter is rejected below). Use raw execute/1 SQL for both directions until it lands.

Write explicit up/0 + down/0 instead of change/0 for anything that triggers an asynchronous mutation or rewrites existing data -- there is no safe way to auto-generate the reverse for:

  • ALTER TABLE ... MODIFY ORDER BY / partition key changes.
  • ALTER TABLE ... MODIFY COLUMN <type> (a real type change).
  • Any UPDATE/DELETE-shaped mutation on existing rows.

execute_ddl/1 raises with an explanation if you try to :modify a column's type, rather than falling through to a generic "not implemented" error.

Things with no migration DSL -- use raw SQL

A few ClickHouse features have no Ecto.Migration equivalent, so this adapter doesn't invent one for them. Use execute/1 directly, as explicit up/0 + down/0 (none of this is auto-reversible):

  • ORDER BY/partition key changes -- ALTER TABLE ... MODIFY ORDER BY new_expr. Note that down/0 can't actually restore the old physical layout; at best it can issue another MODIFY ORDER BY back to the old expression, which affects future merges but not ones that already happened under the new key.

  • Data-skipping indices (minmax, set, bloom_filter, ngrambf_v1, tokenbf_v1, ...):

    execute("ALTER TABLE events ADD INDEX amount_minmax_idx amount TYPE minmax GRANULARITY 4")
execute("ALTER TABLE events DROP INDEX amount_minmax_idx")

    Adding an index is metadata-only and only covers parts written afterward. Run ALTER TABLE ... MATERIALIZE INDEX name in the same migration if it needs to cover existing data immediately.

  • Projections (ALTER TABLE ... ADD PROJECTION) -- out of scope entirely; not supported through any mechanism here besides raw SQL.

  • Kafka-engine ingestion pipelines (source table + target table + materialized view):

    execute("""
CREATE TABLE events (id UInt64, payload String)
ENGINE = MergeTree ORDER BY id
""")

execute("""
CREATE TABLE events_queue (id UInt64, payload String)
ENGINE = Kafka
SETTINGS kafka_broker_list = 'kafka:9092',
         kafka_topic_list = 'events',
         kafka_group_name = 'events_consumer',
         kafka_format = 'JSONEachRow'
""")

execute("""
CREATE MATERIALIZED VIEW events_mv TO events AS
SELECT id, payload FROM events_queue
""")

    Hand-quoting that SETTINGS clause is tedious and error-prone once it has several key/value pairs -- build it with Ecto.Adapters.ClickHouse.Migration.table_options/1 instead, which also supports pulling values like kafka_broker_list from the environment at migration-run time via {:system, "ENV_VAR"} rather than committing them as a literal string:

    execute("""
CREATE TABLE events_queue (id UInt64, payload String)
#{Ecto.Adapters.ClickHouse.Migration.table_options(
  engine: "Kafka",
  settings: [
    kafka_broker_list: {:system, "KAFKA_BROKER_LIST"},
    kafka_topic_list: "events",
    kafka_group_name: "events_consumer",
    kafka_format: "JSONEachRow"
  ]
)}
""")

    Only the explicit ... TO target_table AS SELECT ... view form is supported; the implicit-target-table form (ENGINE = ... AS SELECT ...) creates a hidden backing table with a mangled name down/0 can't address, so avoid it here. Create in this order: target table, Kafka source table, materialized view. Tear down in reverse: view, then Kafka table, then target table -- dropping the target table while the view is still live leaves ingestion silently stalled with no error surfaced anywhere.

ClickHouse-specific column types

FixedString(N) and LowCardinality(T) have no built-in Ecto migration type, so a raw column type reaches column_type!/1 via the quoted-atom escape hatch (add(:col, :"FixedString(16)")) -- Ecto.Migration.add/3 rejects a real Ecto.Type/Ecto.ParameterizedType module outright, so the quoted atom is the only spelling available. Ecto.Adapters.ClickHouse.Migration provides validated builders instead of hand-typing that string:

add(:code, Ecto.Adapters.ClickHouse.Migration.fixed_string(16))
add(:status, Ecto.Adapters.ClickHouse.Migration.low_cardinality(:string))

FixedString(N) also has a schema-side Ecto.ParameterizedType:

field :code, Ecto.Adapters.ClickHouse.Types.FixedString, size: 16

Map(K, V) has no builder -- use the quoted atom directly:

add(:m, :"Map(String, UInt32)")

Every table needs an ENGINE. Pass one explicitly via options: on table/2 (e.g. options: "ENGINE = MergeTree ORDER BY id"); without it, this defaults to ENGINE = MergeTree ORDER BY (<primary key columns>) (or ORDER BY tuple() with none). That default is fine for schema_migrations and quick dev tables, but MergeTree's sort key is a real modeling decision for anything performance-sensitive -- pick it explicitly once that matters.

ORDER BY/PRIMARY KEY is not a Postgres primary key

See Ecto.Adapters.ClickHouse's moduledoc for the short version. In short: ClickHouse never enforces uniqueness on ORDER BY/PRIMARY KEY, and there's no autoincrement, so relying on Ecto's default autogenerated :id silently writes 0 for every row instead of raising. Use primary_key: false with an explicit, application-supplied id, and remember ClickHouse won't reject a duplicate on its own -- deduplication has to be handled at the application level or via a ClickHouse-native mechanism like ReplacingMergeTree if you need it.