ClickHouse 数据插入、更新与删除操作 SQL

本文详细介绍了ClickHouse中数据插入、更新和删除的操作,包括单条与多条插入、UPDATE语句的使用、DELETE操作的执行,以及Mutation日志的查看。还探讨了EXCHANGE、OPTIMIZE、ATTACH和DETACH等高级操作,适合对ClickHouse数据管理感兴趣的读者。
摘要由CSDN通过智能技术生成

f5a290eafca4532cc7ba12bc985f2166.png


1.1.数据****操作

数据操作语言( DML,Data Manipulation Language) 用于在数据库表中添加(插入)、删除和修改(更新)数据。本节主要介绍ClickHouse中的数据插入、更新与删除操作。

1.1.1.概述

数据操作语言DML,包括SQL数据更改语句,它修改存储的数据,但不修改数据模型,例如数据库模式或数据库表结构。DML语言常见的语法模式如下:

INSERT INTO ... VALUES ...
UPDATE ... SET ... WHERE ...
DELETE FROM ... WHERE ...

但是,在 ClickHouse 中,UPDATE 与DELETE 是设计在了 ALTER 指令体系中的。

1.1.2.插入****数据

一次一条数据插入
INSERT INTO clickhouse_tutorial.user_tag (user_id, gender, age, active_level, date)
VALUES (1, 'male', '18', '1', '2022-03-21');
INSERT INTO clickhouse_tutorial.user_tag (user_id, gender, age, active_level, date)
VALUES (2, 'female', '16', '2', '2022-03-21');
一次多条数据插入
INSERT INTO clickhouse_tutorial.user_tag (user_id, gender, age, active_level, date)
VALUES (3, 'female', '20', '3', '2022-03-21'),
       (4, 'female', '22', '4', '2022-03-21');
插入SELECT查询返回数据
INSERT INTO clickhouse_tutorial.user_tag
(UserID, WatchID, EventTime, Sex, Age, OS, RegionID, RequestNum, EventDate)
SELECT
    UserID,
    WatchID,
    EventTime,
    Sex,
    Age,
    OS,
    RegionID,
    RequestNum,
    EventDate
FROM tutorial.hits_v1
 
Query id: bfed9d12-b838-4125-9ee2-f61049bf0a56
 
↙ Progress: 30.91 million rows, 835.62 MB (6.66 million rows/s., 180.00 MB/s.) (0.0 CPU, 172.08 MB RAM)
████████████████████████████████████████████████ 99%
 
Ok.
 
0 rows in set. Elapsed: 7.552 sec. Processed 53.24 million rows, 1.37 GB (7.05 million rows/s., 180.96 MB/s.)

1.1.3.UPDATE更新数据

语法
ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] WHERE filter_expr
功能说明

更新表数据。ClickHouse 中ALTER TABLE 前缀种语法与大多数其他支持 SQL 的数据库系统不同。它旨在表明,与 OLTP 数据库中的类似查询不同,这是一项并非为频繁使用而设计的繁重操作。ALTER 查询是通过一种称为“突变”(Mutation)的机制实现的。

关于 ALTERTABLE … UPDATE命令,详细说明如下:

1.WHERE子句中的过滤表达式filter_expr的值是UInt8类型,指定要更新的数据行。

2.不支持更新用于计算主键或分区键的列。

3.一个UPDATE操作可以包含多个用逗号分隔的命令,例如column1 = expr1,column2= expr2。 UPDATE操作数据处理是同步还是异步,由系统配置项 mutation_sync 设置,可取值为:

0 - execute asynchronously。

1 - wait current server。

2 - wait all replicas if they exist。默认为0,异步后台进程执行,类似于 *MergeTree 表中的合并操作。

4.对于 *MergeTree 表,Mutation操作通过重写整个数据Part来执行,Mutation不具备原子性。数据 Part一旦准备好就被MutationPart替换,并且在Mutation执行期间,SELECT查询结果中可以看到,来自已经变异Part的数据,以及来自尚未变异Part的数据。

5.Mutation按照创建顺序排序,并按该顺序应用于每个MutationPart。

