You need to enable JavaScript to run this app.
导航
ByteHouse云数仓版
  • 文档首页
    • /ByteHouse云数仓版/最佳实践/ByteHouse Unique 表最佳实践
ByteHouse Unique 表最佳实践
最近更新时间：2024.08.30 14:31:35首次发布时间：2022.10.06 11:03:34
我的收藏

ByteHouse Unique 表主要用于实现 upsert 功能。该能力是 ByteHouse 团队自研的独有特性，既能保持高效的查询性能、又支持主键更新。主要解决了开源 ClickHouse 不能支持高效更新操作的痛点，帮助业务更简单地开发实时分析应用。用户通过指定唯一键 UNIQUE KEY 来实现 Upsert 更新写语义，查询自动返回每个唯一键的最新值。

功能特点

Unique 表主要具有以下特点：

  1. 用户通过 UNIQUE KEY 配置唯一键，提供 upsert 更新写语义，查询自动返回每个唯一键的最新值。、
  2. 唯一键支持多字段和表达式。
  3. 在保证实时更新能力的情况下，依然保持较高的查询性能。
  4. 支持根据 UNIQUE KEY 实时删除数据。
  5. 支持指定字段进行版本管理，仅保留最新版本，写入低版本数据时自动忽略。
  6. 支持表级别唯一和分区级别唯一两种模式。

使用限制

注意

当数据量超过亿级时，去重效率会严重下降，进而影响写入效率。

推荐使用时，单分区数据量不超过千万级别。若为全表唯一，则全表数据量建议不超过千万级别。

实时更新的使用场景
  1. 业务需要对交易类数据进行实时分析，在同步 OLTP 数据库到 OLAP 数据库的过程中，由于订单数据等需要支持更新能力，因此对于 OLAP 数据库也有支持实时更新和删除的要求。
  2. 另一类场景虽然不存在更新，但需要去重。在开发实时数据时，很难保证数据流中没有重复数据，因此通常需要存储系统支持数据的幂等写入。

上述场景都可以通过唯一键 upsert 功能来支持，不管是幂等还是更新的需求。

使用示例

例1：分区级别唯一键

-- 引擎默认保证 unique key 在分区内的唯一性
-- 注：UNIQUE KEY 不支持 Nullable
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);

-- 写入相同 key 的数据可以实现更新(upsert语义)
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);

-- 查询自动返回每个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 可以包含多个字段和表达式
-- sipHash64 是一种快速且低冲突率的哈希函数，使用 sipHash64 作为 unique key 需要考虑到可能的 hash 冲突
-- 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 │
└─────────────────────┴────────────┴──────────┴──────────┴────────┴─────────┘

例2：表级别唯一键

-- 设置 partition_level_unique_keys = 0 后，CNCH 保证 unique key 在表级别唯一
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)
UNIQUE KEY product_id
SETTINGS partition_level_unique_keys = 0;

-- 插入测试数据
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);

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);

-- 可以看到，10001 和 10002 这两个产品从 2020-10-29 分区更新到了 2020-10-30 分区
select * from t2 order by toDate(event_time), product_id;
┌──────────event_time─┬─product_id─┬─city────┬─category─┬─amount─┬─revenue─┐
│ 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 │
└─────────────────────┴────────────┴─────────┴──────────┴────────┴─────────┘

例3：自定义版本字段

说明

使用版本字段时有以下限制：

  • 如需要使用整数作为版本字段，建议使用兼容UInt64的无符号整数
  • 支持Date、Datetime等时间类型作为版本字段
  • 不支持Float、Decimal等浮点数类型作为版本字段

默认情况下，相同 unique key 后写入的数据会覆盖已有的数据。这可能会带来以下问题

  • 回溯上游数据时，老数据可能覆盖新数据，导致查询到的数据结果出现回退
  • Lambda 架构下，如果离线和实时任务同时写一个分区，最终保留哪条数据取决于任务的执行顺序

为了解决上面的问题，Cnch Unique 表支持将表中的某个字段指定为版本字段。引擎保证写入相同 key 的数据时，只有数据版本 >= 已有版本时，才会进行覆盖。版本字段支持所有UInt类型和Data/DateTime，且不能为 Nullable。

