This topic describes the benefits and features of the table engines in ApsaraDB for ClickHouse.

Overview

Table engines play a critical role in ApsaraDB for ClickHouse. Table engines determine how data is stored and read, whether concurrent reading and writing, indexing, and primary/secondary replication are supported, and what query types are supported.

ApsaraDB for ClickHouse provides a variety of table engines with a wide range of uses. Table engines in ApsaraDB for ClickHouse fall into four families: MergeTree, Log, Integration, and Special. ApsaraDB for ClickHouse also provides advanced table engines such as Replicated and Distributed. Advanced table engines are functionally independent of but can be used in combination with those table engines in the four families. The following figure shows a summary of the four table engine families. 14600201
  • MergeTree family: includes the most common and powerful table engines for high-load tasks. The common feature of this family is that data can be quickly inserted and processed in the background.
  • Log family: provides relatively simple features and is mainly used to quickly write data to small tables (about 1 million rows) and then read out all data.
  • Integration family: is mainly used to import external data to ApsaraDB for ClickHouse or directly operate external data sources in ApsaraDB for ClickHouse.
  • Special family: is mostly customized for special scenarios.

This topic describes several commonly used table engines. For more information, visit Table Engines.

MergeTree family

The MergeTree engine and other engines in this family (*MergeTree) are the most powerful ones in ApsaraDB for ClickHouse. Table engines in this family are designed to insert a large amount of data into a table. The data is quickly written to the table part by part. Then, rules are applied to merge the parts in the background. This method is more efficient than continually rewriting the data in storage during the INSERT operation.

The following sections describe the MergeTree, ReplacingMergeTree, CollapsingMergeTree, VersionedCollapsingMergeTree, SummingMergeTree, and AggregatingMergeTree engines.

MergeTree

The MergeTree engine is mainly used to analyze a large amount of data. The engine supports data partitioning, storage sorting, primary key indexes, sparse indexes, and data TTL. The MergeTree engine supports all ClickHouse SQL statements, but some features are inconsistent with MySQL. For example, primary keys are not used for deduplication in the MergeTree engine. The following example demonstrates how to use the MergeTree engine.

  1. Create a table named test_tbl. The primary key is (id, create_time). Storage sorting is based on the primary key and the data is partitioned based on create_time. The data of the last month is retained.
    CREATE TABLE test_tbl (
      id UInt16,
      create_time Date,
      comment Nullable(String)
    ) ENGINE = MergeTree()
    PARTITION BY create_time
       ORDER BY  (id, create_time)
         PRIMARY KEY (id, create_time)
         TTL create_time + INTERVAL 1 MONTH
         SETTINGS index_granularity=8192;
         SETTINGS index_granularity=8192;
  2. Write data to the table. Data rows with the same primary key are written.
    insert into test_tbl values(0, '2019-12-12', null);
    insert into test_tbl values(0, '2019-12-12', null);
    insert into test_tbl values(1, '2019-12-13', null);
    insert into test_tbl values(1, '2019-12-13', null);
    insert into test_tbl values(2, '2019-12-14', null);
    insert into test_tbl values(0, '2019-12-12', null);
    insert into test_tbl values(1, '2019-12-13', null);
    insert into test_tbl values(1, '2019-12-13', null);
    insert into test_tbl values(2, '2019-12-14', null);
  3. Query data. Only three data rows have the same id and create_time of the primary key, but five rows are displayed in the results.
    select count(*) from test_tbl;
    ┌─count()─┐
    │       5 │
    └─────────┘
    
    select * from test_tbl;
    ┌─id─┬─create_time─┬─comment─┐
    │  2 │  2019-12-14 │ ᴺᵁᴸᴸ    │
    └────┴─────────────┴─────────┘
    ┌─id─┬─create_time─┬─comment─┐
    │  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
    └────┴─────────────┴─────────┘
    ┌─id─┬─create_time─┬─comment─┐
    │  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
    └────┴─────────────┴─────────┘
    ┌─id─┬─create_time─┬─comment─┐
    │  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
    └────┴─────────────┴─────────┘
    ┌─id─┬─create_time─┬─comment─┐
    │  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
    └────┴─────────────┴─────────┘
    ┌─count()─┐
    │       5 │
    └─────────┘
    
    select * from test_tbl;
    ┌─id─┬─create_time─┬─comment─┐
    │  2 │  2019-12-14 │ ᴺᵁᴸᴸ    │
    └────┴─────────────┴─────────┘
    ┌─id─┬─create_time─┬─comment─┐
    │  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
    └────┴─────────────┴─────────┘
    ┌─id─┬─create_time─┬─comment─┐
    │  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
    └────┴─────────────┴─────────┘
    ┌─id─┬─create_time─┬─comment─┐
    │  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
    └────┴─────────────┴─────────┘
    ┌─id─┬─create_time─┬─comment─┐
    │  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
    └────┴─────────────┴─────────┘
  4. The storage layer processing logic runs during compaction, because the MergeTree engine uses a structure similar to LSM tree. Therefore, after compaction is executed in a forced manner, five rows are still displayed in the query results.
    optimize table test_tbl final;
    
    
    select count(*) from test_tbl;
    ┌─count()─┐
    │       5 │
    └─────────┘
    
    select * from test_tbl;
    ┌─id─┬─create_time─┬─comment─┐
    │  2 │  2019-12-14 │ ᴺᵁᴸᴸ    │
    └────┴─────────────┴─────────┘
    ┌─id─┬─create_time─┬─comment─┐
    │  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
    │  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
    └────┴─────────────┴─────────┘
    ┌─id─┬─create_time─┬─comment─┐
    │  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
    │  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
    └────┴─────────────┴─────────┘
    
    
    select count(*) from test_tbl;
    ┌─count()─┐
    │       5 │
    └─────────┘
    
    select * from test_tbl;
    ┌─id─┬─create_time─┬─comment─┐
    │  2 │  2019-12-14 │ ᴺᵁᴸᴸ    │
    └────┴─────────────┴─────────┘
    ┌─id─┬─create_time─┬─comment─┐
    │  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
    │  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
    └────┴─────────────┴─────────┘
    ┌─id─┬─create_time─┬─comment─┐
    │  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
    │  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
    └────┴─────────────┴─────────┘

