The CnchMergeTree table engine in the ByteHouse cloud data warehouse version supports configuring a unique key (UNIQUE KEY) when creating a table, allowing you to create a unique key table. This article introduces the details of the unique key table's capabilities and provides usage examples.

A unique key table is a CnchMergeTree table with a specified unique key (UNIQUE KEY) and has the following features:

• After the user configures the unique key using UNIQUE KEY, the unique key table supports UPSERT semantics for updates and automatically returns the latest value for each unique key during queries.
• While ensuring real-time update capabilities, it still maintains high query performance.
• The unique key (UNIQUE KEY) supports multiple fields and expressions.
• The unique key table supports various deduplication granularities, such as partition-level deduplication and bucket-level deduplication.
• It supports custom version fields and automatically ignores data with lower versions when writing.
• It supports real-time data deletion based on the UNIQUE KEY.
• It supports partial column updates based on the UNIQUE KEY.

Comparison of capabilities between unique key tables and non-unique key tables #

Types supported by unique key #

1. The unique key supports using a Nullable Key. To use this feature, you need to add the settings allow_nullable_key = 1 parameter. In addition, the embedded Nullable data type must be one of the types supported by the unique key, such as Nullable(UInt32).
2. To use a field type that is not supported as a unique key field, try using sipHash128() to convert the field type to String.

   tip

   sipHash128 is a fast hash function with a low collision rate. No hash collision scenarios have been encountered in practice.

ByteHouse unique key tables provide partition-level uniqueness as the default deduplication granularity. When the columns required for bucketing (cluster by) are a subset of the columns required for the unique key, the deduplication granularity can be optimized to bucket-level uniqueness. Different deduplication granularities can limit the amount of data to be deduplicated at the metadata level, thereby meeting different write requirements:

The unique key table uses a delete-and-insert strategy. When updated data arrives, it locates the original position of each record using the unique key, marks the record as deleted, and then writes the latest data as a new record into a new data file. When reading, based on the delete mark, deleted old version data is filtered out, allowing the latest value of the unique key to be queried.
For example, in the following example, the table is partition-level unique, PK is partition key, UK is unique key, and delete bitmap is the column used to mark deletions.

Data with the delete bitmap column set to 1 is marked as deleted. After incremental data is written, the latest data is:

2020-10-29，1，m
2020-10-29，2，n
2020-10-30，1，z
2020-10-30，2，k

Due to the deduplication feature of unique key tables, to enable efficient data writing, there are the following usage limitations for data volume and unique key quantity:

• It is recommended to set no more than 5 unique key fields. If multiple fields are combined to form a unique key, their sipHash64 hash can be used as the unique key.
• It is recommended to keep the deduplication granularity data volume below 100 million.

Here are two typical usage examples. For more examples, see Best practices for ByteHouse unique key tables.

Partition-level unique example #

CREATE TABLE t1
(
  `event_time` DateTime,
  `product_id` UInt64,
  `city` String,
  `category` String,
  `amount` UInt32,
  `revenue` UInt64
)
ENGINE = CnchMergeTree
PARTITION BY toDate(event_time)
ORDER BY (city, category)
UNIQUE KEY product_id;

INSERT INTO t1 VALUES
('2020-10-29 23:40:00', 10001, 'Beijing', '男装', 5, 500),
('2020-10-29 23:40:00', 10002, 'Beijing', '男装', 2, 200),
('2020-10-29 23:40:00', 10003, 'Beijing', '男装', 1, 100);

-- Writing data with the same key enables update (upsert semantics)
INSERT INTO t1 VALUES
('2020-10-29 23:50:00', 10002, 'Beijing', '男装', 4, 400),
('2020-10-29 23:50:00', 10003, 'Beijing', '男装', 2, 200),
('2020-10-29 23:50:00', 10004, 'Beijing', '男装', 1, 100),
('2020-10-30 00:00:05', 10001, 'Beijing', '男装', 1, 100),
('2020-10-30 00:00:05', 10002, 'Beijing', '男装', 2, 200);

-- Query automatically returns the latest data for each key
select * from t1 order by toDate(event_time), product_id;
┌──────────event_time─┬─product_id─┬─city────┬─category─┬─amount─┬─revenue─┐
│ 2020-10-29 23:40:00 │      10001 │ Beijing │ 男装     │      5 │     500 │
│ 2020-10-29 23:50:00 │      10002 │ Beijing │ 男装     │      4 │     400 │
│ 2020-10-29 23:50:00 │      10003 │ Beijing │ 男装     │      2 │     200 │
│ 2020-10-29 23:50:00 │      10004 │ Beijing │ 男装     │      1 │     100 │
│ 2020-10-30 00:00:05 │      10001 │ Beijing │ 男装     │      1 │     100 │
│ 2020-10-30 00:00:05 │      10002 │ Beijing │ 男装     │      2 │     200 │
└─────────────────────┴────────────┴─────────┴──────────┴────────┴─────────┘

Partition-level unique + bucket unique example #

When the data volume of a single partition in a unique key table reaches a certain scale, it affects write efficiency. At this time, you can use cluster by to distribute partition-level data volume into different buckets. When all columns required by cluster by are included in the unique key, partition-level uniqueness can be achieved, while also benefiting from bucket unique optimization.

CREATE TABLE t2
(
  `event_time` DateTime,
  `product_id` UInt64,
  `city` String,
  `category` String,
  `amount` UInt32,
  `revenue` UInt64
)
ENGINE = CnchMergeTree
PARTITION BY toDate(event_time)
ORDER BY (city, category)
CLUSTER BY product_id INTO 10 BUCKETS
UNIQUE KEY product_id;

INSERT INTO t2 VALUES
('2020-10-29 23:40:00', 10001, 'Beijing', '男装', 5, 500),
('2020-10-29 23:40:00', 10002, 'Beijing', '男装', 2, 200),
('2020-10-29 23:40:00', 10003, 'Beijing', '男装', 1, 100);

-- Writing data with the same key enables update (upsert semantics)
INSERT INTO t2 VALUES
('2020-10-29 23:50:00', 10002, 'Beijing', '男装', 4, 400),
('2020-10-29 23:50:00', 10003, 'Beijing', '男装', 2, 200),
('2020-10-29 23:50:00', 10004, 'Beijing', '男装', 1, 100),
('2020-10-30 00:00:05', 10001, 'Beijing', '男装', 1, 100),
('2020-10-30 00:00:05', 10002, 'Beijing', '男装', 2, 200);

-- Query automatically returns the latest data for each key
select * from t2 order by toDate(event_time), product_id;
┌──────────event_time─┬─product_id─┬─city────┬─category─┬─amount─┬─revenue─┐
│ 2020-10-29 23:40:00 │      10001 │ Beijing │ 男装     │      5 │     500 │
│ 2020-10-29 23:50:00 │      10002 │ Beijing │ 男装     │      4 │     400 │
│ 2020-10-29 23:50:00 │      10003 │ Beijing │ 男装     │      2 │     200 │
│ 2020-10-29 23:50:00 │      10004 │ Beijing │ 男装     │      1 │     100 │
│ 2020-10-30 00:00:05 │      10001 │ Beijing │ 男装     │      1 │     100 │
│ 2020-10-30 00:00:05 │      10002 │ Beijing │ 男装     │      2 │     200 │
└─────────────────────┴────────────┴─────────┴──────────┴────────┴─────────┘

ByteHouse also provides best practices for unique key tables. For details, please refer to Best practices for ByteHouse unique key tables.