简述ClickHouse数据类型

2020-07-12 10:54:10

​ ClickHouse作为一个DBMS，提供了DDL和DML的功能，并支持部分标准的SQL语法。但ClickHouse在基础数据方面，既提供了常规的数据类型、字符串类型格式，又提供了一些常用的复合类型(数组、元组等)，并且与常规的数据库，在DML的使用上也存在不同(例如：UPDATE和DELETE是借助ALTER实现的)。本文将简单介绍ClickHouse所提供的各种数据类型。

一、ClickHouse的数据类型

​ ClickHouse提供了许多数据类型，可以分为基础类型、符合类型和特殊类型，可进入clickhouse数据库中，通过select * from system.data_type_families查询所支持的所有数据类型。

二、基础数据类型

​ ClickHouse基础数据类型可分为数值、字符串和时间三种类型，没有Boolean类型，可用整型的0和1来替代。

1、数值型

数值型可分为Int、Float和Decimal三种类型

1.1 Int

​ 在MySQL中，使用Tinyint、smallint、int和bigint来指代整数类型的取值。ClickHouse中使用了Int8、Int16、Int32、Int64来分别指代不同大小的Int类型(末尾数字表明占用字节大小，1字节=8位)，具体对比关系如下：

名称 字节大小 范围 MySQL
Int8 1 -128到127 Tinyint
Int16 2 -32768到32767 Smallint
Int32 4 -2147483648到214783647 Int
Int64 8 -9223372036854775808到9223372036854775807 Bigint
UInt8 1 0到255 Tinyint Unsinged
UInt16 2 0到65535 Smallint Unsigned
UInt32 4 0到4294967295 Int Unsigned
UInt64 8 0到18446744073709551615 Bigint Unsigned

1.2 Float

​ ClickHouse使用了Float32和Float64来代表单精度浮点数一级双精度浮点数，具体对比关系如下，使用浮点数会引起四舍五入的误差：

名称 大小(字节) 有效精度(位数) C语言类型
Float32 4 7 float
Float64 8 16 Double 
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
-- 1. 转换为Float32类型
clickhouse-server_1 :) select toFloat32('0.123456789098765432') as a, toTypeName(a);

SELECT 
    toFloat32('0.123456789098765432') AS a, 
    toTypeName(a)

┌──────────a─┬─toTypeName(toFloat32('0.123456789098765432'))─┐
│ 0.12345679 │ Float32                                       │
└────────────┴───────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec. 


-- 2. 转换为Float64类型
clickhouse-server_1 :) select toFloat64('0.123456789098765432') as a, toTypeName(a);

SELECT 
    toFloat64('0.123456789098765432') AS a, 
    toTypeName(a)

┌───────────────────a─┬─toTypeName(toFloat64('0.123456789098765432'))─┐
│ 0.12345678909876544 │ Float64                                       │
└─────────────────────┴───────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

​ 和标准的SQL相比，ClickHouse还支持了以下类别的浮点数：

  • Inf：正无穷

    1
    2
    3
    4
    5
    6
    7
    8
    9
    clickhouse-server_1 :) select 0.5/0

    SELECT 0.5 / 0

    ┌─divide(0.5, 0)─┐
    │            inf │
    └────────────────┘

    1 rows in set. Elapsed: 0.007 sec.

  • -Inf：负无穷

    1
    2
    3
    4
    5
    6
    7
    8
    9
    clickhouse-server_1 :) select -0.5 / 0

    SELECT -0.5 / 0

    ┌─divide(-0.5, 0)─┐
    │            -inf │
    └─────────────────┘

    1 rows in set. Elapsed: 0.001 sec.

  • NaN：非数字

    1
    2
    3
    4
    5
    6
    7
    8
    9
    clickhouse-server_1 :) select 0 / 0

    SELECT 0 / 0

    ┌─divide(0, 0)─┐
    │          nan │
    └──────────────┘

    1 rows in set. Elapsed: 0.002 sec.

1.3 Decimal

​ 在高精度的数值运算中，会使用Decimal类型。ClickHouse提供了Decimal32、Decimal64和Decimal128三种精度的定点数，可通过Decimal(P, S)或简写Decimal32(S), Decimal64(S), Decimal128(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 = max(S1, S2)
  • 减法：S = max(S1, S2)
  • 乘法：S = S1 + S2
  • 除法：S = S1 (S1 为被除数，且被除数S1必须大于除数S2，即S1 / S2)
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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- 1. 加法示例
clickhouse-server_1 :) select toDecimal32(2,4) + toDecimal64(2,2)