Although the MergeTree engine has primary key indexes, the indexes are used to accelerate queries, rather than to keep records unique as in databases such as MySQL. Even after compaction is complete, data rows with the same primary key still exist.

ReplacingMergeTree
To solve the problem inherent in the MergeTree, ApsaraDB for ClickHouse provides the ReplacingMergeTree engine to delete duplicates with the same sort key value. Example:
-- Create a table.
CREATE TABLE test_tbl_replacing (
id UInt16,
  create_time Date,
  comment Nullable(String)
) ENGINE = ReplacingMergeTree()
  PARTITION BY create_time
ORDER BY  (id, create_time)
   PRIMARY KEY (id, create_time)
     SETTINGS index_granularity=8192;

-- Write duplicates with the same primary key to the table.
insert into test_tbl_replacing values(0, '2019-12-12', null);
insert into test_tbl_replacing values(0, '2019-12-12', null);
insert into test_tbl_replacing values(1, '2019-12-13', null);
insert into test_tbl_replacing values(1, '2019-12-13', null);
insert into test_tbl_replacing values(2, '2019-12-14', null);

-- Query data. Data rows with the same primary key are displayed before compaction.
select count(*) from test_tbl_replacing;
┌─count()─┐
│       5 │
└─────────┘

select * from test_tbl_replacing;
┌─id─┬─create_time─┬─comment─┐
│  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  2 │  2019-12-14 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘


--  Execute compaction in a forced manner.
optimize table test_tbl_replacing final;


-- Query data again. Data rows with the same primary key disappear.
select count(*) from test_tbl_replacing;
┌─count()─┐
│       3 │
└─────────┘

select * from test_tbl_replacing;
┌─id─┬─create_time─┬─comment─┐
│  2 │  2019-12-14 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
     PRIMARY KEY (id, create_time)
     SETTINGS index_granularity=8192;

-- Write duplicates with the same primary key to the table.
insert into test_tbl_replacing values(0, '2019-12-12', null);
insert into test_tbl_replacing values(0, '2019-12-12', null);
insert into test_tbl_replacing values(1, '2019-12-13', null);
insert into test_tbl_replacing values(1, '2019-12-13', null);
insert into test_tbl_replacing values(2, '2019-12-14', null);

