能否从clickhouse表中删除旧记录？

Question

据我所知，ClickHouse仅允许插入新数据。但是否可以删除早于某个时期的数据块以避免硬盘溢出？

- Stepan Yakovenko

---

考虑使用CH的TTL功能来自动删除旧记录。 - vladimir

Answer 1

轻量级删除

自v22.8版本起可用

MergeTree表的标准DELETE语法已经在#37893中引入。

SET allow_experimental_lightweight_delete = 1;
DELETE FROM merge_table_standard_delete WHERE id = 10;

使用Mutations更改数据

请参阅Mutations功能的文档：https://clickhouse.yandex/docs/en/query_language/alter/#mutations。
该功能于2018年第三季度实现。

删除数据

ALTER TABLE <table> DELETE WHERE <filter expression>

"脏"删除全部

您总是需要指定一个过滤表达式。如果您想通过Mutation删除所有数据，请指定永远为真的内容，例如：

ALTER TABLE <table> DELETE WHERE 1=1

更新数据

同样可以通过类似的方式进行变异 (UPDATE)

ALTER TABLE <table> UPDATE column1 = expr1 [, ...] WHERE <filter expression>

注意异步执行

请注意，上述所有命令不会直接执行数据突变操作（同步）。相反，它们安排在后台独立执行的ClickHouse Mutation（异步）。这就是选择ALTER TABLE语法而不是典型的SQL UPDATE/DELETE的原因。您可以通过...

SELECT *
FROM system.mutations
WHERE is_done = 0

...除非

您将mutations_sync设置更改为：

• 1，这样它会同步等待当前服务器
• 2，这样它会等待所有副本

不使用Mutations更改数据

有一个TRUNCATE TABLE语句，其语法如下：

TRUNCATE TABLE [IF EXISTS] [db.]name [ON CLUSTER cluster]

这会同步截断表格。它会检查表格大小，如果表格大小超过max_table_size_to_drop，则不允许您删除。请参阅此处的文档：

https://clickhouse.tech/docs/en/sql-reference/statements/truncate/

- simPod

---

我一直在使用TRUNCATE TABLE <table>而不是delete where 1=1，因为前者可以同步地从表中删除所有数据而不添加任何变异。 - Tez

将 mutations_sync 更改为 1 可以使删除/更新同步。详见 https://clickhouse.tech/docs/en/operations/settings/settings/#mutations_sync - XX 吕

Answer 2

创建和删除分区的示例

    CREATE TABLE test.partitioned_by_month(d Date, x UInt8) ENGINE = MergeTree 

PARTITION BY toYYYYMM(d) ORDER BY x;

    INSERT INTO test.partitioned_by_month VALUES ('2000-01-01', 1), ('2000-01-02', 2), ('2000-01-03', 3);

INSERT INTO test.partitioned_by_month VALUES ('2000-02-03', 4), ('2000-02-03', 5);

INSERT INTO test.partitioned_by_month VALUES ('2000-03-03', 4), ('2000-03-03', 5);

SELECT * FROM test.partitioned_by_month;

---d------------|-------x-----

 2000-02-03 | 4 

 2000-02-03 | 5 


---d------------|-------x-----

 2000-03-03 | 4 

 2000-03-03 | 5 

---d------------|-------x-----

 2000-01-01 | 1 

 2000-01-02 | 2

 2000-01-03 | 3 

ALTER TABLE test.partitioned_by_month DROP PARTITION 200001;

select * from partitioned_by_month;


---d------------|-------x-----

 2000-03-03 | 4 

 2000-03-03 | 5 

---d------------|-------x-----


 2000-02-03 | 4 

 2000-02-03 | 5 

- Dipesh Maurya

Answer 3

Clickhouse没有像MySQL数据库那样的更新/删除功能。但是我们仍然可以通过组织分区中的数据来进行删除。我不知道您如何管理数据，因此在此举一个例子，例如将数据按月份进行存储。

使用“DROP PARTITION”命令，您可以通过删除该月份的分区来删除该月份的数据。这里是如何删除分区的完整说明 https://clickhouse.yandex/blog/en/how-to-update-data-in-clickhouse。

- Shishir Mudliyar