-- CnchMergeTree 括号内参数为可选的版本字段
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);

-- 回溯前两条数据，由于版本 < 已有版本，写入时自动跳过
INSERT INTO t3 VALUES
('2020-10-29 23:40:00', 10001, 'Beijing', '男装', 5, 500),
('2020-10-29 23:40:00', 10002, 'Beijing', '男装', 2, 200);

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

-- 继续回溯后两条数据，并写入两条新版本数据
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);

-- 查询自动返回最新版本的数据
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 │
└─────────────────────┴────────────┴─────────┴──────────┴────────┴─────────┘

例4：使用分区值作为版本

考虑以下Lambda架构的需求场景

  • 表按天分区，需要基于某个字段实现表粒度去重
  • 实时任务写T+0分区，可能将一些数据从 T+N 分区更新到 T+0 分区
  • 离线任务每天重写 T+1 分区
  • 每个 key 的最新数据需要从其所在的最新分区读取

我们可以将分区字段（日期）作为版本字段来实现该场景，然而这需要额外存储一个日期字段。由于分区下所有数据的日期都是一样的，这样做显然存在资源浪费。
为了优化该场景，Cnch Unique 表支持直接使用分区表达式作为版本。当引擎发现版本字段为分区字段时，会自动从元数据中读取版本，避免额外的数据读写。

-- 创建一张按天分区、表粒度唯一的 unique 表，使用分区字段作为版本
CREATE TABLE t4
(
  `event_time` DateTime,
  `product_id` UInt64,
  `city` String,
  `category` String,
  `amount` UInt32,
  `revenue` UInt64
)
ENGINE = CnchMergeTree(toDate(event_time))
PARTITION BY toDate(event_time)
ORDER BY (city, category)
UNIQUE KEY product_id
SETTINGS partition_level_unique_keys = 0;

-- 10-29 实时任务数据写入
INSERT INTO t4 VALUES
('2020-10-29 10:00:00', 10001, 'Beijing', '男装', 5, 500),
('2020-10-29 10:00:00', 10002, 'Beijing', '男装', 2, 200),
('2020-10-29 10:10:00', 10001, 'Beijing', '男装', 8, 800),
('2020-10-29 10:10:00', 10002, 'Beijing', '男装', 5, 500);

-- 10-30 实时任务数据写入，将 10002 更新到 10-30 分区
INSERT INTO t4 VALUES
('2020-10-30 08:00:00', 10002, 'Beijing', '男装', 10, 1000),
('2020-10-30 08:00:00', 10003, 'Beijing', '男装', 3, 300);

-- 10-30 离线任务重写 10-29 数据
INSERT INTO t4 VALUES
('2020-10-29 10:10:00', 10001, 'Beijing', '男装', 7, 700),
('2020-10-29 10:10:00', 10002, 'Beijing', '男装', 5, 500);

-- 离线任务只会覆盖 10001 数据，10002 保留 10-30 中的最新数据
select * from t4 order by toDate(event_time), product_id;
┌──────────event_time─┬─product_id─┬─city────┬─category─┬─amount─┬─revenue─┐
│ 2020-10-29 10:10:00 │      10001 │ Beijing │ 男装     │      7 │     700 │
│ 2020-10-30 08:00:00 │      10002 │ Beijing │ 男装     │     10 │    1000 │
│ 2020-10-30 08:00:00 │      10003 │ Beijing │ 男装     │      3 │     300 │
└─────────────────────┴────────────┴─────────┴──────────┴────────┴─────────┘

例5：实时删除指定唯一键的数据

在某些应用场景下，用户希望在INSERT时加上一个字段来标识是否删除来扩展INSERT语义。
在 Cnch Unique 表中，为每张表都添加了一个保留字段_delete_flag_，类型为UInt8， 0表示数据写入，非0表示数据删除。该字段不可在CREATE TABLE时指定，也不可查询该字段，仅可以在INSERT时指定，包括INSERT和INSERT SELECT。此外，Cnch Unique 表基于保留字段_delete_flag_，支持了 DELETE FROM 子句。
用法示例如下：

  • INSERT
