ClickHouse学习笔记三之数据定义

一。数据类型

1.基础类型

数值类型

数值类型分为整数、浮点数和定点数三类，接下来分别进行说明。

Int——整数
- 有符号
名称大小(字节) 范围传统概念
Int8 1 -128到127 Tinyint
Int16 2 -32768到32767 Smallint
Int32 4 -2147483648到-2147483647 Int
Int64 8 -9223372036854775808到9223372036854775807 Bigint
- 无符号
名称大小(字节) 范围传统概念
UInt8 1 0到255 Tinyint Unsigned
UInt16 2 0到65535 Smallint Unsigned
UInt32 4 0到4294967295 Int Unsigned
UInt64 8 0到18446744073709551615 Bigint Unsigned
Float——浮点型
名称大小（字节）有效精度（位数）传统概念
Float32 4 7 Float
Float64 8 16 Double

名称	大小(字节)	范围	传统概念
Int8	1	-128到127	Tinyint
Int16	2	-32768到32767	Smallint
Int32	4	-2147483648到-2147483647	Int
Int64	8	-9223372036854775808到9223372036854775807	Bigint

名称	大小(字节)	范围	传统概念
UInt8	1	0到255	Tinyint Unsigned
UInt16	2	0到65535	Smallint Unsigned
UInt32	4	0到4294967295	Int Unsigned
UInt64	8	0到18446744073709551615	Bigint Unsigned

名称	大小（字节）	有效精度（位数）	传统概念
Float32	4	7	Float
Float64	8	16	Double

Decimal——定点数
如果要求更高精度的数值运算，则需要使用定点数。ClickHouse提供了Decimal32、Decimal64和Decimal128三种精度的定点数。可以通过两种形式声明定点：简写方式有Decimal32(S)、Decimal64(S)、Decimal128(S)三种，原生方式为Decimal(P,S)，其中：
- P代表精度，决定总位数（整数部分+小数部分），取值范围是1～38；
- S代表规模，决定小数位数，取值范围是0～P；
简写方式与原生方式对应关系：
名称等效声明范围
Decimal32(S) Decimal(1 ~ 9,S) -1*10^(9-S)到1*10(9-S)
Decimal64(S) Decimal(10 ~ 18,S) -1*10^{(18-S)到1*10}(18-S)
Decimal128(S) Decimal(19 ~ 38,S) -1*10^{(38-S)到1*10}(38-S)
在使用两个不同精度的定点数进行四则运算的时候，它们的小数点位数S会发生变化。变化如下：
- 在进行加法运算时，S取最大值。如toDecimal64(2,4)与toDecimal32(2,2)相加后S=4；
- 在进行减法运算时，其规则与加法运算相同，S同样会取最大值。例如toDecimal32(4,4)与toDecimal64(2,2)相减后S=4；
- 在进行乘法运算时，S取两者S之和。例如下面的查询，toDecimal64(2,4)与toDecimal32(2,2)相乘后S=4+2=6；
- 在进行除法运算时，S取被除数的值，此时要求被除数S必须大于除数S，否则会报错。例如toDecimal64(2,4)与toDecimal32(2,2)相除后S=4；
总结如下：
运算名称运算规则
加 S=max(S1,S2)
减 S=max(S1,S2)
乘 S=S1+S2
除 S=S1(S1为被除数，S1/S2)(S1范围>=S2范围)
注意：在使用定点数时还有一点值得注意：由于现代计算器系统只支持32位和64位CPU，所以Decimal128是在软件层面模拟实现的，它的速度会明显慢于Decimal32与Decimal64。

名称	等效声明	范围
Decimal32(S)	Decimal(1 ~ 9,S)	-110^(9-S)到110(9-S)
Decimal64(S)	Decimal(10 ~ 18,S)	-110^{(18-S)到110}(18-S)
Decimal128(S)	Decimal(19 ~ 38,S)	-110^{(38-S)到110}(38-S)

运算名称	运算规则
加	S=max(S1,S2)
减	S=max(S1,S2)
乘	S=S1+S2
除	S=S1(S1为被除数，S1/S2)(S1范围>=S2范围)

字符串类型

字符串类型可以细分为String、FixedString和UUID三类。

