ClickHouse学习小记--数据库表相关及其操作

章节目录

ClickHouse的数据操作
- 一、数据库
- - 1、创建数据库
  - 2、查询数据库
  - 3、删除数据库
- 二、数据表
- - 1、创建表
  - - 1.1、常规定义方法
    - 1.2、复制其他表的结构
    - 1.3、通过SELECT子句的形式创建
  - 2、删除表
  - 3、默认值表达式
  - 4、临时表
  - 5、分区表
  - 6、视图
  - - 1、创建普通视图
    - 2、创建物化视图
  - 7、数据表基本操作
  - - 7.1、追加新字段
    - 7.2、修改数据类型（MODIFY COLUMN）
    - 7.3、修改备注（COMMENT COLUMN）
    - 7.4、删除已有字段（DROP COLUMN ）
    - 7.5、移动数据表（RENAME语句）
    - 7.6、清空数据表
  - 8、数据分区基本操作
  - - 8.1、查询数据分区
    - 8.2、删除指定分区
    - 8.3、复制分区数据（REPLACE PARTITION）
    - 8.4、卸载、装载分区
    - 8.5、备份、还原分区
  - 9、分布式DDL执行（ON CLUSTER ）
  - 10、数据写入
  - - 10.1、使用VALUES格式的常规语法（VALUES）
    - 10.2、使用指定格式的语法（FORMAT [ ]）
    - 10.3、使用SELECT子句形式的语法（SELCET子句）
  - 11、数据删除、修改（DELETE、UPDATE）
  - - 11.1、删除语句（DELETE）完整语法：
    - 11.2、修改语句（UPDATE）完整语法
- 小结
- - - 数据库引擎
    - 数据库相关语法：
    - 数据表相关语法：
    - - 1）创建表
        
        2）删除表
        
        3）默认值表达式
        
        4）临时表
        
        5）分区表
    - 视图
    - - 1）普通视图（）
      - 2）物化视图
    - 数据表常用操作
    - - 1）追加新字段（ADD COLUMN）
        
        2）修改数据类型（MODIFY COLUMN）
        
        3）修改备注（COMMENT COLUMN）
        
        4）删除已有字段（DROP COLUM）
        
        5）移动表数据（RENAME语句）
        
        6）清空数据表（TRUNCATE TABLE）

DDL操作及定义数据的方法。DDL查询提供了数据表的创建、修改和删除操作，是最常用的功能之一。

ClickHouse的数据操作

一、数据库

数据库起到了命名空间的作用，可以有效规避命名冲突的问题，也为后续的数据隔离提供了支撑。任何一张数据表，都必须归属在某个数据库之下。创建数据库的完整语法如下所示：

CREATE DATABASE IF NOT EXISTS db_name [ENGINE = engine]

其中，IF NOT EXISTS表示如果已经存在一个同名的数据库，则会忽略后续的创建过程；[ENGINE=engine]表示数据库所使用的引擎类型（数据库也支持设置引擎）。

数据库目前一共支持5种引擎，如下：

·Ordinary：

默认引擎，在绝大多数情况下我们都会使用默认引擎，使用时无须刻意声明。在此数据库下可以使用任意类型的表引擎。

·Dictionary：

字典引擎，此类数据库会自动为所有数据字典创建它们的数据表。

·Memory：

内存引擎，用于存放临时数据。此类数据库下的数据表只会停留在内存中，不会涉及任何磁盘操作，当服务重启后数据会被清除。

·Lazy：

日志引擎，此类数据库下只能使用Log系列的表引擎。

·MySQL：

MySQL引擎，此类数据库下会自动拉取远端MySQL中的数据，并为它们创建MySQL表引擎的数据表。

1、创建数据库

在绝大多数情况下都只需使用默认的数据库引擎。例如执行下面的语句，即能够创建属于我们的第一个数据库：

CREATE DATABASE DB_TEST

默认数据库的实质是物理磁盘上的一个文件目录，所以在语句执行之后，ClickHouse便会在安装路径下创建DB_TEST数据库的文件目录：

# pwd 
/chbase/data 
# ls 
DB_TEST default system

