Clickhouse: How to create a distributed table
At first, we should create replicated tables on all nodes in a cluster
CREATE TABLE IF NOT EXISTS ex_test.events ON CLUSTER clickhouse_partner (date Date,time DateTime,event String,client String,value UInt32) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/test_show_click_statistic_repl','{replica}', date, (event, client), 8192)
Then needs to create a distributed table which should include previously created the replicated table
CREATE TABLE IF NOT EXISTS ex_test.events ON CLUSTER clickhouse_partner (date Date,time DateTime,event String,client String,value UInt32) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/test_show_click_statistic_repl','{replica}', date, (event, client), 8192)
where is: Distributed(clickhouse_partner, ex_test, events,rand()) :
clickhouse_partner
– cluster nameex_test
– database nameevents
– previously created replicated tablerand()
– key
Then we can insert data into this distributed table on the second shard:
CREATE TABLE IF NOT EXISTS ex_test.events ON CLUSTER clickhouse_partner (date Date,time DateTime,event String,client String,value UInt32) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/test_show_click_statistic_repl','{replica}', date, (event, client), 8192)
And check data on the first shard:
SELECT * FROM ex_test.events
we should get something like this:
┌───────date─┬────────────────time─┬─event─┬─client─┬─value─┐│ 2019-05-10 │ 2019-05-10 12:35:33 │ test │ Den │ 2 │└────────────┴─────────────────────┴───────┴────────┴───────┘
Resume
We should write data into a distributed table. In our test it is ex_test.events_distributed_x4
And we can read from ex_test.events_distributed_x4
(it is preferred) or ex_test.events