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!