Ecto.Adapters.ClickHouse (clickhouse_adapter_ecto v0.3.3)

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: ""

# So the mix ecto.* tasks know which repo to act on.
config :my_app, ecto_repos: [MyApp.Repo]

Then start the repo under your application's supervision tree, which is what actually starts the connection pool:

# lib/my_app/application.ex
def start(_type, _args) do
  children = [
    MyApp.Repo
  ]

  Supervisor.start_link(children, strategy: :one_for_one, name: MyApp.Supervisor)
end

Without it, every query fails with could not lookup Ecto repo MyApp.Repo because it was not started or it does not exist.

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.