CoalescingMergeTree
The engine inherits from MergeTree. The difference is that when merging data parts for CoalescingMergeTree tables ClickHouse replaces all the rows with the same primary key (or more accurately, with the same sorting key) with one row which contains the latest non-null values of each column. If the sorting key is composed in a way that a single key value corresponds to large number of rows, this significantly reduces storage volume and speeds up data selection.
We recommend using the engine together with MergeTree. Store complete data in MergeTree table, and use CoalescingMergeTree for aggregated data storing, for example, when preparing reports. Such an approach will prevent you from losing valuable data due to an incorrectly composed primary key.
Creating a Table
For a description of request parameters, see request description.
Parameters of CoalescingMergeTree
columns
columns - a tuple with the names of columns where values will be united. Optional parameter.
The columns must be of a numeric type and must not be in the partition or sorting key.
If columns is not specified, ClickHouse unites the values in all columns that are not in the sorting key.
Query clauses
When creating a CoalescingMergeTree table the same clauses are required, as when creating a MergeTree table.
Deprecated Method for Creating a Table
Do not use this method in new projects and, if possible, switch the old projects to the method described above.
All of the parameters excepting columns have the same meaning as in MergeTree.
columns— tuple with names of columns values of which will be summed. Optional parameter. For a description, see the text above.
Usage Example
Consider the following table:
Insert data to it:
The result will looks like this:
ClickHouse may unite all the rows not completely (see below), so we use an aggregate function last_value and GROUP BY clause in the query.
Data Processing
When data are inserted into a table, they are saved as-is. ClickHouse merges the inserted parts of data periodically and this is when rows with the same primary key are summed and replaced with one for each resulting part of data.
ClickHouse can merge the data parts so that different resulting parts of data can consist rows with the same primary key, i.e. the union will be incomplete. Therefore (SELECT) an aggregate function last_value() and GROUP BY clause should be used in a query as described in the example above.
