Skip to content

clickhouse-elixir/clickhouse_ecto

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

78 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

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.