sql-update - clickhouse如何保证每个pk（排序键）一个数据行？

问题描述

我正在努力使用 clickhouse 来保持每个 PK 的唯一数据行。

我选择这个 Column base DB 来快速表达统计数据并且对其速度非常满意。但是，这里有一些重复的数据问题。

测试表看起来像......

CREATE TABLE test2 (
    `uid`                String COMMENT 'User ID',
    `name`              String COMMENT 'name'
) ENGINE ReplacingMergeTree(uid)
ORDER BY uid
PRIMARY KEY uid;

假设我将使用此表来连接显示名称（name此表中的字段）。但是，我可以根据需要在同一个 PK（排序键）中插入许多数据。

例如

INSERT INTO test2
(uid, name) VALUES ('1', 'User1');
INSERT INTO test2
(uid, name) VALUES ('1', 'User2');
INSERT INTO test2
(uid, name) VALUES ('1', 'User3');
SELECT * FROM test2 WHERE uid = '1';

现在，我可以看到 3 行具有相同的排序键。有什么方法可以使密钥唯一，至少，如果密钥存在，则阻止插入？

让我们考虑以下场景

表格和数据是

CREATE TABLE blog (
    `blog_id` String,
    `blog_writer` String
) ENGINE MergeTree
ORDER BY tuple();


CREATE TABLE statistics (
    `date` UInt32,
    `blog_id` String,
    `read_cnt` UInt32,
    `like_cnt` UInt32
) ENGINE MergeTree
ORDER BY tuple();


INSERT INTO blog (blog_id, blog_writer) VALUES ('1', 'name1');
INSERT INTO blog (blog_id, blog_writer) VALUES ('2', 'name2');

INSERT INTO statistics(date, blog_id, read_cnt, like_cnt) VALUES (202007, '1', 10, 20);
INSERT INTO statistics(date, blog_id, read_cnt, like_cnt) VALUES (202008, '1', 20, 0);
INSERT INTO statistics(date, blog_id, read_cnt, like_cnt) VALUES (202009, '1', 3, 1);
INSERT INTO statistics(date, blog_id, read_cnt, like_cnt) VALUES (202008, '2', 11, 2);

这是求和查询

SELECT
    b.writer,
    a.read_sum,
    a.like_sum
FROM
     (
         SELECT
            blog_id,
            SUM(read_cnt) as read_sum,
            SUM(like_cnt) as like_sum
         FROM statistics
         GROUP BY blog_id
     ) a JOIN
     (
         SELECT blog_id, blog_writer as writer FROM blog
     ) b
    ON a.blog_id = b.blog_id;

目前它工作正常，但如果出现新的低点

INSERT INTO statistics(date, blog_id, read_cnt, like_cnt) VALUES (202008, '1', 60, 0);

我期望的是更新低，“name1”的总和read_sum是73. 但它显示93，因为它允许重复插入。

有没有办法

防止重复插入
或在表中设置唯一的保证 PK

谢谢。

标签： sql-updateuniqueprimary-keyclickhouse

想到的一件事是ReplacingMergeTree。它不会立即保证没有重复，但它最终会这样做。正如文档所述：

重复数据删除仅在合并期间发生。合并在未知时间在后台发生，因此您无法计划。一些数据可能仍未处理。

我个人使用的另一种方法是引入另一个名为的列，例如_ts- 插入行时的时间戳。这使您可以跟踪更改，并且借助 clickhouse 的美观limit by功能，您可以轻松获取给定 pk 的最新版本。

CREATE TABLE test2 (
    `uid`               String COMMENT 'User ID',
    `name`              String COMMENT 'name',
    `_ts`               DateTime
) ENGINE MergeTree(uid)
ORDER BY uid;

选择看起来像这样：

SELECT uid, name FROM test2 ORDER BY _ts DESC LIMIT 1 BY uid;

实际上，您不需要 pk，只需指定limit by您需要行唯一的任何行/行。

sql-update - clickhouse如何保证每个pk（排序键）一个数据行？

问题描述

解决方案

推荐阅读