The unique key table in ByteHouse is primarily used to implement the upsert functionality. This capability is a proprietary feature developed in-house by the ByteHouse team that both maintains high query performance and supports unique key updates. The unique key table in ByteHouse mainly solves the pain point that open-source ClickHouse cannot support efficient update operations, helping businesses develop real-time analytics applications more easily. You can implement upsert semantics by specifying a unique key (UNIQUE KEY), and queries will automatically return the latest value for each unique key.

A unique key table is a CnchMergeTree table that uses a unique key index (UNIQUE KEY) and has the following characteristics:

• You configure a unique key using UNIQUE KEY, which supports upsert semantics for write operations, and queries automatically return the latest value for each unique key.
• High query performance is maintained even while ensuring real-time update capability.
• The unique key index (UNIQUE KEY) supports multiple columns and expressions.
• Unique key tables support multiple deduplication granularities, such as partition-level deduplication, bucket deduplication, and more.
• Supports custom version fields; automatically ignores them when writing data of a lower version.
• Supports real-time deletion of data based on UNIQUE KEY.
• Supports partial column updates based on the UNIQUE KEY.

For more information about the capabilities, functional logic, usage restrictions, and other details of unique key tables, please see MySQL Foreign Table.

The number of UNIQUE KEYs #

It is recommended that the number of UNIQUE KEY fields does not exceed five. If a unique key is formed by a combination of multiple fields, you can use their toString(sipHash128()) hash as the unique key.

Deduplication granularity selection #

If deduplication is required, it is recommended to prioritize using partition-level deduplication. Then, based on data volume and write performance requirements (RPS, record per second), determine whether bucket deduplication optimization needs to be enabled.

Bucket deduplication optimizations #

When any of the following situations occurs, it is recommended to enable bucket deduplication optimization:

1. For each deduplication granularity, the data volume exceeds 20 million (20M).
2. At single deduplication granularity, write requests per second exceed 20,000 (20K)/s.

You can enable bucket deduplication optimization by setting the columns required by the CLUSTER BY clause to be a subset of those required by the UNIQUE KEY when creating a table.

Bucket quantity selection #

The write side

When the deduplication granularity data volume (before splitting buckets) is m and the required peak RPS is n. Determine the number of buckets using max{⌈m / 20M⌉, ⌈n / 20K⌉}. If you need to reserve some margin for data volume or RPS, use 2 * max{⌈m /20M⌉, ⌈n / 20K⌉} as the number of buckets.

The query side

During a query, parts with the same number of buckets are sent to the same worker node for computation. Therefore, selecting an appropriate number of buckets has a significant impact on queries. The following principles are generally observed:

1. Ensure that the number of buckets is a multiple of the number of workers to guarantee balanced query load.
2. It is not recommended to use a number of buckets that is less than the number of workers, as this will result in load imbalance.

Best practices

After meeting the requirements on the write side, set write_bucket_num to 2 * max{⌈m / 20M⌉, ⌈n / 20K⌉}. The number of buckets can be set based on write_bucket_num and the number of workers:

1. When write_bucket_num is less than or equal to the number of workers, you can use the number of workers directly. If you consider long-term horizontal scaling, you can use number of workers * 2 as the final bucket count.
2. When write_bucket_num is greater than the number of workers, round up to the nearest integer multiple of the number of workers to determine the final bucket count.

Bucket expression #

1. It is recommended to use the CLUSTER BY EXPRESSION xxHash64V2(cluster_column) % N INTO N BUCKETS clause. For specific usage instructions, refer to RENAME Statement.
2. It is recommended to use high-cardinality columns for CLUSTER BY columns; otherwise, bucket data skew can easily occur, which can affect write and query performance.

Recommended configurations for Bucket table parameters #

It is recommended to use the following SETTINGS parameters:

max_dedup_worker_number = [cnch_vw_write 的 pod 数], pick_dedup_worker_by_mod = 1, dedup_impl_version = 'dedup_in_txn_commit'

A complete example of a table creation command is as follows:

