custom partition in clickhouse

1
votes

I have several questions about custom partitioning in clickhouse. Background: i am trying to build a TSDB on top of clickhouse. We need to support very large batch write and complicated OLAP read.

  1. Let's assume we use the standard partition by month , and we have 20 nodes in our clickhouse cluster. I am wondering will the data from same month all flow to the same node or will clickhouse do some internal balance and put the data from same month to several nodes?

  2. If all the data from same month write to the same node, then it will be very bad for our scenario. I will probably consider patition by （timestamp, tags）where tags are the different tags that define the data source. Our monitoring system will write data to TSDB every 30 seconds. Our read pattern is usually single table range scan or several tables join on a column. Any advice on how should i customize my partition strategy?

  3. Since clickhouse does not support secondary index, and we will run selection query on columns, i think i should put those important columns into the primary key, so my primary key will probably be like (timestamp, ip, port...), any advice on this design or make give a good reason why clickhouse does not support secondary index like bitmap index on other non-primary column?

1
olapopentsdbclickhouse
anyone give a hint here about click-house sharding? - BYsBro
Data location in cluster depends on cluster config and sharding key in distributed table engine. The answer also depends on the method used to write data - either directly on each shard or via INSERT in a Distributed table. See docs for more info here clickhouse.yandex/docs/en/operations/table_engines/distributed . PS: It might be better to address these questions to Clickhouse channel in Telegram (t.me/clickhouse_en). There are more chances to get answers on CH design decisions directly from CH developers. - Yuri Lachin

1 Answers

0
votes
  1. In ClickHouse, partitioning and sharding are two independent mechanisms. Partitioning by month means that data from different months will never be merged to be stored in same file on a filesystem and has nothing to do with data placement between nodes (which is controlled by choosing how exactly do you setup tables and run your INSERT INTO queries).
  2. Partitioning by months or weeks is usually doing fine, for choosing primary key see official documentation: https://clickhouse.yandex/docs/en/operations/table_engines/mergetree/#selecting-the-primary-key
  3. There are no fundamental issues with adding those, for example bloom filter index development is in progress: https://github.com/yandex/ClickHouse/pull/4499