plausible / clickhouse_ecto

Ecto adapter for ClickHouse database

ClickhouseEcto

Ecto adapter for ClickHouse database using clickhousex driver.

Installation

The package can be installed by adding clickhouse_ecto to your list of dependencies in mix.exs:

def deps do
  [
    {:clickhouse_ecto, "~> 0.2.8"}
  ]
end

Configuration

Add configuration for your repo like this:

config :example_app, ExampleApp.ClickHouseRepo,
       adapter: ClickhouseEcto,
       loggers: [Ecto.LogEntry],
       hostname: "localhost",
       port: 8123,
       database: "example_app",
       username: "user",
       password: "654321",
       timeout: 60_000,
       pool_timeout: 60_000,
       ownership_timeout: 60_000,
       pool_size: 30

Examples

Example of Ecto model:

defmodule ExampleApp.Click do
  use ExampleApp.Web, :model

  @primary_key {:date, :date, []}
  @timestamps_opts updated_at: false

  schema "clicks" do
    field :site_id, :integer
    field :source, :string
    field :ip, :string
    field :score, :decimal
    field :width, :integer
    field :height, :integer

    timestamps()
  end

  @doc """
  Builds a changeset based on the `struct` and `params`.
  """
  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, [:site_id, :source, :ip, :points, :width, :height, :date])
    |> validate_required([:date, :site_id])
  end
end

Due to ClickHouse does not support data update and uniq rows identifiers, do not forget to set primary key field and turn off updated_at timestamp updating:

  @primary_key {:date, :date, []}
  @timestamps_opts updated_at: false

Example of data migrations:

defmodule ExampleApp.Repo.Migrations.CreateClick do
  use Ecto.Migration

  def change do
    create_if_not_exists table(:clicks, engine: "MergeTree(date,(date,inserted_at,source,site_id,ip,score,width,height),8192)") do
      add :site_id, :integer, default: 0
      add :source, :string, default: ""
      add :ip, :string, default: ""
      add :score, :float, default: 0.0
      add :width, :integer
      add :height, :integer

      add :date, :date, default: :today
      timestamps(updated_at: false)
    end
  end
end

defmodule ExampleApp.Repo.Migrations.AddUserAgentToClicks do
  use Ecto.Migration

  def change do
    alter table(:clicks) do
      add :user_agent, :string
    end
  end
end

Queries examples:

iex(1)> ExampleApp.Repo.insert %ExampleApp.Click{site_id: 1, date: Ecto.Date.utc, score: 1.1}
[debug] QUERY OK db=7.8ms
INSERT INTO "clicks" ("date","score","site_id","inserted_at") VALUES (?,?,?,?) [{2018, 4, 5}, 1.1, 1, {{2018, 4, 5}, {8, 18, 30, 727360}}]
{:ok,
 %ExampleApp.Click{__meta__: #Ecto.Schema.Metadata<:loaded, "clicks">,
  date: #Ecto.Date<2018-04-05>, height: nil,
  inserted_at: ~N[2018-04-05 08:18:30.727360], ip: nil, score: 1.1, site_id: 1,
  source: nil, width: nil}}
iex(2)> ExampleApp.Repo.all ExampleApp.Click
[debug] QUERY OK source="clicks" db=11.8ms
SELECT c0."date", c0."site_id", c0."source", c0."ip", c0."score", c0."width", c0."height", c0."inserted_at" FROM "clicks" AS c0 []
[%ExampleApp.Click{__meta__: #Ecto.Schema.Metadata<:loaded, "clicks">,
  date: ~D[2018-04-05], height: 0, inserted_at: ~N[2018-04-05 09:15:42.000000],
  ip: "", score: #Decimal<1.1>, site_id: 1, source: "", width: 0}]

Documentation can be generated with ExDoc and published on HexDocs. Once published, the docs can be found at https://hexdocs.pm/clickhouse_ecto.