与此同时，在metadata路径下也会一同创建用于恢复数据库的DB_TEST.sql文件：

# pwd 
/chbase/data/metadata 
# ls 
DB_TEST DB_TEST.sql default system

2、查询数据库

使用SHOW DATABASES查询，即能够返回ClickHouse当前的数据库列表：

SHOW DATABASES

name
DB_TEST
default
system

3、删除数据库

使用USE查询可以实现在多个数据库之间进行切换，而通过SHOW TABLES查询可以查看当前数据库的数据表列表。删除一个数据库，则需要用到下面的DROP查询。

DROP DATABASE [IF EXISTS] db_name

二、数据表

ClickHouse数据表的定义语法，是在标准SQL的基础之上建立的。

1、创建表

ClickHouse目前提供了三种最基本的建表方法。

1.1、常规定义方法

它的完整语法如下所示：

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name 
( name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr], 
  name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
  省略… )
ENGINE = engine

使用[db_name.]参数可以为数据表指定数据库，如果不指定此参数，则默认会使用default数据库。例如执行下面的语句：

CREATE TABLE hits_v1 
( Title String, URL String ,
  EventTime DateTime ) 
ENGINE = Memory;

上述语句将会在default默认的数据库下创建一张内存表。注意末尾的ENGINE参数，它被用于指定数据表的引擎。表引擎决定了数据表的特性，也决定了数据将会被如何存储及加载。例如示例中的Memory表引擎，是ClickHouse最简单的表引擎，数据只会被保存在内存中，在服务重启时数据会丢失。

1.2、复制其他表的结构

具体语法如下所示：

CREATE TABLE 
[IF NOT EXISTS] [db_name1.]table_name 
AS 
[db_name2.] table_name2 
[ENGINE = engine]

这种方式支持在不同的数据库之间复制表结构，ENGINE表引擎可以与原表不同。例如下面的语句：

--创建新的数据库
CREATE DATABASE IF NOT EXISTS new_db 
--将default.hits_v1的结构复制到new_db.hits_v1 
CREATE TABLE IF NOT EXISTS new_db.hits_v1 
AS 
default.hits_v1 
ENGINE = TinyLog

1.3、通过SELECT子句的形式创建

它的完整语法如下：

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name 
ENGINE = engine AS SELECT …

在这种方式下，不仅会根据SELECT子句建立相应的表结构，同时还会将SELECT子句查询的数据顺带写入，例如执行下面的语句：

CREATE TABLE IF NOT EXISTS hits_v1_1 
ENGINE = Memory AS SELECT * FROM hits_v1

2、删除表

上述语句会将SELECT * FROM hits_v1的查询结果一并写入数据表。ClickHouse和大多数数据库一样，使用DESC查询可以返回数据表的定义结构。如果想删除一张数据表，则可以使用下面的DROP语句：

DROP TABLE [IF EXISTS] [db_name.]table_name

3、默认值表达式

表字段支持三种默认值表达式的定义方法，分别是DEFAULT、 MATERIALIZED、ALIAS。无论使用哪种形式，表字段一旦被定义了默认值，它便不再强制要求定义数据类型，因为ClickHouse会根据默认值进行类型推断。如果同时对表字段定义了数据类型和默认值表达式，则以明确定义的数据类型为主，例如下面的例子：

CREATE TABLE dfv_v1 
( id String, 
  c1 DEFAULT 1000, 
  c2 String DEFAULT c1 ) 
ENGINE = TinyLog

c1字段没有定义数据类型，默认值为整型1000；c2字段定义了数据类型和默认值，且默认值等于c1，现在写入测试数据：

INSERT INTO dfv_v1(id) VALUES ('A000')

在写入之后执行以下查询：

SELECT c1, c2, toTypeName(c1), toTypeName(c2) from dfv_v1

c1	c2	toTypeName(c1)	toTypeName(c2)
1000	1000	UInt16	String

由此，默认值的优先级符合我们的预期，其中c1字段根据默认值被推断为UInt16；而c2字段由于同时定义了数据类型和默认值，所以它最终的数据类型来自明确定义的String。

默认值表达式的三种定义方法之间也存在着不同之处，有如下比较。

