Ecto.Adapters.ClickHouse (clickhouse_adapter_ecto v0.2.0)

Copy Markdown

An Ecto.Adapters.SQL-based adapter for ClickHouse, talking to it over its native TCP protocol via ChDriver.

Usage

Point a repo's adapter at this module and configure it like any other Ecto.Repo:

defmodule MyApp.Repo do
  use Ecto.Repo, otp_app: :my_app, adapter: Ecto.Adapters.ClickHouse
end

config :my_app, MyApp.Repo,
  hostname: "localhost",
  port: 9000,
  database: "default",
  username: "default",
  password: ""

Migrations

mix ecto.gen.migration and mix ecto.migrate work as usual. create table(...) and drop table(...) with plain :add columns are supported; ClickHouse has no transactional DDL, so supports_ddl_transaction?/0 returns false. See Ecto.Adapters.ClickHouse.DDL for the full picture of what's supported, including :alter, indexes, and constraints (none of which are, yet).

ClickHouse's ORDER BY/PRIMARY KEY is not a Postgres-style primary key -- it's a sort/skip index for scans, never enforced as unique, and ClickHouse has no autoincrement. Ecto's default add :id, :bigserial, primary_key: true still produces valid DDL, but leaving :id out of an insert (the usual "let the database generate it" pattern) silently writes 0 for every row instead of a fresh value. Give tables an explicit id and sort key instead:

create table(:events, primary_key: false, options: "ENGINE = MergeTree ORDER BY id") do
  add(:id, :uuid, primary_key: true)
  add(:name, :string)
  add(:occurred_at, :utc_datetime)
end

paired with a schema that supplies :id itself:

@primary_key false
schema "events" do
  field(:id, :string)
  field(:name, :string)
  field(:occurred_at, :utc_datetime)
end

Repo.insert!(%Event{id: Ecto.UUID.generate(), name: "signup", occurred_at: DateTime.utc_now(:second)})

The :id field is a plain :string, not Ecto.UUID -- ClickHouse's UUID column round-trips as a hyphenated text string, while Ecto.UUID's dump/1 produces a raw binary ClickHouse rejects. Ecto.UUID.generate/0 still works fine for generating the value itself.

Querying

import Ecto.Query

MyApp.Repo.all(from e in Event, where: e.name == "signup", order_by: e.occurred_at)

What's not supported

  • Repo.update!/1 and Repo.delete!/1 -- ClickHouse mutates existing data via the asynchronous ALTER TABLE ... UPDATE/DELETE statements, not synchronous SQL. Issue those directly as raw queries if you need them.
  • :on_conflict (upserts) and :returning on insert -- ClickHouse's INSERT has neither. Use ReplacingMergeTree/CollapsingMergeTree table engines for upsert-like behavior instead.
  • Real transactions (Repo.transaction/2), and by extension Repo.stream/2 -- ClickHouse's native protocol as used here has no session transaction support. See Ecto.Adapters.ClickHouse.Connection's stream/4 for a workaround.
  • Joins, LIMIT, and OFFSET in delete_all/2 -- only a plain WHERE against a single table.
  • :alter migrations (add/remove/modify column), indexes, and constraints.
  • DISTINCT, window functions, and set operations in queries.

See Ecto.Adapters.ClickHouse.Connection, .DDL, and .Expression for exactly which query and DDL shapes are supported.