View Source Ecto ClickHouse Adapter

Uses Ch as driver.

Installation

defp deps do
  [
    {:ecto_ch, "~> 0.3.0"}
  ]
end

Usage

In your config/config.exs

config :my_app, ecto_repos: [MyApp.Repo]
config :my_app, MyApp.Repo, url: "http://username:password@localhost:8123/database"

In your application code

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

Optionally you can also set the default table engine and options to use in migrations

config :ecto_ch,
  default_table_engine: "TinyLog",
  default_table_options: [cluster: "little-giant", order_by: "tuple()"]

Ecto schemas

For automatic RowBinary encoding please use the custom Ch Ecto type:

defmodule MyApp.Example do
  use Ecto.Schema

  @primary_key false
  schema "example" do
    field :number, Ch, type: "UInt32"
    field :name, Ch, type: "String"
    field :maybe_name, Ch, type: "Nullable(String)"
    field :country_code, Ch, type: "FixedString(2)"
    field :price, Ch, type: "Decimal32(2)"
    field :map, Ch, type: "Map(String, UInt64)"
    field :ipv4, Ch, type: "IPv4"
    field :ipv4s, {:array, Ch}, type: "IPv4"
    field :enum, Ch, type: "Enum8('hello' = 1, 'world' = 2)"
    # etc.
  end
end

MyApp.Repo.insert_all(MyApp.Example, rows)

Some Ecto types like :string, :date, and Ecto.UUID would also work. Others like :decimal, :integer are ambiguous and should not be used.

ecto.ch.schema mix task can be used to generate a schema from an existing ClickHouse table.

Schemaless inserts

For schemaless inserts :types option with a mapping of field->type needs to be provided:

types = [
  number: "UInt32",
  # or `number: :u32`
  # or `number: Ch.Types.u32()`
  # etc.
]

MyApp.Repo.insert_all("example", rows, types: types)

Settings

:settings option can be used to enable asynchronous inserts, lightweight deletes, global FINAL modifier, and more:

MyApp.Repo.insert_all(MyApp.Example, rows, settings: [async_insert: 1])
MyApp.Repo.delete_all("example", settings: [allow_experimental_lightweight_delete: 1])
MyApp.Repo.all(MyApp.AggregatedExample, settings: [final: 1])

Migrations

ClickHouse-specific options can be passed into table.options and index.options

table_options = [cluster: "my-cluster"]
engine_options = [order_by: "tuple()"]
options = table_options ++ engine_options

create table(:posts, primary_key: false, engine: "ReplicatedMergeTree", options: options) do
  add :message, :string
  add :user_id, :UInt64
end

is equivalent to

CREATE TABLE `posts` ON CLUSTER `my-cluster` (
  `message` String,
  `user_id` UInt64
) ENGINE ReplicatedMergeTree ORDER BY tuple()

Caveats

ARRAY JOIN

Since v3.10.2 Ecto supports :array and :left_array join types:

from a in "arrays_test", array_join: r in "arr", select: {a.s, fragment("?", r)}

For an earlier Ecto version :inner_lateral and :left_lateral join types can be used instead:

from a in "arrays_test", inner_lateral_join: r in "arr", select: {a.s, fragment("?", r)}

Both of these queries are equivalent to:

SELECT a0."s", a1 FROM "arrays_test" AS a0 ARRAY JOIN "arr" AS a1

For more ARRAY JOIN examples and other ClickHouse-specific JOIN types please see clickhouse_joins_test.exs.

NULL

DEFAULT expressions on columns are ignored when inserting RowBinary.

See Ch for more details and an example.

Benchmarks

See Ch for benchmarks.