- Notifications
You must be signed in to change notification settings - Fork31
Elixir library client for work with ClickHouse
balance-platform/pillar
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
Elixir client forClickHouse, a fast open-sourceOnline Analytical Processing (OLAP) database management system.
- Direct Usage with connection structure
- Pool of workers
- Async insert
- Buffer for periodical bulk inserts
- Migrations
- DateTime Timezones
- Switching between HTTP adapters
conn=Pillar.Connection.new("http://user:password@localhost:8123/database")# Params are passed in brackets {} in SQL query, and map strtucture does fill# query by values.sql="SELECT count(*) FROM users WHERE lastname = {lastname}"params=%{lastname:"Smith"}{:ok,result}=Pillar.query(conn,sql,params)result#=> [%{"count(*)" => 347}]
Recommended usage, because of limited connections and supervised workers.
defmoduleClickhouseMasterdousePillar,connection_strings:["http://user:password@host-master-1:8123/database","http://user:password@host-master-2:8123/database"],name:__MODULE__,pool_size:15endClickhouseMaster.start_link(){:ok,result}=ClickhouseMaster.select(sql,%{param:value})
connection=Pillar.Connection.new("http://user:password@host-master-1:8123/database")Pillar.async_insert(connection,"INSERT INTO events (user_id, event) SELECT {user_id}, {event}",%{user_id:user.id,event:"password_changed"})# => :ok
For this feature requiredPool of workers.
defmoduleBulkToLogsdousePillar.BulkInsertBuffer,pool:ClickhouseMaster,table_name:"logs",# interval_between_inserts_in_seconds, by default -> 5interval_between_inserts_in_seconds:5,# on_errors is optionalon_errors:&__MODULE__.dump_to_file/2@doc""" dump to file function store failed inserts into file """defdump_to_file(_result,records)doFile.write("bad_inserts/#{DateTime.utc_now()}",inspect(records))end@doc""" retry insert is dangerous (but it is possible and listed as proof of concept) this function may be used in `on_errors` option """defretry_insert(_result,records)do__MODULE__.insert(records)endend
:ok=BulkToLogs.insert(%{value:"online",count:133,datetime:DateTime.utc_now()}):ok=BulkToLogs.insert(%{value:"online",count:134,datetime:DateTime.utc_now()}):ok=BulkToLogs.insert(%{value:"online",count:132,datetime:DateTime.utc_now()})....# All this records will be inserted with 5 second interval.
on_errors parameter allows you to catch any error of bulk insert (for example: one of batch is bad or clickhouse was not available )
Migrations can be generated with mix taskmix pillar.gen.migration migration_name.
Multi-statement migration Example for thisUseCase
defmodulePillar.Migrations.CreateMultipleTablesdodefupdo# for MultiStatement migration result of this function should be List of Strings(0..4)|>Enum.map(fni->"CREATE TABLE IF NOT EXISTS shard_#{i} (field FixedString(10)) ENGINE = Memory"end)endend
mix pillar.gen.migration events_table
But for launching them we have to write own task, like this:
defmoduleMix.Tasks.MigrateClickhousedouseMix.Taskdefrun(_args)doconnection_string=Application.get_env(:my_project,:clickhouse_url)conn=Pillar.Connection.new(connection_string)Pillar.Migrations.migrate(conn)endend
And launch this via command.
mix migrate_clickhouse
In order to be able to use Timezones add timezones database to your project and configure your app:
config:elixir,:time_zone_database,Tzdata.TimeZoneDatabase
Details herehttps://hexdocs.pm/elixir/1.12/DateTime.html#module-time-zone-database
If you have problems with default Pillar HTTP Adapter (Mint over Tesla), you can use alternative one, based on :httpc or define your own and pass itthrough config.
config :pillar, Pillar.HttpClient, http_adapter: Pillar.HttpClient.TeslaMintAdapterAdapter should define one functionpost/3 and return 2 possible results (%Pillar.HttpClient.Response{},%Pillar.HttpClient.TransportError{})
Feel free to make a pull request. All contributions are appreciated!
About
Elixir library client for work with ClickHouse