SELECT toDecimal32(2, 4) + toDecimal64(2, 2)

┌─plus(toDecimal32(2, 4), toDecimal64(2, 2))─┐
│                                     4.0000 │
└────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.006 sec. 


-- 2. 减法示例
clickhouse-server_1 :) select toDecimal32(4, 6) - toDecimal64(2, 2)

SELECT toDecimal32(4, 6) - toDecimal64(2, 2)

┌─minus(toDecimal32(4, 6), toDecimal64(2, 2))─┐
│                                    2.000000 │
└─────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec. 


-- 3. 乘法示例
clickhouse-server_1 :) select toDecimal64(4, 4) * toDecimal32(2, 2)

SELECT toDecimal64(4, 4) * toDecimal32(2, 2)

┌─multiply(toDecimal64(4, 4), toDecimal32(2, 2))─┐
│                                       8.000000 │
└────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec. 


-- 4. 除法示例
clickhouse-server_1 :) select toDecimal64(4, 4) / toDecimal32(2, 2)

SELECT toDecimal64(4, 4) / toDecimal32(2, 2)

┌─divide(toDecimal64(4, 4), toDecimal32(2, 2))─┐
│                                       2.0000 │
└──────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

溢出检查：

对Decimal进行操作时，数值可能会发生溢出。小数部分过多数字会被丢弃(不是四舍五入)，整数数字过多会导致异常。可通过设置decimal_check_overflow来关闭溢出检查，但溢出会导致结果不正确，而且会让计算变慢。在比较计算上也会发生溢出。

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
-- 1. 数值溢出，x/3的结果本应为无限循环小数，只保留了S位，其余小数被丢弃
clickhouse-server_1 :) SELECT toDecimal32(2, 4) AS x, x / 3

SELECT 
    toDecimal32(2, 4) AS x, 
    x / 3

┌──────x─┬─divide(toDecimal32(2, 4), 3)─┐
│ 2.0000 │                       0.6666 │
└────────┴──────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec. 


-- 2. 整数部分溢出导致报错
clickhouse-server_1 :) SELECT toDecimal32(4.2, 8) AS x, x * x

SELECT 
    toDecimal32(4.2, 8) AS x, 
    x * x

Received exception from server (version 20.3.4):
Code: 69. DB::Exception: Received from localhost:9000. DB::Exception: Scale 16 is out of bounds. 

0 rows in set. Elapsed: 0.005 sec. 

clickhouse-server_1 :) SELECT toDecimal32(4.2, 8) AS x, 6 * x

SELECT 
    toDecimal32(4.2, 8) AS x, 
    6 * x

Received exception from server (version 20.3.4):
Code: 407. DB::Exception: Received from localhost:9000. DB::Exception: Decimal math overflow. 

0 rows in set. Elapsed: 0.002 sec.

-- 3. 关闭溢出检查
clickhouse-server_1 :) SET decimal_check_overflow = 0;

SET decimal_check_overflow = 0

Ok.

0 rows in set. Elapsed: 0.001 sec. 

clickhouse-server_1 :) SELECT toDecimal32(4.2, 8) AS x, 6 * x

SELECT 
    toDecimal32(4.2, 8) AS x, 
    6 * x

┌──────────x─┬─multiply(6, toDecimal32(4.2, 8))─┐
│ 4.20000000 │                     -17.74967296 │
└────────────┴──────────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec. 


-- 4. 比较，溢出提示Can't compare
clickhouse-server_1 :) SELECT toDecimal32(1, 8) < 100

SELECT toDecimal32(1, 8) < 100

Received exception from server (version 20.3.4):
Code: 407. DB::Exception: Received from localhost:9000. DB::Exception: Can't compare. 

0 rows in set. Elapsed: 0.010 sec.

2、字符串类型

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

2.1 String

字符串由String类型定义，无长度限制。在使用时也不需要声明大小，代替了其他数据库上的Varchar、Text、Blob等自负类型。而且String类型不限制字符集限制，可以写入任意编码的字符串，但为了标准统一化开发管理，官方建议一套程序中应使用统一的编码类型。

2.2 FixedString

