12-27ClickHouse数据库数据定义手记之不一般的DDL和DML

前提

前面一篇文章已经很详细地介绍了 ClickHouse 中每种数据类型的定义和基本使用，这篇文章会详细地介绍 ClickHouse 中的 DDL 和 DML ，很多操作区别于传统的 DBMS ，特别是代价巨大的 DELETE 和 UPDATE 操作。接下来开始吧:muscle::muscle:

一般情况下，笔者建议ClickHouse的关键字全用大写，这样可以更加凸显出自定义的驼峰命名和大写关键字的不同，可读性和可维护性更高

本文使用的ClickHouse服务版本为当前最新的20.10.3.30

数据库DDL

ClickHouse 服务启动后，默认会生成一个命名为 default 的数据库（除了系统数据库之外，不切换数据库创建表默认就是在 default 数据库创建），数据库就像命名空间，物理上实现了数据隔离，同时有效避免了表命名冲突等问题。通过 SHOW DATABASES 可以列出当前服务中的所有数据库：

f5abc88ff7e4 :) SHOW DATABASES

SHOW DATABASES

┌─name───────────────────────────┐
│ _temporary_and_external_tables │
│ default                        │
│ system                         │
└────────────────────────────────┘

3 rows in set. Elapsed: 0.023 sec.

创建数据库的基本语法如下：

CREATE DATABASE [IF NOT EXISTS] $db_name [ON CLUSTER $cluster] [ENGINE = $engine(...)]

其中有三段可选的子句：

IF NOT EXISTS ：代表不存在 $db_name 同名数据库的时候才创建，否则忽略，不使用此子句如果数据库中已经存在同名的数据库定义则会抛出一个异常
ON CLUSTER $cluster ：指定集群中的所有 ClickHouse 服务实例都执行对应的 DDL ，高级的分布式 DDL 功能
[ENGINE = $engine(...)] ：指定数据库引擎（挺意外的，不单表有对应的表引擎，数据库也有相应的引擎）

常用的数据库 DDL ：

DDL 功能备注 SHOW DATABASES 列出服务中所有数据库 SHOW CREATE DATABASE $db_name 返回建库的 DDL 语句 CREATE DATABASE $db_name 创建数据库 USE DATABASE $db_name 切换（当前会话中使用的）数据库

例如：

f5abc88ff7e4 :) CREATE DATABASE db_test;

CREATE DATABASE db_test

Ok.

0 rows in set. Elapsed: 0.034 sec.

f5abc88ff7e4 :) SHOW CREATE DATABASE db_test;

SHOW CREATE DATABASE db_test

┌─statement──────────────────────────────┐
│ CREATE DATABASE db_test
ENGINE = Atomic │
└────────────────────────────────────────┘

1 rows in set. Elapsed: 0.007 sec.

数据库引擎

这个是 ClickHouse 的一个十分先进的特性，数据库可以定义引擎类型，不同的引擎应用于不同的场景，用得熟练就可以领略一下"万物皆为表"的远大宏愿。官方文档上只提到了三种数据库引擎：默认的数据库引擎、 MySQL 和 Lazy 。但是从大部分参考资料来看， ClickHouse 支持 至少五种 数据库引擎。已知可用的数据库引擎如下：

引擎特点备注 Ordinary 选用此数据库引擎可以使用任意类型的表引擎默认的数据库引擎 Dictionary 字典引擎，会为所有数据字典创建对应的关联表此引擎使用内置的字典功能，应该是不常用的 Memory 内存引擎，表和数据都存放在内存，重启后数据会被清理可以简单认为这类数据库中所有表的引擎被设置为 Memory Lazy 日志引擎，此类型数据库只能使用 Log 系列的表引擎需要配置一个 expiration_time_in_seconds 时间值指定最后一次访问后表驻留于内存的过期时间 MySQL MySQL 引擎，使用此引擎的数据库需要指定一个 MySQL 实例的链接参数，自动同步 MySQL 的表和数据格式 ENGINE = MySQL('HOST:PORT','DATABASE','USERNAME','PASSWORD') Atomic 不指定任何数据库引擎的时候自动选用的数据库引擎 20.10.3.30 版本不指定数据库引擎创建的数据库就是此类型，猜测是 Ordinary 类型的别名

绝大多数情况下，选用 Ordinary 类型或者不需要指定数据库引擎即可。测试一下：

f5abc88ff7e4 :) CREATE DATABASE db_default;

CREATE DATABASE db_default

Ok.

0 rows in set. Elapsed: 0.027 sec.

f5abc88ff7e4 :) SHOW CREATE DATABASE db_default;

SHOW CREATE DATABASE db_default

┌─statement─────────────────────────────────┐
│ CREATE DATABASE db_default
ENGINE = Atomic │
└───────────────────────────────────────────┘

1 rows in set. Elapsed: 0.007 sec.

f5abc88ff7e4 :) CREATE DATABASE db_ordinary ENGINE = Ordinary;

CREATE DATABASE db_ordinary
ENGINE = Ordinary

Ok.

0 rows in set. Elapsed: 0.019 sec.

f5abc88ff7e4 :) CREATE DATABASE db_memory ENGINE = Memory;

CREATE DATABASE db_memory
ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.014 sec.