（1）数据写入：在数据写入时，只有DEFAULT类型的字段可以出现在INSERT语句中。而MATERIALIZED和ALIAS都不能被显式赋值，它们只能依靠计算取值。例如试图为MATERIALIZED类型的字段写入数据，将会得到如下的错误。

DB::Exception: Cannot insert column URL, because it is MATERIALIZED column..

（2）数据查询：在数据查询时，只有DEFAULT类型的字段可以通过SELECT *返回。而MATERIALIZED、ALIAS类型的字段不会出现在SELECT *查询的返回结果集中。

（3）数据存储：在数据存储时，只有DEFAULT和 MATERIALIZED类型的字段才支持持久化。如果使用的表引擎支持物理存储（例如TinyLog表引擎），那么这些列字段将会拥有物理存储。而ALIAS类型的字段不支持持久化，它的取值总是需要依靠计算产生，数据不会落到磁盘。可以使用ALTER语句修改默认值，例如：

ALTER TABLE [db_name.]table MODIFY COLUMN col_name DEFAULT value

修改动作并不会影响数据表内先前已经存在的数据。但是默认值的修改有诸多限制，例如在合并树表引擎中，它的主键字段是无法被修改的；而某些表引擎则完全不支持修改（例如TinyLog）。

4、临时表

ClickHouse也有临时表的概念，创建临时表的方法是在普通表的基础之上添加TEMPORARY关键字，它的完整语法如下所示：

CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name 
( name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr], 
name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr], )

相比普通表而言，临时表有如下两点特殊之处：

它的生命周期是会话绑定的，所以它只支持Memory表引擎，如果会话结束，数据表就会被销毁；
临时表不属于任何数据库，所以在它的建表语句中，既没有数据库参数也没有表引擎参数。

针对第二个特殊项，读者心中难免会产生一个疑问：既然临时表不属于任何数据库，如果临时表和普通表名称相同，会出现什么状况呢？首先在DEFAULT数据库创建测试表并写入数据：

CREATE TABLE tmp_v1 
( title String ) 
ENGINE = Memory; 

INSERT INTO tmp_v1 VALUES ('click')

接着创建一张名称相同的临时表并写入数据：

CREATE TEMPORARY TABLE tmp_v1 
(createtime Datetime) 

INSERT INTO tmp_v1 VALUES (now())

# 现在查询tmp_v1
SELECT * FROM tmp_v1

createtime
2019-08-30 10:20:29

通过返回结果可以得出结论：临时表的优先级是大于普通表的。当两张数据表名称相同的时候，会优先读取临时表的数据。

在ClickHouse的日常使用中，通常不会刻意使用临时表。它更多被运用在ClickHouse的内部，是数据在集群间传播的载体。

5、分区表

数据分区（partition）和数据分片（shard）是完全不同的两个概念。

数据分区：针对本地数据而言的，是数据的一种纵向切分。
数据分片是数据的一种横向切分。

数据分区：借助数据分区，在后续的查询过程中能够跳过不必要的数据目录。
合理地利用分区特性，还可以变相实现数据的更新操作，数据分区支持删除、替换和重置操作。假设数据表按照月份分区，那么数据就可以按月份的粒度被替换更新。
分区虽好，但不是所有的表引擎都可以使用这项特性，目前只有合并树（MergeTree）家族系列的表引擎才支持数据分区。接下来通过一个简单的例子演示分区表的使用方法。首先由PARTITION BY指定分区键，例如下面的数据表partition_v1使用了日期字段作为分区键，并将其格式化为年月的形式：

CREATE TABLE partition_v1 
( ID String, URL String,
  EventTime Date ) 
ENGINE = MergeTree() 
PARTITION BY toYYYYMM(EventTime) 
ORDER BY ID

接着写入不同月份的测试数据：

INSERT INTO partition_v1 VALUES ('A000','', '2019-05-01'), ('A001','', '2019-06-02')

最后通过系统表，查询数据表的分区状态：

SELECT table,partition,path from system.parts WHERE table = 'partition_v1'

table	partition	path
partition_v1	201905	/chbase/data/default/partition_v1/201905_1_1_0/
partition_v1	201906	/chbase/data/default/partition_v1/201906_2_2_0/

