面向OLAP的列式存储DBMS-6-[ClickHouse]的常用DDL操作

参考ClickHouse 数据表、数据分区的相关操作，以及 DDL

DDL（data definition language）是数据定义语言：DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。

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

1 数据库

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

CREATE DATABASE IF NOT EXISTS db_name[ENGINE = engine]

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

数据库目前一共支持5种引擎，如下所示。
(1)Ordinary：默认引擎，在绝大多数情况下我们都会使用默认引擎，使用时无须刻意声明，在此数据库下可以使用任意类型的表引擎。
(2)Dictionary：字典引擎，此类数据库会自动为所有数据字典创建它们的数据表，关于数据字典的详细介绍会在后面展开。
(3)Memory：内存引擎，用于存放临时数据。此类数据库下的数据表只会停留在内存中，不会涉及任何磁盘操作，当服务重启后数据会被清除。
(4)Lazy：日志引擎，此类数据库下只能使用 Log 系列的表引擎，关于 Log 表引擎的详细介绍会后续章节展开。
(5)MySQL：MySQL 引擎，此类数据库下会自动拉取远端 MySQL 中的数据，并为它们创建 MySQL 表引擎的数据表，关于MySQL表引擎的详细介绍也会在后续章节展开。

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

CREATE DATABASE dbtest;
1

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

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

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

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

DROP DATABASE [IF EXISTS] db_name;
1

2 数据表

数据库在物理磁盘上对应一个目录文件，而表则是在数据库对应的目录文件里面再创建一个目录文件，比如我们在数据库dbtest里面创建一张表tbtest，那么相当于在 /var/lib/clickhouse/data/dbtest里面创建一个目录文件tbtest，而往表tbtest里面写的数据则会在目录tbtest中以文本文件的形式保存。

ClickHouse数据表的定义语法，是在标准SQL的基础之上建立的，ClickHouse 目前提供了三种最基本的建表方法：

第一种是常规定义方法，它的完整语法如下所示：

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name (
    column_name1 type [DEFAULT|MATERIALIZED|ALIAS expr],
    column_name2 type [DEFAULT|MATERIALIZED|ALIAS expr],
    ......
) ENGINE = engine
1
2
3
4
5

使用 [db_name.] 参数可以为数据表指定数据库，如果不指定此参数，则默认会使用default数据库。注意结尾的 ENGINE 参数，它被用于指定数据表的引擎，表引擎决定了数据表的特性，也决定了数据将会被如何存储以及如何加载。例如 Memory 表引擎，它是 ClickHouse 最简单的表引擎，数据只会被保存在内存中，在服务重启时数据会丢失。

第二种定义方法是复制其他表的结构，具体语法如下所示：

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

这种方式支持在不同的数据库之间复制表结构，例如下面的语句：

-- 将 A 库下的 a 表拷贝一份到 B 库下的 b 表, 注意：引擎可以更换
CREATE TABLE IF NOT EXISTS A.a AS B.b ENGINE = TinyLog
1
2

第三种定义方法是通过 SELECT 子句的形式创建，它的完整语法如下：

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

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

CREATE TABLE IF NOT EXISTS db.not_exists_table ENGINE = Memory AS SELECT * FROM db.exists_table
1

上述语句会将查询结果一并写入数据表。
ClickHouse 和大多数数据库一样，使用DESC查询可以返回数据表的定义结构。

如果想删除一张数据表，则可以使用下面的 DROP 语句：

DROP TABLE [IF EXISTS] [db_name.]table_name
1

3 默认值表达式

表字段支持三种默认值表达式的定义方法，分别是 DEFAULT、MATERIALIZED 和 ALIAS。无论使用哪种形式，表字段一旦定义了默认值，那么便不再强制要求定义数据类型，因为ClickHouse会根据默认值进行类型推断。

如果同时对表字段定义了数据类型和默认值表达式，则以明确定义的数据类型为主，例如：

use dbtest;
CREATE TABLE table_name (id String, col1 DEFAULT 100, col2 String DEFAULT col1) ENGINE=Memory;
col1 字段没有定义数据类型，默认值为整型 100；
col2 字段定义了数据类型和默认值，且默认值等于 col1。

写入测试数据
INSERT INTO table_name(id) VALUES('AAA');

