Packages
ecto_ch
0.3.2
0.10.0
0.9.4
0.9.3
0.9.2
0.9.1
0.9.0
0.8.9
0.8.8
0.8.7
0.8.6
0.8.5
retired
0.8.4
0.8.3
0.8.2
0.8.1
0.8.0
0.7.5
0.7.4
0.7.3
0.7.2
0.7.1
0.7.0
0.6.6
0.6.5
0.6.4
0.6.3
0.6.2
retired
0.6.1
retired
0.6.0
retired
0.5.1
0.5.0
retired
0.4.1
0.4.0
retired
0.3.10
0.3.9
0.3.8
0.3.7
0.3.6
0.3.5
0.3.4
0.3.3
0.3.2
0.3.1
0.3.0
0.2.2
0.2.1
0.2.0
0.1.11
0.1.10
0.1.9
0.1.8
0.1.7
0.1.6
0.1.5
0.1.4
0.1.3
0.1.2
0.1.1
0.1.0
ClickHouse adapter for Ecto
Current section
Files
Jump to
Current section
Files
README.md
# 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, 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, r}
```
Both of these queries are equivalent to:
```sql
SELECT a0."s", a1 FROM "arrays_test" AS a0 ARRAY JOIN "arr" AS a1
```
#### 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)