面向OLAP的列式存储DBMS-5-[ClickHouse]的表引擎和数据类型

皮皮冰燃

已于 2022-11-11 22:53:21 修改

阅读量1.5k

点赞数

分类专栏： ClickHouse 文章标签：数据库

于 2020-11-03 16:24:43 首次发布

本文链接：https://blog.csdn.net/qq_20466211/article/details/109472233

版权

ClickHouse 专栏收录该内容

17 篇文章 3 订阅

订阅专栏

参考ClickHouse 的数据类型
#ps -ef | grep clickhouse
#systemctl status clickhouse-server查看状态
#clickhouse-client --user default --password bigdata

1 ClickHouse表引擎

表引擎（即表的类型）决定了：
1)数据的存储方式和位置，写到哪里以及从哪里读取数据。
2)支持哪些查询以及如何支持。
3)并发数据访问。
4)索引的使用（如果存在）。
5)是否可以执行多线程请求。
6)数据复制参数。

1.1 TinyLog

最简单的表引擎，用于将数据存储在磁盘上。
每列都存储在单独的压缩文件中，写入时，数据将附加到文件末尾。
注意：该引擎没有并发控制

如果同时从表中读取和写入数据，则读取操作将抛出异常。
如果同时写入多个查询中的表，则数据将被破坏。

这种表引擎的典型用法是 write-once：
首先只写入一次数据，然后根据需要多次读取。
此引擎适用于相对较小的表（建议最多1,000,000行）。
如果有许多小表，则使用此表引擎是适合的，因为它比需要打开的文件更少。
当拥有大量小表时，可能会导致性能低下。
不支持索引。
案例：
创建一个TinyLog引擎的表并插入一条数据

pda1 :) create table people1(age UInt16,name String) ENGINE=TinyLog;
pda1 :) insert into people1(age,name) values (21,'lucy');

此时我们到保存数据的目录/var/lib/clickhouse/data/default/people1中可以看到如下目录结构：
在这里插入图片描述
age.bin 和 name.bin 是压缩过的对应的列的数据，sizes.json 中记录了每个 *.bin 文件的大小：

1.2 Memory

内存引擎，数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失。
读写操作不会相互阻塞，不支持索引。
简单查询下有非常非常高的性能表现（超过10G/s）。

一般用到它的地方不多，除了用来测试，就是在需要非常高的性能，同时数据量又不太大（上限大概 1 亿行）的场景。

1.3 Merge

Merge 引擎 (不要跟 MergeTree 引擎混淆) 本身不存储数据，但可用于同时从任意多个其他的表中读取数据。
读是自动并行的，不支持写入。
读取时，那些被真正读取到数据的表的索引（如果有的话）会被使用。

Merge 引擎的参数：一个数据库名和一个用于匹配表名的正则表达式。

案例：先建people1，people2，people3三个表，然后用 Merge 引擎的 people 表再把它们链接起来。

pda1 :) create table people1(age UInt16,name String) ENGINE=TinyLog;
pda1 :) create table people2(age UInt16,name String) ENGINE=TinyLog;
pda1 :) create table people3(age UInt16,name String) ENGINE=TinyLog;
pda1 :) insert into people1(age,name) values (21,'lily');
pda1 :) insert into people2 (age,name) values (22,'lucy');
pda1 :) insert into people3 (age,name) values (23,'Jim');

pda1 :) create table people(age UInt16,name String) ENGINE=Merge(currentDatabase(),'^people');
pda1 :) select * from people;

在这里插入图片描述

1.4 MergeTree

Clickhouse 中最强大的表引擎当属 MergeTree （合并树）引擎及该系列（*MergeTree）中的其他引擎。

MergeTree 引擎系列的基本理念如下。
当你有巨量数据要插入到表中，你要高效地一批批写入数据片段，并希望这些数据片段在后台按照一定规则合并。

相比在插入时不断修改（重写）数据进存储，这种策略会高效很多。
格式：
ENGINE [=] MergeTree(
date-column
[,sampling_expression],
(primary,key),
index_granularity)

(1)date-column，类型为 Date 的列名。ClickHouse 会自动依据此列按月创建分区，分区名格式为 “YYYYMM” 。
(2)sampling_expression，采样表达式。
(3)(primary, key)，主键。类型为Tuple()。
(4)index_granularity，索引粒度。即索引中相邻”标记”间的数据行数。设为 8192 可以适用大部分场景。

pda1 :) create table mt_people(ts Date,age UInt8,name String) ENGINE=MergeTree(ts,(age,name),8192);

