ClickHouse 数据表、数据分区的相关操作，以及 DDL(四)

​ 本文来源： (https://www.cnblogs.com/traditional/p/15218664.html

楔子

在知晓了 ClickHouse 的主要数据类型之后，接下来我们开始介绍 DDL 操作以及定义数据的方法，DDL 查询提供了数据表的创建、修改和删除操作，是最常用的功能之一。

数据库

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

1
CREATE DATABASE IF NOT EXISTS db_name[ENGINE = engine]

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

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

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

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

1
CREATE DATABASE kagura_nana;

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

1
2
3
[root@satori ~]# ls /var/lib/clickhouse/data/
default  kagura_nana  system
[root@satori ~]#

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

1
2
3
[root@satori ~]# ls /var/lib/clickhouse/metadata/
default  default.sql  kagura_nana  kagura_nana.sql  system  system.sql
[root@satori ~]#

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
satori :) SHOW DATABASES;

SHOW DATABASES

Query id: 2a4de6ad-535f-489f-955e-dae511a18415

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

3 rows in set. Elapsed: 0.002 sec.

satori :)

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

1
DROP DATABASE [IF EXISTS] db_name;

数据表

我们说数据库在物理磁盘上对应一个目录文件，而表则是在数据库对应的目录文件里面再创建一个目录文件，比如我们在数据库 kagura_nana 里面创建一张表 t，那么相当于在 /var/lib/clickhouse/data/kagura_nana 里面创建一个目录文件 t，而往表 t 里面写的数据则会在目录 t 中以文本文件的形式保存，所以整个逻辑还是比较清晰好理解的。而 ClickHouse 数据表的定义语法，是在标准 SQL 的基础之上建立的，所以熟悉数据库的你在看到接下来的语法时，应该会感到很熟悉。ClickHouse 目前提供了三种最基本的建表方法：

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

1
2
3
4
5
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

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

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

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

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

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

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

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

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

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

上述语句会将 SELECT * FROM db.exists_table 的查询结果一并写入数据表。

ClickHouse 和大多数数据库一样，使用 DESC 查询可以返回数据表的定义结构，另外如果想删除一张数据表，则可以使用下面的 DROP 语句：

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

默认值表达式

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

1
CREATE TABLE table_name (    id String,    col1 DEFAULT 100,    col2 String DEFAULT col1) ENGINE=Memory

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

1
INSERT INTO table_name(id) VALUES('AAA');

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

1
satori :) SELECT id, col1, col2, toTypeName(col1), toTypeName(col2) from table_nameSELECT    id,    col1,    col2,    toTypeName(col1),    toTypeName(col2)FROM table_nameQuery id: d9114fe3-172f-4e2f-bd2a-13b514015de9┌─id──┬─col1─┬─col2─┬─toTypeName(col1)─┬─toTypeName(col2)─┐│ AAA │  100 │ 100  │ UInt8            │ String           │└─────┴──────┴──────┴──────────────────┴──────────────────┘1 rows in set. Elapsed: 0.002 sec.satori :)

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

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

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

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

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

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

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

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

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

临时表

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

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

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

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

1
CREATE TABLE tmp_v1(title String) ENGINE = Memory;INSERT INTO tmp_v1 VALUES ('xxx');

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

1
CREATE TEMPORARY TABLE tmp_v1(num UInt8) ENGINE = Memory;INSERT INTO tmp_v1 VALUES (22);

现在查询 tmp_v1 看看会发生什么？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
satori :) SELECT * FROM tmp_v1

SELECT *
FROM tmp_v1

Query id: ad4b5094-3627-4af7-a207-bbd8ef90617a

┌─num─┐
│  22 │
└─────┘

1 rows in set. Elapsed: 0.002 sec.

satori :)

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

分区表

