CODEWITHSUNDEEP
clickhouse
GOGO
GOGO

Reputation: 729

How to understand part and partition of ClickHouse?

I see that clickhouse created multiple directories for each partition key.

Documentation says the directory name format is: partition name, minimum number of data block, maximum number of data block and chunk level. For example, the directory name is 201901_1_11_1.

I think it means that the directory is a part which belongs to partition 201901, has the blocks from 1 to 11 and is on level 1. So we can have another part whose directory is like 201901_12_21_1, which means this part belongs to partition 201901, has the blocks from 12 to 21 and is on level 1.

So I think partition is split into different parts. Am I right?

Upvotes: 9

Views: 12836

Answers (2)

Dinh Ngoc
Dinh Ngoc

Reputation: 11

In my case, I had used groupArray() and arrayEnumerate() for ranking in Populate. I thought that Populate can run query with new data on the partition (in my case: toStartOfDay(Date)), the total sum of new inserted data is correct but the groupArray() function is doesn't work correctly.

I think it's happened because when insert one Part, CH will groupArray() and rank on each Part immediately then merging Parts in one Partition, therefore i wont get exactly the final result of groupArray() and arrayEnumerate() function. Summary, Merge

[groupArray(part_1) + groupArray(part_2)] is different from groupArray(Partition)

with

Partition=part_1 + part_2

The solution that i tried is insert new data as one block size, just like using groupArray() to reduce the new data to the number of rows that is lower than max_insert_block_size=1048576. It did correctly but it's hard to insert new data of 1 day as one Part because it will use too much memory for querying when populating the data of 1 day (almost 150Mn-200Mn rows).

But do u have another solution for Populate with groupArray() for new inserting data, such as force CH to use POPULATE on each Partition, not each Part after merging all the part into one Partition?

Upvotes: 1

Denny Crane
Denny Crane

Reputation: 13350

Parts -- pieces of a table which stores rows. One part = one folder with columns.

Partitions are virtual entities. They don't have physical representation. But you can say that these parts belong to the same partition.

Select does not care about partitions.

Select is not aware about partitioning keys.

BECAUSE each part has special files minmax_{PARTITIONING_KEY_COLUMN}.idx These files contain min and max values of these columns in this part. Also this minmax_ values are stored in memory in a (c++ vector) list of parts.

create table X (A Int64, B Date, K Int64,C String) 
Engine=MergeTree partition by (A, toYYYYMM(B)) order by K;

insert into X values (1, today(), 1, '1');

cd /var/lib/clickhouse/data/default/X/1-202002_1_1_0/
ls -1 *.idx
minmax_A.idx   <-----
minmax_B.idx   <-----
primary.idx

SET send_logs_level = 'debug';
select * from X where A = 555;

(SelectExecutor): MinMax index condition: (column 0 in [555, 555])
(SelectExecutor): Selected 0 parts by date

SelectExecutor checked in-memory part list and found 0 parts because minmax_A.idx = (1,1) and this select needed (555, 555).

CH does not store partitioning key values.

So for example toYYYYMM(today()) = 202002 but this 202002 is not stored in a part or anywhere.

minmax_B.idx stores (18302, 18302) (2020-02-10 == select toInt16(today()))

Upvotes: 20

Related Questions