f5abc88ff7e4 :) CREATE DATABASE db_lazy ENGINE = Lazy(60);

CREATE DATABASE db_lazy
ENGINE = Lazy(60)

Ok.

0 rows in set. Elapsed: 0.017 sec.

数据表DDL

数据表 DDL 有很多用法类似于传统的 DBMS 例如 MySQL 的使用方式，但是也添加了一些新的特性。

建表DDL

就创建数据库表来说，一共有三种方式：

第一种：基于严格的 Schema 语法定义创建数据库表
第二种：拷贝其他表的 Schema 建立新的表，同时可以选择指定表引擎，有点像 MySQL 中的 CREATE TABLE XX LIKE YY
第三种：拷贝其他表的 Schema 建立新的表并且导入 SELECT 查询的数据，同时必须指定表引擎

严格的Schema语法定义

# 语法定义
CREATE TABLE [IF NOT EXISTS] [$db_name.]$table_name [ON CLUSTER $cluster_name](
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [compression_codec] [TTL expr1] [COMMENT comment1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [compression_codec] [TTL expr2] [COMMENT comment1],
    ...
) ENGINE = $engine

举例：

f5abc88ff7e4 :) CREATE TABLE t_test(id UInt64 COMMENT 'ID',name String COMMENT '姓名') ENGINE = Memory;

CREATE TABLE t_test
(
    `id` UInt64 COMMENT 'ID',
    `name` String COMMENT '姓名'
)
ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.032 sec.

拷贝表结构且可以修改表引擎

# 语法定义
CREATE TABLE [IF NOT EXISTS] [$db_name.]$table_name AS [$other_db_name.]$other_table_name [ENGINE = engine]

# 例如
CREATE TABLE default.t_new_test AS default.t_test

举例：

f5abc88ff7e4 :) CREATE TABLE default.t_new_test AS default.t_test;

CREATE TABLE default.t_new_test AS default.t_test

Ok.

0 rows in set. Elapsed: 0.028 sec.

f5abc88ff7e4 :) DESC default.t_new_test;

DESCRIBE TABLE default.t_new_test

┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id   │ UInt64 │              │                    │ ID      │                  │                │
│ name │ String │              │                    │ 姓名    │                  │                │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

2 rows in set. Elapsed: 0.004 sec.

拷贝表结构导入数据并且指定表引擎

# 语法定义
CREATE TABLE [IF NOT EXISTS] [$db_name.]$table_name AS table_function()

# 例如
CREATE TABLE [IF NOT EXISTS] [$db_name.]$table_name ENGINE = $engine AS SELECT ...

举例：

f5abc88ff7e4 :) CREATE TABLE default.t_test_func ENGINE = Memory AS SELECT * FROM t_test;

CREATE TABLE default.t_test_func
ENGINE = Memory AS
SELECT *
FROM t_test

Ok.

0 rows in set. Elapsed: 0.028 sec.

默认值表达式

ClickHouse 推荐所有写入的数据列都包含值， 否则会填充对应类型的零值 ，或者通过默认值表达式指定默认值。假如某个字段定义了默认值，那么该字段就不需要强制指定数据类型， ClickHouse 会基于默认值表达式推断出它的（合理类型内的较窄范围的）数据类型，没错，定义了默认值甚至都不需要定义类型。此外，默认值表达式可以定义为 常量或者基于其他列的计算表达式 ， ClickHouse 会检查这些表达式是否出现循环依赖。默认值表达式包含三种关键字：

DEFAULT $expression ：例如 amount Decimal(10,2) DEFAULT 0 、 c2 UInt32 DEFAULT c1 （ c2 DEFAULT 1000 ）
MATERIALIZED $expression ：例如 a MATERIALIZED (b+1), b UInt16
ALIAS $expression ：例如 a ALIAS (b+1), b UInt16

三者的区别如下：

关键字特点列数据是否持久化 DEFAULT 对应的列可以出现在 INSERT 语句中，不出现则通过表达式计算和填充， SELECT * 查询结果会包含对应的列如果使用的表引擎支持物理持久化则可以持久化 MATERIALIZED 对应的列不可以出现在 INSERT 语句中， SELECT * 查询结果不会包含对应的列，但是 SELECT 指定列名则会返回，返回值依赖于动态计算， dump 数据场景下通过 INSERT ... SELECT * 可以导入对应列数据如果使用的表引擎支持物理持久化则可以持久化 ALIAS 对应的列不可以出现在 INSERT 语句中， SELECT * 查询结果不会包含对应的列，但是 SELECT 指定列名则会返回，返回值依赖于动态计算不支持持久化，甚至列数据都不会存储在表中，取值总是动态计算

如果使用习惯了MySQL中的DEFAULT关键字，那么可以认为ClickHouse的DEFAULT关键与之类似，只是更加先进可以基于表达式进行计算

使用默认值表达式需要注意几点：

可以使用 ALTER 关键字修改某个列的默认值： ALTER TABLE [$db_name.]$table_name MODIFY COLUMN $column_name [DEFAULT|MATERIALIZED|ALIAS] exp ，但是修改的动作不会影响数据表中之前已经存在的数据
默认值的修改有比较多的限制， MergeTree 系列的表引擎中主键字段无法修改默认值，甚至有某些表引擎类型完全不允许修改任意列的默认值（如 TinyLog ）
无法为 Nested 类型数据结构中的元素设置默认值

