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

---

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