partition_v1按年月划分后，目前拥有两个数据分区，且每个分区都对应一个独立的文件目录，用于保存各自部分的数据。

合理设计分区键非常重要，通常会按照数据表的查询场景进行针对性设计。例如在刚才的示例中数据表按年月分区，如果后续的查询按照分区键过滤，例如：

SELECT * FROM partition_v1 WHERE EventTime ='2019-05-01'

那么在后续的查询过程中，可以利用分区索引跳过6月份的分区目录，只加载5月份的数据，从而带来查询的性能提升。当然，使用不合理的分区键也会适得其反，分区键不应该使用度过细的数据字段。例如，按照小时分区，将会带来分区数量的急剧增长，从而导致性能下降。

6、视图

ClickHouse拥有普通和物化两种视图，其中物化视图拥有独立的存储，而普通视图只是一层简单的查询代理。

1、创建普通视图

完整语法如下所示：

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name AS SELECT ...

普通视图不会存储任何数据，它只是一层单纯的SELECT查询映射，起着简化查询、明晰语义的作用，对查询性能不会有任何增强。

假设有一张普通视图view_tb_v1，它是基于数据表tb_v1创建的，那么下面的两条SELECT查询是完全等价的：

-- 普通表
SELECT * FROM tb_v1 
-- tb_v1的视图
SELECT * FROM view_tb_v1

2、创建物化视图

物化视图支持表引擎，数据保存形式由它的表引擎决定，创建物化视图的完整语法如下所示：

CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...

物化视图创建好之后，如果源表被写入新数据，那么物化视图也会同步更新。POPULATE修饰符决定了物化视图的初始化策略：如果使用了POPULATE修饰符，那么在创建视图的过程中，会连带将源表中已存在的数据一并导入，如同执行了SELECT INTO一般；反之，如果不使用POPULATE修饰符，那么物化视图在创建之后是没有数据的，它只会同步在此之后被写入源表的数据。

物化视图目前并不支持同步删除，如果在源表中删除了数据，物化视图的数据仍会保留。

物化视图本质是一张特殊的数据表，例如使用SHOW TABLE查看数据表的列表：

SHOW TABLES

name
.inner.view_test2
.inner.view_test3

物化视图也在其中，它们是使用了.inner.特殊前缀的数据表，所以删除视图的方法是直接使用DROP TABLE查询，如：DROP TABLE view_name

7、数据表基本操作

数据表的基本操作目前只有MergeTree、Merge和Distributed这三类表引擎支持ALTER查询。

7.1、追加新字段

假如需要对一张数据表追加新的字段，可以使用如下语法：

ALTER TABLE tb_name ADD COLUMN [IF NOT EXISTS] name [type] [default_expr] [AFTER name_after]

例如，在数据表的末尾增加新字段：

ALTER TABLE testcol_v1 ADD COLUMN OS String DEFAULT 'mac'

或是通过AFTER修饰符，在指定字段的后面增加新字段：

ALTER TABLE testcol_v1 ADD COLUMN IP String AFTER ID

对于数据表中已经存在的旧数据而言，新追加的字段会使用默认值补全。

7.2、修改数据类型（MODIFY COLUMN）

如果需要改变表字段的数据类型或者默认值，需要使用下面的语法：

ALTER TABLE tb_name MODIFY COLUMN [IF EXISTS] name [type] [default_expr]

修改某个字段的数据类型，实质上会调用相应的toType转型方法。如果当前的类型与期望的类型不能兼容，则修改操作将会失败。

例如，将String类型的IP字段修改为IPv4类型是可行的：

ALTER TABLE testcol_v1 MODIFY COLUMN IP IPv4

而尝试将String类型转为UInt类型就会出现错误：

ALTER TABLE testcol_v1 MODIFY COLUMN OS UInt32 
# 结果
DB::Exception: Cannot parse string 'mac' as UInt32: syntax error at begin of string.

7.3、修改备注（COMMENT COLUMN）

养成良好的编程习惯，做好信息备注。追加备注的语法如下所示：