DEFAULT 关键字举例：

f5abc88ff7e4 :) CREATE TABLE t_d(a UInt16,b DEFAULT (a + 1)) ENGINE = Memory;

CREATE TABLE t_d
(
    `a` UInt16,
    `b` DEFAULT a + 1
)
ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.024 sec.

f5abc88ff7e4 :) INSERT INTO t_d(a,b) VALUES(1,11);

INSERT INTO t_d (a, b) VALUES

Ok.

1 rows in set. Elapsed: 0.007 sec.

f5abc88ff7e4 :) INSERT INTO t_d(a) VALUES(3);

INSERT INTO t_d (a) VALUES

Ok.

1 rows in set. Elapsed: 0.004 sec.

f5abc88ff7e4 :) select * from t_d;

SELECT *
FROM t_d

┌─a─┬──b─┐
│ 1 │ 11 │
└───┴────┘
┌─a─┬─b─┐
│ 3 │ 4 │
└───┴───┘

2 rows in set. Elapsed: 0.004 sec.

MATERIALIZED 关键字举例：

f5abc88ff7e4 :) CREATE TABLE t_m(a UInt16,b MATERIALIZED (a + 1)) ENGINE = Memory;

CREATE TABLE t_m
(
    `a` UInt16,
    `b` MATERIALIZED a + 1
)
ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.019 sec.

f5abc88ff7e4 :) INSERT INTO t_m(a) VALUES (2);

INSERT INTO t_m (a) VALUES

Ok.

1 rows in set. Elapsed: 0.004 sec.

f5abc88ff7e4 :) SELECT * FROM t_m;

SELECT *
FROM t_m

┌─a─┐
│ 2 │
└───┘

1 rows in set. Elapsed: 0.005 sec.

f5abc88ff7e4 :) SELECT b FROM t_m;

SELECT b
FROM t_m

┌─b─┐
│ 3 │
└───┘

1 rows in set. Elapsed: 0.004 sec.

f5abc88ff7e4 :) INSERT INTO t_m(a,b) VALUES (2,3);

INSERT INTO t_m (a, b) VALUES


Received exception from server (version 20.10.3):
Code: 44. DB::Exception: Received from clickhouse-server:9000. DB::Exception: Cannot insert column b, because it is MATERIALIZED column..

0 rows in set. Elapsed: 0.004 sec.

ALIAS 关键字举例：

f5abc88ff7e4 :) CREATE TABLE t_a(a UInt16,b ALIAS (a + 1)) ENGINE = Memory;

CREATE TABLE t_a
(
    `a` UInt16,
    `b` ALIAS a + 1
)
ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.021 sec.

f5abc88ff7e4 :) INSERT INTO TABLE t_a(a) VALUES (11);

INSERT INTO t_a (a) VALUES

Ok.

1 rows in set. Elapsed: 0.003 sec.

f5abc88ff7e4 :) SELECT *,b FROM t_a;

SELECT
    *,
    b
FROM t_a

┌──a─┬──b─┐
│ 11 │ 12 │
└────┴────┘

1 rows in set. Elapsed: 0.005 sec.

其他常用的表操作

命令功能 SHOW CREATE TABLE $table_name 查看建表语句 DESC TABLE $table_name | DESCRIBE TABLE $table_name 展示表的所有列定义 DROP TABLE $table_name 删除表，表数据会从文件系统中物理删除 TRUNCATE TABLE $table_name 清空表数据

例如：

f5abc88ff7e4 :) DESCRIBE TABLE p_v1

DESCRIBE TABLE p_v1

┌─name──────┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ Id        │ UInt64 │              │                    │         │                  │                │
│ EventTime │ Date   │              │                    │         │                  │                │
│ name      │ String │ DEFAULT      │ 'dv'               │         │                  │                │
│ age       │ UInt16 │              │                    │         │                  │                │
└───────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

4 rows in set. Elapsed: 0.004 sec.

f5abc88ff7e4 :) SHOW CREATE TABLE p_v1

SHOW CREATE TABLE p_v1

┌─statement─────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.p_v1
(
    `Id` UInt64,
    `EventTime` Date,
    `name` String DEFAULT 'dv',
    `age` UInt16
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventTime)
ORDER BY Id
SETTINGS index_granularity = 8192 │
└───────────────────────────────────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.008 sec.

列压缩编码

ClickHouse 服务为了节省磁盘空间，会使用高性能压缩算法对存储的数据进行压缩。默认启用的是 lz4 （ lz4 fast compression ）压缩算法，在 MergeTree 家族引擎下可以通过 ClickHouse 服务端配置中的 compression 节点选项配置来改变默认的压缩算法。基本语法：

## 建表
$column_name [type] COCEC($算法类型)

## 修改
ALTER TABLE $table_name MODIFY COLUMN $column_name CODEC($算法类型);

## 建表例子
CREATE TABLE codec_example
(
    ts DateTime CODEC(LZ4),
    dt Date CODEC(DEFAULT)
)

可以选用的算法：