FixedString和传统的char类型类似，用来定义一些固定长度的字符串。通过FixedString(N)来定义声明，N表示字符串长度，当数据长度小于N字节时，会降低一定效率。但Char类型通常使用空格填充末尾字符，而FixedString用null字节来填充。

  • 当向ClickHouse中插入数据时
    • 如果字符串包含字节数少于N，将用null字节来填充
    • 如果字符串包含字节数大于N，将抛出Too large value for FixedString(N)的异常
  • 当查询数据时
    • ClickHouse不会删除字符串末尾的空字节，如果使用WHERE子句，则需要手动添加空字节来匹配FixedString的值
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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
-- 1. 创建一个用来测试的表，并插入测试数据，若没有test库则需要create database test来进行创建
clickhouse-server_1 :) use test

clickhouse-server_1 :) create table FixedStringTest ( a FixedString(2) ) engine=MergeTree order by a


-- 2. 插入正常数据
clickhouse-server_1 :) insert into FixedStringTest values ('a')


-- 3. 插入大于N数据
clickhouse-server_1 :) insert into FixedStringTest values ('abc')


-- 4. 查询数据
-- where查询时并不会自动删除后面的null，所以无法得到想要的结果
clickhouse-server_1 :) select * from FixedStringTest where a = 'a'

SELECT *
FROM FixedStringTest
WHERE a = 'a'

Ok.

0 rows in set. Elapsed: 0.002 sec. 

-- 查询可看到，长度为2
clickhouse-server_1 :) select a, length(a) from FixedStringTest

SELECT 
    a, 
    length(a)
FROM FixedStringTest

┌─a─┬─length(a)─┐
│ a │         2 │
└───┴───────────┘

1 rows in set. Elapsed: 0.002 sec. 

-- 需手动填充空字节用于where匹配
clickhouse-server_1 :) select * from FixedStringTest where a = 'a\0'

SELECT *
FROM FixedStringTest
WHERE a = 'a\0'

┌─a─┐
│ a │
└───┘

1 rows in set. Elapsed: 0.002 sec.

2.3 UUID

UUID经常会在数据库中使用，有时候还会作为主键，ClickHouse直接将UUID作为了一种数据类型。UUID一共有32位，格式为8-4-4-4-12,如果UUID类型的字段在写入数据时没有被赋值，则会按照格式用0来填充。ClickHouse还提供了generateUUIDv4来生成随机的UUID。

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
27
28
29
30
31
32
33
-- 1. 创建测试表
CREATE TABLE t_uuid (x UUID, y String) ENGINE=TinyLog

-- 2. 插入UUID
INSERT INTO t_uuid SELECT generateUUIDv4(), 'Example 1'

-- 3. 查询
clickhouse-server_1 :) select * from t_uuid

SELECT *
FROM t_uuid

┌────────────────────────────────────x─┬─y─────────┐
│ a603876a-38cb-460a-bafb-a08d4ab1b9c4 │ Example 1 │
└──────────────────────────────────────┴───────────┘

1 rows in set. Elapsed: 0.002 sec. 

-- 4. 不指定UUID插入数据
INSERT INTO t_uuid (y) VALUES ('Example 2')

-- 查询结果
clickhouse-server_1 :) select * from t_uuid

SELECT *
FROM t_uuid

┌────────────────────────────────────x─┬─y─────────┐
│ a603876a-38cb-460a-bafb-a08d4ab1b9c4 │ Example 1 │
│ 00000000-0000-0000-0000-000000000000 │ Example 2 │
└──────────────────────────────────────┴───────────┘

2 rows in set. Elapsed: 0.002 sec.

3、时间类型

时间类型分为DateTime、DateTime64和Date三种。ClickHouse目前不存在时间戳类型，时间类型的最高精度到秒，所以若需要毫秒、微秒等时间，只能用UInt类型实现

3.1 DateTime

DateTime类型精确到秒，可表示的时间范围为['1970-01-01 00:00:00', '2105-12-31 23:59:59']，创建表时，可以为DateTime的列显示设置失去，如果未为表设置失去，启动ClickHouse服务时，它将使用系统设置中的timezone参数，也可以使用--use_client_time_zone参数来启动clickhouse-client，默认格式为YYYY-MM-DD hh:mm:ss输出至，也可以使用formatDateTime函数来更改输出，插入数据时格式则取决于date_time_input_format的设置。

1. 创建设置了DateTime类型列的表并插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 创建表
CREATE TABLE dt ( `timestamp` DateTime('Asia/Shanghai'), `event_id` UInt8 ) ENGINE = TinyLog;