ALTER TABLE tb_name COMMENT COLUMN [IF EXISTS] name 'some comment'

例如，为ID字段增加备注：

ALTER TABLE testcol_v1 COMMENT COLUMN ID '主键ID'

使用DESC查询可以看到上述增加备注的操作已经生效：
DESC testcol_v1

name	type	comment
ID	String	主键ID

7.4、删除已有字段（DROP COLUMN ）

假如要删除某个字段，可以使用下面的语句：

ALTER TABLE tb_name DROP COLUMN [IF EXISTS] name

例如，执行下面的语句删除URL字段：

ALTER TABLE testcol_v1 DROP COLUMN URL

上述列字段在被删除之后，它的数据也会被连带删除。进一步来到testcol_v1的数据目录查验，会发现URL的数据文件已经被删除了：

# pwd 
/chbase/data/data/default/testcol_v1/201907_2_2_0 
# ll total 56 
-rw-r-----. 1 clickhouse clickhouse 28 Jul 2 21:02  
-rw-r-----. 1 clickhouse clickhouse 30 Jul 2 21:02  
-rw-r-----. 1 clickhouse clickhouse 30 Jul 2 21:02  
-rw-r-----. 1 clickhouse clickhouse 30 Jul 2 21:02 
省略…

7.5、移动数据表（RENAME语句）

在Linux系统中，mv命令的本意是将一个文件从原始位置A移动到目标位置B，但是如果位置A与位置B相同，则可以变相实现重命名的作用。ClickHouse的RENAME查询就与之有着异曲同工之妙。
RENAME语句的完整语法如下所示：

RENAME TABLE [db_name11.]tb_name11 TO [db_name12.]tb_name12, [db_name21.]tb_name21 TO [db_name22.]tb_name22, ...

RENAME可以修改数据表的名称，如果将原始数据库与目标数据库设为不同的名称，那么就可以实现数据表在两个数据库之间移动的效果。例如在下面的例子中，testcol_v1从default默认数据库被移动到了db_test数据库，同时数据表被重命名为testcol_v2：

 RENAME TABLE default.testcol_v1 TO db_test.testcol_v2

需要注意的是，数据表的移动只能在单个节点的范围内。换言之，数据表移动的目标数据库和原始数据库必须处在同一个服务节点内，而不能是集群中的远程节点。

7.6、清空数据表

假设需要将表内的数据全部清空，而不是直接删除这张表，则可以使用TRUNCATE语句，它的完整语法如下所示：

TRUNCATE TABLE [IF EXISTS] [db_name.]tb_name

例如执行下面的语句，就能将db_test.testcol_v2的数据一次性清空：

TRUNCATE TABLE db_test.testcol_v2

8、数据分区基本操作

目前只有MergeTree系列的表引擎支持数据分区。

8.1、查询数据分区

ClickHouse内置了许多system系统表，用于查询自身的状态信息。其中parts系统表专门用于查询数据表的分区信息。例如执行下面的语句，就能够得到数据表partition_v2的分区状况：

SELECT partition_id,name,table,database FROM system.parts WHERE table = 'partition_v2'

partition_id	name	table	database
201905	201905_1_1_0_6	partition_v2	default

如上，目前partition_v2共拥有1个分区，其中partition_id或者name等同于分区的主键，可以基于它们的取值确定一个具体的分区。

8.2、删除指定分区

删除一个指定分区的语法如下所示：

ALTER TABLE tb_name DROP PARTITION partition_expr

假如现在需要更新partition_v2数据表整个7月份的数据，则可以先将7月份的分区删除：

ALTER TABLE partition_v2 DROP PARTITION 201907

然后将整个7月份的新数据重新写入，就可以达到更新的目的：

INSERT INTO partition_v2 VALUES ('A004-update','', '2019-07-02'),…

8.3、复制分区数据（REPLACE PARTITION）

ClickHouse支持将A表的分区数据复制到B表，这项特性可以用于快速数据写入、多表间数据同步和备份等场景，它的完整语法如下：

ALTER TABLE B REPLACE PARTITION partition_expr FROM A

不过需要注意的是，并不是任意数据表之间都能够相互复制，它们还需要满足两个前提条件：

