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

前提

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

❝

一般情况下，笔者建议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过期1个星期的数据会被移动到其他数据卷
基于d过期2个星期的数据会被移动到其他磁盘目录
基于d过期1个月的数据会被物理删除

可以通过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
《ClickHouse原理解析与应用实践》

（c-10-d e-a-20201208 封面自《斗罗大陆》小舞）

原文始发于微信公众号（Throwable）：ClickHouse数据库数据定义手记-不一般的DDL和DML

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/38314.html

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

前提

数据库DDL

数据库引擎

数据表DDL

建表DDL

默认值表达式

其他常用的表操作

列压缩编码

临时表

视图

普通视图

物化视图

基本的列操作

追加新的列 – ADD COLUMN

修改列 – MODIFY COLUMN

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

删除列 – DROP COLUMN

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

数据分区

查询分区信息

删除分区

分区的卸载与装载

分区数据的拷贝覆盖

分区数据的移动

重置分区列数据

重置分区索引

其他分区操作

TTL表达式

表TTL表达式

列TTL表达式

DML

SELECT

INSERT

UPDATE和DELETE

小结

相关推荐

发表回复