-- 插入数据
INSERT INTO dt Values (1546300800, 1), ('2020-07-12 00:00:00', 2);

-- 查询数据
clickhouse-server_1 :) select * from dt;

SELECT *
FROM dt

┌───────────timestamp─┬─event_id─┐
│ 2019-01-01 08:00:00 │        1 │
│ 2020-07-12 00:00:00 │        2 │
└─────────────────────┴──────────┘

2 rows in set. Elapsed: 0.002 sec.
  • 将datetime插入为整数时，它会被视为Unix时间戳(UTC)，1546300800表示’2019-01-01 00:00:00’UTC，但由于timestamp列已指定了时区Asia/Shanghai，所以会显示为东八区时间2019-01-01 08:00:00

2. 指定时区删选DateTime值

1
2
3
4
5
6
7
8
9
10
11
SELECT * FROM dt WHERE timestamp = toDateTime('2020-07-12 00:00:00', 'Asia/Shanghai')
-- 结果为
┌───────────timestamp─┬─event_id─┐
│ 2020-07-12 00:00:00 │        2 │
└─────────────────────┴──────────┘

-- 也可通过where字符串值过滤
SELECT * FROM dt WHERE timestamp = '2020-07-12 00:00:00'
┌───────────timestamp─┬─event_id─┐
│ 2020-07-12 00:00:00 │        2 │
└─────────────────────┴──────────┘

3. 获取DateTime列和时区类型

1
2
3
4
5
SELECT toDateTime(now(), 'Asia/Shanghai') AS column, toTypeName(column) AS x
-- 结果
┌──────────────column─┬─x─────────────────────────┐
│ 2020-07-12 12:41:28 │ DateTime('Asia/Shanghai') │
└─────────────────────┴───────────────────────────┘

4. 转换列时区

1
2
3
4
5
6
SELECT toDateTime(timestamp, 'Europe/London') as lon_time, toDateTime(timestamp, 'Europe/Moscow') as mos_time FROM dt
-- 结果
┌────────────lon_time─┬────────────mos_time─┐
│ 2019-01-01 00:00:00 │ 2019-01-01 03:00:00 │
│ 2020-07-11 17:00:00 │ 2020-07-11 19:00:00 │
└─────────────────────┴─────────────────────┘

3.2 DateTime64

DateTime64在DateTime类型基础上增加了精度设置，可以记录到亚秒，其余使用方法和DateTime相同。

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 创建测试表
CREATE TABLE dt64 (`timestamp` DateTime64(3, 'Asia/Shanghai'), `event_id` UInt8 ) ENGINE = TinyLog

-- 插入测试数据
INSERT INTO dt64 Values (1546300800000, 1), ('2019-01-01 00:00:00', 2)

-- 查询结果
select * from dt64
-- 结果为
┌───────────────timestamp─┬─event_id─┐
│ 2019-01-01 08:00:00.000 │        1 │
│ 2019-01-01 00:00:00.000 │        2 │
└─────────────────────────┴──────────┘

3.3 Date

Date类型不包含具体的时间，只精确到天，存储的日期值不带时区，同样支持字符串的形式写入。

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 创建测试表
create table date_test(`c1` Date, `event_id` UInt8 ) Engine = TinyLog

-- 插入数据，即使插入包含具体时间的值，也只会写入日期
insert into date_test values ('2019-01-01 00:00:00', 1), ('2020-07-12', 2)

-- 查询
select * from date_test
-- 结果为
┌─────────c1─┬─event_id─┐
│ 2019-01-01 │        1 │
│ 2020-07-12 │        2 │
└────────────┴──────────┘

三、复合类型

除了数值、字符串和时间这些基础数据类型之外，ClickHouse还提供了数组、元组、枚举和嵌套四种复合类型。

1、数组Array

数组有两种定义形式：array(T)和[]

1
2
3
4
5
6
7
8
9
10
11
select array(1, 2) as a, toTypeName(a)
-- 结果
┌─a─────┬─toTypeName([1, 2])─┐
│ [1,2] │ Array(UInt8)       │
└───────┴────────────────────┘

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