None ：无压缩
Default ：默认的压缩算法，缺省值也是使用默认的压缩算法
LZ4 ： lz4 压缩算法中的快速压缩算法版本
LZ4HC[(level)] ： lz4 高压缩率压缩算法版本， level 默认值为 9 ，支持 [1,12] ，推荐选用 [4,9]
ZSTD[(level)] ： zstd 压缩算法， level 默认值为 1 ，支持 [1,22]

采用不同的表引擎，会支持不同的压缩算法，目前的支持列表如下：

MergeTree 系列引擎：支持所有可选的压缩算法，并且支持在服务端配置默认压缩算法
Log 系列引擎：默认使用 lz4 压缩算法，支持所有可选的压缩算法
Set 系列引擎：只支持默认的压缩算法
Join 系列引擎：只支持默认的压缩算法

还有几个特殊的编码解码方法如 Delta(delta_bytes) 、 DoubleDelta 、 Gorilla 和 T64 ，这里不做展开。

临时表

ClickHouse 也支持临时表，不过有比较多的限制：

Memory

创建临时表的语法如下：

CREATE TEMPORARY TABLE [IF NOT EXISTS] $table_name
(
    $column_name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    $column_name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
)

注意：官方文档提醒，绝大多数情况下，临时表都不是手动创建的而是内部创建，一般用于分布式全局的外部数据查询，例如用于集群间数据的查询传递，因此官方应该是不建议使用临时表

视图

ClickHouse 支持视图功能，目前一共支持两种视图：普通（ Normal ）视图和物化（ Materialized ）视图。通过 DROP TABLE [$db_name.]$view_table_name 语句可以直接删除视图，而通过 SHOW TABLES 可以展示所有的表，视图也会被认为是一种特殊的表一并进行展示。

普通视图

普通视图的创建语法如下：

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] [$db_name.]$view_table_name [ON CLUSTER] AS SELECT ...

普通视图不会存储任何数据，它只是一个查询映射，起到了简化查询语义的作用，对查询的性能也不会有任何正负作用。假设有一个表叫 t_test ，创建一个普通视图 view_test ：

CREATE VIEW view_test AS SELECT * FROM t_test

如果直接从视图 view_test 做查询 SELECT * FROM view_test ，语义完全等价于 SELECT * FROM (SELECT * FROM t_test) 。

物化视图

物化视图支持定义表引擎，因为其数据保存的形式由表引擎决定。创建物化视图的语法如下：

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [$db_name.]$view_table_name [ON CLUSTER] [TO[$db_name.]$table_name] [ENGINE = $engine] [POPULATE] AS SELECT ...

物化视图的特点如下：

需要定义表引擎，决定数据存储的形式
物化视图创建后会生成内部表，表名以 .inner. 开头，可以通过 SHOW TABLES 验证
[TO[$db_name.]$table_name] 子句和 [POPULATE] 子句互斥，二者只能选其中一者或者都不选
在使用 [TO[$db_name.]$table_name] 子句的时候，必须显式指定表引擎
POPULATE 关键字决定了物化视图的更新策略，如果使用了 POPULATE 则在创建视图的过程会将源表已经存在的数据一并导入，类似于 CREATE TABLE ... AS ，如果不指定 POPULATE 关键字，创建的物化视图是全新没有数据的，创建完成之后才会开始同步源表写入的数据（官方不推荐使用 POPULATE 关键字，因为启用会有可能导致创建物化视图过程中新写入源表的数据无法同步到视图中）
物化视图中的数据不支持同步删除，如果源表的数据不存在或者源表被删除了，物化视图的数据依然存在

ClickHouse 中的物化视图的实现更像是数据插入触发器。如果视图查询中存在某些聚合，则这些聚合操作仅仅会作用于这些新写入的数据。对源表的现有数据进行的任何更改（例如更新、删除、删除分区等）都不会更改物化视图中的数据。

笔者注：物化视图是一把双刃剑，用的合理会简化大量同步和聚合的工作，滥用则会导致维护十分困难还会影响性能

基本的列操作

基本的列操作都是围绕 ALTER 关键字执行。通用的基本语法是：

ALTER TABLE [$db_name.]$table_name [ON CLUSTER cluster] ADD|DROP|CLEAR|COMMENT|MODIFY COLUMN ...

下面为了简化语法，暂时省略 [$db_name.] 和 [ON CLUSTER cluster] 等子句。

追加新的列 - ADD COLUMN

ADD COLUMN 语句用于在指定的表添加一个新的列。基本语法：

ALTER TABLE $table_name ADD COLUMN [IF EXISTS] $column_name [type] [default_expr] [codec] [AFTER $pre_column_name]

type ：可选，用于指定列类型
default_expr ：可选，用于设置默认值表达式
codec ：可选，见前面一节的 列压缩编码
AFTER 子句：可选，用于指定在哪个已经存在的列后面添加新的列

举例：

ALTER TABLE default.p_v1 ADD COLUMN age UInt16 AFTER name

修改列 - MODIFY COLUMN

MODIFY COLUMN 语句可以用于修改已经存在的列的类型、默认值表达式或者 TTL 表达式。基本语法：

ALTER TABLE $table_name MODIFY COLUMN [IF EXISTS] $column_name [type] [default_expr] [TTL]