两张表需要拥有相同的分区键；
它们的表结构完全相同。

假设数据表partition_v2与先前的partition_v1分区键和表结构完全相同，那么应先在partition_v1中写入一批8月份的新数据：

INSERT INTO partition_v1 VALUES ('A006-v1','', '2019-08-05'),('A007- v1','', '2019-08-20')

再执行下面的语句：

ALTER TABLE partition_v2 REPLACE PARTITION 201908 FROM partition_v1

即能够将partition_v1的整个201908分区中的数据复制到

partition_v2： SELECT * from partition_v2 ORDER BY EventTime

8.4、卸载、装载分区

表分区可以通过DETACH语句卸载，分区被卸载后，它的物理数据并没有删除，而是被转移到了当前数据表目录的detached子目录下。
而装载分区则是反向操作，它能够将detached子目录下的某个分区重新装载回去。卸载与装载这一对伴生的操作，常用于分区数据的迁移和备份场景。

1）卸载某个分区（DETACH PARTITION ）语法如下所示：

ALTER TABLE tb_name DETACH PARTITION partition_expr

例如，执行下面的语句能够将partition_v2表内整个8月份的分区卸载：

ALTER TABLE partition_v2 DETACH PARTITION 201908

此时再次查询这张表，会发现其中2019年8月份的数据已经没有了。而进入partition_v2的磁盘目录，则可以看到被卸载的分区目录已经被移动到了detached目录中。

一旦分区被移动到了detached子目录，就代表它已经脱离了ClickHouse的管理，ClickHouse并不会主动清理这些文件。这些分区文件会一直存在，除非我们主动删除或者使用ATTACH语句重新装载它们。

2）装载某个分区（ATTACH PARTITION）完整语法如下所示：

ALTER TABLE tb_name ATTACH PARTITION partition_expr

再次执行下面的语句，就可以将刚才已被卸载的201908分区重新装载回去：

ALTER TABLE partition_v2 ATTACH PARTITION 201908

8.5、备份、还原分区

关于分区数据的备份，可以通过FREEZE与FETCH实现。

9、分布式DDL执行（ON CLUSTER ）

ClickHouse支持集群模式，一个集群拥有1到多个节点。
CREATE、ALTER、DROP、RENMAE及TRUNCATE这些DDL语句，都支持分布式执行。这意味着，如果在集群中任意一个节点上执行DDL语句，那么集群中的每个节点都会以相同的顺序执行相同的语句。这项特性意义非凡，它就如同批处理命令一样，省去了需要依次去单个节点执行DDL的烦恼。

将一条普通的DDL语句转换成分布式执行十分简单，只需加上ON CLUSTER cluster_name声明即可。例如，执行下面的语句后将会对ch_cluster集群内的所有节点广播这条DDL语句：

CREATE TABLE partition_v3 ON CLUSTER ch_cluster
( ID String, URL String, EventTime Date ) 
ENGINE = MergeTree() 
PARTITION BY toYYYYMM(EventTime) 
ORDER BY ID

当然，如果现在执行这条语句是不会成功的。因为到目前为止还没有配置过ClickHouse的集群模式，目前还不存在一个名为ch_cluster的集群。

10、数据写入

INSERT语句支持三种语法范式，三种范式各有不同，可以根据写入的需求灵活运用。

10.1、使用VALUES格式的常规语法（VALUES）

INSERT INTO [db.]table [(c1, c2, c3…)] VALUES (v11, v12, v13…), (v21, v22, v23…), ...

其中，c1、c2、c3是列字段声明，可省略。VALUES后紧跟的是由元组组成的待写入数据，通过下标位与列字段声明一一对应。数据支持批量声明写入，多行数据之间使用逗号分隔。

在使用VALUES格式的语法写入数据时，支持加入表达式或函数，例如：

INSERT INTO partition_v2 VALUES ('A0014',toString(1+2), now())

10.2、使用指定格式的语法（FORMAT [ ]）

INSERT INTO [db.]table [(c1, c2, c3…)] FORMAT format_name data_set

ClickHouse支持多种数据格式，以常用的CSV格式写入为例：

