ClickHouse 的数据类型(三)

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

楔子

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

下面就来分门别类的介绍一下。

基础类型

基础类型只有数值、字符串和时间三种类型，注：准确来说，还有布尔类型（Bool），但由于没有 true、false，所以一般都用整型（UInt8）表示布尔类型，1 为真 0 为假。

数值类型

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

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）Float

与整数类似，ClickHouse 直接使用 Float32 和 Float64 代表单精度浮点数和双精度浮点数，可以看成是 float 和 double。

ClickHouse 的浮点数支持正无穷、负无穷以及非数字的表达方式。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
satori :) select 1 / 0, -1 / 0, 0 / 0

SELECT
    1 / 0,
    -1 / 0,
    0 / 0

Query id: e3b3712c-0506-4b3b-b2d8-7f936c548740

┌─divide(1, 0)─┬─divide(-1, 0)─┬─divide(0, 0)─┐
│          inf │          -inf │          nan │
└──────────────┴───────────────┴──────────────┘

1 rows in set. Elapsed: 0.002 sec.

satori :)

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 取最大值。

1
2
3
4
5
6
7
8
9
10
11
12
13
satori :) select toDecimal32(22, 3) + toDecimal32(33, 2)

SELECT toDecimal32(22, 3) + toDecimal32(33, 2)

Query id: a223565d-e6ba-4db9-aa7d-1cae37424128

┌─plus(toDecimal32(22, 3), toDecimal32(33, 2))─┐
│                                       55.000 │
└──────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

satori :)

在进行乘法运算时，S 取两者之和：

1
2
3
4
5
6
7
8
9
10
11
12
13
satori :) select toDecimal32(22, 3) * toDecimal32(33, 2)

SELECT toDecimal32(22, 3) * toDecimal32(33, 2)

Query id: 0f1bd695-9f37-43b4-a6d1-b2a62a1dd6c3

┌─multiply(toDecimal32(22, 3), toDecimal32(33, 2))─┐
│                                        726.00000 │
└──────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

satori :)

在进行除法运算时，S 取被除数的值，此时要求被除数的 S 必须大于除数 S，否则报错。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
satori :) select toDecimal64(6, 3) /  toDecimal64(3, 2)

SELECT toDecimal64(6, 3) / toDecimal64(3, 2)

Query id: 55b4a58f-b722-4487-8391-2c08e6c764ca

┌─divide(toDecimal64(6, 3), toDecimal64(3, 2))─┐
│                                        2.000 │
└──────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

satori :) select toDecimal64(6, 3) /  toDecimal64(3, 4)  -- 这里会报错，因为被除数的 S 小于除数的 S

SELECT toDecimal64(6, 3) / toDecimal64(3, 4)

Query id: 6f782411-0d15-4ab8-adab-be19c9f6b0e2


0 rows in set. Elapsed: 0.003 sec.

Received exception from server (version 21.7.3):
Code: 69. DB::Exception: Received from localhost:9000. DB::Exception:
Decimal result's scale is less than argument's one: While processing toDecimal64(6, 3) / toDecimal64(3, 4).

satori :)

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

字符串类型

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

1）String

字符串由 String 定义，长度不限，因为在使用 String 的时候无需声明大小。它完全代替了传统意义上的 Varchar、Text、Clob 和 Blob 等字符类型。String 类型不限定字符集，因为它根本没有这个概念，所以可以将任意编码的字符串存入其中。但是为了程序的规范性和可维护性，在同一套程序中使用统一的编码，比如 utf-8，就是一种很好的约定。

2）FiexedString

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

可以使用 toFixedString 生成 FixedString。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
satori :) select toFixedString('satori', 7), length(toFixedString('satori', 7))

SELECT
    toFixedString('satori', 7),
    length(toFixedString('satori', 7))

Query id: 45fe6e26-0540-40de-9eaf-aeefd12a3a1b

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

1 rows in set. Elapsed: 0.002 sec.

satori :)

3）UUID

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

时间类型

时间类型分为 DateTime、DateTime64 和 Date三类。

1）DateTime

DateTime 类型包含年、月、日、时、分、秒信息，精确到秒，支持使用字符串的方式写入；

2）DateTime64

DateTime64 可以记录亚秒，它在 DateTime 之上增加了精度的设置。举个栗子：DateTime64 类型的时间可以是 2018-01-01 12:12:32.22；但如果是 DateTime 的话，则是2018-01-01 12:12:32，也就是说最后的 .22 没了。

3）Date

Date 类型不包含具体的时间信息，只精确到天，并且和 DateTime、DateTime64 一样，支持字符串写入。

复合类型

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

Array

数据有两种定义形式，常规方式 Array(T)，比如某个字段是包含 UInt8 的数组，那么就可以声明为 Array(UInt8)；需要说明的是，ClickHouse 中的类型是区分大小写的，比如这里的 Array 就不可以写成 array，UInt8 不可以写成 uint8。

当然在查询的时候，我们可以通过 array 函数创建一个数组。注意：ClickHouse 中的绝大部分函数也是区分大小写的，只要是你在其它关系型数据库中没有见过的函数，基本上都区分大小写。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
satori :) select array(1, 2) as a, toTypeName(a)  -- toTypeName 表示获取字段的类型