pda1 :) insert into mt_people values('2020-03-15',20,'zhangsan');
pda1 :) insert into mt_people values('2020-03-20',21,'lisi');
pda1 :) insert into mt_people values('2020-03-25',22,'wangwu');

在这里插入图片描述在/var/lib/clickhouse/data/default/mt_people下可以看到

在这里插入图片描述进入其中一个目录
其中*.bin是按列保存数据的文件
其中*.mrk保存块偏移量
其中primary.idx保存主键索引

1.5 ReplacingMergeTree

这个引擎是在 MergeTree 的基础上，添加了“处理重复数据”的功能，该引擎和MergeTree的不同之处在于它会删除具有相同主键的重复项。
数据的去重只会在合并的过程中出现。
合并会在未知的时间在后台进行，所以你无法预先作出计划。有一些数据可能仍未被处理。

因此，ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。

格式：
ENGINE [=] ReplacingMergeTree(
date-column
[,sampling_expression],
(primary,key),
index_granularity,
[ver])
ReplacingMergeTree比MergeTree只多了一个ver，这个ver指代版本列，它和时间一起配置，区分哪条数据是最新的。

pda1 :) create table rmt_people(ts Date,age UInt8,name String,point UInt8) ENGINE=ReplacingMergeTree(ts,(age,name),8192,point);

pda1 :) insert into rmt_people values ('2020-05-10',21,'kate',10);
pda1 :) insert into rmt_people values ('2020-05-10',21,'kate',30);
pda1 :) insert into rmt_people values ('2020-05-11',21,'kate',10);

在这里插入图片描述
等待一段时间或执行optimize table rmt_people手动触发merge。

pda1 :) optimize table rmt_people;

在这里插入图片描述

1.6 SummingMergeTree

该引擎继承自 MergeTree。

区别在于，当合并 SummingMergeTree 表的数据片段时，ClickHouse 会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值。

如果主键的组合方式使得单个键值对应于大量的行，则可以显著的减少存储空间并加快数据查询的速度，对于不可加的列，会取一个最先出现的值。

1.7 Distributed

分布式引擎，本身不存储数据, 但可以在多个服务器上进行分布式查询。
读是自动并行的。
读取时，远程服务器表的索引（如果有的话）会被使用。

2 clickhouse数据类型

作为一款分析型数据库，ClickHouse提供了许多数据类型，它们可以划分三类： 基础类型、复合类型和特殊类型。其中基础类型使ClickHouse具备了描述数据的基本能力，而另外两种类型则使ClickHouse的数据表达能力更加丰富立体。
在这里插入图片描述

2.1 基础类型

基础类型只有数值、字符串和时间这三种类型。值得一提的是，与大多数普通的数据库不一样的是，ClickHouse没有Boolean类型，但是呢，可以使用整型的0或1替代。
注：准确来说，还有布尔类型（Bool），但由于没有 true、false，所以一般都用整型（UInt8）表示布尔类型，1为真0为假。

2.1.1 时间

时间类型分为DateTime、DateTime64和Date三类。ClickHouse目前没有时间戳类型。

字符串："2022-11-03 15:45:26.219"
时间戳：1667461526219毫秒

2.1.1.1 DateTime

DateTime类型包含时、分、秒信息，精确到秒，支持使用字符串形式写入：

创建
CREATE TABLE DateTime_test(
	t1 Datetime
) ENGINE=Memory

写入带了毫秒报错
insert into DateTime_test values('2022-11-03 15:45:26.219')
写入不带毫秒正常
insert into DateTime_test values('2022-11-03 15:45:26')

查询
select t1,toTypeName(t1) from DateTime_test

t1                 |toTypeName(t1)|
-------------------|--------------|
2022-11-03 15:45:26|DateTime      |

2.1.1.2 DateTime64

DateTime64可以记录亚秒，它在DateTime之上增加了精度的设置，例如：
精度表示截取几位毫秒数。

创建
CREATE TABLE DateTime64_test(
	t1 DateTime64(3)
) ENGINE=Memory

写入带了毫秒正常
insert into DateTime64_test values('2022-11-03 15:45:26.219')

查询
select t1,toTypeName(t1) from DateTime64_test

2.1.1.3 Date

Date类型不包括具体的时间信息，只精确到天，它同样也支持字符串形式写入：

创建
CREATE TABLE Date_test(
	t1 Date
) ENGINE=Memory

写入带了时:分:秒.毫秒报错
insert into Date_test values('2022-11-03 15:45:26.219')
写入不带时:分:秒.毫秒正常
insert into Date_test values('2022-11-03')

