参考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 函数进行转换。