Skip to content

plausible/ecto_ch

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

Ecto ClickHouse Adapter

Hex Package Hex Docs

Uses Ch as driver.

Installation

defp deps do
  [
    {:ecto_ch, "~> 0.4.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

ClickHouse doesn't support UPDATE statements as of now, so Repo.update/2 and Repo.update_all/3 raise when called. But Repo.alter_update_all/3 -- which executes ALTER TABLE ... UPDATE -- can be used instead.

Note that ALTER TABLE ... UPDATE is considered an admin operation and comes with a performance cost. Please read https://clickhouse.com/blog/handling-updates-and-deletes-in-clickhouse for more information.

For examples, please see clickhouse_alter_update_test.exs.

For 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.