-- 引擎默认保证 unique key 在分区内的唯一性
-- 注：UNIQUE KEY 不支持 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);

-- 指定删除字段进行数据删除，删除字段设置非0时表示删除，设置为0时表示正常的upsert操作
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);

-- 查询结果中包含了新加入的一行数据，并删除了两行旧数据
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
-- 引擎默认保证 unique key 在分区内的唯一性
-- 注：UNIQUE KEY 不支持 Nullable

-- 指定版本号
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);

-- 指定删除字段并指定版本号，版本号小于查询结果中相应行的版本号，删除操作不会起作用
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);

-- 查询结果不变，没有任何数据被删除
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 │
└─────────────────────┴────────────┴─────────┴──────────┴────────┴─────────┴─────────┘

-- 指定删除字段进行数据删除，不指定版本号或者版本号设置为0，删除操作会跳过版本检查，直接执行
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);

-- 查询结果删除了两行旧数据
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
-- 引擎默认保证 unique key 在分区内的唯一性
-- 注：UNIQUE KEY 不支持 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);

-- 通过INSERT SELECT 来删除revenue >= 200的数据
INSERT INTO t5x (event_time, product_id, city, category, amount, revenue, _delete_flag_) SELECT *, 1 as _delete_flag_ from t5x where revenue >= 200;

-- 查询结果中已删除revenue >= 200的数据
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
-- 引擎默认保证 unique key 在分区内的唯一性
-- 注：UNIQUE KEY 不支持 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);

-- 通过 DELETE FROM 来删除revenue == 500的数据
DELETE FROM t5y WHERE revenue = 500;
-- 通过 DELETE FROM 来删除revenue >= 200的数据
DELETE FROM t5y WHERE revenue >= 200;

-- 查询结果中已删除revenue >= 200的数据
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 │
└─────────────────────┴────────────┴─────────┴──────────┴────────┴─────────┘

例6：实时更新指定唯一键的数据

-- 引擎默认保证 unique key 在分区内的唯一性
-- 注：UNIQUE KEY 不支持 Nullable

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 语句来进行更新
UPDATE t6 SET amount = 10, revenue = 1000 WHERE event_time = '2020-10-29 23:40:00' and product_id=10001;

-- 查询结果中 10001 数据行进行了 UPDATE 变更
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 语句来进行更新，使用 limit 限定范围
UPDATE t6 SET category = concat('新',category) WHERE event_time = '2020-10-29 23:40:00' order by product_id limit 1;

-- 查询结果中仅 10001 数据行进行了 UPDATE 变更
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 语句交换列
UPDATE t6 SET amount=revenue, revenue=amount  WHERE event_time = '2020-10-29 23:40:00';

-- 查询结果 amount 、revenue 列进行了交换
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 │
└─────────────────────┴────────────┴─────────┴──────────┴────────┴─────────┘

例7：Bucket table

Bucket table 是 Cnch 在建表的时候的一种性能优化选项，在 Cnch 中使用 Bucket table 时，系统会依据用户建表语句中提供的一个或者多个列、表达式整理表数据，将相同值的数据聚簇在同一个 bucket number 下，从而在查询计算中获得更好的性能。

  • 非 bucket 级别唯一

当不使用 enable_bucket_level_unique_keys 指定 bucket 级别唯一时。Cnch Unique 表使用表引擎指定的去重范围，默认为分区级别唯一，此时同分区下不同 bucket 不会存在重复数据。

-- 引擎默认保证 unique key 在分区内的唯一性
-- 注：UNIQUE KEY 不支持 Nullable
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 │
└────────────┴────┴────┘
  • Bucket 级别唯一

在 bucket 级别唯一的情况下，相同分区的不同 bucket 下可能存在相同 unique key 的数据；此时 partition_level_unique_keys 用于指定 bucket 的去重范围（分区级/表级）。
如果 Cnch Unique 表为分区级别唯一，且需要在 **enable_bucket_level_unique_keys = 1 **同时达到分区级唯一的效果，则需要用户侧保证 cluster by 字段与 unique key 字段的唯一对应关系。注意：仅需要保证在cluster by里所需的列在unique key字段里都包含即可。

