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