查询
select t1,toTypeName(t1) from Date_test

t1        |toTypeName(t1)|
----------|--------------|
2022-11-03|Date          |

2.1.2 数值类型

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

2.1.2.1 Int

在普遍观念中，常用 Tinyint、Smallint、Int 和 Bigint 指代整数的不同取值范围，而 ClickHouse 则直接使用 Int8、Int16、Int32、Int64 来指代 4 种大小的 Int 类型，其末尾的数字则表示该类型的整数占多少位。可以认为：Int8 等价于 Tinyint、Int16 等价于 Smallint、Int32 等价于 Int、Int64 等价于 Bigint。

ClickHouse 也支持无符号的整数，使用前缀 U 表示，比如：UInt8、UInt16、UInt32、UInt64。

2.1.2.2 Float

与整数类似，ClickHouse 直接使用 Float32 和 Float64 代表单精度浮点数和双精度浮点数，可以看成是 float 和 double。
ClickHouse 的浮点数支持正无穷、负无穷以及非数字的表达方式。
在这里插入图片描述

2.1.2.3 Decimal

如果要求高精度的数值运算，则需要使用Decimal、即定点数（类似于浮点数），ClickHouse 提供了 Decimal32、Decimal64 和 Decimal128 三种精度的Decimal。

在定义表字段的类型时，可以通过两种形式声明：简写方式有 Decimal32(S)、Decimal64(S)、Decimal128(S) 三种。

原生方式为 Decimal(P, S)，表示该定点数的整数位加上小数位的总长度最大为 P，其中小数位长度最多为 S。

Decimal32的P为10、Decimal64的P为19、Decimal128的P为39。比如某个字段类型是 Decimal32(3)，那么表示该字段存储的定点数，其整数位加上小数位的总长度不超过10，其中小数部分如果超过3位则只保留3位。

而在 SQL 中我们可以通过 toDecimal32 或 toDecimal64 将一个整数或浮点数变成定点数，比如：toDecimal32(2, 5) 得到的结果就是 2.00000。

另外使用两个不同精度的 Decimal 进行四则远算的时候，它们的小数点位数会 S 发生变化。

在进行加法和减法运算时，S取最大值。
在进行乘法运算时，S 取两者之和。
在进行除法运算时，S 取被除数的值，此时要求被除数的 S 必须大于除数 S，否则报错。

9c1bacd594e2 :) select toDecimal32(22, 3) + toDecimal32(33, 2)
9c1bacd594e2 :) select toDecimal32(22, 3) * toDecimal32(33, 2)
9c1bacd594e2 :) select toDecimal32(22, 3) / toDecimal32(33, 2)
9c1bacd594e2 :) select toDecimal32(22, 2) / toDecimal32(33, 3)报错

另外还有一点需要注意：由于现代计算机系统只支持 32 或者 64 位，所以 Decimal128 是在软件层面模拟出来的，它的速度会比 Decimal32、Decimal64 要慢。

2.1.3 字符串类型

字符串类型可以细分为 String、FixedString 和 UUID 三类，从命名来看仿佛不像是一款数据库提供的类型，反倒像一门编程语言的设计。

2.1.3.1 String

字符串由 String 定义，长度不限，因为在使用 String 的时候无需声明大小。它完全代替了传统意义上的 Varchar、Text、Clob 和 Blob 等字符类型。

String 类型不限定字符集，因为它根本没有这个概念，所以可以将任意编码的字符串存入其中。但是为了程序的规范性和可维护性，在同一套程序中使用统一的编码，比如 utf-8，就是一种很好的约定。

2.1.3.2 FiexedString

FixedString 类型和传统意义上的 Char 类型有些类似，对于一些有着明确长度的场合，可以使用 FixedString(N) 来声明固定长度的字符串。但与 char 不同的是，FixedString 使用 NULL 字节来填充末尾字符，而 char 通常使用空格填充。

可以使用 toFixedString 生成 FixedString。

9c1bacd594e2 :) select toFixedString('test', 7), length(toFixedString('test', 7))

┌─toFixedString('test', 7)─┬─length(toFixedString('test', 7))─┐
│ test                     │                                7 │
└──────────────────────────┴──────────────────────────────────┘

2.1.3.3 UUID

UUID 是一种数据库常见的主键类型，在 ClickHouse 中直接把它作为一种数据类型。UUID 共有 32 位，它的格式为 8-4-4-4-12。如果一个 UUID 类型的字段在写入数据的时候没有被赋值，那么它会按照相应格式用 0 填充。