举例：

ALTER TABLE default.p_v1 MODIFY COLUMN age UInt32

类型修改的时候，本质上会使用内置函数 toType() 进行转换，如果当前类型与期望类型不能兼容无法转换，则列修改操作会失败，抛出异常。

添加或者修改列备注 - COMMENT COLUMN

ClickHouse 中添加或者修改列注释使用特殊的 COMMENT COLUMN 子句。基本语法：

ALTER TABLE $table_name COMMENT COLUMN [IF EXISTS] $column_name '备注内容'

举例：

ALTER TABLE default.p_v1 COMMENT COLUMN age '年龄'

删除列 - DROP COLUMN

DROP COLUMN 语句用于删除列，对应的列数据会从文件系统中 物理删除 。基本语法：

ALTER TABLE $table_name DROP COLUMN [IF EXISTS] $column_name

举例：

ALTER TABLE default.p_v1 DROP COLUMN age

重置对应列和分区的所有值 - CLEAR COLUMN

CLEAR COLUMN 语句用于重置对应的列和指定分区的所有值为默认值，如果没有设置默认值表达式，则对应列的所有值重置为其类型的零值。基本语法：

ALTER TABLE $table_name CLEAR COLUMN [IF EXISTS] $column_name IN PARTITION $partition_name

举例：

f5abc88ff7e4 :) CREATE TABLE p_v1(Id UInt64,EventTime Date,name String DEFAULT 'dv')ENGINE = MergeTree() PARTITION BY toYYYYMM(EventTime) ORDER BY Id

CREATE TABLE p_v1
(
    `Id` UInt64,
    `EventTime` Date,
    `name` String DEFAULT 'dv'
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventTime)
ORDER BY Id

Ok.

0 rows in set. Elapsed: 0.047 sec.

f5abc88ff7e4 :) INSERT INTO p_v1 VALUES(1,'2020-11-28','doge1'),(2,'2020-10-29','doge2');

INSERT INTO p_v1 VALUES

Ok.

2 rows in set. Elapsed: 0.074 sec.

f5abc88ff7e4 :) ALTER TABLE p_v1 clear column name IN partition 202011;

ALTER TABLE p_v1
    CLEAR COLUMN name     IN PARTITION 202011


Ok.

0 rows in set. Elapsed: 0.163 sec.

f5abc88ff7e4 :) SELECT * FROM  p_v1;

SELECT *
FROM p_v1

┌─Id─┬──EventTime─┬─name──┐
│  2 │ 2020-10-29 │ doge2 │
└────┴────────────┴───────┘
┌─Id─┬──EventTime─┬─name─┐
│  1 │ 2020-11-28 │ dv   │
└────┴────────────┴──────┘

数据分区

数据分区在 ClickHouse 中就是分区表，本质是数据表 DDL ，但是考虑到数据分区的重要性，把这个特性单独拉出来一个 h2 章节。 ClickHouse 中的数据分区是指同一个本地实例中的数据的纵向切分，跟横向切分中的数据分片概念完全不同。 ClickHouse 目前只有使用了 MergeTree 系列表引擎的表（包括 REPLICATED* 系列和使用了 MergeTree 系列表引擎的物化视图）才支持数据分区。这里仅仅简单介绍一下 PARTITION 关键字的使用和常用的分区相关操作。

分区是数据表中记录按指定条件的逻辑组合，可以通过任意条件（例如按月、按日或者按事件类型）设置分区，每个分区的数据分别储存，以简化数据操作和提高性能，因此在访问数据的时候尽可能使用最小的分区子集。 ClickHouse 在创建表的时候通过 PARTITION BY expr 子句指定分区定义，分区键可以是基于表中数据列组成的任意表达式。例如有个字段是 Date 类型，如果按月分区可以使用表达式 toYYYYMM(date_column) ，例如：

CREATE TABLE pv (
    visitDate Date,
    hour UInt8,
    clientID String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(visitDate)
ORDER BY hour;

创建完此表，使用 INSERT INTO pv VALUES('2020-11-01',1,'11'),('2020-10-01',2,'22'); 写入两条数据后，通过下面的语句查询分区信息：

f5abc88ff7e4 :) SELECT partition,name,path,active FROM system.parts WHERE table = 'pv'

SELECT
    partition,
    name,
    path,
    active
FROM system.parts
WHERE table = 'pv'

┌─partition─┬─name─────────┬─path─────────────────────────────────────────────────────────────────────────────┬─active─┐
│ 202010    │ 202010_5_5_0 │ /var/lib/clickhouse/store/f17/f1729bf7-7baf-40d0-bd4f-6ee13877d0db/202010_5_5_0/ │      1 │
│ 202010    │ 202010_7_7_0 │ /var/lib/clickhouse/store/f17/f1729bf7-7baf-40d0-bd4f-6ee13877d0db/202010_7_7_0/ │      1 │
│ 202011    │ 202011_2_2_0 │ /var/lib/clickhouse/store/f17/f1729bf7-7baf-40d0-bd4f-6ee13877d0db/202011_2_2_0/ │      1 │
│ 202011    │ 202011_4_4_0 │ /var/lib/clickhouse/store/f17/f1729bf7-7baf-40d0-bd4f-6ee13877d0db/202011_4_4_0/ │      1 │
│ 202011    │ 202011_6_6_0 │ /var/lib/clickhouse/store/f17/f1729bf7-7baf-40d0-bd4f-6ee13877d0db/202011_6_6_0/ │      1 │
└───────────┴──────────────┴──────────────────────────────────────────────────────────────────────────────────┴────────┘