数据分区（partition）和数据分片（shard）是完全不同的而个概念，数据分区是针对本地数据而言的，是数据的一种纵向切分；而数据分片是数据的一种横向切分（后续章节会详细说）。数据分区对于一款 OLAP 数据库而言意义非凡，借助数据分区，在后续的查询过程中能够跳过不必要的数据目录，从而提升查询的性能。合理地利用分区特性，还可以变相实现数据的更新操作，因为数据分区支持删除、替换和重置操作。假设数据表按照月份分区，那么数据就可以按月份的粒度被替换更新。

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

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

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

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

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

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

img

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

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

1
SELECT * FROM partition_v1 WHERE EventDate ='2019-05-01'

那么在后续的查询过程中，可以利用分区索引跳过 6 月份的分区目录，只加载 5 月份的数据，从而带来查询的性能提升。

当然，使用不合理的分区键也会适得其反，分区键不应该使用粒度过细的数据字段。例如按照小时分区，将会带来分区数量的急剧增长，从而导致性能下降。关于数据分区更详细的原理说明，也会在后续章节进行。

视图

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

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

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

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

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

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

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

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

可以看到相较于普通视图，我们在创建物化的视图的时候，在 create 后面多写了一个 MATERIALIZED 来表示创建的是物化视图、以及指定了一个表引擎。但需要注意的是，物化视图是可以存储数据的。比如此时的物化视图 girls_view_1 是根据表 girls 的 id、name、age 三个字段创建的，如果之后再往 girls 里面写数据，那么新写入的数据对应的 id、name、age 就会同步到 girl_view_1 中。也就是说，物化视图创建好之后，如果源表被写入新数据，那么物化视图也会同步更新。

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

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

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

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

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

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

物化视图在同步数据的时候就会将数据写入到 other_girls 中，当然 other_girls 的表结构与 SELECT 选择的字段的类型、数量要相匹配，并且此时我们既可以通过物化视图查看数据，也可以通过 other_girls 来查看。这种用法，我们后面介绍表引擎的时候会说，目前先了解物化视图的用法。

数据表的基本操作

目前只有 MergeTree、Merge 和 Distributed 这三类表引擎支持 ALTER 查询，如果现在还不明白这些表引擎的作用也不必担心，目前只需简单了解这些信息即可，后面会对它们进行介绍。

追加新字段

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

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

修改字段类型

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

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

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

修改备注

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

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

img

删除已有字段

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

1
ALTER TABLE table_name DROP COLUMN [IF EXISTS] name

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

移动数据表

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

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

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

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

清空数据表

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

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

数据分区的基本操作

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

查询分区信息

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

img

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

删除指定分区

合理地设计分区键并利用分区的删除功能，就能够达到数据更新的目的，删除一个指定分区的语法如下所示：

1
ALTER TABLE table_name DROP PARTITION partition_expr

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

1
ALTER TABLE partition_v1 DROP PARTITION 201906

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

复制分区数据

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

1
ALTER TABLE B REPLACE PARTITION partition_expr FROM A

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

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

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

1
ALTER TABLE partition_v2 REPLACE PARTITION 201905 FROM partition_v1

重置分区数据

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

1
ALTER TABLE table_name CLEAR COLUMN column_name IN PARTITION partition_expr

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

装载与卸载分区

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

1
ALTER TABLE table_name DETACH PARTITION partition_expr

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

1
ALTER TABLE partition_v3 DETACH PARTITION 201908

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

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

1
ALTER TABLE table_name ATTACH PARTITION partition_expr

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

1
ALTER TABLE partition_v3 ATTACH PARTITION 201908

备份与还原文件

关于分区数据的备份，可以通过 FREEZE 与 FETCH 实现，由于目前还缺少相关的背景知识，所以我们在后续章节介绍。

分布式 DDL 执行

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

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

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

当然，如果现在执行这条语句是不会成功的。因为到目前为止还没有配置过 ClickHouse 的集群模式，目前还不存在一个名为 ch_cluster 的集群，这部内容会放到后续章节展开说明。