查询测试数据
select *,toTypeName(col1),toTypeName(col2) from table_name;
┌─id──┬─col1─┬─col2─┬─toTypeName(col1)─┬─toTypeName(col2)─┐
│ AAA │  100 │ 100  │ UInt8            │ String           │
└─────┴──────┴──────┴──────────────────┴──────────────────┘
1
2
3
4
5
6
7
8
9
10
11
12
13

其中col1字段根据默认值被推断为UInt8；而col2字段由于同时定义了数据类型和默认值，所以它最终的数据类型来自明确定义的String。

默认值表达式的三种定义方法之间也存在着不同之处，可以从如下三个方面进行比较。

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

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

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

可以使用 ALTER 语句修改默认值，例如:

ALTER TABLE [db_name.]table_name MODIFY COLOMN col_name DEFAUET value
1

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

4 临时表

ClickHouse也有临时表的概念，创建临时表的方法是在普通表的基础之上添加 TEMPORARY关键字，CREATE TEMPORARY TABLE…，剩余的部分和创建普通表完全一样。

相比普通表而言，临时表有如下两点特殊之处：
(1)它的生命周期是会话绑定的，所以它只支持 Memory 表引擎，如果会话结束，数据表就会被销毁;
(2)临时表不属于任何数据库，所以在它的建表语句中，既没有数据库参数也没有表引擎参数;

针对第二个特殊项，有人心中难免会产生一个疑问：既然临时表不属于任何数据库，如果临时表和普通表名称相同，会出现什么状况呢？接下来不妨做个测试。

首先在 DEFAULT 数据库创建测试表并写入数据：
CREATE TABLE tmp_v1(title String) ENGINE = Memory;
INSERT INTO tmp_v1 VALUES ('xxx');

接着创建一张名称相同的临时表并写入数据：
CREATE TEMPORARY TABLE tmp_v1(num UInt8) ENGINE = Memory;
INSERT INTO tmp_v1 VALUES (22);

现在查询 tmp_v1 看看会发生什么？
select * from tmp_v1
┌─num─┐
│  22 │
└─────┘
1
2
3
4
5
6
7
8
9
10
11
12
13

通过返回结果可以得出结论，临时表的优先级是大于普通表的。当两张数据表名称相同的时候，会优先读取临时表的数据。当然在ClickHouse的日常使用中，不会刻意地使用临时表，它更多被运用在ClickHouse的内部，是数据在集群之间传播的载体。

5 分区表

数据分区（partition）和数据分片（shard）是完全不同的而个概念，数据分区是针对本地数据而言的，是数据的一种纵向切分；而数据分片是数据的一种横向切分。

数据分区对于一款OLAP数据库而言意义非凡，借助数据分区，在后续的查询过程中能够跳过不必要的数据目录，从而提升查询的性能。合理地利用分区特性，还可以变相实现数据的更新操作，因为数据分区支持删除、替换和重置操作。假设数据表按照月份分区，那么数据就可以按月份的粒度被替换更新。

分区虽好，但不是所有的表引擎都可以使用这项特性，目前只有合并树(MergeTree)家族系列的表引擎才支持数据分区。接下来通过一个简单的例子演示分区表的使用方法，首先由PARTITION BY指定分区键，例如下面的数据表 partition_v1使用了日期字段作为分区键，并将其格式化为年月的形式：