-- Query data. Data rows with the same primary key are displayed before compaction.
select count(*) from test_tbl_replacing;
┌─count()─┐
│       5 │
└─────────┘

select * from test_tbl_replacing;
┌─id─┬─create_time─┬─comment─┐
│  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  2 │  2019-12-14 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘


-- Execute compaction in a forced manner.
optimize table test_tbl_replacing final;


-- Query data again. Data rows with the same primary key disappear.
select count(*) from test_tbl_replacing;
┌─count()─┐
│       3 │
└─────────┘

select * from test_tbl_replacing;
┌─id─┬─create_time─┬─comment─┐
│  2 │  2019-12-14 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
Although the ReplacingMergeTree engine can deduplicate the rows with the same primary key, it still has the following limits:
  • Before the thorough optimize operation is complete, deduplication may not be really implemented. For example, some data can be deduplicated, but data rows with the same primary key still exist.
  • In a distributed scenario, data with the same primary key may be sharded to different nodes. Only data on the same shard can be deduplicated.
  • The optimize operation is performed in the background and its execution time is unpredictable.
  • A manual optimize operation takes a long time if a large amount of data is involved. Real-time queries are unavailable in this case.

Therefore, the ReplacingMergeTree engine can be used to ensure that the data is eventually deduplicated, but not that query results contain deduplicated data.

CollapsingMergeTree

The CollapsingMergeTree engine can overcome the limits of the ReplacingMergeTree engine. This engine requires that the Sign column is specified in the statement to create a table. The Sign column has two values: 1 indicates a status row and -1 indicates a cancel row. Each time you want to add a new state, write a status row. If you want to delete a state, write a cancel row.

After compaction is complete, data rows with the same primary key but different Sign values are collapsed (deleted) Before compaction is executed, both status and cancel rows are displayed. To collapse (delete) data rows of the same primary key, you must make the following modifications at the business layer:
  • To perform the delete operation, you must write cancel rows. Cancel rows contain the data with the same primary keys in original status rows (except the Sign column). Therefore, the values of status rows must be recorded at the application layer. Alternatively, you can query the values of status rows before you perform the delete operation.
  • The status and cancel rows may not be collapsed when you initiate a query request, because the compaction execution time is unpredictable. In addition, data with the same primary key may be sharded to different nodes. Only data on the same shard can be deduplicated. Therefore, when aggregation such as COUNT (*) or SUM (col) is executed, data duplicates may exist. To obtain correct results, you must rewrite COUNT () and SUM (col) to COUNT (Sign) and SUM (col * Sign) at the business layer.

Example:

-- Create a table.
CREATE TABLE UAct
(
UserID UInt64,
PageViews UInt8,
    Duration UInt8,
    Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID;

-- Add a status row. The value of the Sign column is 1.
INSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1);

-- Add a cancel row to cancel the preceding status row.
    The value of the Sign column is -1 and the remaining values are consistent with the status row.
-- Add another status row with the same primary key. Modify the PageViews value from 5 to 6 and the Duration value from 146 to 185.
    INSERT INTO UAct VALUES (4324182021466249494, 5, 146, -1), (4324182021466249494, 6, 185, 1);

-- Query data: Before compaction is executed, both status and cancel rows are displayed.
SELECT * FROM UAct;┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐│ 4324182021466249494 │         5 │      146 │   -1 ││ 4324182021466249494 │         6 │      185 │    1 │└─────────────────────┴───────────┴──────────┴──────┘┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐│ 4324182021466249494 │         5 │      146 │    1 │└─────────────────────┴───────────┴──────────┴──────┘

-- To obtain correct results, you must rewrite the following SQL statements:
-- sum(PageViews) => sum(PageViews * Sign),
-- sum(Duration) => sum(Duration * Sign)
SELECT
UserID,
sum(PageViews * Sign) AS PageViews,
sum(Duration * Sign) AS Duration
FROM UAct
GROUP BY UserID
HAVING sum(Sign) > 0;
┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │         6 │      185 │
└─────────────────────┴───────────┴──────────┘


-- Execute compaction in a forced manner.
optimize table UAct final;

-- Query data again. Data rows with the same primary key disappear. The status and cancel rows are collapsed and only the latest status row is displayed.
select * from UAct;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

-- Add a status row. The value of the Sign column is 1.
INSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1);