2.2 复合类型

除了基础数据类型之外，ClickHouse 还提供了数组、元组、枚举和嵌套，总共四种复合类型。这些类型通常都是其他数据库原生不具备的特性，拥有了复合类型之后，ClickHouse 的数据模型表达能力就更强了。

2.2.1 数组Array

定义类型：array(T) 或 [T]，其中T是数组的数据类型。

数组里面的元素可以有多种，但前提是它们必须能够兼容，比如：[1, 2.13] 可以，但是 [1, ‘ABC’] 则不行。而在定义表字段的时候，如果使用 Array 类型，则需要指定明确的元素类型，比如：

建表
CREATE TABLE array_test(a Array(String)) ENGINE=Memory
写入
insert into array_test values (['a','b','c'])
查询
select * from array_test

toTypeName 表示获取字段的类型.
select array(1, 2) as a, toTypeName(a)
select [1, 2] as a, toTypeName(a)
┌─a─────┬─toTypeName([1, 2])─┐
│ [1,2] │ Array(UInt8)       │
└───────┴────────────────────┘

从上述的例子中可以发现，在查询时并不需要主动声明数据的元素类型，因为 ClickHouse 的数组拥有类型推断的能力，推断的依据是：以最小存储代价为原则，即使用最小可表达的数据类型。比如：array(1, 2) 会使用 UInt8 作为数组类型。但如果数组中存在 NULL 值，元素类型将变为 Nullable。

select [1, 2, NULL] as a, toTypeName(a)
┌─a──────────┬─toTypeName([1, 2, NULL])─┐
│ [1,2,NULL] │ Array(Nullable(UInt8))   │
└────────────┴──────────────────────────┘

2.2.2 元组Tuple

元组由1~n个元素组成，每个元素之间允许设置不同的数据类型，且彼此之间不要求兼容。元组同样支持类型推断，其推断依据仍然是以最小存储代价为原则。与数组类似，在 SQL 中我们可以通过 Tuple(T) 来定义。

类似数组，我们可以使用 tuple 函数在查询的时候创建元组：

SELECT tuple(1, 'a', now()) as a, (1, 3, '666') as b
┌─a─────────────────────────────┬─b───────────┐
│ (1,'a','2022-11-11 14:21:06') │ (1,3,'666') │
└───────────────────────────────┴─────────────┘

关于数组和元组的区别，熟悉 Python 的话应该很清楚，答案是元组不可变。在定义表字段时，元组也需要指定明确的元素类型。

建表
CREATE TABLE tuple_test(a Tuple(Int8,String,Int8)) ENGINE = Memory
写入
insert into tuple_test values ((1,'lena',10))
查询
select * from tuple_test

在数据写入的过程中会进行类型检查。

2.2.3 枚举Enum

ClickHouse支持枚举类型，这是一种在定义常量时经常会使用的数据类型。ClickHouse 提供了 Enum8 和 Enum16 两种枚举类型，它们之间除了取值范围不同之外，别无二致。

枚举固定使用 (String:Int) 键值对的形式定义数据，所以 Enum8 和 Enum16 分别会对应 (String:Int8) 和 (String:Int16)，例如：

CREATE TABLE table_name(e Enum('ready'=1, 'start'=2, 'success'=3,'error'=4)) ENGINE = Memory;

在定义枚举集合的时候，有几点需要注意。
首先，Key 和 Value 是不允许重复的，要保证唯一性。
其次，Key 和 Value 的值都不能为 Null，但 Key 允许为空字符串。在写入枚举数据的时候，只会用到 Key 字符串部分，例如：

INSERT INTO table_name VALUES('ready')

另外在数据写入的时候，会对照枚举集合项的内容进行逐一检查，如果 Key 字符串不存在集合范围内则会抛出异常，比如执行下面的语句就会报错：

INSERT INTO table_name VALUES('abc')  -- 会报错

可能有人觉得，完全可以使用 String 代替枚举，为什么还需要专门实现枚举类型呢？答案是出于对性能的考虑。因为虽然枚举中定义的 Key 是属于 String 类型，但是在后续对枚举的所有操作中（包括排序、分子、去重、过滤等），会使用 Int 类型的 Value 值。

2.2.4 嵌套Nested

创建
CREATE TABLE nested_test(
	id Int8,
	man Nested(
		id Int8,
		name String
	)
) ENGINE = Memory
写入
insert into nested_test values (1,[10,20],['lily','lucy'])
查询
select class.id from nested_test
select * from nested_test 