String
字符串由String定义，长度不限。因此在使用String的时候无须声明大小。它完全代替了传统意义上数据库的Varchar、Text、Clob和Blob等字符类型。String类型不限定字符集，因为它根本就没有这个概念，所以可以将任意编码的字符串存入其中。但是为了程序的规范性和可维护性，在同一套程序中应该遵循使用统一的编码，例如“统一保持UTF8编码”就是一种很好的约定。
FixedString
FixedString类型和传统意义上的Char类型有些类似，对于一些字符有明确长度的场合，可以使用固定长度的字符串。定长字符串通过FixedString(N)声明，其中N表示字符串长度。但与Char不同的是，FixedString使用null字节填充末尾字符，而Char通常使用空格填充。比如在下面的例子中，字符串‘abc’虽然只有3位，但长度却是5，因为末尾有2位空字符填充。
UUID
UUID是一种数据库常见的主键类型，在ClickHouse中直接把它作为一种数据类型。UUID共有32位，它的格式为844412。如果一个UUID类型的字段在写入数据时没有被赋值，则会依照格式使用0填充。

时间类型

时间类型分为DateTime、DateTime64和Date三类。ClickHouse目前没有时间戳类型。时间类型最高的精度是秒，也就是说，如果需要处理毫秒、微秒等大于秒分辨率的时间，则只能借助UInt类型实现。

DateTime
DateTime类型包含时、分、秒信息，精确到秒，支持使用字符串形式写入：
2021-09-22 00:00:00
DateTime64
DateTime64可以记录亚秒，它在DateTime之上增加了精度的设置:
2021-09-22 00:00:00.00
Date
Date类型不包含具体的时间信息，只精确到天，它同样也支持字符串形式写入:
2021-09-22

2.复合类型

除了基础数据类型之外，ClickHouse还提供了数组、元组、枚举和嵌套四类复合类型。这些类型通常是其他数据库原生不具备的特性。拥有了复合类型之后，ClickHouse的数据模型表达能力更强了。虽然很实用，这里涉及的编程知识太多，不做展开了。

Array数组

Tuple元组

Enum枚举

Nested嵌套

3.特殊类型

Nullable

Domain

二。如何定义数据表

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

1.数据库

CREATE DATABASE IF NOT EXISTS db_name [ ENGINE = engine ];
SHOW DATABASES;
USE db_name;
DROP DATABASE [IF EXISTS] db_name;

[ENGINE=engine]表示数据库所使用的引擎类型。数据库目前一共支持5种引擎，如下所示:

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

默认数据库的实质是物理磁盘上的一个文件目录，所以在语句执行之后，ClickHouse便会在安装路径下创建DB_TEST数据库的文件目录，与此同时，在metadata路径下也会一同创建用于恢复数据库的DB_TEST.sql文件。

2.数据表

ClickHouse数据表的定义语法，是在标准SQL的基础之上建立的，所以熟悉数据库的读者们在看到接下来的语法时，应该会感到很熟悉。ClickHouse目前提供了三种最基本的建表方法。

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

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

使用[db_name.]参数可以为数据表指定数据库，如果不指定此参数，则默认会使用default数据库。注意末尾的ENGINE参数，它被用于指定数据表的引擎。表引擎决定了数据表的特性，也决定了数据将会被如何存储及加载。

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

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

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

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

ClickHouse和大多数数据库一样，使用DESC查询可以返回数据表的定义结构。Drop删除表

DESC [db_name.]table_name; 
DROPTABLE[IFEXISTS][db_name.]table_name;

3.默认表达式

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

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

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

数据写入：在数据写入时，只有DEFAULT类型的字段可以出现在INSERT语句中。而MATERIALIZED和ALIAS都不能被显式赋值，它们只能依靠计算取值。
数据查询：在数据查询时，只有DEFAULT类型的字段可以通过SELECT返回。而MATERIALIZED和ALIAS类型的字段不会出现在SELECT查询的返回结果集中。
数据存储：在数据存储时，只有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|ALIASexpr],
name2 [type] [DEFAULT|MATERIALIZED|ALIASexpr],
);

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

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

临时表的优先级是大于普通表的。当两张数据表名称相同的时候，会优先读取临时表的数据。在ClickHouse的日常使用中，通常不会刻意使用临时表。它更多被运用在ClickHouse的内部，是数据在集群间传播的载体.

5.分区表

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

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

CREATE TABLE partition_v1 (
ID String,
domain String,
EventTime DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventTime)
ORDER BY ID;

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

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

合理设计分区键非常重要，通常会按照数据表的查询场景进行针对性设计。例如在刚才的示例中数据表按年月分区，如果后续的查询按照分区键过滤。当然，使用不合理的分区键也会适得其反，分区键不应该使用粒度过细的数据字段。例如，按照小时分区，将会带来分区数量的急剧增长，从而导致性能下降。关于数据分区更详细的原理说明，以后跟大家详细讲解。

6.视图

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

CREAT EVIEW [IF NOT EXISTS] [db_name.]view_name 
AS
SELECT ...;

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

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

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

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

物化视图本质是一张特殊的数据表，例如使用SHOWTABLE查看数据表的列表可以查到.inner.特殊前缀的数据表，所以删除视图的方法是直接使用DROP。