5 rows in set. Elapsed: 0.005 sec.

可见分区一共有 202010 和 202011 两个，而 name 属性是分区数据部分（ parts ）的名称，例如 202010_5_5_0 ：

第一部分 202010 是分区名称
第一个 5 是数据块最小编号（ MinBlockNum ）
第二个 5 是数据块最大编号（ MaxBlockNum ）
最后的 0 表示分区的层级，指某个分区合并过的次数

这个 name 属性是合并树家族表引擎特有的，后面如果有机会分析合并树的基本原理的时候会更加深入分析其具体含义。分区完毕之后，通过分区键进行查询就能采用分区最小数据集：

f5abc88ff7e4 :) SELECT * FROM pv WHERE visitDate = '2020-11-01'

SELECT *
FROM pv
WHERE visitDate = '2020-11-01'

┌──visitDate─┬─hour─┬─clientID─┐
│ 2020-11-01 │    1 │ 11       │
└────────────┴──────┴──────────┘

3 rows in set. Elapsed: 0.020 sec.

如果一个分区有多部分没合并，一般在写入数据的15分钟之后会对新写入的分区部分数据进行合并，然后对应的部分就会变成非活跃状态，可以通过OPTIMIZE TABLE table_name PARTITION partition进行执行计划触发合并，不过这是一个相当耗时的操作，一般不建议主动使用。

数据分区的其他操作主要是围绕 ALTER 关键字，语法是：

ALTER TABLE $table_name $OP PARTITION|PART $partition(_part)_expr

查询分区信息

查询分区信息主要依赖到系统表 system.parts ，可以通过 DESC system.parts 查看列元数据定义（一共有 44 个列），这里一般选用下面几个常用的属性：

partition
name
active
path
database
table
engine

例如：

f5abc88ff7e4 :) SELECT database,table,engine,partition,name,path,active FROM system.parts WHERE table = 'pv';

SELECT
    database,
    table,
    engine,
    partition,
    name,
    path,
    active
FROM system.parts
WHERE table = 'pv'

┌─database─┬─table─┬─engine────┬─partition─┬─name─────────┬─path─────────────────────────────────────────────────────────────────────────────┬─active─┐
│ default  │ pv    │ MergeTree │ 202010    │ 202010_5_5_0 │ /var/lib/clickhouse/store/f17/f1729bf7-7baf-40d0-bd4f-6ee13877d0db/202010_5_5_0/ │      1 │
│ default  │ pv    │ MergeTree │ 202010    │ 202010_7_7_0 │ /var/lib/clickhouse/store/f17/f1729bf7-7baf-40d0-bd4f-6ee13877d0db/202010_7_7_0/ │      1 │
│ default  │ pv    │ MergeTree │ 202011    │ 202011_2_2_0 │ /var/lib/clickhouse/store/f17/f1729bf7-7baf-40d0-bd4f-6ee13877d0db/202011_2_2_0/ │      1 │
│ default  │ pv    │ MergeTree │ 202011    │ 202011_4_4_0 │ /var/lib/clickhouse/store/f17/f1729bf7-7baf-40d0-bd4f-6ee13877d0db/202011_4_4_0/ │      1 │
│ default  │ pv    │ MergeTree │ 202011    │ 202011_6_6_0 │ /var/lib/clickhouse/store/f17/f1729bf7-7baf-40d0-bd4f-6ee13877d0db/202011_6_6_0/ │      1 │
└──────────┴───────┴───────────┴───────────┴──────────────┴──────────────────────────────────────────────────────────────────────────────────┴────────┘

5 rows in set. Elapsed: 0.005 sec.

删除分区

物理删除分区或者分区部分通过 DROP PARTITION|PART 子句完成，基本语法如下：

ALTER TABLE $table_name DROP PARTITION|PART partition(_part)_expr

此删除操作是异步的，执行语句完毕后对应的分区或者分区部分会先设置为非活跃（也就是设置 active = 0 ），然后在 10 分钟后进行物理删除。

例如：

ALTER TABLE pv DROP PARTITION 202010;

ALTER TABLE pv DROP PART 202010_5_5_0;

ALTER TABLE pv DROP PART all_5_5_0;

分区的卸载与装载

ClickHouse 的分区支持热卸载和热装载（仔细阅读文档发现应该是相对耗时的异步操作，操作时候需要谨慎），主要通过关键字 DETACH PARTITION|PART 和 ATTACH PARTITION|PART 完成，两者刚好也是互逆操作。

卸载分区 - DETACH PARTITION|PART

基本语法如下：

ALTER TABLE $table_name DETACH PARTITION|PART $partition_expr