SELECT
    [1, 2] AS a,
    toTypeName(a)

Query id: b22e371f-d5fc-4245-b299-a79a344fc7ea

┌─a─────┬─toTypeName(array(1, 2))─┐
│ [1,2] │ Array(UInt8)            │
└───────┴─────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

satori :)

这里只是演示，关于具体的语法后面说，

在查询的时候简写成 [v1, v2, v3, …] 也是可以的；

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
satori :) select [1, 2] as a, toTypeName(a)

SELECT
    [1, 2] AS a,
    toTypeName(a)

Query id: 50d4e367-cd1b-4826-bca8-eb913e6fbaeb

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

1 rows in set. Elapsed: 0.002 sec.

satori :)

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
satori :) select [1, 2, NULL] as a, toTypeName(a)

SELECT
    [1, 2, NULL] AS a,
    toTypeName(a)

Query id: 95eb5bfa-5008-4bd0-ac94-bd2e39de6485

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

1 rows in set. Elapsed: 0.002 sec.

satori :)

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

1
2
3
CREATE TABLE table_name (
	arr Array(String)  --指定明确类型
) ENGINE = Memory

Tuple

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
satori :) SELECT tuple(1, 'a', now()) as a, (1, 3, '666') as b

SELECT
    (1, 'a', now()) AS a,
    (1, 3, '666') AS b

Query id: ab97ea39-accd-4eaa-85b0-c1728fde57e4

┌─a─────────────────────────────┬─b───────────┐
│ (1,'a','2021-08-05 01:10:07') │ (1,3,'666') │
└───────────────────────────────┴─────────────┘

1 rows in set. Elapsed: 0.002 sec.

satori :)

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

1
2
3
4
5
6
CREATE TABLE table_name (
    -- 可以指定多个类型
    -- 但是注意：Tuple(String, Int8) 表示 tpl 字段的值只能是含有两个元素的元组
    -- 并且第一个元素为 String，第二个元素为 Int8
    tpl Tuple(String, Int8)
) ENGINE = Memory

而在数据写入的过程中会进行类型检查。例如，写入 ('abc', 123) 是可行的，但是 ('abc', 'def') 则报错。

Enum

ClickHouse 支持枚举类型，这是一种在定义常量时经常会使用的数据类型。ClickHouse 提供了 Enum8 和 Enum16 两种枚举类型，它们之间除了取值范围不同之外，别无二致。枚举固定使用 (String:Int) 键值对的形式定义数据，所以 Enum8 和 Enum16 分别会对应 (String:Int8) 和 (String:Int16)，例如：

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

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

1
INSERT INTO table_name VALUES('ready')

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

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

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

Nested

嵌套类型，顾名思义是一种嵌套表结构。一张数据表，可以定义任意多个嵌套类型字段，但每个字段的嵌套层级只支持一级，即嵌套表内不能继续使用嵌套类型。对于简单场景的层级关系或关联关系，使用嵌套类型也是一种不错的选择。例如，我们下面创建一张表 nested_test，具体的建表逻辑后面会说，当然本身也不是特别难的东西。

1
2
3
4
5
6
7
8
CREATE TABLE nested_test (
    name String,
    age UInt8,
    dept Nested(
        id UInt32,
        name String
    )
) ENGINE = Memory;

ClickHouse 的嵌套类型和传统的嵌套类型不相同，导致在初次接触它的时候会让人十分困惑。以上面这张表为例，如果按照它的字面意思来理解，会很容易理解成 nested_test 与 dept 是一对一的包含关系，其实这是错误的。不信可以执行下面的语句，看看会是什么结果:

img

我们看到报错了，现在大家应该明白了，嵌套类型本质是一种多维数组的结构。嵌套表中的每个字段都是一个数组，并且行与行之间数组的长度无须对齐。所以需要把刚才的 INSERT 语句调整成下面的形式:

1
2
3
INSERT INTO nested_test VALUES('nana', 20, [10000, 10001, 10002], ['唐辛子', 'ななかぐら', 'ゴウマ']);
-- 行与行之间，数组长度无需对齐。
INSERT INTO nested_test VALUES('nana', 20, [10000, 10001], ['唐辛子', 'ななかぐら']);

img

需要注意的是，在同一行数据内每个数组字段的长度必须相等。例如，在下面的示例中，由于行内数组字段的长度没有对齐，所以会抛出异常：

img

提示我们长度不一样。

在访问嵌套类型的数据时需要使用点符号，例如:

img

特殊类型

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

Nullable

准确来说，Nullable 并不能算是一种独立的数据类型，它更像是一种辅助的修饰符，需要与基础数据类型一起搭配使用。Nullable 类型与 Python 类型注解里面的 Optional 有些相似，它表示某个基础数据类型可以是 NULL 值。其具体用法如下所示:

1
2
3
4
CREATE TABLE null_test (
    col1 String,
    col2 Nullable(UInt8)
) ENGINE = Memory

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

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

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

Domain

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

1
2
3
4
5
6
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数据是无法被写入的，例如：

1
2
3
4
5
6
7
INSERT INTO ip4_test VALUES('www,nana.com', '192.0.0')

Exception on client:
Code: 441. DB::Exception: Invalid IPv4 value.: data for INSERT was parsed from query

Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 21.7.3 revision 54449.

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

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