CREATE TABLE orders
(
  `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 EXPRESSION xxHash64V2(product_id) % 6 INTO 6 BUCKETS
UNIQUE KEY product_id
SETTINGS [..., ]max_dedup_worker_number = [cnch_vw_write 的 pod 数], pick_dedup_worker_by_mod = 1, dedup_impl_version = 'dedup_in_txn_commit';

Batch accumulation is prioritized #

ByteHouse is designed to handle large volumes of data, so it is recommended to prioritize batch aggregation upstream whenever possible.

Non-deduplication mode #

When the data you need to import meets the following two criteria, you can enable import without deduplication at the query level:

1. The imported data itself does not contain duplicate unique keys.
2. There are no duplicate unique keys between the imported data and the historical data.

Use the following parameters to enable non-deduplicated import at the query level:

dedup_key_mode='append', enable_unique_partial_update = 0

The complete usage example is as follows:

INSERT INTO target_table SELECT * FROM source_table SETTINGS dedup_key_mode='append', enable_unique_partial_update = 0;

Parameter description

Concurrent writes #

If concurrent writes occur during data import, you may first consider aligning writes at the upper layer according to deduplication granularity. If achieving the previous method is difficult, you can enable concurrent write optimization when deduplication becomes a bottleneck. ByteHouse offers better large-scale deduplication performance by configuring table-level parameters to enable batch deduplication using background threads.
Table-level parameter settings are as follows:

cloud_enable_staging_area=1, cloud_staging_area_wait_mode='wait_txn_visible'

A complete example of a table creation command is as follows:

CREATE TABLE orders
(
  `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 EXPRESSION xxHash64V2(product_id) % 6 INTO 6 BUCKETS
UNIQUE KEY product_id
SETTINGS [..., ]cloud_enable_staging_area=1, cloud_staging_area_wait_mode='wait_txn_visible';

Parameter descriptions

Major ELT tasks #

When a single INSERT SELECT/DELETE/UPDATE operation affects more than 100 million rows, this task can be considered a large ELT task. ByteHouse supports making full use of multiple worker resources and centralized scheduling of deduplication tasks by setting table-level and query-level parameters. An example of parameter settings is as follows:

# 表级参数设置
dedup_impl_version = 'dedup_in_txn_commit'

# query 级别参数设置
optimize_unique_table_write = 1, max_insert_threads = 8

The complete usage example is as follows:

• Table-level parameter settings

  CREATE TABLE orders
  (
    `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 EXPRESSION xxHash64V2(product_id) % 6 INTO 6 BUCKETS
  UNIQUE KEY product_id
  SETTINGS [..., ]dedup_impl_version = 'dedup_in_txn_commit';
  

• Query-level parameter settings

  INSERT INTO target_table SELECT * FROM source_table SETTINGS optimize_unique_table_write = 1, max_insert_threads = 8;
  

Parameter descriptions

Coexistence of online and offline import #

When online and offline import tasks coexist, large-volume offline tasks can severely impact the write latency of real-time imports, resulting in significant performance fluctuations for the latter. In this scenario, you can enable optimization to resolve the issue. By configuring table-level parameters and relying on the staging area, deduplication bandwidth can be preferentially allocated to real-time tasks, thereby reducing the impact of large offline ELT tasks on real-time write latency.
Example settings for table-level parameters are as follows:

cloud_enable_staging_area=1, cloud_staging_area_wait_mode='wait_txn_visible'，real_time_task_optimize_algo='strict_optimize'

The following is a complete example of a table creation command:

CREATE TABLE orders
(
  `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 EXPRESSION xxHash64V2(product_id) % 6 INTO 6 BUCKETS
UNIQUE KEY product_id
SETTINGS [..., ]cloud_enable_staging_area=1, cloud_staging_area_wait_mode='wait_txn_visible'，real_time_task_optimize_algo='strict_optimize';

Parameter description

Example 1: Partition-level unique key #

-- By default, the engine ensures the uniqueness of the unique key within a partition.
-- Note: UNIQUE KEY does not support nullable columns
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 updates (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);

-- The 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 │
└─────────────────────┴────────────┴─────────┴──────────┴────────┴─────────┘

-- UNIQUE KEY can include multiple columns and expressions
-- sipHash64 is a fast hash function with a low collision rate; when using sipHash64 as a unique key, it is necessary to consider the possibility of hash collisions.
-- sipHash64：https://clickhouse.com/docs/en/sql-reference/functions/hash-functions#siphash64
CREATE TABLE t1m
(
  `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, sipHash64(city));

INSERT INTO t1m 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),
('2020-10-29 23:50:00', 10002, 'Shanghai', '男装', 4, 400),
('2020-10-29 23:50:00', 10003, 'Beijing', '男装', 2, 200),
('2020-10-29 23:50:00', 10004, 'Beijing', '男装', 1, 100);

select * from t1m;
┌──────────event_time─┬─product_id─┬─city─────┬─category─┬─amount─┬─revenue─┐
│ 2020-10-29 23:40:00 │      10001 │ Beijing  │ 男装     │      5 │     500 │
│ 2020-10-29 23:40:00 │      10002 │ Beijing  │ 男装     │      2 │     200 │
│ 2020-10-29 23:50:00 │      10003 │ Beijing  │ 男装     │      2 │     200 │
│ 2020-10-29 23:50:00 │      10004 │ Beijing  │ 男装     │      1 │     100 │
│ 2020-10-29 23:50:00 │      10002 │ Shanghai │ 男装     │      4 │     400 │
└─────────────────────┴────────────┴──────────┴──────────┴────────┴─────────┘

Example 2: Custom version field #

By default, data written with the same unique key will overwrite the existing data. This may lead to the following issues

• When tracing upstream data, older data may overwrite newer data, causing the query results to revert.
• Under the Lambda architecture, if offline and real-time tasks write to the same partition at the same time, which data is ultimately retained depends on the order in which the tasks are executed.

To address the issue mentioned above, ByteHouse unique key tables support designating a field in the table as the version field. The engine ensures that when writing data with the same key, overwriting occurs only if the data version is greater than or equal to the existing version. The version field supports all UInt types, as well as Data and DateTime, and cannot be nullable.

-- The parameters in parentheses for CnchMergeTree are optional version fields
CREATE TABLE t3
(
  `event_time` DateTime,
  `product_id` UInt64,
  `city` String,
  `category` String,
  `amount` UInt32,
  `revenue` UInt64
)
ENGINE = CnchMergeTree(event_time)
PARTITION BY toDate(event_time)
ORDER BY (city, category)
UNIQUE KEY product_id;

INSERT INTO t3 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:50:00', 10001, 'Beijing', '男装', 8, 800),
('2020-10-29 23:50:00', 10002, 'Beijing', '男装', 5, 500);

-- When backtracking the previous two records, if the version is less than the existing version, the records are automatically skipped during the write process.
INSERT INTO t3 VALUES
('2020-10-29 23:40:00', 10001, 'Beijing', '男装', 5, 500),
('2020-10-29 23:40:00', 10002, 'Beijing', '男装', 2, 200);

-- Rollback is not available for versions 10001 and 10002
select * from t3 order by toDate(event_time), product_id;
┌──────────event_time─┬─product_id─┬─city────┬─category─┬─amount─┬─revenue─┐
│ 2020-10-29 23:50:00 │      10001 │ Beijing │ 男装     │      8 │     800 │
│ 2020-10-29 23:50:00 │      10002 │ Beijing │ 男装     │      5 │     500 │
└─────────────────────┴────────────┴─────────┴──────────┴────────┴─────────┘

-- Continue to trace back the last two data entries, and write two data entries of the new version
INSERT INTO t3 VALUES
('2020-10-29 23:50:00', 10001, 'Beijing', '男装', 8, 800),
('2020-10-29 23:50:00', 10002, 'Beijing', '男装', 5, 500),
('2020-10-29 23:55:00', 10001, 'Beijing', '男装', 10, 1000),
('2020-10-29 23:55:00', 10002, 'Beijing', '男装', 7, 700);

-- Query automatically returns the latest version's data
select * from t3 order by toDate(event_time), product_id;
┌──────────event_time─┬─product_id─┬─city────┬─category─┬─amount─┬─revenue─┐
│ 2020-10-29 23:55:00 │      10001 │ Beijing │ 男装     │     10 │    1000 │
│ 2020-10-29 23:55:00 │      10002 │ Beijing │ 男装     │      7 │     700 │
└─────────────────────┴────────────┴─────────┴──────────┴────────┴─────────┘

Example 3: Delete the data with the specified unique key in real time #

In certain application scenarios, users want to add a field when performing an INSERT operation to indicate the deletion status of a record, thereby extending the semantics of INSERT.
In unique key tables in ByteHouse, each table includes a reserved field_delete_flag_ of type UInt8, where 0 indicates data being written and any nonzero value indicates data deletion. This field cannot be specified in CREATE TABLE statements or queried. It can only be specified during INSERT operations, including both INSERT and INSERT SELECT. In addition, the ByteHouse unique key tables support the DELETE FROM clause by using the reserved field_delete_flag_.
Usage examples are as follows:

• INSERT

-- By default, the engine ensures the uniqueness of a unique key within a partition.
-- Note: UNIQUE KEY does not support Nullable
CREATE TABLE t5
(
  `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 t5 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);

Specify the deletion field to perform data deletion. Setting the deletion field to a nonzero value indicates deletion, while setting it to zero indicates a normal upsert operation.
INSERT INTO t5 (event_time, product_id, city, category, amount, revenue, _delete_flag_) VALUES
('2020-10-29 23:50:00', 10001, 'Beijing', '男装', 4, 400, 5),
('2020-10-29 23:50:00', 10002, 'Beijing', '男装', 2, 200, 1),
('2020-10-29 23:50:00', 10004, 'Beijing', '男装', 1, 100, 0);

The query result includes a newly added row of data and no longer contains two old rows of data.
select * from t5 order by toDate(event_time), product_id;
┌──────────event_time─┬─product_id─┬─city────┬─category─┬─amount─┬─revenue─┐
│ 2020-10-29 23:40:00 │      10003 │ Beijing │ 男装     │      1 │     100 │
│ 2020-10-29 23:50:00 │      10004 │ Beijing │ 男装     │      1 │     100 │
└─────────────────────┴────────────┴─────────┴──────────┴────────┴─────────┘

• INSERT WITH VERSION

-- By default, the engine ensures the uniqueness of the unique key within a partition.
Note: UNIQUE KEY does not support Nullable.

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

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

If you specify a field to delete and a version number, and the version number is less than the version number of the corresponding row in the query result, the delete operation will not take effect.
INSERT INTO t5m (event_time, product_id, city, category, amount, revenue, version, _delete_flag_) VALUES
('2020-10-29 23:40:00', 10001, 'Beijing', '男装', 4, 400, 5, 1),
('2020-10-29 23:40:00', 10002, 'Beijing', '男装', 2, 200, 5, 1),
('2020-10-29 23:40:00', 10003, 'Beijing', '男装', 1, 100, 5, 1);

-- The query results remain unchanged; no data has been deleted
select * from t5m order by toDate(event_time), product_id;
┌──────────event_time─┬─product_id─┬─city────┬─category─┬─amount─┬─revenue─┬─version─┐
│ 2020-10-29 23:40:00 │      10001 │ Beijing │ 男装     │      5 │     500 │      10 │
│ 2020-10-29 23:40:00 │      10002 │ Beijing │ 男装     │      2 │     200 │      10 │
│ 2020-10-29 23:40:00 │      10003 │ Beijing │ 男装     │      1 │     100 │      10 │
└─────────────────────┴────────────┴─────────┴──────────┴────────┴─────────┴─────────┘

-- Specify the deletion field to perform data deletion. If the version number is not specified or is set to 0, the deletion operation will skip version checking and execute directly.
INSERT INTO t5m (event_time, product_id, city, category, amount, revenue, _delete_flag_) VALUES
('2020-10-29 23:40:00', 10001, 'Beijing', '男装', 4, 400, 1);
INSERT INTO t5m (event_time, product_id, city, category, amount, revenue, version, _delete_flag_) VALUES
('2020-10-29 23:40:00', 10003, 'Beijing', '男装', 1, 100, 0, 1);

-- The query deleted two rows of old data
select * from t5m order by toDate(event_time), product_id;
┌──────────event_time─┬─product_id─┬─city────┬─category─┬─amount─┬─revenue─┬─version─┐
│ 2020-10-29 23:40:00 │      10002 │ Beijing │ 男装     │      2 │     200 │      10 │
└─────────────────────┴────────────┴─────────┴──────────┴────────┴─────────┴─────────┘

• INSERT SELECT

-- By default, the engine ensures the uniqueness of the unique key within a partition.
-- Note: UNIQUE KEY does not support Nullable

CREATE TABLE t5x
(
  `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 t5x 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);

-- Delete data where revenue >= 200 by using INSERT SELECT
INSERT INTO t5x (event_time, product_id, city, category, amount, revenue, _delete_flag_) SELECT *, 1 as _delete_flag_ from t5x where revenue >= 200;

-- Data with revenue >= 200 has been deleted from the query results
select * from t5x order by toDate(event_time), product_id;
┌──────────event_time─┬─product_id─┬─city────┬─category─┬─amount─┬─revenue─┐
│ 2020-10-29 23:40:00 │      10003 │ Beijing │ 男装     │      1 │     100 │
└─────────────────────┴────────────┴─────────┴──────────┴────────┴─────────┘

• DELETE FROM

-- By default, the engine ensures unique keys are unique within a partition
-- Note: UNIQUE KEY does not support Nullable

CREATE TABLE t5y
(
  `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 t5y 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);

-- Use DELETE FROM to delete data where revenue equals 500
DELETE FROM t5y WHERE revenue = 500;
-- Delete data where revenue is greater than or equal to 200 using DELETE FROM.
DELETE FROM t5y WHERE revenue >= 200;

-- Data with revenue greater than or equal to 200 has been deleted from the query results
select * from t5y order by toDate(event_time), product_id;
┌──────────event_time─┬─product_id─┬─city────┬─category─┬─amount─┬─revenue─┐
│ 2020-10-29 23:40:00 │      10003 │ Beijing │ 男装     │      1 │     100 │
└─────────────────────┴────────────┴─────────┴──────────┴────────┴─────────┘

Example 4: Update data for a specified unique key in real time #

-- By default, the engine ensures the uniqueness of the unique key within a partition
Note: UNIQUE KEY does not support nullable columns.

CREATE TABLE t6
(
  `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 t6 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);

-- Update using the UPDATE statement
UPDATE t6 SET amount = 10, revenue = 1000 WHERE event_time = '2020-10-29 23:40:00' and product_id=10001;

-- 10,001 rows in the query result were updated
select * from t6 order by toDate(event_time), product_id;
┌──────────event_time─┬─product_id─┬─city────┬─category─┬─amount─┬─revenue─┐
│ 2020-10-29 23:40:00 │      10001 │ Beijing │ 男装     │     10 │    1000 │
│ 2020-10-29 23:40:00 │      10002 │ Beijing │ 男装     │      2 │     200 │
│ 2020-10-29 23:40:00 │      10003 │ Beijing │ 男装     │      1 │     100 │
└─────────────────────┴────────────┴─────────┴──────────┴────────┴─────────┘

Update using the UPDATE statement and restrict the scope with limit.
UPDATE t6 SET category = concat('新',category) WHERE event_time = '2020-10-29 23:40:00' order by product_id limit 1;

-- Only 10,001 data rows in the query result were updated
select * from t6 order by toDate(event_time), product_id;
┌──────────event_time─┬─product_id─┬─city────┬─category─┬─amount─┬─revenue─┐
│ 2020-10-29 23:40:00 │      10001 │ Beijing │ 新男装   │     10 │    1000 │
│ 2020-10-29 23:40:00 │      10002 │ Beijing │ 男装     │      2 │     200 │
│ 2020-10-29 23:40:00 │      10003 │ Beijing │ 男装     │      1 │     100 │
└─────────────────────┴────────────┴─────────┴──────────┴────────┴─────────┘

-- Swap columns using the UPDATE statement
UPDATE t6 SET amount=revenue, revenue=amount  WHERE event_time = '2020-10-29 23:40:00';

-- The amount and revenue columns in the query result have been swapped
select * from t6 order by toDate(event_time), product_id;
┌──────────event_time─┬─product_id─┬─city────┬─category─┬─amount─┬─revenue─┐
│ 2020-10-29 23:40:00 │      10001 │ Beijing │ 新男装   │   1000 │      10 │
│ 2020-10-29 23:40:00 │      10002 │ Beijing │ 男装     │    200 │       2 │
│ 2020-10-29 23:40:00 │      10003 │ Beijing │ 男装     │    100 │       1 │
└─────────────────────┴────────────┴─────────┴──────────┴────────┴─────────┘

Example 5: Bucket table #

A Bucket table is a performance optimization option available when creating tables in ByteHouse. When a Bucket table is used in ByteHouse, the system organizes table data based on one or more columns or expressions specified in the table creation statement, clustering rows with the same value under the same bucket number. This approach enables better performance during query processing.

• Not unique at the bucket level
  When enable_bucket_level_unique_keys is not used to specify bucket-level uniqueness. ByteHouse unique key tables enforce uniqueness within the scope specified by the table engine. By default, uniqueness is enforced at the partition level; at this time, different buckets within the same partition do not contain duplicate data.

  -- By default, the engine ensures the uniqueness of the unique key within a partition
  -- Note: UNIQUE KEY does not support nullable columns
  CREATE TABLE t7
  (
      `d` Date,
      `id` Int32,
      `s` String
  )
  ENGINE = CnchMergeTree
  PARTITION BY d
  CLUSTER BY s INTO 10 BUCKETS
  ORDER BY s
  UNIQUE KEY id;
  
  INSERT INTO t7 VALUES 
  ('2023-06-26', 1, '1a'), ('2023-06-26', 2, '2a'), ('2023-06-26', 3, '3a'), ('2023-06-26', 3, '3b'), ('2023-06-26', 3, '3c');
  
  select * from t7 order by id;
  ┌──────────d─┬─id─┬─s──┐
  │ 2023-06-26 │  1 │ 1a │
  │ 2023-06-26 │  2 │ 2a │
  │ 2023-06-26 │  3 │ 3c │
  └────────────┴────┴────┘
  

• Unique at the bucket level
  When all columns required by the cluster by clause are included in the unique key fields, data with the same unique key will always fall into the same bucket, enabling bucket-level deduplication. At this point, partition_level_unique_keys is used to specify the scope of deduplication for the bucket (partition-level or table-level).
  The bucket feature is the only way to reduce the amount of data subject to deduplication, thereby improving write RPS and P99 latency; enable_bucket_level_unique_keys = 1 allows skipping part-level bucket validation, further enhancing write efficiency;

  -- By default, the engine ensures the uniqueness of the unique key within a partition
  Note: UNIQUE KEY does not support Nullable.
  CREATE TABLE t7m
  (
      `d` Date,
      `id` Int32,
      `s` String
  )
  ENGINE = CnchMergeTree
  PARTITION BY d
  CLUSTER BY id INTO 10 BUCKETS
  ORDER BY s
  UNIQUE KEY id
  SETTINGS enable_bucket_level_unique_keys = 1;
  
  INSERT INTO t7m VALUES 
  ('2023-06-26', 1, '1a'), ('2023-06-26', 2, '2a'), ('2023-06-26', 3, '3a'), ('2023-06-26', 3, '3b'), ('2023-06-26', 3, '3c');
  
  select * from t7m order by id;
  ┌──────────d─┬─id─┬─s──┐
  │ 2023-06-26 │  1 │ 1a │
  │ 2023-06-26 │  2 │ 2a │
  │ 2023-06-26 │  3 │ 3c │
  └────────────┴────┴────┘
  
  INSERT INTO t7m VALUES ('2023-06-26', 3, '3d');
  
  select * from t7m order by id;
  ┌──────────d─┬─id─┬─s──┐
  │ 2023-06-26 │  1 │ 1a │
  │ 2023-06-26 │  2 │ 2a │
  │ 2023-06-26 │  3 │ 3d │
  └────────────┴────┴────┘
  

Example 6: Partial column update #

The ByteHouse Cloud Data Warehouse Edition supports partial column update mode.
In row update mode, columns not specified are filled with default values. In column update mode, if a default column has an existing value, it will be retained; otherwise, the default value will be used. For details, see Basic Data Types.

Example 7: MySQL DML benchmarking and dedup_key_mode #

The unique key table in ByteHouse is designed to be compatible with MySQL and supports its different write modes.

CREATE TABLE mysql_test (
  a int NOT NULL,
  b int DEFAULT NULL,
  PRIMARY KEY (a)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE mysql_test(
    `a` Int32,
    `b` Nullable(Int32)
)
ENGINE = CnchMergeTree
ORDER BY a
UNIQUE KEY a

MySQL [db]> insert into mysql_test values (1,1), (1,1);

ERROR 1062 (23000): Duplicate entry '1' for key 'mysql_test.PRIMARY'

insert into mysql_test FORMAT Values SETTINGS dedup_key_mode='throw' (1,1), (1,1) ;

DB::Exception: Found duplication in the block when insert with setting dedup_key_mode=DedupKeyMode::THROW SQLSTATE: 22000.

MySQL [db]> insert ignore into mysql_test values (1,1), (1,1);
Query OK, 1 row affected, 1 warning (0.07 sec)
Records: 2  Duplicates: 1  Warnings: 1

MySQL [db]> select * from mysql_test;

insert ignore into mysql_test values (1,1), (1,1) ;

select * from mysql_test

MySQL [db]> select * from mysql_test;

MySQL [db]> replace into mysql_test values (1, 2);

MySQL [db]> select * from mysql_test;

select * from mysql_test

insert into mysql_test values (1, 2) ;
select * from mysql_test