分区卸载并不会对该分区数据进行物理删除，而是把整个分区数据转移到对应数据表目录的 detached 子目录下，此时直接通过 SELECT 查询对应分区的数据集不会返回任何数据（这个是当然的，数据目录都被移动了…）。分区一旦被移动到了 detached 子目录下就会一直存在，除非主动删除或者使用 ATTACH | DROP DETACHED 命令去重新装载或者删除对应的数据目录。

装载分区 - ATTACH PARTITION|PART

基本语法如下：

ALTER TABLE $table_name ATTACH PARTITION|PART $partition_expr

分区装载是分区卸载的逆操作，其实就是把 detached 子目录下的分区数据重新转移到数据表的分区目录中。

移除分区卸载备份 - DROP DETACHED PARTITION|PART

基本语法如下：

ALTER TABLE $table_name DROP DETACHED PARTITION|PART $partition_expr

移除 detached 子目录下对应的分区数据，物理删除，移除完成之后无法通过 ATTACH 关键字重新装载。

分区数据的拷贝覆盖

基本语法如下：

ALTER TABLE $table_y_name REPLACE PARTITION $partition_expr FROM $table_x_name

直接拷贝数据表 table_x_name 的分区到数据表 table_y_name 的已经存在的分区，覆盖整个分区的数据，并且 table_x_name 原有的分区数据不会被删除。前提条件如下：

两个表的表结构完全一样（列定义）
两个表的分区键完全一样

分区数据的移动

基本语法如下：

ALTER TABLE $table_source MOVE PARTITION $partition_expr TO TABLE $table_dest

移动数据表 table_source 指定分区到数据表 table_dest 中，类似于剪切操作，数据表 table_source 原有的分区数据会被删除。前提条件如下：

storage policy

重置分区列数据

基本语法如下：

ALTER TABLE $table_name CLEAR COLUMN $column_name IN PARTITION $partition_expr

重置分区的列数据为默认值，如果没有定义默认值表达式，则重置为对应类型的零值。

重置分区索引

基本语法如下：

ALTER TABLE $table_name CLEAR INDEX $index_name IN PARTITION $partition_expr

文档中提到：有点像重置分区列数据的操作，但是只重置分区的对应的索引，不会重置数据（具体功能未知，因为尚未深入了解索引的原理）。

其他分区操作

ALTER TABLE table_name FREEZE [PARTITION partition_expr]
ALTER TABLE table_name FETCH PARTITION partition_expr FROM 'path-in-zookeeper'
ALTER TABLE table_name MOVE PARTITION|PART partition_expr TO DISK|VOLUME 'disk_name'

涉及到配置、磁盘路径甚至是 Zookeeper 中的路径，比较复杂，暂时不做展开。

TTL表达式

TTL （ Time To Live ）表达式是 ClickHouse 一项比较创新的高级功能，用于指定数据的存活时间。 TTL 表达式有列字段级别（到期会重置对应列的过期数据），也有表级别（到期会删除整张表）。如果同时指定了列 TTL 和表 TTL ，则按照先到期先执行的原则处理。 TTL 表达式用于确定目标的生命周期，表达式计算的结果必须是 Date 或者 DateTime 数据类型，时间间隔使用关键字 INTERVAL 定义，而且还可以在表达式中定义数据在磁盘和数据卷之间移动的逻辑（限于表 TTL ）。基本的语法如下：

TTL time_column
TTL time_column + interval

## 需要使用INTERVAL关键字定义时间间隔
TTL date_time + INTERVAL 1 MONTH
TTL date_time + INTERVAL 15 HOUR

到目前为止，ClickHouse只提供了TTL定义和更新的语法，没有提供指定某个TTL表达式进行停止的语法，只有一个全局停止所有TTL合并的命令：SYSTEM STOP/START TTL MERGES

表TTL表达式

表 TTL 通过某个 Date 或者 DateTime 数据类型进行表级别设定过期时间（从目前来看，应该只有 MergeTree 表引擎家族的表支持表级别 TTL ），当触发 TTL 清理时，那些满足过期时间的数据列将会被删除（或者被移动）。基本表达式如下：

TTL $expr [DELETE|TO DISK 'aaa'|TO VOLUME 'bbb'], ...

举个例子：

