# Ecto ClickHouse Adapter
[](https://hex.pm/packages/ecto_ch)
[](https://hexdocs.pm/ecto_ch)
Uses [Ch](https://github.com/plausible/ch) as driver.
## Installation
```elixir
defp deps do
[
{:ecto_ch, "~> 0.3.0"}
]
end
```
## Usage
In your `config/config.exs`
```elixir
config :my_app, ecto_repos: [MyApp.Repo]
config :my_app, MyApp.Repo, url: "http://username:password@localhost:8123/database"
```
In your application code
```elixir
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
```elixir
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:
```elixir
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`](https://hexdocs.pm/ecto_ch/Mix.Tasks.Ecto.Ch.Schema.html) 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:
```elixir
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,](https://clickhouse.com/docs/en/optimize/asynchronous-inserts) lightweight [deletes,](https://clickhouse.com/docs/en/guides/developer/lightweght-delete) global [FINAL](https://clickhouse.com/docs/en/operations/settings/settings#final) modifier, and [more:](https://clickhouse.com/docs/en/operations/settings/settings)
```elixir
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`
```elixir
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
```sql
CREATE TABLE `posts` ON CLUSTER `my-cluster` (
`message` String,
`user_id` UInt64
) ENGINE ReplicatedMergeTree ORDER BY tuple()
```
## Caveats
#### [ARRAY JOIN](https://clickhouse.com/docs/en/sql-reference/statements/select/array-join)
Since [`v3.10.2`](https://github.com/elixir-ecto/ecto/blob/40133ace8c71f1f81432858e71d3d73527f85107/CHANGELOG.md?plain=1#L10) Ecto supports `:array` and `:left_array` join types:
```elixir
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:
```elixir
from a in "arrays_test", inner_lateral_join: r in "arr", select: {a.s, fragment("?", r)}
```
Both of these queries are equivalent to:
```sql
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.](./test/ecto/integration/clickhouse_joins_test.exs)
#### NULL
`DEFAULT` expressions on columns are ignored when inserting RowBinary.
[See Ch for more details and an example.](https://github.com/plausible/ch#null-in-rowbinary)
## Benchmarks
[See Ch for benchmarks.](https://github.com/plausible/ch#benchmarks)