id|class.id|class.name     |
--|--------|---------------|
 1|[10,20] |['lily','lucy']|

嵌套类型，顾名思义是一种嵌套表结构。一张数据表，可以定义任意多个嵌套类型字段，但每个字段的嵌套层级只支持一级，即嵌套表内不能继续使用嵌套类型。

对于简单场景的层级关系或关联关系，使用嵌套类型也是一种不错的选择。例如，我们下面创建一张表 nested_test，具体的建表逻辑后面会说，当然本身也不是特别难的东西。

CREATE TABLE nested_test (
    name String,
    age UInt8,
    dept Nested(
        id UInt32,
        name String
    )
) ENGINE = Memory;

ClickHouse 的嵌套类型和传统的嵌套类型不相同，导致在初次接触它的时候会让人十分困惑。以上面这张表为例，如果按照它的字面意思来理解，会很容易理解成 nested_test 与 dept 是一对一的包含关系，其实这是错误的。不信可以执行下面的语句，看看会是什么结果:
在这里插入图片描述
我们看到报错了，现在大家应该明白了，嵌套类型本质是一种多维数组的结构。嵌套表中的每个字段都是一个数组，并且行与行之间数组的长度无须对齐。所以需要把刚才的 INSERT 语句调整成下面的形式:

INSERT INTO nested_test VALUES('lucy', 20, [100], ['lily']);
-- 行与行之间，数组长度无需对齐。
INSERT INTO nested_test VALUES('lucy', 20, [200, 300], ['peter', 'jim']);

需要注意的是，在同一行数据内每个数组字段的长度必须相等。
在这里插入图片描述

2.3 特殊类型

ClickHouse 还有一类不同寻常的数据类型，将它们定义为特殊类型。

2.3.1 Nullable

准确来说，Nullable 并不能算是一种独立的数据类型，它更像是一种辅助的修饰符，需要与基础数据类型一起搭配使用。

Nullable 类型与 Python 类型注解里面的 Optional 有些相似，它表示某个基础数据类型可以是 NULL 值。其具体用法如下所示:

CREATE TABLE null_test(col1 String,col2 Nullable(UInt8))ENGINE = Memory;

通过 Nullable 修饰后 col2 字段可以被写入 NULL 值:

INSERT INTO null_test VALUES ('nana', NULL);

在使用 Nullable 类型的时候还有两点值得注意：首先，它只能和基础类型搭配使用，不能用于数组和元组这些复合类型，也不能作为索引字段；其次，应该慎用 Nullable 类型，包括 Nullable 的数据表，不然会使查询和写入性能变慢。因为在正常情况下，每个列字段的数据会被存储在对应的 [Column].bin 文件中。如果一个列字段被 Nullable 类型修饰后，会额外生成一个 [Column].null.bin 文件专门保存它的 NULL 值。这意味着在读取和写入数据时，需要一倍的额外文件操作。

2.3.2 Domain

域名类型分为 IPv4 和 IPv6 两类，本质上它们是对整型和字符串的进一步封装。IPv4 类型是基于 UInt32 封装的，它的具体用法如下所示：

CREATE TABLE ip4_test(url String,ip IPv4) ENGINE = Memory;
INSERT INTO ip4_test VALUES ('www.nana.com', '127.0.0.1');

细心的人可能会问，直接使用字符串不就行了吗？为何多此一举呢？至少有如下两个原因：
(1)出于便捷性的考量，例如IPv4类型支持格式检查，格式错误的IP数据是无法被写入的，例如：

INSERT INTO ip4_test VALUES('www,nana.com', '192.0.0')报错

(2)出于性能的考量，同样以 IPv4 为例，IPv4 使用 UInt32 存储，相比 String 更加紧凑，占用的空间更小，查询性能更快。IPv6 类型是基于 FixedString(16) 封装的，它的使用方法与 IPv4 别无二致，此处不再赘述。

在使用 Domain 类型的时候还有一点需要注意，虽然它从表象上看起来与 String 一样，但 Domain 类型并不是字符串，所以它不支持隐式的自动类型转换。如果需要返回 IP 的字符串形式，则需要显式调用 IPv4NumToString 或 IPv6NumToString 函数进行转换。

皮皮冰燃

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
面向OLAP的列式存储DBMS-5-[ClickHouse]的表引擎和数据类型

pip install clickhouse_driverfrom clickhouse_driver import Clienthost = '10.23.241.111'db = 'system'client = Client(host=host,database=db)sql = "select * from clusters"a = client.execute(sql)print(a)
复制链接

扫一扫