-- 引擎默认保证 unique key 在分区内的唯一性
-- 注：UNIQUE KEY 不支持 Nullable
CREATE TABLE t7m
(
    `d` Date,
    `id` Int32,
    `s` String
)
ENGINE = CnchMergeTree
PARTITION BY d
CLUSTER BY s 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, s;
┌──────────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');

-- 相同分区，不同 bucket 存在相同的 unique key
select * from t7m order by id, s;
┌──────────d─┬─id─┬─s──┐
│ 2023-06-26 │  1 │ 1a │
│ 2023-06-26 │  2 │ 2a │
│ 2023-06-26 │  3 │ 3c │
│ 2023-06-26 │  3 │ 3d │
└────────────┴────┴────┘

例8：部分列更新

ByteHouse云数仓版支持部分列更新模式。
在行更新模式时，缺省列采用默认值填充。而在列更新模式下，缺省列如果有原值会保留，否则填充默认值。详情请参见部分列更新。

例9：MySQL DML 对标 & dedup_key_mode

CNCH Unique 表对标并支持了 MySQL 的不同写入模式。

MySQL DML 语义

MySQL

CNCH Unique 表

DDL 示例

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

insert into
检查主键(唯一键)是否存在，如果存在则写入报错

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.

insert ignore
检查主键(唯一键)是否存在，如果存在则忽略当前写入的新数据

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;

┌─a─┬─b─┐
│ 1 │ 1 │
└───┴───┘
1 row in set (0.00 sec)

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

select * from mysql_test

┌─a─┬─b─┐
│ 1 │ 1 │
└───┴───┘
1 rows in set.

replace into
如果主键(唯一键)存在，使用写入数据进行替换；
如果主键(唯一键)不存在，效果同 insert into；

MySQL [db]> select * from mysql_test;

┌─a─┬─b─┐
│ 1 │ 1 │
└───┴───┘
1 rows in set.
1 row in set (0.00 sec)

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

Query OK, 2 rows affected (0.11 sec)

MySQL [db]> select * from mysql_test;

┌─a─┬─b─┐
│ 1 │ 2 │
└───┴───┘
1 rows in set.
1 row in set (0.00 sec)

select * from mysql_test

┌─a─┬─b─┐
│ 1 │ 1 │
└───┴───┘
1 rows in set.

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

┌─a─┬─b─┐
│ 1 │ 2 │
└───┴───┘

支持的 SQL 语句
  1. SELECT
  2. INSERT
    1. INSERT VALUES
    2. INSERT SELECT
    3. INSERT FORMAT
    4. INSERT INFILE

说明

在并发 INSERT 时的性能情况：

  1. 对于每个unique table，insert是由单线程执行的；
  2. 并发的insert将按顺序执行，因此大批量处理（insert infile）可能会耗时较长。
  1. CREATE TABLE with UNIQUE KEY

注意

此情况下包含以下限制:

  1. 只有 [U]Int8/16/32/64, Boolean, Date, DateTime, String 这些数据类型可以用作 UNIQUE KEY
  2. UNIQUE KEY不可以与 CLUSTER BY 一起使用(未来会提供支持)；
  3. 每个 String 类型的UNIQUE KEY 大小必须 <= 1 MB (此值取决于 max_string_size_for_unique_key)，否则insert 会失败.
  1. DROP TABLE
  2. DELETE TABLE
DELETE FROM [db.]table WHERE expr;
  1. UNDROP TABLE
  2. TRUNCATE TABLE
  3. ALTER TABLE DROP PARTITION
  4. OPTIMIZE TABLE
  5. RENAME TABLE
  6. UPDATE TABLE
UPDATE table_name
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...] 
    [LIMIT ...]

使用限制
  • 当前默认为分区级别去重，即 unique id 在相同分区下会实现 upsert 能力，在不同分区可能出现重复；
  • 在 Kafka 数据源导入时，用户需要保证相同唯一键的数据写入同一个的 Topic Partition，并禁用 Topic 扩容；
  • 唯一键 Unique Key 最多支持指定 10 个字段。