CREATE TABLE test_ttl
(
    d DateTime,
    a Int
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d
TTL d + INTERVAL 1 MONTH [DELETE],   # <--- 这里DELETE可以不填，因为默认策略就是DELETE
    d + INTERVAL 1 WEEK TO VOLUME '数据卷',
    d + INTERVAL 2 WEEK TO DISK '磁盘目录';

上面的例子说明：

d
d
d

可以通过 ALTER 关键字修改表级别的 TTL ，如：

ALTER TABLE $table_name MODIFY TTL $data(_time)_column + INTERVAL 1 DAY

列TTL表达式

列级别的 TTL 通过表达式定义列数据过期时候，过期的列数据会被 ClickHouse 重置为默认值或者对应类型的零值。 KEY （主键）列不能定义 TTL 表达式，如果某个列的所有数据都已经过期了，那么 ClickHouse 会把该列直接从文件系统中移除。基本语法如下：

$column_name type $Date(_Time)_column + INTERVAL_EXP

# 例如
CREATE TABLE example_table
(
    d DateTime,
    a Int TTL d + INTERVAL 1 MONTH,
    b Int TTL d + INTERVAL 1 MONTH,
    c String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d;

DML

DML 对应于日常开发理解中的 CURD ，主要关键字包括 INSERT 、 SELECT 、 UPDATE 和 DELETE 。

SELECT

ClickHouse 中的 SELECT 基本用法和主流的关系型 DBMS 相似，支持指定列、 * 、内置函数和大量的聚合相关的关键，这里不做深入展开，后面分析关键和函数的时候应该会大量用到 SELECT 操作。

INSERT

INSERT 关键字的主要功能就是写入数据，此操作在 ClickHouse 中会比主流的关系型 DBMS 更加多样灵活。基本语法如下：

INSERT INTO [$db_name.]$table_name [(c1, c2, c3) | (*)] VALUES (v11, v12, v13), (v21, v22, v23), ...

指定列写入： INSERT INTO table (x_col,y_col,z_col) VALUES (x1,y1,z1),(x2,y2,z2) 或者 INSERT INTO table COLUMNS(x_col,y_col,z_col) VALUES (x1,y1,z1),(x2,y2,z2)
不指定列（按照列定义顺序一一对应写入）写入： INSERT INTO table VALUES (x1,y1,z1),(x2,y2,z2) 或者 INSERT INTO table (*) VALUES (x1,y1,z1),(x2,y2,z2)
排除指定的列写入（剩余的列）： INSERT INTO table (* EXCEPT(x_col,y_col)) VALUES (z1),(z2)

写入的时候如果有的列没有被填充数据，会使用默认值或者对应类型的零值填充。

还可以指定数据格式进行数据写入，基本语法是：

INSERT INTO [$db_name.]$table_name [(c1, c2, c3)] FORMAT $format_name $data_set

例如：

写入基于 Tab 分隔的数据：

INSERT INTO test_tab FORMAT TabSeparated
1 foo
2 bar

写入 csv 格式的数据：

INSERT INTO test_csv FORMAT CSV
1,'foo'
2,'bar'

最后一种是通过 SELECT 子句写入数据，此过程支持表达式或者函数，基本语法如下：

INSERT INTO [$db_name.]$table_name [(c1, c2, c3)] SELECT ...

## 例如
INSERT INTO test_insert SELECT 1,'doge',now()

追求性能的前提下，尽可能不要在后面的 SELECT 子句中附带函数，因为函数最终也是需要 ClickHouse 服务端进行解析和调用，大量使用会导致写入性能下降。

出于写入性能的考量，官方建议：

批量写入数据
写入数据之前通过分区键对数据进行预分组

ClickHouse 对于数据写入都是面向 Block 数据结构，单个 Block 数据块写入是原子性的，而单个 Block 数据块允许写入的行数由配置项 max_insert_block_size 控制，默认值是 1048576 ，注意此原子性基于 CLI 命令写入数据是不生效的，只有使用 JDBC 或者 HTTP 协议的时候才生效。

UPDATE和DELETE

ClickHouse 虽然提供 UPDATE 和 DELETE 关键字，但是这两种操作是重量级操作，被称为 Mutation 查询，通过 ALTER 执行。 Mutation 查询有几个特点：

system.mutations

基本语法如下：

# DELETE
ALTER $table_name DELETE WHERE $filter_exp

## DELETE例子
ALTER test_delete DELETE WHERE id = 1;

# UPDATE
ALTER $table_name UPDATE columnx = exp_x,... WHERE $filter_exp

## UPDATE例子
ALTER test_update UPDATE name = 'throwable' WHERE id = 1;

查询 system.mutations 的执行结果：

SELECT database,table,mutation_id,blick_numbers.number,is_done FROM system.mutations

ClickHouse对写入和查询性能的倾斜，导致他会放弃一些特性，例如事务和高效的精确更新或删除功能，这些是利弊权衡，没有所谓正确与否。

小结

这篇文章比较详细地介绍了 ClickHouse 中常用的 DDL 和 DML ，部分高级特性如分布式 DDL 会在后面分析 ClickHouse 集群搭建的时候再介绍。接下来会详细学习一下 ClickHouse 目前支持的主流的表引擎和对应的使用场景。

参考资料

https://clickhouse.tech

（c-10-d e-a-20201208）

前提

数据库DDL

数据库引擎

数据表DDL

建表DDL

严格的Schema语法定义

拷贝表结构且可以修改表引擎

拷贝表结构导入数据并且指定表引擎

默认值表达式

其他常用的表操作

列压缩编码

临时表

视图

普通视图

物化视图

基本的列操作

追加新的列 - ADD COLUMN

修改列 - MODIFY COLUMN

添加或者修改列备注 - COMMENT COLUMN

删除列 - DROP COLUMN

重置对应列和分区的所有值 - CLEAR COLUMN

数据分区

查询分区信息

删除分区

分区的卸载与装载

卸载分区 - DETACH PARTITION|PART

装载分区 - ATTACH PARTITION|PART

移除分区卸载备份 - DROP DETACHED PARTITION|PART

分区数据的拷贝覆盖

分区数据的移动

重置分区列数据

重置分区索引

其他分区操作

TTL表达式

表TTL表达式

列TTL表达式

DML

SELECT

INSERT

UPDATE和DELETE

小结

Recommend

About Joyk