Uses Ch as driver.
defp deps do [ {:ecto_ch, "~> 0.11.0"} ] end
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()"]
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.
ClickHouse String columns store arbitrary bytes. For UTF-8 text, define a custom Ecto.Type (Elixir 1.16+):
defmodule MyApp.UTF8String do use Ecto.Type @impl true def type, do: :string @impl true def cast(value), do: Ecto.Type.cast(:string, value) @impl true def dump(value), do: Ecto.Type.dump(:string, value) @impl true def load(value) when is_binary(value), do: {:ok, String.replace_invalid(value)} def load(_value), do: :error end
Use MyApp.UTF8String for text and :binary for payloads:
defmodule MyApp.Event do use Ecto.Schema @primary_key false schema "events" do field :message, MyApp.UTF8String field :payload, :binary end end
Ch 0.9.x sanitizes all String columns, including :binary fields. This behavior will be removed in Ch 1.0.
See the UTF-8 round-trip test.
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 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])
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()
Column options like :default, :null, and :comment are supported as well, and work in create, add, and modify:
create table(:posts, primary_key: false, engine: "MergeTree", options: [order_by: "tuple()"]) do add :user_id, :UInt64, comment: "ClickPipes source" end
is equivalent to
CREATE TABLE `posts` ( `user_id` UInt64 COMMENT 'ClickPipes source' ) ENGINE MergeTree ORDER BY tuple()
Starting with ClickHouse 26.8, the X-ClickHouse-Format request header takes precedence over a FORMAT clause in a SELECT query. Since Ch always sends this header, use the :format query option to request a custom response format instead of including FORMAT in the SQL:
Repo.query!("SELECT * FROM events", [], format: "JSONCompact")
This does not affect INSERT ... FORMAT ... input formats.
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 alter_update_test.exs.
For ARRAY JOIN examples and other ClickHouse-specific JOIN types please see joins_test.exs.
DEFAULT expressions on columns are ignored when inserting RowBinary.