可以看出，在查询时并不需要主动声明数组的元素类型，这是因为ClickHouse的数组拥有类型推断的能力。但在快速创建数组时，ClickHouse会自动将参数定义为最小的可表达的数据类型。例如若数组中存在NULL值，数组元素类型会变为Nullable。但在进行表定义时，数组需要指定明确的元素类型

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT array(1, 2, NULL) AS x, toTypeName(x)
-- 结果
┌─x──────────┬─toTypeName([1, 2, NULL])─┐
│ [1,2,NULL] │ Array(Nullable(UInt8))   │
└────────────┴──────────────────────────┘

-- 建表时需要指定类型
create table array_test(c1 Array(String)) engine = TinyLog     ## 可创建成功，数据写入过程中会进行类型检查

create table array_test(c1 Array) engine = TinyLog     ## 会创建失败
-- 结果
Received exception from server (version 20.3.4):
Code: 42. DB::Exception: Received from localhost:9000. DB::Exception: Array data type family must have exactly one argument - type of elements.

当一个数组内包含了多种数据类型，则ClickHouse会引发异常。

1
2
3
4
select array(1, 'a')
-- 结果
Received exception from server (version 20.3.4):
Code: 386. DB::Exception: Received from localhost:9000. DB::Exception: There is no supertype for types UInt8, String because some of them are String/FixedString and some of them are not.

2、元组Tuple

元组类型有1～n个元素组成，每个元素都是一个单独的类型。与数组类似，元组也可用两种方式定义：tuple(T)或(t1, t2, …)

1
2
3
4
5
6
7
8
9
10
SELECT tuple(1,'a') AS x, toTypeName(x)
-- 结果
┌─x───────┬─toTypeName(tuple(1, 'a'))─┐
│ (1,'a') │ Tuple(UInt8, String)      │
└─────────┴───────────────────────────┘

SELECT tuple(1, NULL) AS x, toTypeName(x)
┌─x────────┬─toTypeName(tuple(1, NULL))──────┐
│ (1,NULL) │ Tuple(UInt8, Nullable(Nothing)) │
└──────────┴─────────────────────────────────┘

定义表字段时，同样需要指定明确的元素类型

1
2
3
4
5
6
7
8
create table tuple_test ( c1 Tuple(String, Int8)) Engine = TinyLog
-- 插入数据时也会进行类型检查
insert into tuple_test values(('abc', 123))    ## 可插入成功

insert into tuple_test values(('abc', 'def'))	## 会提示类型报错
-- 结果
Exception on client:
Code: 6. DB::Exception: Cannot parse string 'def' as Int8: syntax error at begin of string. Note: there are toInt8OrZero and toInt8OrNull functions, which returns zero/NULL instead of throwing exception.

3、枚举Enum

ClickHouse提供了Enum8和Enum16两种枚举类型，ClickHouse可自动选择Enum插入数据的类型，也可以手动指定。他们仅取值范围不同：

  • Enum8包含[-128, 127]范围内列举的256个值-
  • Enum16最多包含[-32768, 32767]范围内列举的65536个值

命名值必须声明为'string: Int'的Key/Value键值对形式，所以Enum8和Enum16分别为(String: Int8)和(String: Int16).

在使用枚举类型时，有以下限制：

  • Key和Value不允许重复，需保证唯一性
  • Key和Value值都不能为Null，但Key允许是空字符串
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
27
28
29
30
31
32
33
34
-- 创建测试表
CREATE TABLE t_enum (x Enum('hello' = 1, 'world' = 2)) ENGINE = TinyLog

-- 插入数据
INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello')
-- 插入成功

-- 若插入enum之外的key，则会抛出异常
INSERT INTO t_enum values('a')
-- 结果：
Exception on client:
Code: 36. DB::Exception: Unknown element 'a' for type Enum8('hello' = 1, 'world' = 2)

-- 查询数据，会输出字符串类型的Key值
SELECT * FROM t_enum
┌─x─────┐
│ hello │
│ world │
│ hello │
└───────┘

-- 若需要查看对应的value值，则需要将Enum值转换为整数类型，CAST(x, T)则将x类型转换为T类型
SELECT CAST(x, 'Int8') FROM t_enum
┌─CAST(x, 'Int8')─┐
│               1 │
│               2 │
│               1 │
└─────────────────┘

-- 若在select中直接创建Enum类型，仍然需要CAST函数转换
SELECT toTypeName(CAST('hello', 'Enum(\'hello\' = 1, \'world\' = 2)'))
┌─toTypeName(CAST('hello', 'Enum(\'hello\' = 1, \'world\' = 2)'))─┐
│ Enum8('hello' = 1, 'world' = 2)                                 │
└─────────────────────────────────────────────────────────────────┘