6.在Mutation提交之前插入到表中的数据会被执行Mutation操作,提交之后插入的数据不会执行Mutation操作。

7.Mutation操作不会阻塞数据插入。

8.可以查看 system.mutations 表跟踪突变的进度。

9.即使重新启动 ClickHouse 服务器,成功提交的变更仍将继续执行。一旦提交,就无法回滚突变。

10.如果Mutation由于某种原因被卡住,可以使用 KILL MUTATION 查询取消它。

11.已经完成Mutation的条目不会立即删除。保留条目的数量由 finished_mutations_to_keep 存储引擎参数确定。

12.在系统配置表system.settings中,有关mutation的配置项如下:

SELECT *
FROM system.settings
WHERE name LIKE '%mutation%'
FORMAT Vertical
 
Query id: 24f6ca70-7117-41c5-bc3e-dd6615d5ee6d
 
Row 1:
──────
name:        background_merges_mutations_concurrency_ratio
value:       2
changed:     0
description: Ratio between a number of how many operations could be processed and a number threads to process them. Only has meaning at server startup.
min:         ????
max:         ????
readonly:    0
type:        Float
 
Row 2:
──────
name:        mutations_sync
value:       0
changed:     0
description: Wait for synchronous execution of ALTER TABLE UPDATE/DELETE queries (mutations). 0 - execute asynchronously. 1 - wait current server. 2 - wait all replicas if they exist.
min:         ????
max:         ????
readonly:    0
type:        UInt64
 
Row 3:
──────
name:        allow_nondeterministic_mutations
value:       0
changed:     0
description: Allow non-deterministic functions in ALTER UPDATE/ALTER DELETE statements
min:         ????
max:         ????
readonly:    0
type:        Bool
 
3 rows in set. Elapsed: 0.003 sec.
实例讲解
1、更新之前的数据
SELECT
    WatchID,
    JavaEnable,
    GoodEvent
FROM tutorial.hits_v1
WHERE WatchID = 7043438415214026105
 
Query id: e0dc9ae5-8f24-48e5-a56d-d107afa1dfe3
 
┌─────────────WatchID─┬─JavaEnable─┬─GoodEvent─┐
│ 7043438415214026105 │          1 │         1 │
└─────────────────────┴────────────┴───────────┘
Clickhouse中,更新数据的方式主要有两种:MUTATION操作和使用特定的MergeTree引擎。 首先,MUTATION操作是通过ALTER TABLE语句来实现数据更新删除。然而,MUTATION操作是一种较重的操作,与标准的SQL语法中的UPDATE和DELETE不同,它是异步执行的。因此,MUTATION操作更适用于批量数据不频繁更新删除的场景。你可以参考中提供的链接了解更多关于MUTATION操作的信息。 其次,Clickhouse通过使用CollapsingMergeTree、VersionedCollapsingMergeTree和ReplacingMergeTree等特定的MergeTree引擎,来实现数据更新删除。 CollapsingMergeTree、VersionedCollapsingMergeTree和ReplacingMergeTree都是通过INSERT语句插入最新的数据,新数据会“抵消”或“替换”掉老数据。但是,这些操作都是在数据文件后台Merge时发生的,也就是说,在Merge之前,新数据和老数据会同时存在。因此,在查询时需要注意避免查询到老数据。 使用CollapsingMergeTree和VersionedCollapsingMergeTree需要标记位字段和版本字段,而使用ReplacingMergeTree则更加方便。你可以参考中提供的Clickhouse官方文档链接了解如何使用CollapsingMergeTree和VersionedCollapsingMergeTree。 综上所述,你可以根据具体业务需求选择合适的方式来实现Clickhouse中的数据更新删除操作。请参考和提供的链接获取更详细的信息。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* *3* [Clickhouse如何实现数据更新](https://blog.csdn.net/shuyun123456789/article/details/122726982)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v92^chatsearchT0_1"}}] [.reference_item style="max-width: 50%"] - *2* [ClickHouse 数据插入更新删除操作 SQL](https://blog.csdn.net/m0_67401055/article/details/126098952)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v92^chatsearchT0_1"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值