CREATE TABLE partition_v1 (
    ID String,
    URL String,
    EventDate Date
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY ID;
1
2
3
4
5
6
7

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

INSERT INTO partition_v1 VALUES ('a1', 'www.a1.com', '2019-05-01'), ('a2', 'www.a2.com', '2019-06-02');
1

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

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

可以看到，partition_v1按年月划分后，目前拥有两个数据分区，且每个分区都对应一个独立的文件目录，用于保存各自部分的数据。合理设计分区键非常重要，通常会按照数据表的查询场景进行针对性设计。

例如在刚才示例中的数据表按年月分区，如果后续的查询按照分区键过滤，例如：

SELECT * FROM partition_v1 WHERE EventDate ='2019-05-01';
┌─ID─┬─URL────────┬──EventDate─┐
│ a1 │ www.a1.com │ 2019-05-01 │
└────┴────────────┴────────────┘
1
2
3
4

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

6 视图

ClickHouse拥有普通和物化两种视图，其中物化视图拥有独立的存储；而普通视图和关系型数据库中的视图类似，只是一层简单的查询代理。创建普通视图的完整语法如下所示：

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

普通视图不会存储任何数据，它只是一层单纯的SELECT查询映射，起着简化查询、明晰语义的作用，对查询性能不会有任何增强。假设有一张普通视图 view_tb_v1，它是基于数据表tb_v1的id和name两个字段创建的，那么下面的两条SELECT查询是完全等价的：

SELECT * FROM view_tb_v1;
SELECT id, name FROM tb_v1;
1
2

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

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

我们来对这些语法规则举例说明，我们先来创建一张物化视图：

-- 物化视图本质上可以看成是一张特殊的数据表，在创建的时候也需要指定引擎
CREATE MATERIALIZED VIEW girls_view_1 ENGINE=TinyLog()
AS SELECT id, name, age FROM girls;
1
2
3

可以看到相较于普通视图，我们在创建物化的视图的时候，在 create 后面多写了一个 MATERIALIZED 来表示创建的是物化视图、以及指定了一个表引擎。

但需要注意的是，物化视图是可以存储数据的。比如此时的物化视图 girls_view_1是根据表girls的id、name、age三个字段创建的，如果之后再往 girls里面写数据，那么新写入的数据对应的 id、name、age 就会同步到 girl_view_1 中。也就是说，物化视图创建好之后，如果源表被写入新数据，那么物化视图也会同步更新。

但需要注意的是，默认情况下，物化视图在创建时不会拷贝源表中的数据，它只会同步在此之后被写入源表的数据，所以当前girls里面的已存在的数据并没有写入到girls_view_1中。如果希望在创建的物化视图的时候，就顺带把表中的数据也同步过去该怎么做呢？

-- 只需要在 AS SELECT 的前面加上 POPULATE 即可
-- 此时表girls的数据，更准确的说是SELECT查询得到的结果集才会进入物化视图中
CREATE MATERIALIZED VIEW girls_view_1 ENGINE=TinyLog()
POPULATE AS SELECT id, name, age FROM girls;
1
2
3
4

所以POPULATE修饰符决定了物化视图的初始化策略：如果使用了POPULATE修饰符，那么在创建视图的过程中，会连带将源表中已存在的数据（更准确的说是 SELECT 查询得到的结果集）一并导入。反之，如果不使用POPULATE修饰符，那么物化视图在创建之后是没有数据的，它只会同步在此之后被写入源表的数据。

另外物化视图本质是一张特殊的数据表，如果存在的话，那么使用SHOW TABLE也能查看到。而如果删除一个视图，则直接使用DROP TABLE即可，注意：没有DROP VIEW，只要是视图，删除语句都是DROP TABLE，所以这也侧面说明了视图名和表名不可以重复。

然后物化视图还有一个用法，首先创建一个物化视图的时候其实本质上还是会创建一张表，默认名称是 " .inner.物化视图名 "，只不过这张表是隐藏的，其作用就是负责保存物化视图从源表中同步过来的数据。那么问题来了，负责存储物化视图数据的表可不可以我们自己指定呢？答案是可以的。

CREATE MATERIALIZED VIEW girls_view_1 TO other_girls
AS SELECT id, name, age FROM girls;
1
2

物化视图在同步数据的时候就会将数据写入到other_girls中，当然other_girls的表结构与SELECT选择的字段的类型、数量要相匹配，并且此时我们既可以通过物化视图查看数据，也可以通过other_girls来查看。

7 数据表的基本操作

目前只有MergeTree、Merge和Distributed这三类表引擎支持ALTER查询。
一、追加新字段

ALTER TABLE table_name ADD COLUMN [IF NOT EXISTS] 字段名 [类型] [默认值] [插在哪个字段后面]
1

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

ALTER TABLE table_name MODIFY COLUMN [IF NOT EXISTS] 字段名 [类型] [默认值]
1

修改某个字段的数据类型，实质上会调用相应的 toType 转型方法。如果当前的类型与期望的类型不能兼容，则修改类型失败。例如将 String 类型的 IP 字段转成 IPv4 类型是可行的，但是转成 UInt 则会出现错误。

三、修改备注
做好信息备注是保持良好编程习惯的美德之一，所以如果你还没有为列字段添加备注信息，那么就赶紧行动吧。追加备注的语法如下所示：

ALTER TABLE table_name COMMENT COLUMN [IF EXISTS] 字段名 'some comment'

例如
desc table_name
┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id   │ String │              │                    │         │                  │                │
│ col1 │ UInt8  │ DEFAULT      │ 100                │         │                  │                │
│ col2 │ String │ DEFAULT      │ col1               │         │                  │                │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
alter table table_name comment column id '这是ID'
desc table_name
┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id   │ String │              │                    │ 这是ID  │                  │                │
│ col1 │ UInt8  │ DEFAULT      │ 100                │         │                  │                │
│ col2 │ String │ DEFAULT      │ col1               │         │                  │                │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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

ALTER TABLE table_name DROP COLUMN [IF EXISTS] name
1

如果某个字段被删除，那么对应的数据也就被删除了。

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

RENAME TABLE [db_name1.]table_name1 TO [db_name2.]table_name2, [db_name1.]table_name3 TO [db_name2.]table_name3......
1

RENAME 可以修改数据表的名称，如果将原始数据库与目标数据库设为不同的名称，那么就可以实现数据表在两个数据库之间移动的效果，并且还可以同时移动多张。

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

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

TRUNCATE TABLE [IF EXISTS] [db_name.]table_name
1

8 数据分区的基本操作

了解并善用数据分区益处颇多，熟练掌握它的使用方法，可以为后续的程序设计带来极大的灵活性和便利性，目前只有 MergeTree 系列的表引擎支持数据分区。

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

select partition_id,name,table,database from system.parts where table = 'partition_v1';
┌─partition_id─┬─name─────────┬─table────────┬─database─┐
│ 201905       │ 201905_1_1_0 │ partition_v1 │ default  │
│ 201906       │ 201906_2_2_0 │ partition_v1 │ default  │
└──────────────┴──────────────┴──────────────┴──────────┘
1
2
3
4
5

如上所示，目前partition_v1共拥有2个分区，其中partition_id或者name等同于分区的主键，可以基于它们的取值确定一个具体的分区。
二、删除指定分区
合理地设计分区键并利用分区的删除功能，就能够达到数据更新的目的，删除一个指定分区的语法如下所示：

ALTER TABLE table_name DROP PARTITION partition_expr

假如现在需要更新 partition_v1 数据表整个6月份的数据，
则可以先将6月份的分区删除；
ALTER TABLE partition_v1 DROP PARTITION 201906
然后将整个6月份的新数据重新写入，就可以达到更新的目的。
1
2
3
4
5
6

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

ALTER TABLE B REPLACE PARTITION partition_expr FROM A
1

不过需要注意的是，并不是任意数据表之间都能够相互复制，它们还需要满足两个前提条件：
(1)两张表需要拥有相同的分区键;
(2)它们的表结构完全相同;

假设有一个数据表partition_v2，并且与之前partition_v1的分区键和表结构完全相同，那么如果想将partition_v1中5月份的数据导入到partition_v2中，就可以这么做。

ALTER TABLE partition_v2 REPLACE PARTITION 201905 FROM partition_v1
1

四、重置分区数据
如果数据表某一列的数据有误，需要将其重置为默认值，此时可以使用下面的语句实现:

ALTER TABLE table_name CLEAR COLUMN column_name IN PARTITION partition_expr
1

首先如果声明了默认值表达式，那么以表达式为准；否则以相应数据类型的默认值为准，比如String类型的默认值就是空字符串。

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

ALTER TABLE table_name DETACH PARTITION partition_expr
1

假设有一个分区表partition_v3，里面有很多月的数据，那么执行下面的语句就可以将该表中整个8月份的分区卸载。

ALTER TABLE partition_v3 DETACH PARTITION 201908
1

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

记住，一旦分区被移动到了 detached 子日录，就代表它已经脱离了 ClickHouse 的管理，ClickHouse 并不会主动清理这些文件。这此分区文件会一直存在，除非我们主动删除或者使用 ATTACH 语句重新装载它们。装载某个分区的完整语法如下所示：

ALTER TABLE table_name ATTACH PARTITION partition_expr

再次执行下面的语句，就可以将刚才已被卸载的 201908 分区重新装载回去:
ALTER TABLE partition_v3 ATTACH PARTITION 201908
1
2
3
4

六、备份与还原文件
关于分区数据的备份，可以通过 FREEZE 与 FETCH 实现。

9 分布式 DDL 执行

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

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

CREATE TABLE partition_v4 ON CLUSTER ch_cluster(
    ID String,
    URL String,
    EventDate Date
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY ID
1
2
3
4
5
6
7

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