ClickHouse + ActiveRecord


Why?

Few monts ago I’ve posted article about ClickHouse, and how we can use it with Rails (gem https://github.com/archan937/clickhouse).

But what if we need simple AR models and deal with ClickHouse data within them?

Answer is here: https://github.com/PNixx/clickhouse-activerecord gem!

Gem configuration

Just install gem:

gem 'clickhouse-activerecord'

Database.yml configuration (Rails6 with second database)

Nothing special, just a few lines!

development:
  primary:
    adapter: postgresql
    encoding: unicode
    pool: 5
    username:
    password:
    host: localhost
    database: mailer
    port: 5432
  clickhouse:
    adapter: clickhouse
    database: events
    host: XXX
    port: 8123
    username: default
    password: XXX
    debug: true

Model configuration

We can use same AR classes with simple database configuration!

class MailLog < ActiveRecord::Base
  connects_to database: { writing: :clickhouse, reading: :clickhouse }
  scope :delivered, -> { where(status: 'delivered') }
  scope :opened, -> { where(status: 'opened') }
  scope :clicked, -> { where(status: 'clicked') }
end

Queries!

Let’s try something awesome!

What about grouping by date with on-the-fly transformation DateTime value into Date (with toDate ClickHouse function)?

[54] pry(main)> MailLog.group(:status).group('toDate(created_at)').count
  Clickhouse  (52.8ms)  SELECT COUNT(*) AS count_all, mail_logs.status AS mail_logs_status, toDate(created_at) AS todate_created_at FROM mail_logs GROUP BY mail_logs.status, toDate(created_at)
=> {["delivered", "2020-11-15"]=>2,
 ["clicked", "2020-11-08"]=>25071,
 ["delivered", "2020-11-12"]=>33,
 ["unsubscribed", "2020-11-08"]=>24970,
 ["opened", "2020-11-08"]=>24878,
 ["delivered", "2020-11-14"]=>2578,
 ["unsubscribed", "2020-11-12"]=>15,
 ["opened", "2020-11-14"]=>2487,
 ["opened", "2020-11-15"]=>4,
 ["delivered", "2020-11-08"]=>25081,
 ["unsubscribed", "2020-11-14"]=>2647,
 ["clicked", "2020-11-15"]=>2,
 ["clicked", "2020-11-14"]=>2488,
 ["clicked", "2020-11-12"]=>23,
 ["opened", "2020-11-12"]=>29}

Enjoy!