-- Add a cancel row to cancel the preceding status row. The value of the Sign column is -1 and the remaining values are consistent with the status row.
-- Add another status row with the same primary key. Modify the PageViews value from 5 to 6 and the Duration value from 146 to 185.
INSERT INTO UAct VALUES (4324182021466249494, 5, 146, -1), (4324182021466249494, 6, 185, 1);

-- Query data: Before compaction is executed, both status and cancel rows are displayed.
SELECT * FROM UAct;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

-- To obtain correct results, you must rewrite the following SQL statements: 
-- sum(PageViews) => sum(PageViews * Sign)
-- sum(Duration) => sum(Duration * Sign)
SELECT
    UserID,
    sum(PageViews * Sign) AS PageViews,
    sum(Duration * Sign) AS Duration
FROM UAct
GROUP BY UserID
HAVING sum(Sign) > 0;
┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │         6 │      185 │
└─────────────────────┴───────────┴──────────┘


-- Execute compaction in a forced manner.
optimize table UAct final;

-- Query data again. Data rows with the same primary key disappear. The status and cancel rows are collapsed and only the latest status row is displayed.
select * from UAct;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

The CollapsingMergeTree engine can immediately delete data rows with the same primary key. However, if continuous status change and multi-threaded writing are involved, status and cancel rows may be out of order and not be collapsed.

Example:

-- Create a table.
CREATE TABLE UAct_order
(
UserID UInt64,
PageViews UInt8,
    Duration UInt8,
    Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID;

-- Add a cancel row.
INSERT INTO UAct_order VALUES (4324182021466249495, 5, 146, -1);
-- Add a status row.
INSERT INTO UAct_order VALUES (4324182021466249495, 5, 146, 1);

-- Execute compaction in a forced manner.
optimize table UAct_order final;

-- Query data. Even after compaction is complete, data rows with the same primary key are not collapsed. Two rows are displayed.
select * from UAct_order;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249495 │         5 │      146 │   -1 │
│ 4324182021466249495 │         5 │      146 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
    Duration UInt8,
    Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID;

--  Add a cancel row.
INSERT INTO UAct_order VALUES (4324182021466249495, 5, 146, -1);
--  Add a status row.
INSERT INTO UAct_order VALUES (4324182021466249495, 5, 146, 1);

-- Execute compaction in a forced manner.
optimize table UAct_order final;

-- Query data. Even after compaction is complete, data rows with the same primary key are not collapsed. Two rows are displayed.
select * from UAct_order;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249495 │         5 │      146 │   -1 │
│ 4324182021466249495 │         5 │      146 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
VersionedCollapsingMergeTree

To solve the problem of the CollapsingMergeTree engine that out-of-order data rows cannot be collapsed, the VersionedCollapsingMergeTree engine requires that the Version column is specified in the statement to create a table. The column is used to record the mapping between the status row and cancel row in the case of out-of-order writing. Data rows with the same primary key and same Version value but different Sign values are deleted during compaction.

Like the CollapsingMergeTree engine, to obtain correct results, you must rewrite COUNT () and SUM (col) to COUNT (Sign) and SUM (col * Sign) at the business layer.

Example:

-- Create a table.
CREATE TABLE UAct_version
(
UserID UInt64,
PageViews UInt8,
    Duration UInt8,
    Sign Int8,
    Version UInt8
)
ENGINE = VersionedCollapsingMergeTree(Sign, Version)
ORDER BY UserID;


-- Add a cancel row. The value of the Sign column is -1 and the value of the Version column is 1.
INSERT INTO UAct_version VALUES (4324182021466249494, 5, 146, -1, 1);
- Add a status row. The value of the Sign column is 1 and the value of the Version column is 1. Add another status row. The value of the Sign column is 1 and the value of the Version column is 2. Modify the PageViews value from 5 to 6 and the Duration value from 146 to 185.
INSERT INTO UAct_version VALUES (4324182021466249494, 5, 146, 1, 1),(4324182021466249494, 6, 185, 1, 2);


-- Query data: Before compaction is executed, both status and cancel rows are displayed.
SELECT * FROM UAct_version;┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐│ 4324182021466249494 │         5 │      146 │   -1 ││ 4324182021466249494 │         6 │      185 │    1 │└─────────────────────┴───────────┴──────────┴──────┘┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐│ 4324182021466249494 │         5 │      146 │    1 │└─────────────────────┴───────────┴──────────┴──────┘


-- To obtain correct results, you must rewrite the following SQL statements:
-- sum(PageViews) => sum(PageViews * Sign),
-- sum(Duration) => sum(Duration * Sign)
SELECT
    UserID,
    sum(PageViews * Sign) AS PageViews,
sum(Duration * Sign) AS Duration
FROM UAct_version
GROUP BY UserID
HAVING sum(Sign) > 0;
┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │         6 │      185 │
└─────────────────────┴───────────┴──────────┘


-- Execute compaction in a forced manner.
optimize table UAct_version final;


-- Query data again. Even if the cancel and status rows are out of order, they can still be collapsed.
select * from UAct_version;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐
│ 4324182021466249494 │         6 │      185 │    1 │       2 │
└─────────────────────┴───────────┴──────────┴──────┴─────────┘
ENGINE = VersionedCollapsingMergeTree(Sign, Version)
ORDER BY UserID;


-- Add a cancel row. The value of the Sign column is -1 and the value of the Version column is 1.
INSERT INTO UAct_version VALUES (4324182021466249494, 5, 146, -1, 1);
-- Add a status row. The value of the Sign column is 1 and the value of the Version column is 1. Add another status row. The value of the Sign column is 1 and the value of the Version column is 2. Modify the PageViews value from 5 to 6 and the Duration value from 146 to 185.
INSERT INTO UAct_version VALUES (4324182021466249494, 5, 146, 1, 1),(4324182021466249494, 6, 185, 1, 2);


-- Query data: Before compaction is executed, both status and cancel rows are displayed.
SELECT * FROM UAct_version;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘


-- To obtain correct results, you must rewrite the following SQL statements: 
-- sum(PageViews) => sum(PageViews * Sign)
-- sum(Duration) => sum(Duration * Sign)
SELECT
    UserID,
    sum(PageViews * Sign) AS PageViews,
    sum(Duration * Sign) AS Duration
FROM UAct_version
GROUP BY UserID
HAVING sum(Sign) > 0;
┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │         6 │      185 │
└─────────────────────┴───────────┴──────────┘


-- Execute compaction in a forced manner.
optimize table UAct_version final;


-- Query data again. Even if the cancel and status rows are out of order, they can still be collapsed.
select * from UAct_version;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐
│ 4324182021466249494 │         6 │      185 │    1 │       2 │
└─────────────────────┴───────────┴──────────┴──────┴─────────┘
SummingMergeTree

ApsaraDB for ClickHouse also supports the SummingMergeTree engine, which can be used to pre-aggregate primary key columns. When compaction is performed, data rows with the same primary key are summed and replaced with one data row. This can greatly save storage space and improve aggregate computing performance.

Take note of the following points:
  • In ApsaraDB for ClickHouse, data is pre-aggregated only when compaction is performed. The case may occur where some data is pre-aggregated while some is not because the compaction execution time is unpredictable. Therefore, the GROUP BY clause is still required in the SQL statement to perform aggregate computing.
  • ApsaraDB for ClickHouse pre-aggregates all columns other than the primary key columns. If these columns can be aggregated (such as numeric data types), the values are summed. If the columns cannot be aggregated (such as string data types), a random value is selected.
  • We recommend that you use the SummingMergeTree engine in combination with the MergeTree engine to accelerate queries. The former is used to store specific results and the latter is used to store pre-aggregated results.

Example:

-- Create a table.
CREATE TABLE summtt
(
key UInt32,
value UInt32
)
ENGINE = SummingMergeTree()
ORDER BY key

-- Insert data.
INSERT INTO summtt Values(1,1),(1,2),(2,1)

-- Query data. Data rows with the same primary key are displayed before compaction.
select * from summtt;
┌─key─┬─value─┐
│   1 │     1 │
│   1 │     2 │
│   2 │     1 │
└─────┴───────┘

-- Perform aggregate computing by using the GROUP BY clause.
SELECT key, sum(value) FROM summtt GROUP BY key
┌─key─┬─sum(value)─┐
│   2 │          1 │
│   1 │          3 │
└─────┴────────────┘

-- Execute compaction in a forced manner.
optimize table summtt final;

 -- Query data again. Data rows are pre-aggregated after compaction.
select * from summtt;
┌─key─┬─value─┐
│   1 │     3 │
│   2 │     1 │
└─────┴───────┘


-- Perform aggregate computing by using the GROUP BY clause after compaction.
SELECT key, sum(value) FROM summtt GROUP BY key
┌─key─┬─sum(value)─┐
│   2 │          1 │
│   1 │          3 │
└─────┴────────────┘
    key UInt32,
    value UInt32
)
ENGINE = SummingMergeTree()
ORDER BY key