INSERT INTO partition_v2 FORMAT CSV \ 'A0017','', '2019-10-01' \ 'A0018','', '2019-10-01'

10.3、使用SELECT子句形式的语法（SELCET子句）

INSERT INTO [db.]table [(c1, c2, c3…)] SELECT ...

通过SELECT子句可将查询结果写入数据表，假设需要将partition_v1的数据写入partition_v2，则可以使用下面的语句：

INSERT INTO partition_v2 SELECT * FROM partition_v1

在通过SELECT子句写入数据的时候，同样也支持加入表达式或函数，例如：

INSERT INTO partition_v2 SELECT 'A0020', '', now()

虽然VALUES和SELECT子句的形式都支持声明表达式或函数，但是表达式和函数会带来额外的性能开销，从而导致写入性能的下降。所以如果追求极致的写入性能，就应该尽可能避免使用它们。
ClickHouse内部所有的数据操作都是面向Block数据块的，所以INSERT查询最终会将数据转换为Block数据块。也正因如此，INSERT语句在单个数据块的写入过程中是具有原子性的。在默认的情况下，每个数据块最多可以写入1048576行数据（由max_insert_block_size参数控制）。也就是说，如果一条INSERT语句写入的数据少于max_insert_block_size行，那么这批数据的写入是具有原子性的，即要么全部成功，要么全部失败。需要注意的是，只有在ClickHouse服务端处理数据的时候才具有这种原子写入的特性，例如使用JDBC或者HTTP接口时。因为max_insert_block_size参数在使用CLI命令行或者INSERT SELECT子句写入时是不生效的。

11、数据删除、修改（DELETE、UPDATE）

ClickHouse提供了DELETE和UPDATE的能力，这类操作被称为Mutation查询，它可以看作ALTER语句的变种。虽然Mutation能最终实现修改和删除，但不能完全以通常意义上的UPDATE和DELETE来理解。

Mutation语句是一种“很重”的操作，更适用于批量数据的修改和删除；
它不支持事务，一旦语句被提交执行，就会立刻对现有数据产生影响，无法回滚；
Mutation语句的执行是一个异步的后台过程，语句被提交之后就会立即返回。所以这并不代表具体逻辑已经执行完毕，它的具体执行进度需要通过系统表查询。

11.1、删除语句（DELETE）完整语法：

ALTER TABLE [db_name.]table_name DELETE WHERE filter_expr

数据删除的范围由WHERE查询子句决定。例如，执行下面语句可以删除partition_v2表内所有ID等于A003的数据：

ALTER TABLE partition_v2 DELETE WHERE ID = 'A003'

由于演示的数据很少，DELETE操作给人的感觉和常用的OLTP数据库无异。但是我们心中应该要明白这是一个异步的后台执行动作。

整个Mutation操作的逻辑就比较清晰了。每执行一条ALTER DELETE语句，都会在mutations系统表中生成一条对应的执行计划，当is_done等于1时表示执行完毕。与此同时，在数据表的根目录下，会以mutation_id为名生成与之对应的日志文件用于记录相关信息。而数据删除的过程是以数据表的每个分区目录为单位，将所有目录重写为新的目录，新目录的命名规则是在原有名称上加上.block_numbers.number。数据在重写的过程中会将需要删除的数据去掉。旧的数据目录并不会立即删除，而是会被标记成非激活状态（active为0）。等到MergeTree引擎的下一次合并动作触发时，这些非激活目录才会被真正从物理意义上删除。

11.2、修改语句（UPDATE）完整语法

数据修改除了需要指定具体的列字段之外，整个逻辑与数据删除一样，它的完整语法如下所示：

ALTER TABLE [db_name.]table_name UPDATE column1 = expr1 [, ...] WHERE filter_expr

UPDATE支持在一条语句中同时定义多个修改字段，分区键和主键不能作为修改字段。例如，执行下面的语句即能够根据WHERE条件同时修改partition_v2内的URL和OS字段：

ALTER TABLE partition_v2 UPDATE URL = '',OS = 'mac' WHERE ID IN (SELECT ID FROM partition_v2 WHERE EventTime = '2019-06-01')

小结

数据库引擎