Clickhouse: How to create a distributed table

Uncategorized

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 name
events – previously created replicated table
rand() – 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