--  Insert data.
INSERT INTO summtt Values(1,1),(1,2),(2,1)

-- Query data. Data rows with the same primary key are displayed before compaction.
select * from summtt;
┌─key─┬─value─┐
│   1 │     1 │
│   1 │     2 │
│   2 │     1 │
└─────┴───────┘

-- Perform aggregate computing by using the GROUP BY clause.
SELECT key, sum(value) FROM summtt GROUP BY key
┌─key─┬─sum(value)─┐
│   2 │          1 │
│   1 │          3 │
└─────┴────────────┘

-- Execute compaction in a forced manner.
optimize table summtt final;

-- Query data again. Data rows are pre-aggregated after compaction.
select * from summtt;
┌─key─┬─value─┐
│   1 │     3 │
│   2 │     1 │
└─────┴───────┘


-- Perform aggregate computing by using the GROUP BY clause after compaction.
SELECT key, sum(value) FROM summtt GROUP BY key
┌─key─┬─sum(value)─┐
│   2 │          1 │
│   1 │          3 │
└─────┴────────────┘
AggregatingMergeTree

The AggregatingMergeTree engine also provides pre-aggregation to improve aggregate computing performance. The difference from the SummingMergeTree engine: The SummingMergeTree engine uses the SUM function to aggregate non-primary key columns, while the AggregatingMergeTree engine can use various aggregate functions.

The syntax of the AggregatingMergeTree engine is complex. It must be used in combination with materialized views or the special data type of AGGREGATEFUNCTION in ApsaraDB for ClickHouse. The INSERT and SELECT statements also have unique formats and requirements: the -STATE syntax is required for writing and the -MERGE syntax is used for queries.

The following examples demonstrate how to use the AggregatingMergeTree engine.

Example 1: Use the AggregatingMergeTree engine in combination with a materialized view.

-- Create a detail table.
CREATE TABLE visits
(
UserID UInt64,
CounterID UInt8,
    StartDate Date,
    Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID;

-- Create a materialized view for the detail table to pre-aggregate the detail table.
-- Note: The following functions are used for pre-aggregation: sumState and uniqState.
    They both use the write syntax <agg>-State.
    CREATE MATERIALIZED VIEW visits_agg_view
ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate)
AS SELECT
CounterID,
StartDate,
sumState(Sign)    AS Visits,

uniqState(UserID) AS Users
FROM visits
GROUP BY CounterID, StartDate;

-- Insert detail data.
INSERT INTO visits VALUES(0, 0, '2019-11-11', 1);
INSERT INTO visits VALUES(1, 1, '2019-11-12', 1);

-- Perform final aggregation of the materialized view.
-- Note: The following aggregate functions are used: sumMerge and uniqMerge.
They both use the query syntax <agg>-Merge. SELECT
StartDate,
sumMerge(Visits) AS Visits,
uniqMerge(Users) AS Users
FROM visits_agg_view
GROUP BY StartDate
ORDER BY StartDate;

-- The SUM and UNIQ functions cannot be used.
-- If you execute the following SQL statement, the Illegal type AggregateFunction(sum, Int8) of argument error is returned:
SELECT
    StartDate,
    sum(Visits),
    uniq(Users)
FROM visits_agg_view
GROUP BY StartDate
ORDER BY StartDate;
    uniqState(UserID) AS Users
FROM visits
GROUP BY CounterID, StartDate;

-- Insert detail data.
INSERT INTO visits VALUES(0, 0, '2019-11-11', 1);
INSERT INTO visits VALUES(1, 1, '2019-11-12', 1);

-- Perform final aggregation of the materialized view.
-- Note: The following aggregate functions are used: sumMerge and uniqMerge.
SELECT
    StartDate,
    sumMerge(Visits) AS Visits,
    uniqMerge(Users) AS Users