4、嵌套Nested

一个数据表可以定义任意多个嵌套类型字段，但每个字段只能有一层潜逃，即嵌套字段内不能继续使用嵌套类型。ClickHouse的嵌套类型本质是一个多维数组的结构，即嵌套表中的每个字段都是一个数组。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 创建测试表
create table nested_test(name String, age UInt8, dept Nested(id UInt8, name String)) Engine = TinyLog

-- 插入数据
insert into nested_test values ('alice', 18, [10000, 10001], ['aaa', 'bbb'])

-- 插入数据时，同一行数据中嵌套类型的数组字段长度必须相同，否则会抛出以下异常
insert into nested_test values ('bob', 22, [10000], ['aaa', 'bbb'])
Received exception from server (version 20.3.4):
Code: 190. DB::Exception: Received from localhost:9000. DB::Exception: Elements 'dept.id' and 'dept.name' of Nested data structure 'dept' (Array columns) have different array sizes.. 

-- 查询数据
select name, dept.id, dept.name from nested_test;
┌─name──┬─dept.id─┬─dept.name─────┐
│ alice │ [16,17] │ ['aaa','bbb'] │
└───────┴─────────┴───────────────┘

四、其他类型

​ ClickHouse除了上述类型之外，还定义了一些其他字段类型作为对基础数据的进一步修饰和封装。

1、Nullable

​ Nullable并不能算一种独立的数据类型，它需要和基础数据类型配合使用，并不能用于数组和元组这些复合类型，通过Nullable修饰之后，该基础类型字段可以输入Null值。

​ 使用了Nullable修饰后，该字段不能作为索引字段，并且会使查询和写入性能变慢。这是由于一般情况下每个字段的数据会存储在对应的column.bin文件中，如果该字段被Nullable修饰，会额外生成column.null.bin文件专门保存null值，这表示读取和写入，需要一倍的额外文件操作。故官方并不建议经常使用Nullable类型

1
2
3
4
5
6
7
8
9
10
11
12
-- 创建测试表
CREATE TABLE t_null(x Int8, y Nullable(Int8)) ENGINE TinyLog

-- 插入数据
INSERT INTO t_null VALUES (1, NULL), (2, 3)

-- 查询
select * from t_null
┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
│ 2 │    3 │
└───┴──────┘

2、Domain

Domain类型分为IPv4和IPv6两种，本质是对整型和字符串型的进一步封装，虽然Domain类型看起来和String类型相同，但它并不是字符串类型，不支持隐式的自动类型转换，如果需要返回IP的字符串形式，需要调用IPv4NumToString或IPv6NumToString函数进行转换。IPv4类型是基于UInt32封装，IPv6基于FixedString(16)封装。他们使用方式相同，以下用IPv4作为示例。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 创建测试表
CREATE TABLE hits (url String, from IPv4) ENGINE = MergeTree() ORDER BY url;

-- 插入数据
insert into hits values('www.zzdb.com', '1.1.1.1'),('test.db.com', '2.2.2.2')

-- Domain格式会进行数据类型检查
insert into hits values ('www.test.com', '192.168.1')
Exception on client:
Code: 441. DB::Exception: Invalid IPv4 value.

-- 查询数据
select * from hits
┌─url──────────┬────from─┐
│ test.db.com  │ 2.2.2.2 │
│ www.zzdb.com │ 1.1.1.1 │
└──────────────┴─────────┘

-- 转换为字符串类型
SELECT toTypeName(s), IPv4NumToString(from) as s FROM hits LIMIT 1;
┌─toTypeName(IPv4NumToString(from))─┬─s───────┐
│ String                            │ 2.2.2.2 │
└───────────────────────────────────┴─────────┘
  1. 一、ClickHouse的数据类型
  2. 二、基础数据类型
    1. 1、数值型
      1. 1.1 Int
      2. 1.2 Float
      3. 1.3 Decimal
    2. 2、字符串类型
      1. 2.1 String
      2. 2.2 FixedString
      3. 2.3 UUID
    3. 3、时间类型
      1. 3.1 DateTime
      2. 3.2 DateTime64
      3. 3.3 Date
  3. 三、复合类型
    1. 1、数组Array
    2. 2、元组Tuple
    3. 3、枚举Enum
    4. 4、嵌套Nested
  4. 四、其他类型
    1. 1、Nullable
    2. 2、Domain