三。数据表基本操作

1.追加字段

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

ALTER TABLE tb_name ADD COLUMN [IF NOT EXISTS] name [type] [default_expr] [AFTERname_after];
ALTER TABLE testcol_v1 ADD COLUMN OS String DEFAULT 'mac'; 
ALTER TABLE test col_v1 ADD COLUMN IPS tring AFTER ID ;

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

2.修改数据类型

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

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

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

ALTER TABLE test col_v1 MODIFY COLUMN IP IPv4;

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

3.修改备注

做好信息备注是良好的习惯之一，追加备注的语法如下所示：

ALTER TABLE tb_name COMMENT COLUMN [IF EXISTS] name 'somecomment';

4.删除已有字段

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

ALTER TABLE tb_name DROP COLUMN [IF EXISTS] name;

列字段在被删除之后，它的数据也会被连带删除。

5.移动数据表

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

RENAME TABLE default.testcol_v1 TO db_test.testcol_v2;

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

6.清空数据表

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

TRUNCATE TABLE [IF EXISTS] [db_name.]tb_name;

四。数据分区的基本操作

1.查询分区信息

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

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

2.删除指定分区

合理地设计分区键并利用分区的删除功能，就能够达到数据更新的目的。假如现在需要更新partition_v2数据表整个7月份的数据，则可以先将7月份的分区删除,然后将整个7月份的新数据重新写入，就可以达到更新的目的：

ALTER TABLE partition_v2 DROP PARTITION 201907;
INSERT INTO partition_v2 VALUES ('A004update','www.123.com','20210922'),…;

3.复制分区数据

ClickHouse支持将A表的分区数据复制到B表，这项特性可以用于快速数据写入、多表间数据同步和备份等场景,不过需要注意的是，并不是任意数据表之间都能够相互复制，它们还需要满足两个前提条件：

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

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

INSERT INTO partition_v1 VALUES ('A006v1','www.v1.com','20210805'),('A007v1','www.v1.com','20210820');
ALTERT ABLE partition_v2 REPLACE PARTITION 201908 FROM partition_v1;

4.重置分区数据

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

ALTER TABLE partition_v2 CLEAR COLUMN URL in PARTITION 201908;

5.装载和卸载分区

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

ALTER TABLE partition_v2 DETACH PARTITION 201908;
ALTER TABLE partition_v2 ATTACH PARTITION 201908;

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

6.备份与还原分区

分区数据的备份，可以通过FREEZE与FETCH实现，这是个复杂且重要的事件，留到后面单独讲解。

五。分布式DLL执行

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

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

六。数据的增删改

1. 增

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

第一种是使用VALUES格式的常规语法：

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

第二种是使用指定格式的语法：

INSERT INTO [db.]table [(c1,c2,c3…)] FORMAT format_name data_set;
INSERT INTO partition_v2 FORMAT CSV \
'A0017','www.123.com','20211001' \
'A0018','www.123.com','20211001';

第三种是使用SELECT子句形式的语法：

INSERT INTO partition_v2 SELECT * FROM partition_v1;

虽然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命令行或者INSERTSELECT子句写入时是不生效的。

2.删

ClickHouse提供了DELETE和UPDATE的能力，这类操作被称为Mutation查询，它可以看作ALTER语句的变种。虽然Mutation能最终实现修改和删除，但不能完全以通常意义上的UPDATE和DELETE来理解，我们必须清醒地认识到它的不同：首先，Mutation语句是一种“很重”的操作，更适用于批量数据的修改和删除；其次，它不支持事务，一旦语句被提交执行，就会立刻对现有数据产生影响，无法回滚；最后，Mutation语句的执行是一个异步的后台过程，语句被提交之后就会立即返回。所以这并不代表具体逻辑已经执行完毕，它的具体执行进度需要通过system.mutations系统表查询。

ALTER TABLE partition_v2 DELETE WHERE ID='A003';

在执行了DELETE操作之后数据目录发生了一些变化。每一个原有的数据目录都额外增加了一个同名目录，并且在末尾处增加了_6的后缀。此外，目录下还多了一个名为mutation_6.txt的文件.mutation_6.txt是一个日志文件，它完整地记录了这次DELETE操作的执行语句和时间，而文件名的后缀_6与新增目录的后缀对应。那么后缀的数字从何而来呢？

SELECT database,table,mutation_id,block_numbers.number as num,is_done FROM system.mutations;

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

3.改

数据修改除了需要指定具体的列字段之外，整个逻辑与数据删除一模一样，UPDATE支持在一条语句中同时定义多个修改字段，分区键和主键不能作为修改字段。

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