FROM visits_agg_view
GROUP BY StartDate
ORDER BY StartDate;

-- The SUM and UNIQ functions cannot be used.
--  If you execute the following SQL statement, the Illegal type AggregateFunction(sum, Int8) of argument error is returned: 
SELECT
    StartDate,
    sum(Visits),
    uniq(Users)
FROM visits_agg_view
GROUP BY StartDate
ORDER BY StartDate;

Example 2: Use the AggregatingMergeTree engine in combination with the AGGREGATEFUNCTION data type.

-- Create a detail table.
CREATE TABLE detail_table
(   CounterID UInt8,
StartDate Date,
UserID UInt64
) ENGINE = MergeTree() 
PARTITION BY toYYYYMM(StartDate) 
ORDER BY (CounterID, StartDate);

-- Insert detail data.
INSERT INTO detail_table VALUES(0, '2019-11-11', 1);
INSERT INTO detail_table VALUES(1, '2019-11-12', 1);

-- Create a pre-aggregate table.
-Note: The data type of the UserID column is AggregateFunction(uniq, UInt64).
CREATE TABLE agg_table
(   CounterID UInt8,
    StartDate Date,
    UserID AggregateFunction(uniq, UInt64)
) ENGINE = AggregatingMergeTree() 
PARTITION BY toYYYYMM(StartDate) 
ORDER BY (CounterID, StartDate);

-- Read data from the detail table and insert the data to the pre-aggregate table.
-- Note: The following aggregate function is used in the subquery: uniqState. It uses the write syntax <agg>-State.
INSERT INTO agg_table
select CounterID, StartDate, uniqState(UserID)
from detail_table
group by CounterID, StartDate

-- You cannot use the common INSERT statement in the AggregatingMergeTree engine to insert data.
-- If you execute the following SQL statement, the Cannot convert UInt64 to AggregateFunction(uniq, UInt64) error is returned:
INSERT INTO agg_table VALUES(1, '2019-11-12', 1);

-- Query data in the pre-aggregate table.
-- Note: The following aggregate function is used in the SELECT statement: uniqMerge. It uses the query syntax <agg>-Merge
SELECT uniqMerge(UserID) AS state
FROM agg_table
GROUP BY CounterID, StartDate;
) ENGINE = MergeTree() 
PARTITION BY toYYYYMM(StartDate) 
ORDER BY (CounterID, StartDate);

                        

Log family

The Log family provides relatively simple features and is mainly used to quickly write data to small tables (about 1 million rows) and then read out all data.

Table engines in this family have the following characteristics:
  • Data is appended to a disk in sequence.
  • The DELETE and UPDATE operations are not supported.
  • Indexes are not supported.
  • Atomic writing is not supported.
  • The INSERT operation blocks the SELECT operation.
Table engines in this family differ in the following ways:
  • TinyLog: does not support concurrent reading of data files and delivers poor query performance. It uses a simple format and is suitable for temporary storage of intermediate data.
  • StripLog: supports concurrent reading of data files and delivers higher query performance than TinyLog. All columns are stored to a large file to reduce the number of files.
  • Log: supports concurrent reading of data files and delivers higher query performance than TinyLog. Each column is stored to a separate file.

Integration family

The Integration family is mainly used to import external data to ApsaraDB for ClickHouse or directly operate external data sources in ApsaraDB for ClickHouse.
  • Kafka: directly imports data from Kafka topics to ApsaraDB for ClickHouse.
  • MySQL: uses MySQL as the storage engine and directly performs operations such as SELECT on MySQL tables in ApsaraDB for ClickHouse.
  • JDBC or ODBC: reads data from sources by specifying JDBC and ODBC connection strings.
  • HDFS: directly reads data files in a specified format on HDFS.

Special family

The Special family is mostly customized for special scenarios.
  • Memory: stores data in memory. Data will be lost after restart. It delivers excellent query performance and is suitable for small tables of less than 1 million rows that do not have data persistence requirements. In ApsaraDB for ClickHouse, the engine is usually used in temporary tables.
  • Buffer: A memory buffer is set for a table. When the buffer conditions are met, data will be flushed to a disk.
  • File: Data is directly stores to local files.
  • Null: The written data is discarded and no data can be read.